[Oracle] 테이블 기준 mapper.xml 자동으로 들어주는 프로시저

참고

• 필수 뷰 : V_COLUMNINFO

CREATE OR REPLACE PROCEDURE UNIVCOOP_DEV.PROC_TABLE_TO_DAO_XML
(
   V_TABLE_NAME IN VARCHAR2,
   V_PACKAGE_URL IN VARCHAR2 := '패키지URL',
   SQL_DESC OUT VARCHAR2
)
IS 
cnt numeric(10);
BEGIN
	/**
	 * 
	 * 
	 * DECLARE 
	SQL_DESC VARCHAR2(30000);
BEGIN
	PROC_TABLE_TO_DAO_XML('CM_BBS','com....', 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;