MySQL 프로시저 파라미터 구문 생성

MySQL에서 파라미터를 만들 때 사용하는 쿼리이다.

카멜 표기를 하는 예제인데 카멜 표기가 완벽히 되지는 않지만 시간을 절약할 수 있다.

SELECT CONCAT(',IN pi_'
                                    , CASE WHEN DATA_TYPE IN ('varchar','char','text','longtext','datetime') THEN
                                        'str'
                                      ELSE
                                        'int'
                                      END
                                    , REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
          REPLACE(REPLACE(
                                            PASCAL_CASE
                                            , 'type','Type'), 'secret','Secret'), 'idx','Idx'), 'memo','Memo'), 'raw','Raw')
                                            , 'img','Img'), 'date','Date'), 'count','Count'), 'good','Good'), 'order','Order')
         , 'info','Info'), 'board','Board')
                                    , ' '
                                    ,CASE WHEN DATA_TYPE IN ('varchar','char','text','longtext','datetime') THEN 
                                        REPLACE(UPPER(COLUMN_TYPE), ' UNSIGNED','')
                                     ELSE
                                        UPPER(DATA_TYPE)
                                     END, ' -- ') AS IN_PARAMS
FROM (
              SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, REPLACE(REPLACE(CONCAT( UPPER(SUBSTRING(COLUMN_NAME,1,1)),LOWER(SUBSTRING(COLUMN_NAME,2)) ), ' ', ''), '_', '') AS PASCAL_CASE
              FROM INFORMATION_SCHEMA.COLUMNS

WHERE

              TABLE_NAME = '<테이블명>' ) AS FOO