[SQL] 카멜케이스 변환

where절에 TABLE_NAME값만 카멜케이스로 뽑아오고 싶은 테이블 넣어주면된다

 

ORACLE

create or replace FUNCTION FN_GETCAMEL(COLUMN_NAME IN VARCHAR2)
RETURN VARCHAR2
IS
RSLT VARCHAR2(30);
BEGIN
--카멜표기로 변환(SITE_NUM -> siteNum)
SELECT LOWER(SUBSTR(REPLACE(INITCAP(COLUMN_NAME),'_'),1,1))
|| SUBSTR(REPLACE(INITCAP(COLUMN_NAME),'_'),2) INTO RSLT
FROM DUAL;
--리턴
RETURN RSLT;
END;
/

--구글 카멜변환(https://heavenly-appear.tistory.com/270)
SELECT COLUMN_NAME
, DATA_TYPE
, CASE WHEN DATA_TYPE='NUMBER' THEN 'private int ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE IN('VARCHAR2','CHAR') THEN 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
WHEN DATA_TYPE='DATE' THEN 'private Date ' || FN_GETCAMEL(COLUMN_NAME) || ';'
ELSE 'private String ' || FN_GETCAMEL(COLUMN_NAME) || ';'
END AS CAMEL_CASE
, '<result property="'||FN_GETCAMEL(COLUMN_NAME)||'" column="'||COLUMN_NAME||'" />' RESULTMAP
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'ATTACH';

 

MS_SQL

DROP FUNCTION dbo.FN_GETCAMEL;

CREATE FUNCTION dbo.FN_GETCAMEL(@COLUMN_NAME NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX);

    -- 언더스코어를 기준으로 문자열 분할
    DECLARE @Parts TABLE (Part NVARCHAR(MAX));
    INSERT INTO @Parts (Part)
    SELECT value FROM STRING_SPLIT(@COLUMN_NAME, '_');

    -- 카멜 표기로 변환
    SET @Result = '';
    DECLARE @First BIT = 1;
    SELECT @Result = @Result + CASE WHEN @First = 1 THEN LOWER(Part) ELSE UPPER(LEFT(Part, 1)) + LOWER(SUBSTRING(Part, 2, LEN(Part))) END,
           @First = 0
    FROM @Parts;

    -- 결과 반환
    RETURN @Result;
END;


SELECT COLUMN_NAME,
       DATA_TYPE,
       CASE 
           WHEN DATA_TYPE = 'NUMBER' THEN 'private int ' + dbo.FN_GETCAMEL(COLUMN_NAME) + ';'
           WHEN DATA_TYPE IN ('VARCHAR2', 'CHAR') THEN 'private String ' + dbo.FN_GETCAMEL(COLUMN_NAME) + ';'
           WHEN DATA_TYPE = 'DATE' THEN 'private Date ' + dbo.FN_GETCAMEL(COLUMN_NAME) + ';'
           ELSE 'private String ' + dbo.FN_GETCAMEL(COLUMN_NAME) + ';'
       END AS CAMEL_CASE,
       '<result property="' + dbo.FN_GETCAMEL(COLUMN_NAME) + '" column="' + COLUMN_NAME + '" />' AS RESULTMAP
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'paid_locker';

 

함수삭제

DROP FUNCTION [테이블이름]