-- wrap iname=PKG_MGM.sql
-- SQLPLUS user_id/password@db_sid @PKG_MGM.plb
CREATE OR REPLACE PACKAGE PKG_MGM
IS
-- 일시 : 2013.05.08
-- 작성 : 박장원
TYPE TYPE_CURSOR IS REF CURSOR;
TYPE TYPE_RECORD IS RECORD ( R_COL01 VARCHAR2(4000)
, R_COL02 VARCHAR2(4000)
, R_COL03 VARCHAR2(4000)
, R_COL04 VARCHAR2(4000)
, R_COL05 VARCHAR2(4000)
, R_COL06 VARCHAR2(4000)
, R_COL07 VARCHAR2(4000)
, R_COL08 VARCHAR2(4000)
, R_COL09 VARCHAR2(4000)
, R_COL10 VARCHAR2(4000));
TYPE TYPE_TABLE IS TABLE OF TYPE_RECORD;
TYPE TYPE_ARRAY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
-- TEMP TABLE 생성 스크립트 추출
FUNCTION FNC_DDL_TBL ( P_TABLE_NAME IN VARCHAR2
, P_ALIAS IN VARCHAR2 := '') RETURN TYPE_TABLE PIPELINED;
-- MERGE INTO 스크립트 추출
FUNCTION FNC_DML_MRG (P_TABLE_NAME IN VARCHAR2) RETURN TYPE_TABLE PIPELINED;
-- Primary Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_PK (P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED;
-- Foreign Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_FK (P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED;
-- Insert 구문 추출
FUNCTION FNC_DML_INS (P_TABLE_NAME IN VARCHAR2) RETURN TYPE_TABLE PIPELINED;
-- Table 정의서
FUNCTION FNC_TBL_SCHEMA ( P_TABLE_NAME IN VARCHAR2 := '%'
, P_SYSTEM_NAME IN VARCHAR2 := 'SYSTEM'
, P_WRITER_NAME IN VARCHAR2 := 'SYSTEM') RETURN TYPE_TABLE PIPELINED;
-- Index 스크립트 추출
FUNCTION FNC_DDL_IDX (P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED;
-- Select 스크립트 추출
FUNCTION FNC_DML_SEL (P_TABLE_LIST IN VARCHAR2) RETURN TYPE_TABLE PIPELINED;
-- COLUMN의 DATA_DEFAULT 가져오기
FUNCTION FNC_DATA_DEFAULT ( TABLE_NAME IN VARCHAR2
, COLUMN_NAME IN VARCHAR2) RETURN VARCHAR2;
-- COLUMN의 COLUMN_EXPRESSION 가져오기
FUNCTION FNC_COLUMN_EXPRESSION ( TABLE_NAME IN VARCHAR2
, INDEX_NAME IN VARCHAR2
, COLUMN_POSITION IN NUMBER) RETURN VARCHAR2;
END PKG_MGM;
/
CREATE OR REPLACE PACKAGE BODY PKG_MGM
IS
-- TEMP TABLE 생성 스크립트 추출
FUNCTION FNC_DDL_TBL ( P_TABLE_NAME IN VARCHAR2
, P_ALIAS IN VARCHAR2 := '') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (SELECT COL1
, COL2
, COL3
, COL4
FROM (SELECT RPAD(DECODE(COL.COLUMN_ID, 1, 'SELECT', ','), 8, ' ') AS COL1
, NVL2(P_ALIAS, P_ALIAS || '.', P_ALIAS) || RPAD(COL.COLUMN_NAME, 40, ' ') AS COL2
, RPAD('AS ' || COL.COLUMN_NAME, 40, ' ') AS COL3
, '-- ' || CST.PK || REPLACE(CMT.COMMENTS, CHR(10), ' ') || ' ' || TO_CHAR(TRIM(CST.AK || ' ' || CST.FK)) AS COL4
, COL.TABLE_NAME
, COL.COLUMN_ID
FROM USER_TAB_COLUMNS COL
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || CTC.POSITION || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS FK
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.TABLE_NAME = CTC.TABLE_NAME
WHERE CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
UNION ALL
SELECT DECODE(NO, 1, 'CREATE TABLE ', 2, 'AS', 3, 'FROM ') AS COL1
, DECODE(NO, 1, T.TABLE_NAME || '_TEMP', 3, T.TABLE_NAME || NVL2(P_ALIAS, ' ' || P_ALIAS, P_ALIAS) || ';') AS COL2
, NULL AS COL3
, DECODE(NO, 3, '-- ' || REPLACE(T.COMMENTS, CHR(10), ' ')) AS COL4
, T.TABLE_NAME
, DECODE(NO, 1, -1, 2, 0) AS COLUMN_ID
FROM USER_TAB_COMMENTS T
, (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 3) D
ORDER BY COLUMN_ID ASC)
WHERE TABLE_NAME = UPPER(P_TABLE_NAME))
LOOP
V_ROW.R_COL01 := CSR_TBL.COL1;
V_ROW.R_COL02 := CSR_TBL.COL2;
V_ROW.R_COL03 := CSR_TBL.COL3;
V_ROW.R_COL04 := CSR_TBL.COL4;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- MERGE INTO 스크립트 추출
FUNCTION FNC_DML_MRG (P_TABLE_NAME IN VARCHAR2) RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (WITH TBL_COL
AS
( SELECT COL.TABLE_NAME
, D.NO
, COL.COLUMN_ID
, COL.COLUMN_NAME
, '-- ' || CST.PK || REPLACE(CMT.COMMENTS, CHR(10), ' ') || ' ' || TRIM(CST.AK || ' ' || CST.FK ) AS COMMENTS
, CST.ISPK
, COL.NULLABLE
, PKG_MGM.FNC_DATA_DEFAULT(COL.TABLE_NAME, COL.COLUMN_NAME) AS DATA_DEFAULT
, '#' || LOWER(COL.COLUMN_NAME) || '#' AS PARAM_COLUMN
, DECODE(COL.COLUMN_ID, FIRST_VALUE(COL.COLUMN_ID) OVER (PARTITION BY D.NO, COL.TABLE_NAME ORDER BY D.NO, COL.COLUMN_ID), 'Y', 'N') AS ISFIRSTCOL
, DECODE(COL.COLUMN_ID, LAST_VALUE(COL.COLUMN_ID) OVER (PARTITION BY D.NO, COL.TABLE_NAME ORDER BY D.NO, COL.COLUMN_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 'Y', 'N') AS ISLASTCOL
, FLOOR(MAX(LENGTH(COL.COLUMN_NAME)) OVER (PARTITION BY D.NO, COL.TABLE_NAME) / 4) * 4 + 4 AS COLLEN
, 4 AS TAB
FROM USER_TAB_COLUMNS COL
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || CTC.POSITION || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS FK
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.TABLE_NAME = CTC.TABLE_NAME
WHERE CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 11) D ON D.NO IN (1, DECODE(CST.ISPK, 'Y', 3), DECODE(CST.ISPK, NULL, 5), 10, 11)
)
, TBL_DML
AS
( SELECT C.TABLE_NAME
, C.NO
, C.COLUMN_ID
, LPAD(' ', C.TAB * 2, CHR(32)) AS COL1
, DECODE(C.NO , 1, DECODE(C.ISFIRSTCOL, 'Y', RPAD('USING' , C.TAB * 2, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)))
, LPAD(' ', C.TAB * 2, CHR(32))) AS COL2
, DECODE(C.NO , 1, DECODE(C.ISFIRSTCOL, 'Y', RPAD('(SELECT' , C.TAB * 2, CHR(32)) , RPAD(',', C.TAB * 2, CHR(32)))
, 3, DECODE(C.ISFIRSTCOL, 'Y', RPAD('ON (' , C.TAB * 3, CHR(32)) , RPAD(' AND', C.TAB * 3, CHR(32)))
, 5, DECODE(C.ISFIRSTCOL, 'Y', RPAD('UPDATE SET' , C.TAB * 3, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)) || RPAD(',', C.TAB * 1, CHR(32)))
, 10, DECODE(C.ISFIRSTCOL, 'Y', RPAD('INSERT (' , C.TAB * 3, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)) || RPAD(',', C.TAB * 1, CHR(32)))
, 11, DECODE(C.ISFIRSTCOL, 'Y', RPAD('VALUES (' , C.TAB * 3, CHR(32)) , LPAD(' ', C.TAB * 2, CHR(32)) || RPAD(',', C.TAB * 1, CHR(32)))) AS COL3
, DECODE(C.NO , 1, RPAD(DECODE(C.NULLABLE, 'N', 'NVL('|| C.PARAM_COLUMN || ', ' || DATA_DEFAULT || ')', C.PARAM_COLUMN) , C.TAB * 15, CHR(32))
, 3, RPAD('D.' || C.COLUMN_NAME , C.TAB * 10, CHR(32))
, 5, RPAD(C.COLUMN_NAME , C.TAB * 10, CHR(32))
, 10, RPAD(C.COLUMN_NAME || DECODE(C.ISLASTCOL, 'Y', ')') , C.TAB * 10, CHR(32))
, 11, RPAD('D.' || C.COLUMN_NAME || DECODE(C.ISLASTCOL, 'Y', ')') , C.TAB * 10, CHR(32))) AS COL4
, DECODE(C.NO , 1, RPAD('AS ' || C.COLUMN_NAME , C.TAB * 10, CHR(32))
, 3, '= T.' || C.COLUMN_NAME || DECODE(C.ISLASTCOL, 'Y', ')')
, 5, RPAD('= D.' || C.COLUMN_NAME , C.TAB * 10, CHR(32))) AS COL5
, DECODE(C.NO , 3, NULL, C.COMMENTS) AS COL6
FROM TBL_COL C
UNION ALL
SELECT TBL.TABLE_NAME
, D.NO
, 0 AS COLUMN_ID
, DECODE(D.NO , 0, '-- ' || REPLACE(TBL.COMMENTS, CHR(10), ' ')
, 1, 'MERGE INTO '
, LPAD(' ', 8, CHR(32))) AS COL1
, DECODE(D.NO , 1, TBL.TABLE_NAME || ' T'
, 3, LPAD(' ', 8, CHR(32))
, 5, 'WHEN '
, 10, 'WHEN '
, LPAD(' ', 8, CHR(32))) AS COL2
, DECODE(D.NO , 2, RPAD(',', 8, CHR(32))
, 3, RPAD('FROM DUAL)', 12, CHR(32))
, 5, 'MATCHED THEN'
, 6, RPAD('WHERE', 8, CHR(32))
, 8, RPAD('DELETE WHERE', 16, CHR(32))
, 10, 'NOT MATCHED THEN'
, 12, RPAD('WHERE', 8, CHR(32))) AS COL3
, DECODE(D.NO , 2, RPAD('#check_delete#', 60, CHR(32))
, 3, 'D'
, 6, '(1 = 1)'
, 8, '(D.CHECK_DELETE = ' || CHR(39) || 'true' || CHR(39) || ')'
, 12, '(1 = 1);') AS COL4
, DECODE(D.NO , 2, RPAD('AS CHECK_DELETE', 40, CHR(32))) AS COL5
, DECODE(D.NO , 2, '-- 삭제 Check') AS COL6
FROM USER_TAB_COMMENTS TBL
JOIN (SELECT LEVEL - 1 AS NO
FROM DUAL
CONNECT BY LEVEL <= 13) D ON D.NO <> 11
)
SELECT T.*
FROM TBL_DML T
WHERE TABLE_NAME = UPPER(P_TABLE_NAME)
ORDER BY T.NO, T.COLUMN_ID)
LOOP
V_ROW.R_COL01 := CSR_TBL.COL1;
V_ROW.R_COL02 := CSR_TBL.COL2;
V_ROW.R_COL03 := CSR_TBL.COL3;
V_ROW.R_COL04 := CSR_TBL.COL4;
V_ROW.R_COL05 := CSR_TBL.COL5;
V_ROW.R_COL06 := CSR_TBL.COL6;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Primary Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_PK (P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (WITH CST_TBL
AS
( SELECT CST.*
, COL.COLUMN_NAME
, COL.POSITION
FROM ( SELECT S.OWNER
, S.CONSTRAINT_NAME
, S.TABLE_NAME
, S.STATUS
, S.CONSTRAINT_TYPE
, S.LAST_CHANGE
FROM USER_CONSTRAINTS S
WHERE S.CONSTRAINT_TYPE IN ('P', 'U')) CST
JOIN USER_CONS_COLUMNS COL ON COL.OWNER = CST.OWNER
AND COL.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
AND COL.TABLE_NAME = CST.TABLE_NAME
)
SELECT 'ALTER TABLE ' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';' AS SCRIPT_DROP
, 'ALTER TABLE ' || TABLE_NAME || ' ADD CONSTRAINT ' || CONSTRAINT_NAME ||
DECODE(CONSTRAINT_TYPE, 'P', ' PRIMARY KEY ', 'U', ' UNIQUE ') || '(' || CST_COLUMNS || ');' AS SCRIPT_ADD
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' ENABLE;' AS SCRIPT_ENABLE
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' DISABLE;' AS SCRIPT_DISABLE
, TO_CHAR(LAST_CHANGE, 'YYYY/MM/DD HH24:MI:SS') AS MODIFY_DTIME
FROM (SELECT TABLE_NAME AS TABLE_NAME
, CONSTRAINT_NAME AS CONSTRAINT_NAME
, CONSTRAINT_TYPE AS CONSTRAINT_TYPE
, LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY POSITION) AS CST_COLUMNS
, MIN(LAST_CHANGE) AS LAST_CHANGE
FROM CST_TBL
GROUP BY TABLE_NAME
, CONSTRAINT_NAME
, CONSTRAINT_TYPE)
WHERE TABLE_NAME LIKE UPPER(P_TABLE_NAME))
LOOP
V_ROW.R_COL01 := CSR_TBL.SCRIPT_DROP;
V_ROW.R_COL02 := CSR_TBL.SCRIPT_ADD;
V_ROW.R_COL03 := CSR_TBL.SCRIPT_ENABLE;
V_ROW.R_COL04 := CSR_TBL.SCRIPT_DISABLE;
V_ROW.R_COL05 := CSR_TBL.MODIFY_DTIME;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Foreign Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_FK (P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (WITH CST_TBL
AS
( SELECT CST.*
, COL.COLUMN_NAME
, COL.POSITION
FROM ( SELECT S.OWNER
, S.CONSTRAINT_NAME
, S.TABLE_NAME
, S.R_OWNER
, S.R_CONSTRAINT_NAME
, S.DELETE_RULE
, S.STATUS
, S.LAST_CHANGE
FROM USER_CONSTRAINTS S
START WITH S.CONSTRAINT_TYPE IN ('P', 'U')
--AND S.TABLE_NAME = UPPER(P_TABLE_NAME)
CONNECT BY PRIOR S.OWNER = S.R_OWNER
AND PRIOR S.CONSTRAINT_NAME = S.R_CONSTRAINT_NAME) CST
JOIN USER_CONS_COLUMNS COL ON COL.OWNER = CST.OWNER
AND COL.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
AND COL.TABLE_NAME = CST.TABLE_NAME
)
SELECT 'ALTER TABLE ' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';' AS SCRIPT_DROP
, 'ALTER TABLE ' || TABLE_NAME || ' ADD CONSTRAINT ' || CONSTRAINT_NAME || ' FOREIGN KEY (' || FK_COLUMNS || ') REFERENCES '
|| R_TABLE_NAME || ' (' || PK_COLUMNS || ')' || DECODE(DELETE_RULE, 'SET NULL', ' ON DELETE ' || DELETE_RULE) || ';' AS SCRIPT_ADD
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' ENABLE;' AS SCRIPT_ENABLE
, 'ALTER TABLE ' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' DISABLE;' AS SCRIPT_DISABLE
, TO_CHAR(LAST_CHANGE, 'YYYY/MM/DD HH24:MI:SS') AS MODIFY_DTIME
FROM (SELECT T02.TABLE_NAME AS TABLE_NAME
, T02.CONSTRAINT_NAME AS CONSTRAINT_NAME
, LISTAGG(T02.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY T02.POSITION) AS FK_COLUMNS
, T01.TABLE_NAME AS R_TABLE_NAME
, LISTAGG(T01.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY T01.POSITION) AS PK_COLUMNS
, T02.DELETE_RULE AS DELETE_RULE
, MIN(T02.LAST_CHANGE) AS LAST_CHANGE
FROM CST_TBL T01
JOIN CST_TBL T02 ON T02.R_OWNER = T01.OWNER
AND T02.R_CONSTRAINT_NAME = T01.CONSTRAINT_NAME
AND T02.POSITION = T01.POSITION
GROUP BY T02.TABLE_NAME
, T02.CONSTRAINT_NAME
, T01.TABLE_NAME
, T02.DELETE_RULE)
WHERE R_TABLE_NAME LIKE UPPER(P_TABLE_NAME))
LOOP
V_ROW.R_COL01 := CSR_TBL.SCRIPT_DROP;
V_ROW.R_COL02 := CSR_TBL.SCRIPT_ADD;
V_ROW.R_COL03 := CSR_TBL.SCRIPT_ENABLE;
V_ROW.R_COL04 := CSR_TBL.SCRIPT_DISABLE;
V_ROW.R_COL05 := CSR_TBL.MODIFY_DTIME;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Insert 구문 추출
FUNCTION FNC_DML_INS (P_TABLE_NAME IN VARCHAR2) RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (SELECT COL1
, COL2
, COL3
, COL4
FROM (SELECT TAB.TABLE_NAME
, DECODE(D.NO, 1, 0
, 2, 99998
, 3, 99999) AS NO
, DECODE(D.NO, 1, 'SELECT ' || CHR(39) || 'INSERT INTO ' || TAB.TABLE_NAME || ' VALUES (' || CHR(39)
, 2, '|| ' || CHR(39) || ');' || CHR(39) || ' AS INS_SCRIPT'
, 3, 'FROM ' || TAB.TABLE_NAME || ';') AS COL1
, NULL AS COL2
, NULL AS COL3
, NULL AS COL4
FROM USER_TAB_COMMENTS TAB
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 3) D ON 1 = 1
UNION ALL
SELECT COL.TABLE_NAME
, COL.COLUMN_ID AS NO
, DECODE(COL.COLUMN_ID, 1, ' ', '|| CHR(44) ') AS COL1
, CASE
WHEN COL.DATA_TYPE IN ('NVARCHAR2', 'VARCHAR2', 'CHAR', 'LONG', 'NUMBER') THEN '|| CHR(39) '
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)') THEN '|| ' || CHR(39) || 'TO_DATE(' || CHR(39) || '|| CHR(39) '
ELSE ' '
END AS COL2
, '|| ' || RPAD(
CASE
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)') THEN 'TO_CHAR('
END
|| COL.COLUMN_NAME ||
CASE
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)') THEN ', ' || CHR(39) || 'YYYYMMDDHH24MISS' || CHR(39) || ')'
END
, 70, ' ') || ' ' AS COL3
, CASE
WHEN COL.DATA_TYPE IN ('NVARCHAR2', 'VARCHAR2', 'CHAR', 'LONG', 'NUMBER') THEN '|| CHR(39) '
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)') THEN '|| CHR(39) || CHR(44) || CHR(39) || ' || CHR(39) || 'YYYYMMDDHH24MISS' || CHR(39) || ' || CHR(39) || ' || CHR(39) || ')' || CHR(39)
ELSE ' '
END AS COL4
FROM USER_TAB_COLUMNS COL
) T
WHERE T.TABLE_NAME = UPPER(P_TABLE_NAME)
ORDER BY T.NO ASC)
LOOP
V_ROW.R_COL01 := CSR_TBL.COL1;
V_ROW.R_COL02 := CSR_TBL.COL2;
V_ROW.R_COL03 := CSR_TBL.COL3;
V_ROW.R_COL04 := CSR_TBL.COL4;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Table 정의서
FUNCTION FNC_TBL_SCHEMA ( P_TABLE_NAME IN VARCHAR2 := '%'
, P_SYSTEM_NAME IN VARCHAR2 := 'SYSTEM'
, P_WRITER_NAME IN VARCHAR2 := 'SYSTEM') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (SELECT COL01
, COL02
, COL03
, COL04
, COL05
, COL06
, COL07
, COL08
, COL09
, COL10
FROM (SELECT TABLE_NAME AS TABLE_NAME
, NO AS GROUP_NO
, 1 AS ORDER_NO
, DECODE(NO, 2, '시스템명', 3, '테이블ID', 4, '테이블명', 5, 'NO') AS COL01
, DECODE(NO, 2, P_SYSTEM_NAME, 3, TABLE_NAME, 4, REPLACE(COMMENTS, CHR(10), ' '), 5, '컬럼ID') AS COL02
, DECODE(NO, 5, '컬럼명') AS COL03
, DECODE(NO, 5, 'KEY') AS COL04
, DECODE(NO, 2, '작성일', 3, '작성자', 4, NULL, 5, 'TYPE') AS COL05
, DECODE(NO, 2, TO_CHAR(SYSDATE, 'YYYY.MM.DD'), 3, P_WRITER_NAME, 4, NULL, 5, '길이') AS COL06
, DECODE(NO, 5, 'BYTE') AS COL07
, DECODE(NO, 5, 'NULLABLE') AS COL08
, DECODE(NO, 5, 'DEFAULT') AS COL09
, DECODE(NO, 5, '특성') AS COL10
FROM (SELECT T.TABLE_NAME
, T.COMMENTS
, D.NO
FROM USER_TAB_COMMENTS T
, (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 5) D
WHERE T.TABLE_TYPE = 'TABLE')
UNION ALL
SELECT COL.TABLE_NAME AS TABLE_NAME
, 6 AS GROUP_NO
, COL.COLUMN_ID AS ORDER_NO
, TO_CHAR(COL.COLUMN_ID) AS COL01
, COL.COLUMN_NAME AS COL02
, REPLACE(CMT.COMMENTS, CHR(10), ' ') AS COL03
, DECODE(CST.ISPK, 'Y', 'PK', ' ') AS COL04
, COL.DATA_TYPE AS COL05
, CASE
WHEN COL.DATA_TYPE = 'NUMBER' AND COL.DATA_SCALE IS NOT NULL THEN '(' || NVL(COL.DATA_PRECISION, 38) || ', ' || COL.DATA_SCALE || ')'
WHEN COL.DATA_TYPE IN ('CHAR', 'VARCHAR2') THEN '(' || COL.CHAR_LENGTH || DECODE(COL.CHAR_USED, 'B', ' BYTE', 'C', ' CHAR') || ')'
ELSE ' '
END AS COL06
, TO_CHAR(COL.DATA_LENGTH) AS COL07
, DECODE(COL.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') AS COL08
, TRIM(PKG_MGM.FNC_DATA_DEFAULT(COL.TABLE_NAME, COL.COLUMN_NAME)) AS COL09
, TRIM(DECODE(CST.ISFK, 'Y', 'FK', ' ') || ' ' || DECODE(CST.ISAK, 'Y', 'AK', ' ')) AS COL10
FROM USER_TAB_COLUMNS COL
JOIN USER_TAB_COMMENTS TAB ON TAB.TABLE_NAME = COL.TABLE_NAME
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || CTC.POSITION || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS FK
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.TABLE_NAME = CTC.TABLE_NAME
WHERE CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
WHERE TAB.TABLE_TYPE = 'TABLE')
WHERE TABLE_NAME LIKE UPPER(P_TABLE_NAME)
ORDER BY TABLE_NAME
, GROUP_NO
, ORDER_NO)
LOOP
V_ROW.R_COL01 := CSR_TBL.COL01;
V_ROW.R_COL02 := CSR_TBL.COL02;
V_ROW.R_COL03 := CSR_TBL.COL03;
V_ROW.R_COL04 := CSR_TBL.COL04;
V_ROW.R_COL05 := CSR_TBL.COL05;
V_ROW.R_COL06 := CSR_TBL.COL06;
V_ROW.R_COL07 := CSR_TBL.COL07;
V_ROW.R_COL08 := CSR_TBL.COL08;
V_ROW.R_COL09 := CSR_TBL.COL09;
V_ROW.R_COL10 := CSR_TBL.COL10;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Index 스크립트 추출
FUNCTION FNC_DDL_IDX (P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_ROW IN (SELECT IDX.TABLE_NAME
, IDX.INDEX_NAME
, 'CREATE ' || DECODE(IDX.UNIQUENESS, 'UNIQUE', 'UNIQUE ', '') || 'INDEX ' || IDX.INDEX_NAME || ' ON ' || IDX.TABLE_OWNER || '.' || IDX.TABLE_NAME
|| ' (' ||
(SELECT LISTAGG(NVL(PKG_MGM.FNC_COLUMN_EXPRESSION(COL.TABLE_NAME, COL.INDEX_NAME, COL.COLUMN_POSITION), COL.COLUMN_NAME) || ' ' || COL.DESCEND, ', ') WITHIN GROUP (ORDER BY COL.COLUMN_POSITION)
FROM USER_IND_COLUMNS COL
WHERE COL.TABLE_NAME = IDX.TABLE_NAME
AND COL.INDEX_NAME = IDX.INDEX_NAME)
|| ') '
|| 'TABLESPACE ' || IDX.TABLESPACE_NAME || ';' AS CREATE_SCRIPT
, 'ALTER INDEX ' || IDX.INDEX_NAME || ' REBUILD TABLESPACE ' || IDX.TABLESPACE_NAME || ';' AS REBULT_SCRIPT
, 'DROP INDEX ' || IDX.INDEX_NAME || ';' AS DROP_SCRIPT
, TO_CHAR(OBJ.LAST_DDL_TIME, 'YYYY/MM/DD HH24:MI:SS') AS MODIFY_DTIME
FROM USER_INDEXES IDX
JOIN USER_OBJECTS OBJ ON OBJ.OBJECT_NAME = IDX.INDEX_NAME
WHERE IDX.TABLE_NAME LIKE UPPER(P_TABLE_NAME))
LOOP
V_ROW.R_COL01 := CSR_ROW.TABLE_NAME;
V_ROW.R_COL02 := CSR_ROW.INDEX_NAME;
V_ROW.R_COL03 := CSR_ROW.DROP_SCRIPT;
V_ROW.R_COL04 := CSR_ROW.CREATE_SCRIPT;
V_ROW.R_COL05 := CSR_ROW.REBULT_SCRIPT;
V_ROW.R_COL06 := CSR_ROW.MODIFY_DTIME;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- Select 스크립트 추출
FUNCTION FNC_DML_SEL (P_TABLE_LIST IN VARCHAR2) RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (WITH TBL_LIST
AS
( SELECT T.TABLE_NAME
FROM (SELECT NO
, TRIM(UPPER(DECODE(NO , 1, TABLE01
, 2, TABLE02
, 3, TABLE03
, 4, TABLE04
, 5, TABLE05
, 6, TABLE06
, 7, TABLE07
, 8, TABLE08
, 9, TABLE09
, 10, TABLE10))) AS TABLE_NAME
FROM (SELECT NO
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 1) AS TABLE01
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 2) AS TABLE02
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 3) AS TABLE03
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 4) AS TABLE04
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 5) AS TABLE05
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 6) AS TABLE06
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 7) AS TABLE07
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 8) AS TABLE08
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 9) AS TABLE09
, REGEXP_SUBSTR(TABLE_LIST, '[^, ]*[^, ]', 1, 10) AS TABLE10
FROM (SELECT P_TABLE_LIST AS TABLE_LIST FROM DUAL)
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 10) ON 1 = 1)) T
WHERE T.TABLE_NAME IS NOT NULL
)
, TBL_CST
AS
( SELECT CST.OWNER
, CST.CONSTRAINT_NAME
, CST.CONSTRAINT_TYPE
, CST.TABLE_NAME
, CST.R_OWNER
, CST.R_CONSTRAINT_NAME
, SIGN(COUNT(*) OVER (PARTITION BY CST.TABLE_NAME, CST.R_CONSTRAINT_NAME ORDER BY CST.TABLE_NAME, CST.R_CONSTRAINT_NAME) - 1) AS TYPE
FROM USER_CONSTRAINTS CST
WHERE CST.TABLE_NAME IN (SELECT TABLE_NAME FROM TBL_LIST)
START WITH CST.CONSTRAINT_TYPE IN ('P', 'U')
AND CST.TABLE_NAME IN (SELECT TABLE_NAME FROM TBL_LIST)
CONNECT BY PRIOR CST.OWNER = CST.R_OWNER
AND PRIOR CST.CONSTRAINT_NAME = CST.R_CONSTRAINT_NAME
)
, TBL_REF
AS
( SELECT T01.TYPE
, DECODE(T01.TABLE_NAME, T02.TABLE_NAME, 'Y', 'N') AS SELF_JOIN
, DECODE(T01.TYPE, 0, T01.OWNER , 1, T02.OWNER) AS L_OWNER
, DECODE(T01.TYPE, 0, T01.TABLE_NAME , 1, T02.TABLE_NAME) AS L_TABLE_NAME
, DECODE(T01.TYPE, 0, T01.CONSTRAINT_TYPE , 1, T02.CONSTRAINT_TYPE) AS L_CONSTRAINT_TYPE
, DECODE(T01.TYPE, 0, T01.CONSTRAINT_NAME , 1, T02.CONSTRAINT_NAME) AS L_CONSTRAINT_NAME
, DECODE(T01.TYPE, 0, T02.OWNER , 1, T01.OWNER) AS R_OWNER
, DECODE(T01.TYPE, 0, T02.TABLE_NAME , 1, T01.TABLE_NAME) AS R_TABLE_NAME
, DECODE(T01.TYPE, 0, T02.CONSTRAINT_TYPE , 1, T01.CONSTRAINT_TYPE) AS R_CONSTRAINT_TYPE
, DECODE(T01.TYPE, 0, T02.CONSTRAINT_NAME , 1, T01.CONSTRAINT_NAME) AS R_CONSTRAINT_NAME
FROM TBL_CST T01
LEFT OUTER JOIN TBL_CST T02 ON T02.OWNER = T01.R_OWNER
AND T02.CONSTRAINT_NAME = T01.R_CONSTRAINT_NAME
)
, TBL_FULL
AS
( SELECT ROW_NUMBER() OVER (ORDER BY T.DRV_TBL, T.TYPE, T.REF_TBL, T.R_TABLE_NAME) AS NO
, T.*
, DECODE(T.DRV_TBL + REF_TBL, 0, 'Y', 'N') AS ISDRVTBL
FROM ( SELECT SIGN(COUNT(DECODE(T.SELF_JOIN, 'N', 1)) OVER (PARTITION BY T.L_TABLE_NAME) - 1) AS DRV_TBL
, NVL2(R_TABLE_NAME, 1, 0) AS REF_TBL
, T.*
FROM TBL_REF T) T
WHERE (T.DRV_TBL = 0 OR REF_TBL = 1) AND L_OWNER IS NOT NULL
)
, TBL_L
AS
( SELECT TBL.NO AS L_NO
, 'T' || TO_CHAR(TBL.NO, 'FM00') AS L_ALIAS
, TBL.L_OWNER AS L_OWNER
, TBL.L_TABLE_NAME AS L_TABLE_NAME
, TBL.L_CONSTRAINT_NAME AS L_CONSTRAINT_NAME
, TBL.ISDRVTBL AS L_ISDRVTBL
, FLOOR(MAX(LENGTH(TBL.L_TABLE_NAME)) OVER () / 4) * 4 + 4 AS L_TABLEN
, TBL.SELF_JOIN AS L_SELF_JOIN
FROM TBL_FULL TBL
)
, TBL_R
AS
( SELECT TBL.NO AS R_NO
, (SELECT L.L_ALIAS
FROM TBL_L L
WHERE L.L_OWNER = TBL.R_OWNER
AND L.L_TABLE_NAME = TBL.R_TABLE_NAME
AND L.L_SELF_JOIN = 'N' ) AS R_ALIAS
, TBL.R_OWNER AS R_OWNER
, TBL.R_TABLE_NAME AS R_TABLE_NAME
, TBL.R_CONSTRAINT_NAME AS R_CONSTRAINT_NAME
, TBL.ISDRVTBL AS R_ISDRVTBL
FROM TBL_FULL TBL
)
, TBL_JOIN
AS
( SELECT L.L_NO
, DECODE(L.L_SELF_JOIN, 'Y', R.R_ALIAS, L.L_ALIAS) AS L_ALIAS
, L.L_OWNER
, L.L_TABLE_NAME
, L.L_CONSTRAINT_NAME
, L.L_ISDRVTBL
, L.L_TABLEN
, L.L_SELF_JOIN
, L.L_COL
, L.L_POS
, L.L_COLLEN
, R.R_NO
, R.R_ALIAS
, R.R_OWNER
, R.R_TABLE_NAME
, R.R_CONSTRAINT_NAME
, R.R_ISDRVTBL
, R.R_COL
, R.R_POS
, R.R_COLLEN
, DECODE(L.L_POS, FIRST_VALUE(L.L_POS) OVER (PARTITION BY L.L_NO ORDER BY L.L_NO, L.L_POS), 'Y', 'N') AS ISFIRSTCOL
, DECODE(L.L_POS, LAST_VALUE(L.L_POS) OVER (PARTITION BY L.L_NO ORDER BY L.L_NO, L.L_POS
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 'Y', 'N') AS ISLASTCOL
FROM (SELECT L.*
, C.COLUMN_NAME AS L_COL
, C.POSITION AS L_POS
, FLOOR(MAX(LENGTH(C.COLUMN_NAME)) OVER () / 4) * 4 + 4 AS L_COLLEN
FROM TBL_L L
LEFT OUTER JOIN USER_CONS_COLUMNS C ON C.OWNER = L.L_OWNER
AND C.CONSTRAINT_NAME = L.L_CONSTRAINT_NAME
AND C.TABLE_NAME = L.L_TABLE_NAME
AND 'N' = L.L_ISDRVTBL) L
LEFT OUTER JOIN (SELECT R.*
, C.COLUMN_NAME AS R_COL
, C.POSITION AS R_POS
, FLOOR(MAX(LENGTH(C.COLUMN_NAME)) OVER () / 4) * 4 + 4 AS R_COLLEN
FROM TBL_R R
JOIN USER_CONS_COLUMNS C ON C.OWNER = R.R_OWNER
AND C.CONSTRAINT_NAME = R.R_CONSTRAINT_NAME
AND C.TABLE_NAME = R.R_TABLE_NAME) R ON R.R_NO = L.L_NO
AND R.R_POS = L.L_POS
)
SELECT NO
, TBL_NO
, COL_NO
, COL1
, COL2
, COL3
, COL4
FROM ( SELECT 0 AS NO
, T01.L_NO AS TBL_NO
, T02.COLUMN_ID AS COL_NO
, RPAD(DECODE(T01.L_ALIAS || T02.COLUMN_ID, 'T011', 'SELECT', ','), 8, ' ') AS COL1
, T01.L_ALIAS || '.' || T02.COLUMN_NAME AS COL2
, T02.COLUMN_ALIAS AS COL3
, T02.COLUMN_COMMENT AS COL4
FROM (SELECT L_NO
, L_ALIAS
, L_TABLE_NAME
FROM TBL_L
WHERE L_SELF_JOIN = 'N') T01
JOIN (SELECT RPAD(COL.COLUMN_NAME, 40, ' ') AS COLUMN_NAME
, RPAD('AS ' || COL.COLUMN_NAME, 40, ' ') AS COLUMN_ALIAS
, '-- ' || CST.PK || REPLACE(CMT.COMMENTS, CHR(10), ' ') || ' ' || TO_CHAR(TRIM(CST.AK || ' ' || CST.FK)) AS COLUMN_COMMENT
, COL.TABLE_NAME
, COL.COLUMN_ID
FROM USER_TAB_COLUMNS COL
JOIN USER_COL_COMMENTS CMT ON CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
LEFT OUTER JOIN (SELECT CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'Y')) AS ISPK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'P', 'PK(' || CTC.POSITION || ') ')) AS PK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'U', 'Y')) AS ISAK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'U', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS AK
, MIN( DECODE(CST.CONSTRAINT_TYPE, 'R', 'Y')) AS ISFK
, LISTAGG( DECODE(CST.CONSTRAINT_TYPE, 'R', CTC.CONSTRAINT_NAME || '(' || CTC.POSITION || ')'), ', ') WITHIN GROUP (ORDER BY CST.LAST_CHANGE) AS FK
FROM USER_CONS_COLUMNS CTC
JOIN USER_CONSTRAINTS CST ON CST.OWNER = CTC.OWNER
AND CST.CONSTRAINT_NAME = CTC.CONSTRAINT_NAME
AND CST.TABLE_NAME = CTC.TABLE_NAME
WHERE CST.CONSTRAINT_TYPE IN ('P', 'R', 'U')
GROUP BY CTC.OWNER
, CTC.TABLE_NAME
, CTC.COLUMN_NAME) CST ON CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME) T02 ON T02.TABLE_NAME = T01.L_TABLE_NAME
UNION ALL
SELECT 1 AS NO
, DENSE_RANK() OVER (ORDER BY NVL(T.R_ALIAS, 'T00'), T.L_ALIAS) AS TBL_NO
, NVL(T.L_POS, 0) AS COL_NO
, DECODE(T.L_ISDRVTBL, 'Y', 'FROM ' || RPAD(T.L_TABLE_NAME, T.L_TABLEN, CHR(32))
, DECODE(T.ISFIRSTCOL, 'Y', DECODE(T.L_SELF_JOIN, 'Y', ' ' || RPAD('CONNECT', T.L_TABLEN, CHR(32)), 'JOIN ' || RPAD(T.L_TABLE_NAME, T.L_TABLEN, CHR(32)))
, LPAD(' ', T.L_TABLEN + 5, CHR(32))))
|| DECODE(T.L_ISDRVTBL, 'Y', ' ' || T.L_ALIAS
, RPAD(DECODE(T.ISFIRSTCOL, 'Y', DECODE(T.L_SELF_JOIN, 'Y', ' ', ' ' || T.L_ALIAS), ' '), 5, CHR(32))) AS COL1
, DECODE(T.L_ISDRVTBL, 'N', DECODE(T.ISFIRSTCOL, 'Y', DECODE(T.L_SELF_JOIN, 'Y', 'BY ', 'ON '), 'AND ') || T.L_ALIAS || '.' || RPAD(T.L_COL, T.L_COLLEN, CHR(32))) AS COL2
, DECODE(T.L_ISDRVTBL, 'N', '= ' || DECODE(T.L_SELF_JOIN, 'Y', 'PRIOR ') || T.R_ALIAS || '.' || T.R_COL) AS COL3
, NULL AS COL4
FROM TBL_JOIN T
UNION ALL
SELECT D.NO - 1 AS NO
, T.L_NO AS TBL_NO
, 9999 AS COL_NO
, NULL AS COL1
, NULL AS COL2
, NULL AS COL3
, NULL AS COL4
FROM TBL_L T
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 2) D ON 1 = 1
WHERE T.L_SELF_JOIN = 'N')
ORDER BY NO, TBL_NO, COL_NO)
LOOP
V_ROW.R_COL01 := CSR_TBL.COL1;
V_ROW.R_COL02 := CSR_TBL.COL2;
V_ROW.R_COL03 := CSR_TBL.COL3;
V_ROW.R_COL04 := CSR_TBL.COL4;
PIPE ROW(V_ROW);
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- COLUMN의 DATA_DEFAULT 가져오기
FUNCTION FNC_DATA_DEFAULT ( TABLE_NAME IN VARCHAR2
, COLUMN_NAME IN VARCHAR2) RETURN VARCHAR2
IS
V_RTN VARCHAR2(32767);
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ' || CHR(39) || TABLE_NAME || CHR(39) || ' AND COLUMN_NAME = ' || CHR(39) || COLUMN_NAME || CHR(39);
EXECUTE IMMEDIATE V_SQL INTO V_RTN;
V_RTN := TRIM(SUBSTR (V_RTN, 1, 4000));
RETURN V_RTN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
-- COLUMN의 COLUMN_EXPRESSION 가져오기
FUNCTION FNC_COLUMN_EXPRESSION ( TABLE_NAME IN VARCHAR2
, INDEX_NAME IN VARCHAR2
, COLUMN_POSITION IN NUMBER) RETURN VARCHAR2
IS
V_RTN VARCHAR2(32767);
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'SELECT COLUMN_EXPRESSION FROM USER_IND_EXPRESSIONS WHERE TABLE_NAME = ' || CHR(39) || TABLE_NAME || CHR(39) || ' AND INDEX_NAME = ' || CHR(39) || INDEX_NAME || CHR(39) || ' AND COLUMN_POSITION = ' || COLUMN_POSITION;
EXECUTE IMMEDIATE V_SQL INTO V_RTN;
V_RTN := TRIM(SUBSTR (V_RTN, 1, 4000));
RETURN V_RTN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
RETURN NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END PKG_MGM;
/