Oracleテーブルに基づいてxml Mapperを自動的に作成するプロセス
44508 ワード
CREATE OR REPLACE PROCEDURE UNIVCOOP_DEV.PROC_TABLE_TO_DAO_XML
(
V_TABLE_NAME IN VARCHAR2,
V_PACKAGE_URL IN VARCHAR2 ,
SQL_DESC OUT VARCHAR2
)
IS
cnt numeric(10);
BEGIN
/**
*
*
* DECLARE
SQL_DESC VARCHAR2(30000);
BEGIN
PROC_TABLE_TO_DAO_XML('CM_BBS','com.them.api.domain.',SQL_DESC);
DBMS_OUTPUT.PUT_LINE(SQL_DESC);
END;
*/
SQL_DESC := '';
SQL_DESC := SQL_DESC || '<?xml version="1.0" encoding="UTF-8"?>' || CHR(13);
SQL_DESC := SQL_DESC || '<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">' || CHR(13);
SQL_DESC := SQL_DESC || '<mapper namespace="' || V_PACKAGE_URL || '.dao.' || REPLACE(INITCAP( V_TABLE_NAME ),'_','') || 'Dao">' || CHR(13);
-- **** SELECT ****
SQL_DESC := SQL_DESC || CHR(9) || '<select id="select" parameterType="' || V_PACKAGE_URL || '.dto.' || REPLACE(INITCAP( V_TABLE_NAME ),'_','') || 'DtoReq" resultType="' || V_PACKAGE_URL || '.dto.'|| REPLACE(INITCAP( V_TABLE_NAME ),'_','') || '">' || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'SELECT * ' || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'FROM ' || V_TABLE_NAME || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'WHERE ' || CHR(13) ;
cnt := 1;
FOR C_DATA IN (SELECT * FROM V_COLUMNINFO vc WHERE TABLE_NAME = V_TABLE_NAME AND PK_FLAG = 'Y')
LOOP
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) ;
IF cnt > 1 THEN
SQL_DESC := SQL_DESC || 'AND ';
ELSE
SQL_DESC := SQL_DESC || CHR(9);
END IF;
SQL_DESC := SQL_DESC || C_DATA.COLUMN_NAME || ' = ' || RTRIM(C_DATA.COLUMN_NAME_DAO,',') || CHR(13);
cnt := cnt + 1;
END LOOP;
SELECT LTRIM(SQL_DESC, CHR(9) || CHR(9) || 'AND ') INTO SQL_DESC FROM DUAL;
SQL_DESC := SQL_DESC || CHR(9) || '</select>' || CHR(13);
-- **** INSERT ****
SQL_DESC := SQL_DESC || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || '<insert id="insert" parameterType="' || V_PACKAGE_URL || '.dto.' || REPLACE(INITCAP( V_TABLE_NAME ),'_','') || 'Dto">' || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'INSERT INTO ' || V_TABLE_NAME || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || '(' || CHR(13) ;
cnt := 1;
FOR C_DATA IN (SELECT * FROM V_COLUMNINFO vc WHERE TABLE_NAME = V_TABLE_NAME)
LOOP
IF MOD(cnt, 4) = 0 THEN
SQL_DESC := SQL_DESC || CHR(13);
END IF;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || CHR(9) || C_DATA.COLUMN_NAME || ',' ;
cnt := cnt + 1;
END LOOP;
SELECT RTRIM(SQL_DESC, ',' || CHR(9)) INTO SQL_DESC FROM DUAL;
SQL_DESC := SQL_DESC || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || ')' || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'SELECT' || CHR(13) || CHR(9) ;
cnt := 1;
FOR C_DATA IN (SELECT * FROM V_COLUMNINFO vc WHERE TABLE_NAME = V_TABLE_NAME)
LOOP
IF MOD(cnt, 4) = 0 THEN
SQL_DESC := SQL_DESC || CHR(13) || CHR(9) || CHR(9) || CHR(9) ;
ELSIF cnt = 1 THEN
SQL_DESC := SQL_DESC || CHR(9) || CHR(9);
END IF;
SQL_DESC := SQL_DESC || CHR(9) ;
IF C_DATA.COLUMN_NAME = 'REG_DTM' OR C_DATA.COLUMN_NAME = 'MOD_DTM' THEN
SQL_DESC := SQL_DESC || 'TO_CHAR(SYSDATE, ''YYYYMMDDHH24MISS''),' || CHR(9);
ELSE
SQL_DESC := SQL_DESC || C_DATA.COLUMN_NAME_DAO || CHR(9);
END IF;
cnt := cnt + 1;
END LOOP;
SELECT RTRIM(SQL_DESC, ',' || CHR(9)) INTO SQL_DESC FROM DUAL;
SQL_DESC := SQL_DESC || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || CHR(9) || 'FROM DUAL';
SQL_DESC := SQL_DESC || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || '</insert>' || CHR(13) || CHR(13);
-- **** UPDATE ****
SQL_DESC := SQL_DESC || CHR(9) || '<update id="update" parameterType="' || V_PACKAGE_URL || '.dto.' || REPLACE(INITCAP( V_TABLE_NAME ),'_','') || 'Dto">' || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'UPDATE ' || V_TABLE_NAME || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || ' SET ' ;
cnt := 1;
FOR C_DATA IN (SELECT * FROM V_COLUMNINFO vc WHERE TABLE_NAME = V_TABLE_NAME AND PK_FLAG IS NULL AND COLUMN_NAME NOT IN ('REG_ID','REG_DTM'))
LOOP
SQL_DESC := SQL_DESC || CHR(13) || CHR(9) || CHR(9) || CHR(9) || CHR(9) || C_DATA.COLUMN_NAME || ' = ' ;
IF C_DATA.COLUMN_NAME = 'MOD_DTM' THEN
SQL_DESC := SQL_DESC || 'TO_CHAR(SYSDATE, ''YYYYMMDDHH24MISS'')' || ',';
ELSE
SQL_DESC := SQL_DESC || C_DATA.COLUMN_NAME_DAO;
END IF;
cnt := cnt + 1;
END LOOP;
SELECT RTRIM(SQL_DESC, ',') INTO SQL_DESC FROM DUAL;
SQL_DESC := SQL_DESC || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'WHERE' || CHR(13) || CHR(9) ;
cnt := 1;
FOR C_DATA IN (SELECT * FROM V_COLUMNINFO vc WHERE TABLE_NAME = V_TABLE_NAME AND PK_FLAG = 'Y')
LOOP
IF cnt = 1 THEN
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) ;
ELSE
SQL_DESC := SQL_DESC || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'AND ';
END IF;
SQL_DESC := SQL_DESC || C_DATA.COLUMN_NAME || ' = ' || RTRIM(C_DATA.COLUMN_NAME_DAO,',') || CHR(9);
cnt := cnt + 1;
END LOOP;
SELECT LTRIM(SQL_DESC, CHR(13) || CHR(9) || 'AND ') INTO SQL_DESC FROM DUAL;
SQL_DESC := SQL_DESC || CHR(13) || CHR(9) || '</update>';
-- **** DELETE ****
SQL_DESC := SQL_DESC || CHR(13) || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || '<delete id="delete" parameterType="' || V_PACKAGE_URL || '.dto.' || REPLACE(INITCAP( V_TABLE_NAME ),'_','') || 'DtoReq">' || CHR(13) ;
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'DELETE FROM ' || V_TABLE_NAME ;
SQL_DESC := SQL_DESC || CHR(13);
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) || 'WHERE' || CHR(13) || CHR(9) ;
cnt := 1;
FOR C_DATA IN (SELECT * FROM V_COLUMNINFO vc WHERE TABLE_NAME = V_TABLE_NAME AND PK_FLAG = 'Y')
LOOP
SQL_DESC := SQL_DESC || CHR(9) || CHR(9) ;
IF cnt > 1 THEN
SQL_DESC := SQL_DESC || 'AND ';
END IF;
SQL_DESC := SQL_DESC || C_DATA.COLUMN_NAME || ' = ' || RTRIM(C_DATA.COLUMN_NAME_DAO,',') || CHR(13);
cnt := cnt + 1;
END LOOP;
SELECT LTRIM(SQL_DESC, CHR(13) ) INTO SQL_DESC FROM DUAL;
SQL_DESC := SQL_DESC || CHR(9) || '</delete>';
SQL_DESC := SQL_DESC || CHR(13) || '</mapper>';
END;
Reference
この問題について(Oracleテーブルに基づいてxml Mapperを自動的に作成するプロセス), 我々は、より多くの情報をここで見つけました https://velog.io/@mrc07700/Oracle-테이블-기준으로-xml-Mapper-자동으로-만들어-주는-프로시저テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol