오라클

PACKAGE로 여러 Function을 묶어서 TYPE으로 리턴하여 여러 Rows를 리턴하게 구현

-- SELECT * FROM TABLE(PKG_AUTO.SELECT_Q('테이블명') );
 
SELECT * FROM TABLE(PKG_AUTO.SELECT_Q('ESA_ACC') );
SELECT * FROM TABLE(PKG_AUTO.UPDATE_Q('ESA_ACC') );
SELECT * FROM TABLE(PKG_AUTO.DELETE_UPDATE_Q('ESA_ACC') ); /* sts를 D로 업데이트 */
SELECT * FROM TABLE(PKG_AUTO.INSERT_Q('ESA_ACC') );
SELECT * FROM TABLE(PKG_AUTO.MERGE_Q('ESA_ACC') );

CREATE OR REPLACE PACKAGE PKG_AUTO
AS
    TYPE AUTO_QUERY IS RECORD
    (
        QUERYS     VARCHAR2(4000)
    );
    -- Type재정의      
    TYPE SET_QUERY_MASTER IS TABLE OF AUTO_QUERY;      
    FUNCTION SELECT_Q(
        IN_TABLE_NAME     IN   VARCHAR2
    )
    RETURN  SET_QUERY_MASTER PIPELINED;
    FUNCTION INSERT_Q(
        IN_TABLE_NAME     IN   VARCHAR2
    )
    RETURN  SET_QUERY_MASTER PIPELINED;
    FUNCTION UPDATE_Q(
        IN_TABLE_NAME     IN   VARCHAR2
    )
    RETURN  SET_QUERY_MASTER PIPELINED;
    FUNCTION DELETE_UPDATE_Q(
        IN_TABLE_NAME     IN   VARCHAR2
    )
    RETURN  SET_QUERY_MASTER PIPELINED;
    FUNCTION DELETE_Q(
        IN_TABLE_NAME     IN   VARCHAR2
    )
    RETURN  SET_QUERY_MASTER PIPELINED;
    FUNCTION MERGE_Q(
        IN_TABLE_NAME     IN   VARCHAR2
    )
    RETURN  SET_QUERY_MASTER PIPELINED;
    
END PKG_AUTO;
 
 
CREATE OR REPLACE PACKAGE BODY PKG_AUTO
AS
FUNCTION SELECT_Q(
   IN_TABLE_NAME     IN   VARCHAR2
)
RETURN  SET_QUERY_MASTER PIPELINED
IS
CURSOR cur_data IS
    WITH T AS (
        SELECT ROWNUM AS RNUM, R.*
        FROM
        (
        SELECT *
        FROM VW_TABLE tb
        WHERE TABLE_NAME = IN_TABLE_NAME
        ORDER BY COLUMN_POSITION,COLUMN_ID
        ) R
        ORDER BY RNUM          
    )
SELECT '<select id="selectXXX" resultType="map" parameterType="map">' FROM DUAL UNION ALL
SELECT RPAD('/*', 4, ' ') || ' xxxMap.selectId ' || LPAD('*/', 4, ' ')
FROM T
WHERE RNUM = 1   
UNION ALL
SELECT (CASE WHEN ROWNUM = 1 THEN 'SELECT ' ELSE '  , 'END)
        || RPAD(COLUMN_NAME, C_MAX_LENGTH + 2, ' ')
        || ' AS '
        || RPAD(COLUMN_NAME, C_MAX_LENGTH + 2, ' ')
        || RPAD('/*', 4, ' ')
        || C_COMMENTS
        || LPAD('*/', 4, ' ')
  FROM T   
  WHERE COLUMN_NAME NOT IN ('REG_ID','REG_DT','MOD_ID','MOD_DT')
UNION ALL
SELECT '    FROM '
        || RPAD(TABLE_NAME, LENGTH(TABLE_NAME) + 3, ' ')
        ||  RPAD('/*', 4, ' ') || T_COMMENTS || LPAD('*/', 4, ' ')
FROM T
WHERE RNUM = 1   
UNION ALL      
SELECT (CASE WHEN COLUMN_POSITION = 1 THEN ' WHERE ' ELSE ' AND ' END)
    || RPAD(COLUMN_NAME || CASE COLUMN_NAME WHEN 'SYS_ID' THEN ' = #{g.tenant}'
                                            WHEN 'REG_ID' THEN ' = #{g.usr_id}'
                                            WHEN 'REG_DT' THEN ' = #{g.now}'
                                            WHEN 'MOD_ID' THEN ' = #{g.usr_id}'
                                            WHEN 'MOD_DT' THEN ' = #{g.now}'
                                            ELSE ' = #{p.' || C_CAMEL_CASE || '}' END
      , C_MAX_LENGTH + 7, ' ')
        || RPAD('/*', 4, ' ')
        || C_COMMENTS
        || LPAD('*/', 4, ' ') 
  FROM T
 WHERE IS_PK = 'Y'
UNION ALL
SELECT '    AND STS != ''D''' FROM DUAL UNION ALL
SELECT '</select>' FROM DUAL ;
BEGIN
    -- Fetch Cursor
    FOR ret_data IN cur_data LOOP
        -- 해당row를 pipe 시킨다.       
        PIPE ROW(ret_data);
    END LOOP;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Error : ');   
END SELECT_Q;
 
-------INSERT
FUNCTION INSERT_Q(
   IN_TABLE_NAME     IN   VARCHAR2
)
RETURN SET_QUERY_MASTER PIPELINED
IS
CURSOR cur_data IS
    WITH T AS (
        SELECT ROWNUM AS RNUM, R.*
        FROM
        (
        SELECT *
        FROM VW_TABLE tb
        WHERE TABLE_NAME = IN_TABLE_NAME
        ORDER BY COLUMN_POSITION,COLUMN_ID
        ) R
        ORDER BY RNUM          
    )
SELECT '<insert id="insertXXX" parameterType="map">' FROM DUAL UNION ALL
SELECT RPAD('/*', 4, ' ') || ' xxxMap.insertId ' || LPAD('*/', 4, ' ')
FROM T
WHERE RNUM = 1   
UNION ALL
SELECT 'INSERT INTO '
    || RPAD(TABLE_NAME
    || ' (', LENGTH(TABLE_NAME) + 4, ' ')
    || RPAD('/*', 4, ' ')
    || T_COMMENTS
    || LPAD('*/', 4, ' ')
  FROM T
  WHERE RNUM = 1       
UNION ALL
SELECT (CASE WHEN ROWNUM = 1 THEN ' ' ELSE '    , ' END)
    || RPAD(COLUMN_NAME, C_MAX_LENGTH + 2, ' ')
    || RPAD('/*', 4, ' ')
    || (CASE WHEN LENGTHB(C_COMMENTS) > 14 THEN C_COMMENTS ELSE RPAD(C_COMMENTS, 14, ' ') END)
    || LPAD('*/', 4, ' ')
  FROM T 
UNION ALL
SELECT ') VALUES ('
FROM DUAL
UNION ALL
SELECT (CASE WHEN ROWNUM = 1 THEN ' ' ELSE '    , ' END)
      ||CASE COLUMN_NAME WHEN 'SYS_ID' THEN '#{g.tenant}'
                         WHEN 'REG_ID' THEN '#{g.usr_id}'
                         WHEN 'REG_DT' THEN '#{g.now}'
                         WHEN 'MOD_ID' THEN '#{g.usr_id}'
                         WHEN 'MOD_DT' THEN '#{g.now}'
                         WHEN 'STS'    THEN '''C'''
        ELSE '#{p.' || C_CAMEL_CASE || '}' END  
  FROM T
UNION ALL
SELECT ')' FROM DUAL UNION ALL
SELECT '</insert>' FROM DUAL;
BEGIN
    -- Fetch Cursor
    FOR ret_data IN cur_data LOOP
        -- 해당row를 pipe 시킨다.       
        PIPE ROW(ret_data);
    END LOOP;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Error : ');   
END INSERT_Q;
 
-------UPDATE
FUNCTION UPDATE_Q(
   IN_TABLE_NAME     IN   VARCHAR2
)
RETURN  SET_QUERY_MASTER PIPELINED
IS
CURSOR cur_data IS
    WITH T AS (
        SELECT ROWNUM AS RNUM, R.*
        FROM
        (
        SELECT *
        FROM VW_TABLE tb
        WHERE TABLE_NAME = IN_TABLE_NAME
        ORDER BY COLUMN_POSITION,COLUMN_ID
        ) R
        ORDER BY RNUM          
    )
SELECT '<update id="updateXXX" parameterType="map">' FROM DUAL UNION ALL
SELECT RPAD('/*', 4, ' ') || ' xxxMap.updateId ' || LPAD('*/', 4, ' ') FROM T WHERE RNUM = 1 
UNION ALL 
SELECT 'UPDATE ' || RPAD(TABLE_NAME, LENGTH(TABLE_NAME) + 3, ' ') || RPAD('/*', 4, ' ') || T_COMMENTS || LPAD('*/', 4, ' ') 
FROM T
WHERE RNUM = 1     
UNION ALL
SELECT (CASE WHEN ROWNUM = 1 THEN '   SET ' ELSE '     , 'END) ||  
       RPAD(COLUMN_NAME, C_MAX_LENGTH + 2, ' ') || ' = ' ||
       RPAD(CASE COLUMN_NAME WHEN 'MOD_ID' THEN '#{g.usr_id}'
                             WHEN 'MOD_DT' THEN '#{g.now}'
                             WHEN 'STS'    THEN '''U'''
             ELSE '#{p.' || C_CAMEL_CASE || '}' END, C_MAX_LENGTH + 12, ' ')
                         || RPAD('/*', 4, ' ')
                         || (CASE WHEN LENGTHB(C_COMMENTS) > 14 THEN C_COMMENTS ELSE RPAD(C_COMMENTS, 14, ' ') END)
                         || LPAD('*/', 4, ' ')
  FROM T
 WHERE IS_PK = 'N' 
 AND COLUMN_NAME NOT IN ('REG_ID','REG_DT','SYS_ID')
UNION ALL
SELECT (CASE WHEN COLUMN_POSITION = 1 THEN ' WHERE ' ELSE ' AND ' END)
     || RPAD(COLUMN_NAME ||  CASE COLUMN_NAME WHEN 'SYS_ID' THEN ' = #{g.tenant}'
                                              ELSE ' = #{p.' || C_CAMEL_CASE || '}' END
     , C_MAX_LENGTH * 2 + 7, ' ')
      || RPAD('/*', 4, ' ')
      || (CASE WHEN LENGTHB(C_COMMENTS) > 14 THEN C_COMMENTS ELSE RPAD(C_COMMENTS, 14, ' ') END)
      || LPAD('*/', 4, ' ') 
  FROM T
 WHERE IS_PK = 'Y' UNION ALL
 SELECT '</update>' FROM DUAL;
BEGIN
    -- Fetch Cursor
    FOR ret_data IN cur_data LOOP
        -- 해당row를 pipe 시킨다.       
        PIPE ROW(ret_data);
    END LOOP;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Error : ');   
END UPDATE_Q;
-------UPDATE_DELETE
FUNCTION DELETE_UPDATE_Q(
   IN_TABLE_NAME     IN   VARCHAR2
)
RETURN  SET_QUERY_MASTER PIPELINED
IS
CURSOR cur_data IS
    WITH T AS (
        SELECT ROWNUM AS RNUM, R.*
        FROM
        (
        SELECT *
        FROM VW_TABLE tb
        WHERE TABLE_NAME = IN_TABLE_NAME
        ORDER BY COLUMN_POSITION,COLUMN_ID
        ) R
        ORDER BY RNUM          
    )
SELECT '<update id="deleteXXX" parameterType="map">' FROM DUAL UNION ALL
SELECT RPAD('/*', 4, ' ') || ' xxxMap.deleteId ' || LPAD('*/', 4, ' ')
FROM T
WHERE RNUM = 1
UNION ALL 
SELECT 'UPDATE ' || RPAD(TABLE_NAME, LENGTH(TABLE_NAME) + 3, ' ') || RPAD('/*', 4, ' ') || T_COMMENTS || LPAD('*/', 4, ' ') 
FROM T
WHERE RNUM = 1     
UNION ALL
SELECT  '   SET STS = ''D'''
FROM DUAL
UNION ALL
SELECT  ',  MOD_ID  = #{g.usr_id}'
FROM DUAL  
UNION ALL
SELECT  ',  MOD_DT  = #{g.now}'
FROM DUAL     
UNION ALL
SELECT (CASE WHEN COLUMN_POSITION = 1 THEN ' WHERE ' ELSE ' AND ' END)
    || RPAD(COLUMN_NAME
    || CASE COLUMN_NAME WHEN 'SYS_ID' THEN ' = #{g.tenant}'
                        ELSE ' = #{p.' || C_CAMEL_CASE || '}' END
     , C_MAX_LENGTH * 2 + 7, ' ')
     || RPAD('/*', 4, ' ')
     || (CASE WHEN LENGTHB(C_COMMENTS) > 14 THEN C_COMMENTS ELSE RPAD(C_COMMENTS, 14, ' ') END)
     || LPAD('*/', 4, ' ') 
  FROM T
 WHERE IS_PK = 'Y' UNION ALL
 SELECT '</update>' FROM DUAL;
BEGIN
    -- Fetch Cursor
    FOR ret_data IN cur_data LOOP
        -- 해당row를 pipe 시킨다.       
        PIPE ROW(ret_data);
    END LOOP;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Error : ');   
END DELETE_UPDATE_Q;
 
------- DELETE
FUNCTION DELETE_Q(
   IN_TABLE_NAME     IN   VARCHAR2
)
RETURN  SET_QUERY_MASTER PIPELINED
IS
CURSOR cur_data IS
    WITH T AS (
        SELECT ROWNUM AS RNUM, R.*
        FROM
        (
        SELECT *
        FROM VW_TABLE tb
        WHERE TABLE_NAME = IN_TABLE_NAME
        ORDER BY COLUMN_POSITION,COLUMN_ID
        ) R
        ORDER BY RNUM          
    )
SELECT '<delete id="deleteXXX" parameterType="map">' FROM DUAL UNION ALL
SELECT RPAD('/*', 4, ' ') || ' xxxMap.deleteId ' || LPAD('*/', 4, ' ')
FROM T
WHERE RNUM = 1
UNION ALL 
SELECT 'DELETE FROM ' || RPAD(TABLE_NAME, LENGTH(TABLE_NAME) + 3, ' ') || RPAD('/*', 4, ' ') || T_COMMENTS || LPAD('*/', 4, ' ') 
FROM T
WHERE RNUM = 1
UNION ALL
SELECT (CASE WHEN COLUMN_POSITION = 1 THEN ' WHERE ' ELSE ' AND ' END)
     || RPAD(COLUMN_NAME || CASE COLUMN_NAME WHEN 'SYS_ID' THEN ' = #{g.tenant}'
                                             ELSE ' = #{p.' || C_CAMEL_CASE || '}' END
      , C_MAX_LENGTH * 2 + 7, ' ')
       || RPAD('/*', 4, '   ')
       || (CASE WHEN LENGTHB(C_COMMENTS) > 14 THEN C_COMMENTS ELSE RPAD(C_COMMENTS, 14, ' ') END)
       || LPAD('*/', 4, '   ') 
  FROM T
 WHERE IS_PK = 'Y' UNION ALL
 SELECT '</delete>' FROM DUAL;
BEGIN
    -- Fetch Cursor
    FOR ret_data IN cur_data LOOP
        -- 해당row를 pipe 시킨다.       
        PIPE ROW(ret_data);
    END LOOP;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Error : ');   
END DELETE_Q;
 
------- MERGE
FUNCTION MERGE_Q(
   IN_TABLE_NAME     IN   VARCHAR2
)
RETURN  SET_QUERY_MASTER PIPELINED
IS
CURSOR cur_data IS
    WITH T AS (
        SELECT ROWNUM AS RNUM, R.*
        FROM
        (
        SELECT *
        FROM VW_TABLE tb
        WHERE TABLE_NAME = IN_TABLE_NAME
        ORDER BY COLUMN_POSITION,COLUMN_ID
        ) R
        ORDER BY RNUM          
    )
SELECT '<update id="mergeXXX" parameterType="map">' FROM DUAL UNION ALL
SELECT RPAD('/*', 4, ' ') || ' xxxMap.mergeId ' || LPAD('*/', 4, ' ')
FROM T
WHERE RNUM = 1
UNION ALL
SELECT 'MERGE INTO ' || RPAD(TABLE_NAME || ' A', LENGTH(TABLE_NAME) + 4, ' ') || RPAD('/*', 4, ' ') || T_COMMENTS || LPAD('*/', 4, ' ') 
FROM T
WHERE RNUM = 1   
UNION ALL
SELECT (CASE WHEN ROWNUM = 1 THEN ' USING ( SELECT '
             ELSE RPAD(' ', 18) || ', ' END)
     || RPAD(  CASE COLUMN_NAME WHEN 'SYS_ID' THEN '#{g.tenant}'
                                WHEN 'REG_ID' THEN '#{g.usr_id}'
                                WHEN 'REG_DT' THEN '#{g.now}'
                                WHEN 'MOD_ID' THEN '#{g.usr_id}'
                                WHEN 'MOD_DT' THEN '#{g.now}'
               ELSE  '#{p.' || C_CAMEL_CASE || '}' END
      , C_MAX_LENGTH + 10, ' ')
     || ' AS '
     || RPAD(COLUMN_NAME, C_MAX_LENGTH + 2, ' ')
     || RPAD('/*', 4, ' ')
     || (CASE WHEN LENGTHB(C_COMMENTS) >= 14 THEN C_COMMENTS ELSE RPAD(C_COMMENTS, 14, ' ') END)
     || LPAD('*/', 4, ' ')
  FROM T
UNION ALL
SELECT '    FROM DUAL  ) B' FROM DUAL
UNION ALL
SELECT (CASE WHEN RNUM = 1 THEN '   ON ('
             ELSE ' AND ' END)
     || RPAD('A.'
     || COLUMN_NAME
     || ' = B.'
     || COLUMN_NAME, C_MAX_LENGTH * 2 + 7, ' ')
     || RPAD('/*', 4, ' ')
     || (CASE WHEN LENGTHB(C_COMMENTS) > 14 THEN C_COMMENTS ELSE RPAD(C_COMMENTS, 14, ' ') END)
     || LPAD('*/', 4, ' ') 
  FROM T
 WHERE COLUMN_POSITION IS NOT NULL    
UNION ALL  
SELECT '    )' FROM DUAL
UNION ALL    
SELECT 'WHEN MATCHED THEN' FROM DUAL
UNION ALL    
SELECT '    UPDATE' FROM DUAL
UNION ALL
SELECT (CASE WHEN ROWNUM = 1 THEN ' SET ' ELSE '    , ' END)
     || COLUMN_NAME
     || CASE COLUMN_NAME WHEN 'STS' THEN ' = ''U''' ELSE  ' = B.'
     || COLUMN_NAME END
  FROM T
 WHERE IS_PK = 'N'
 AND COLUMN_NAME NOT IN ('SYS','REG_ID','REG_DT')
UNION ALL
SELECT 'WHEN NOT MATCHED THEN' FROM DUAL
UNION ALL    
SELECT '  INSERT (' FROM DUAL
UNION ALL
SELECT (CASE WHEN RNUM = 1 THEN '   ' ELSE '    , ' END) || COLUMN_NAME
FROM T
UNION ALL    
SELECT '  ) VALUES (' FROM DUAL
UNION ALL
SELECT (CASE WHEN RNUM = 1 THEN '   ' ELSE '    , ' END)
     || CASE COLUMN_NAME WHEN 'STS' THEN '  ''C''' ELSE  ' B.'
     || COLUMN_NAME END
FROM T
UNION ALL    
SELECT '  )' FROM DUAL UNION ALL
SELECT '</update>' FROM DUAL;
BEGIN
    -- Fetch Cursor
    FOR ret_data IN cur_data LOOP
        -- 해당row를 pipe 시킨다.       
        PIPE ROW(ret_data);
    END LOOP;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Error : ');   
END MERGE_Q;
 
END PKG_AUTO;

mariadb

# CALL SP_AUTO_SQL('테이블명')
CALL SP_AUTO_SQL('BIGHIT_GR_SEND')

CREATE PROCEDURE `SP_AUTO_SQL`(`p_table_nm` TEXT)
BEGIN
    DECLARE _generate_sql VARCHAR(21800);
    SET @maxlangth:= 0;
    SET @commonMaxlangth:= 0;
    SET _generate_sql = CONCAT('
    SELECT CONCAT(''<select id="selectXXX" resultType="map" parameterType="map">'') UNION ALL
    SELECT CONCAT(''/***xxx.selectXXX***/'') AS QUERY UNION ALL
    SELECT CONCAT(''SELECT  '') UNION ALL
    SELECT IF(ORDINAL_POSITION = 1
    , CONCAT(''  '',COLUMN_NAME, LPAD('' '',(@maxlangth*2) - CHAR_LENGTH(COLUMN_NAME),'' ''), '' /*    '',COLUMN_COMMENT ,''    */'')
    , CONCAT('', '',CONCAT(COLUMN_NAME, LPAD('' '',(@maxlangth*2) - CHAR_LENGTH(COLUMN_NAME),'' ''), '' /*    '',COLUMN_COMMENT ,''    */'')))
      FROM (
          SELECT COLUMN_NAME
        , COLUMN_COMMENT
        , ORDINAL_POSITION
        , IF(@maxlangth < CHAR_LENGTH(COLUMN_NAME), @maxlangth:=CHAR_LENGTH(COLUMN_NAME), @maxlangth)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
        AND COLUMN_NAME NOT IN (''UPDATE_USER'', ''UPDATE_DATE'')
        ORDER BY ORDINAL_POSITION
        ) AS C
    UNION ALL
    SELECT CONCAT(''FROM '',''',p_table_nm,''')
    UNION ALL
    SELECT ''<where>'' UNION ALL
    SELECT CONCAT(''    AND '', COLUMN_NAME, LPAD('' '',(@maxlangth * 2) - CHAR_LENGTH(COLUMN_NAME) - 5,'' ''), '' = #{p.'', LOWER(COLUMN_NAME), ''} '',LPAD('' '',(@maxlangth + 4) - CHAR_LENGTH(COLUMN_NAME),'' ''),'' /*    '',COLUMN_COMMENT ,''    */'')
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
       AND COLUMN_KEY = ''PRI''
       AND COLUMN_NAME NOT IN (''INIT_USER'', ''INIT_DATE'')
    UNION ALL
    SELECT ''</where>'' UNION ALL
    SELECT ''</select>''
    UNION ALL
    SELECT '''' UNION ALL
    SELECT CONCAT(''<insert id="insert" parameterType = "map">'') UNION ALL
    SELECT CONCAT(''/***xxx.insertXXX***/'') AS QUERY UNION ALL
    SELECT CONCAT(''INSERT INTO '', UPPER(''',p_table_nm,'''), '' ('') UNION ALL
    SELECT IF(ORDINAL_POSITION = 1, CONCAT(''  '',COLUMN_NAMES), CONCAT('', '',COLUMN_NAMES))
      FROM (
        SELECT CONCAT(COLUMN_NAME, LPAD('' '', (@maxlangth*2) - CHAR_LENGTH(COLUMN_NAME), '' ''), '' /*    '',COLUMN_COMMENT , ''    */'') AS COLUMN_NAMES
        , ORDINAL_POSITION
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE UPPER(TABLE_NAME) = ''', p_table_nm,'''
        AND COLUMN_NAME NOT IN (''UPDATE_USER'', ''UPDATE_DATE'')
        ORDER BY ORDINAL_POSITION
        ) AS C
    UNION ALL
    SELECT '') VALUES ('' UNION ALL
        SELECT CONCAT(IF(ORDINAL_POSITION = 1, CONCAT('' '', '' #{p.'',LOWER(COLUMN_NAMES),''}'')
             , CONCAT('', '',''#{p.'',LOWER(COLUMN_NAMES),''}'') ), LPAD('' '',(@maxlangth*2) - CHAR_LENGTH(COLUMN_NAMES) - 5,'' ''), '' /*    '',COLUMN_COMMENT, ''    */'')
          FROM (
            SELECT COLUMN_NAME AS COLUMN_NAMES
            , COLUMN_COMMENT
            , ORDINAL_POSITION
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
            AND COLUMN_NAME NOT IN (''UPDATE_USER'', ''UPDATE_DATE'')
            ORDER BY ORDINAL_POSITION
            ) AS C
    UNION ALL
    SELECT '')'' UNION ALL
    SELECT ''</insert>''
    UNION ALL
    SELECT '''' UNION ALL
    SELECT CONCAT(''<update id="update" parameterType = "map">'') UNION ALL
    SELECT CONCAT(''/***xxx.updateXXX***/'') AS QUERY UNION ALL
    SELECT CONCAT(''UPDATE '', ''',p_table_nm,''') UNION ALL
    SELECT CONCAT( '' SET'') UNION ALL
    SELECT COLS
    FROM (
    SELECT IF(@rownum = 1, CONCAT(''  '', COLUMN_NAME, LPAD('' '',(@maxlangth+4) - CHAR_LENGTH(COLUMN_NAME),'' ''), ''= #{p.'', LOWER(COLUMN_NAME), ''}'', LPAD('' '',(@maxlangth+4) - CHAR_LENGTH(COLUMN_NAME),'' ''), ''/* '',COLUMNS.COLUMN_COMMENT,''    */'')
                         , CONCAT('', '', COLUMN_NAME, LPAD('' '',(@maxlangth+4) - CHAR_LENGTH(COLUMN_NAME),'' ''), ''= #{p.'', LOWER(COLUMN_NAME), ''}'', LPAD('' '',(@maxlangth+4) - CHAR_LENGTH(COLUMN_NAME),'' ''), ''/* '',COLUMNS.COLUMN_COMMENT,''    */'')
             ) AS COLS
         , @rownum:=@rownum+1 AS rows1
      FROM INFORMATION_SCHEMA.COLUMNS
      ,(SELECT @rownum:=1) rn
     WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
       AND COLUMN_KEY = ''''
       AND COLUMN_NAME NOT IN (''INIT_USER'', ''INIT_DATE'')
    ) CO
    UNION ALL
    SELECT ''<where>'' UNION ALL
    SELECT CONCAT(''    AND '', COLUMN_NAME, LPAD('' '',(@maxlangth+4) - CHAR_LENGTH(COLUMN_NAME),'' ''), '' = #{p.'', LOWER(COLUMN_NAME), ''} '',LPAD('' '',(@maxlangth + 4) - CHAR_LENGTH(COLUMN_NAME),'' ''),'' /*    '',COLUMN_COMMENT ,''    */'')
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
       AND COLUMN_KEY = ''PRI''
       AND COLUMN_NAME NOT IN (''INIT_USER'', ''INIT_DATE'')
    UNION ALL
    SELECT ''</where>''
    UNION ALL
    SELECT ''</update>''
    UNION ALL
    SELECT '''' UNION ALL
    SELECT CONCAT(''<delete id="delete" parameterType = "map">'') UNION ALL
    SELECT CONCAT(''/***xxx.deleteXXX***/'') AS QUERY UNION ALL
    SELECT CONCAT(''DELETE FROM '', UPPER(''',p_table_nm,''')) UNION ALL
    SELECT ''<where>'' UNION ALL
    SELECT CONCAT(''    AND '', COLUMN_NAME, LPAD('' '',(@maxlangth+4) - CHAR_LENGTH(COLUMN_NAME),'' ''), '' = #{p.'', LOWER(COLUMN_NAME), ''} '',LPAD('' '',(@maxlangth + 4) - CHAR_LENGTH(COLUMN_NAME),'' ''),'' /*    '',COLUMN_COMMENT ,''    */'')
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
       AND COLUMN_KEY = ''PRI''
       AND COLUMN_NAME NOT IN (''INIT_USER'', ''INIT_DATE'')
    UNION ALL
    SELECT ''</where>''
    UNION ALL
    SELECT ''</delete>''
    UNION ALL
    SELECT '''' UNION ALL
    SELECT CONCAT(''<update id="merge" parameterType = "map">'') UNION ALL
    SELECT CONCAT(''/***xxx.mergeXXX***/'') AS QUERY UNION ALL
    SELECT CONCAT(''INSERT INTO '', UPPER(''',p_table_nm,'''), '' ('') UNION ALL
    SELECT IF(ORDINAL_POSITION = 1, CONCAT(''  '', COLUMN_NAMES), CONCAT('', '',COLUMN_NAMES))
      FROM (
        SELECT CONCAT(COLUMN_NAME, LPAD('' '',(@maxlangth+10) - CHAR_LENGTH(COLUMN_NAME),'' ''),'' /*    '',COLUMN_COMMENT ,''    */'') AS COLUMN_NAMES
        , ORDINAL_POSITION
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
        AND COLUMN_NAME NOT IN (''UPDATE_USER'', ''UPDATE_DATE'')
        ORDER BY ORDINAL_POSITION
        ) AS C
    UNION ALL
    SELECT '') VALUES ('' UNION ALL
        SELECT CONCAT(IF(ORDINAL_POSITION = 1, CONCAT(''   #{p.'',LOWER(COLUMN_NAMES),''}'')
             , CONCAT('', '','' #{p.'', LOWER(COLUMN_NAMES),''}'') ), LPAD('' '',(@maxlangth+4) - CHAR_LENGTH(COLUMN_NAMES),'' ''),'' /*    '',COLUMN_COMMENT, ''    */'')
          FROM (
            SELECT COLUMN_NAME AS COLUMN_NAMES
            , COLUMN_COMMENT
            , ORDINAL_POSITION
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
            AND COLUMN_NAME NOT IN (''UPDATE_USER'', ''UPDATE_DATE'')
            ORDER BY ORDINAL_POSITION
            ) AS C
    UNION ALL
    SELECT '')'' UNION ALL
    SELECT ''ON DUPLICATE KEY UPDATE'' UNION ALL
    SELECT COLS
    FROM (
    SELECT IF(@rownum = 1, CONCAT(COLUMN_NAME, LPAD('' '',(@maxlangth+10) - CHAR_LENGTH(COLUMN_NAME),'' ''),'' = #{p.'', LOWER(COLUMN_NAME), ''} /* '',COLUMNS.COLUMN_COMMENT,''    */'')
         , CONCAT('', '',COLUMN_NAME, LPAD('' '',(@maxlangth+10) - CHAR_LENGTH(COLUMN_NAME),'' ''),'' = #{p.'', LOWER(COLUMN_NAME), ''}'',LPAD('' '',(@maxlangth+10) - CHAR_LENGTH(COLUMN_NAME),'' ''), ''/* '',COLUMNS.COLUMN_COMMENT,''    */'')) AS COLS
         , @rownum:=@rownum+1 AS rows1
      FROM INFORMATION_SCHEMA.COLUMNS
      ,(SELECT @rownum:=1) rn
     WHERE UPPER(TABLE_NAME) = ''',p_table_nm,'''
       AND COLUMN_KEY != ''PRI''
       AND COLUMN_NAME NOT IN (''INIT_USER'', ''INIT_DATE'')
    ) CO
    UNION ALL
    SELECT ''</update>''' );
    -- call debug_msg(1, _generate_sql);
    SET @statement = _generate_sql;
    PREPARE DYNQUERY FROM @statement;
    EXECUTE DYNQUERY;
    DEALLOCATE PREPARE DYNQUERY;
END