-- ANSI 파일
-- wrap iname=PKG_MGM.sql
-- SQLPLUS USER_ID/PASSWORD@SERVICE_ID @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_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2
, P_ALIAS IN VARCHAR2 := '') RETURN TYPE_TABLE PIPELINED;
-- MERGE INTO 스크립트 추출
FUNCTION FNC_DML_MRG ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2) RETURN TYPE_TABLE PIPELINED;
-- Primary Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_PK ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED;
-- Foreign Key DROP, ADD 스크립트 추출
FUNCTION FNC_CST_FK ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED;
-- Insert 구문 추출
FUNCTION FNC_DML_INS ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2) RETURN TYPE_TABLE PIPELINED;
-- Table 정의서
FUNCTION FNC_TBL_SCHEMA ( P_OWNER IN VARCHAR2
, 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_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED;
-- Create Table 스크립트 추출
FUNCTION FNC_DDL_CT ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR) RETURN TYPE_TABLE PIPELINED;
-- COLUMN의 DATA_DEFAULT 가져오기
FUNCTION FNC_DATA_DEFAULT ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2
, P_COLUMN_NAME IN VARCHAR2) RETURN VARCHAR2;
-- COLUMN의 COLUMN_EXPRESSION 가져오기
FUNCTION FNC_COLUMN_EXPRESSION ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2
, P_INDEX_NAME IN VARCHAR2
, P_COLUMN_POSITION IN NUMBER) RETURN VARCHAR2;
END PKG_MGM;
/
CREATE OR REPLACE PACKAGE BODY PKG_MGM
IS
-- TEMP TABLE 생성 스크립트 추출
FUNCTION FNC_DDL_TBL ( P_OWNER IN VARCHAR2
, 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
, RPAD(NVL2(P_ALIAS, P_ALIAS || '.', P_ALIAS) || COL.COLUMN_NAME, 40, ' ') AS COL2
, RPAD('AS ' || COL.COLUMN_NAME, 40, ' ') AS COL3
, '-- ' || CST.PK || REPLACE(REPLACE(CMT.COMMENTS, CHR(10), ''), CHR(13), '') || ' ' || TO_CHAR(TRIM(CST.AK || ' ' || CST.FK)) AS COL4
, COL.OWNER
, COL.TABLE_NAME
, COL.COLUMN_ID
FROM ALL_TAB_COLUMNS COL
JOIN ALL_COL_COMMENTS CMT ON CMT.OWNER = COL.OWNER
AND 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 ALL_CONS_COLUMNS CTC
JOIN ALL_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.OWNER = COL.OWNER
AND 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(REPLACE(T.COMMENTS, CHR(10), ''), CHR(13), '')) AS COL4
, T.OWNER
, T.TABLE_NAME
, DECODE(NO, 1, -1, 2, 0) AS COLUMN_ID
FROM ALL_TAB_COMMENTS T
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 3) D ON 1 = 1
ORDER BY COLUMN_ID ASC)
WHERE OWNER = UPPER(P_OWNER)
AND 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_OWNER IN VARCHAR2
, 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.OWNER
, COL.TABLE_NAME
, D.NO
, COL.COLUMN_ID
, COL.COLUMN_NAME
, '-- ' || CST.PK || REPLACE(REPLACE(CMT.COMMENTS, CHR(10), ''), CHR(13), '') || ' ' || TRIM(CST.AK || ' ' || CST.FK ) AS COMMENTS
, CST.ISPK
, COL.NULLABLE
, PKG_MGM.FNC_DATA_DEFAULT(COL.OWNER, COL.TABLE_NAME, COL.COLUMN_NAME) AS DATA_DEFAULT
, DECODE(COL.COLUMN_NAME, 'SYS_ID', '#{g.tenant}', '#{p.' || 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 ALL_TAB_COLUMNS COL
JOIN ALL_COL_COMMENTS CMT ON CMT.OWNER = COL.OWNER
AND 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 ALL_CONS_COLUMNS CTC
JOIN ALL_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.OWNER = COL.OWNER
AND 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.OWNER
, 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 || ', ' || C.DATA_DEFAULT || ')', C.PARAM_COLUMN) , C.TAB * 15, CHR(32))
, 3, RPAD('S.' || 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('S.' || 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('= S.' || 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.OWNER
, TBL.TABLE_NAME
, D.NO
, 0 AS COLUMN_ID
, DECODE(D.NO , 0, '-- ' || REPLACE(REPLACE(TBL.COMMENTS, CHR(10), ''), CHR(13), '')
, 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('#{p.del_yn}', 60, CHR(32))
, 3, 'S'
, 6, '(1 = 1)'
, 8, '(S.DEL_YN = ' || CHR(39) || 'Y' || CHR(39) || ')'
, 12, '(1 = 1);') AS COL4
, DECODE(D.NO , 2, RPAD('AS DEL_YN', 40, CHR(32))) AS COL5
, DECODE(D.NO , 2, '-- 삭제여부 [Y/N]') AS COL6
FROM ALL_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 T.OWNER = UPPER(P_OWNER)
AND T.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_OWNER IN VARCHAR2
, 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 ALL_CONSTRAINTS S
WHERE S.CONSTRAINT_TYPE IN ('P', 'U')) CST
JOIN ALL_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 ' || OWNER || '.' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';' AS SCRIPT_DROP
, 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ADD CONSTRAINT ' || CONSTRAINT_NAME ||
DECODE(CONSTRAINT_TYPE, 'P', ' PRIMARY KEY ', 'U', ' UNIQUE ') || '(' || CST_COLUMNS || ');' AS SCRIPT_ADD
, 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' ENABLE;' AS SCRIPT_ENABLE
, 'ALTER TABLE ' || OWNER || '.' || 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 OWNER AS OWNER
, 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 OWNER
, TABLE_NAME
, CONSTRAINT_NAME
, CONSTRAINT_TYPE)
WHERE OWNER = UPPER(P_OWNER)
AND 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_OWNER IN VARCHAR2
, 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 ALL_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 ALL_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 ' || OWNER || '.' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';' AS SCRIPT_DROP
, 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ADD CONSTRAINT ' || CONSTRAINT_NAME || ' FOREIGN KEY (' || FK_COLUMNS || ') REFERENCES '
|| R_OWNER || '.' || R_TABLE_NAME || ' (' || PK_COLUMNS || ')' || DECODE(DELETE_RULE, 'SET NULL', ' ON DELETE ' || DELETE_RULE) || ';' AS SCRIPT_ADD
, 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MODIFY CONSTRAINT ' || CONSTRAINT_NAME || ' ENABLE;' AS SCRIPT_ENABLE
, 'ALTER TABLE ' || OWNER || '.' || 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.OWNER AS OWNER
, 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.OWNER AS R_OWNER
, 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.OWNER
, T02.TABLE_NAME
, T02.CONSTRAINT_NAME
, T01.OWNER
, T01.TABLE_NAME
, T02.DELETE_RULE)
WHERE R_OWNER = UPPER(P_OWNER)
AND 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_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2) RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_TBL IN (SELECT COL1
, COL2
, COL3
, COL4
, COL5
, COL6
FROM (SELECT TAB.OWNER
, 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
, NULL AS COL5
, NULL AS COL6
FROM ALL_TAB_COMMENTS TAB
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 3) D ON 1 = 1
UNION ALL
SELECT COL.OWNER
, 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)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') THEN '|| ' || CHR(39) || 'TO_DATE(' || CHR(39) || '|| CHR(39) '
ELSE ' '
END AS COL2
, '|| ' || RPAD(
CASE
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') THEN 'TO_CHAR('
END
|| COL.COLUMN_NAME ||
CASE
WHEN COL.DATA_TYPE IN ('DATE', 'TIMESTAMP(0)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') 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)', 'TIMESTAMP(0) WITH TIME ZONE', 'TIMESTAMP(0) WITH LOCAL TIME ZONE') THEN '|| CHR(39) || CHR(44) || CHR(39) || ' || CHR(39) || 'YYYYMMDDHH24MISS' || CHR(39) || ' || CHR(39) || ' || CHR(39) || ')' || CHR(39)
ELSE ' '
END AS COL4
, '--' AS COL5
, 'REPLACE(' || COL.COLUMN_NAME || ', CHR(39), CHR(39) || ' || CHR(39) || ' || CHR(39) || ' || CHR(39) || ' || CHR(39))' AS COL6
FROM ALL_TAB_COLUMNS COL
) T
WHERE T.OWNER = UPPER(P_OWNER)
AND 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;
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;
-- Table 정의서
FUNCTION FNC_TBL_SCHEMA ( P_OWNER IN VARCHAR2
, 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 T.OWNER AS OWNER
, T.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, T.TABLE_NAME, 4, REPLACE(T.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.OWNER
, T.TABLE_NAME
, REPLACE(REPLACE(T.COMMENTS, CHR(10), ''), CHR(13), '') AS COMMENTS
, D.NO
FROM ALL_TAB_COMMENTS T
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 5) D ON 1 = 1
WHERE T.TABLE_TYPE = 'TABLE') T
UNION ALL
SELECT COL.OWNER AS OWNER
, 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(REPLACE(CMT.COMMENTS, CHR(10), ''), CHR(13), '') 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.OWNER, COL.TABLE_NAME, COL.COLUMN_NAME)) AS COL09
, TRIM(DECODE(CST.ISFK, 'Y', 'FK', ' ') || ' ' || DECODE(CST.ISAK, 'Y', 'AK', ' ')) AS COL10
FROM ALL_TAB_COLUMNS COL
JOIN ALL_TAB_COMMENTS TAB ON TAB.OWNER = COL.OWNER
AND TAB.TABLE_NAME = COL.TABLE_NAME
JOIN ALL_COL_COMMENTS CMT ON CMT.OWNER = COL.OWNER
AND 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 ALL_CONS_COLUMNS CTC
JOIN ALL_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.OWNER = COL.OWNER
AND CST.TABLE_NAME = COL.TABLE_NAME
AND CST.COLUMN_NAME = COL.COLUMN_NAME
WHERE TAB.TABLE_TYPE = 'TABLE')
WHERE OWNER = UPPER(P_OWNER)
AND TABLE_NAME LIKE UPPER(P_TABLE_NAME)
ORDER BY OWNER
, 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_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2 := '%') RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_ROW IN (SELECT IDX.OWNER
, IDX.TABLE_NAME
, IDX.INDEX_NAME
, 'CREATE ' || DECODE(IDX.UNIQUENESS, 'UNIQUE', 'UNIQUE ', '') || 'INDEX ' || IDX.TABLE_OWNER || '.' || IDX.INDEX_NAME || ' ON ' || IDX.TABLE_OWNER || '.' || IDX.TABLE_NAME
|| ' (' ||
(SELECT LISTAGG(NVL(PKG_MGM.FNC_COLUMN_EXPRESSION(COL.INDEX_OWNER, COL.TABLE_NAME, COL.INDEX_NAME, COL.COLUMN_POSITION), COL.COLUMN_NAME) || ' ' || COL.DESCEND, ', ') WITHIN GROUP (ORDER BY COL.COLUMN_POSITION)
FROM ALL_IND_COLUMNS COL
WHERE COL.INDEX_OWNER = IDX.OWNER
AND COL.TABLE_NAME = IDX.TABLE_NAME
AND COL.INDEX_NAME = IDX.INDEX_NAME)
|| ') '
|| 'TABLESPACE ' || IDX.TABLESPACE_NAME || ';' AS CREATE_SCRIPT
, 'ALTER INDEX ' || IDX.TABLE_OWNER || '.' || IDX.INDEX_NAME || ' REBUILD TABLESPACE ' || IDX.TABLESPACE_NAME || ';' AS REBULT_SCRIPT
, 'DROP INDEX ' || IDX.TABLE_OWNER || '.' || IDX.INDEX_NAME || ';' AS DROP_SCRIPT
, TO_CHAR(OBJ.LAST_DDL_TIME, 'YYYY/MM/DD HH24:MI:SS') AS MODIFY_DTIME
FROM ALL_INDEXES IDX
JOIN ALL_OBJECTS OBJ ON OBJ.OWNER = IDX.OWNER
AND OBJ.OBJECT_NAME = IDX.INDEX_NAME
WHERE IDX.OWNER = UPPER(P_OWNER)
AND IDX.TABLE_NAME LIKE UPPER(P_TABLE_NAME)
AND IDX.INDEX_TYPE NOT IN ('LOB'))
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;
-- Create Table 스크립트 추출
FUNCTION FNC_DDL_CT ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR) RETURN TYPE_TABLE PIPELINED
IS
V_ROW TYPE_RECORD;
BEGIN
FOR CSR_ROW IN (SELECT COL01
, COL02
, COL03
, COL04
FROM (SELECT CASE T.NO
WHEN 1 THEN 'CREATE TABLE ' || T.OWNER || '.' || T.TABLE_NAME
WHEN 2 THEN
CASE C.COLUMN_ID
WHEN 1 THEN '( '
ELSE ', '
END
WHEN 3 THEN ');'
END AS COL01
, RPAD(C.COLUMN_NAME, T.COL_MLEN) AS COL02
, CASE T.NO
WHEN 1 THEN RPAD(' ', 72)
WHEN 2 THEN RPAD(C.DATA_TYPE
|| C.DATA_LENGTH
|| ' '
|| C.DATA_DEFAULT
|| NVL2(C.DATA_DEFAULT, ' ', NULL)
|| C.NULLABLE
, 72)
END AS COL03
, CASE T.NO
WHEN 1 THEN '-- COMMENT ON TABLE '
|| T.OWNER
|| '.'
|| T.TABLE_NAME
|| ' IS '
|| T.COMMENTS
WHEN 2 THEN '-- COMMENT ON COLUMN '
|| C.OWNER
|| '.'
|| C.TABLE_NAME
|| '.'
|| RPAD(C.COLUMN_NAME, T.COL_MLEN)
|| 'IS '
|| C.COMMENTS
END AS COL04
FROM (SELECT TAB.OWNER
, TAB.TABLE_NAME
, TAB.TABLE_TYPE
, CHR(39) || REPLACE(REPLACE(TAB.COMMENTS, CHR(10), ''), CHR(13), '') || CHR(39) || ';' AS COMMENTS
, D.NO
, (SELECT (CEIL(MAX(LENGTH(CMT.COLUMN_NAME)) / 4) + 1) * 4
FROM ALL_COL_COMMENTS CMT
WHERE CMT.OWNER = TAB.OWNER
AND CMT.TABLE_NAME = TAB.TABLE_NAME) AS COL_MLEN
FROM ALL_TAB_COMMENTS TAB
JOIN (SELECT LEVEL AS NO
FROM DUAL
CONNECT BY LEVEL <= 3) D ON 1 = 1) T
LEFT OUTER JOIN (SELECT COL.OWNER
, COL.TABLE_NAME
, COL.COLUMN_ID + 1 AS COLUMN_ID
, COL.COLUMN_NAME
, COL.DATA_TYPE
, CASE
WHEN COL.DATA_TYPE = 'NUMBER'
AND COL.DATA_SCALE IS NOT NULL THEN '(' || NVL(COL.DATA_PRECISION, 38) || ', ' || NVL(COL.DATA_SCALE, 0) || ')'
WHEN COL.CHAR_COL_DECL_LENGTH IS NOT NULL
AND COL.CHAR_USED IS NOT NULL THEN '(' || COL.CHAR_LENGTH || ' ' || DECODE(COL.CHAR_USED, 'B', 'BYTE', 'C', 'CHAR') || ')'
END AS DATA_LENGTH
, CASE
WHEN COL.DATA_DEFAULT IS NOT NULL THEN 'DEFAULT ' || PKG_MGM.FNC_DATA_DEFAULT(COL.OWNER, COL.TABLE_NAME, COL.COLUMN_NAME)
END AS DATA_DEFAULT
, DECODE(COL.NULLABLE, 'N', 'NOT NULL') AS NULLABLE
, CHR(39) || REPLACE(REPLACE(CMT.COMMENTS, CHR(10), ''), CHR(13), '') || CHR(39) || ';'
AS COMMENTS
FROM ALL_TAB_COLUMNS COL
JOIN ALL_COL_COMMENTS CMT ON CMT.OWNER = COL.OWNER
AND CMT.TABLE_NAME = COL.TABLE_NAME
AND CMT.COLUMN_NAME = COL.COLUMN_NAME
) C ON C.OWNER = T.OWNER
AND C.TABLE_NAME = T.TABLE_NAME
AND 2 = T.NO
WHERE T.TABLE_TYPE = 'TABLE'
AND T.OWNER = UPPER(P_OWNER)
AND T.TABLE_NAME = UPPER(P_TABLE_NAME)
ORDER BY T.NO
, C.COLUMN_ID)
)
LOOP
V_ROW.R_COL01 := CSR_ROW.COL01;
V_ROW.R_COL02 := CSR_ROW.COL02;
V_ROW.R_COL03 := CSR_ROW.COL03;
V_ROW.R_COL04 := CSR_ROW.COL04;
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 ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2
, P_COLUMN_NAME IN VARCHAR2) RETURN VARCHAR2
IS
V_RTN VARCHAR2(32767);
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'SELECT DATA_DEFAULT FROM ALL_TAB_COLUMNS WHERE OWNER = ' || CHR(39) || P_OWNER || CHR(39) || ' AND TABLE_NAME = ' || CHR(39) || P_TABLE_NAME || CHR(39) || ' AND COLUMN_NAME = ' || CHR(39) || P_COLUMN_NAME || CHR(39);
EXECUTE IMMEDIATE V_SQL INTO V_RTN;
V_RTN := TRIM(SUBSTR(REPLACE(REPLACE(V_RTN, CHR(10), ''), CHR(13), ''), 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 ( P_OWNER IN VARCHAR2
, P_TABLE_NAME IN VARCHAR2
, P_INDEX_NAME IN VARCHAR2
, P_COLUMN_POSITION IN NUMBER) RETURN VARCHAR2
IS
V_RTN VARCHAR2(32767);
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'SELECT COLUMN_EXPRESSION FROM ALL_IND_EXPRESSIONS WHERE INDEX_OWNER = ' || CHR(39) || P_OWNER || CHR(39) || ' AND TABLE_NAME = ' || CHR(39) || P_TABLE_NAME || CHR(39) || ' AND INDEX_NAME = ' || CHR(39) || P_INDEX_NAME || CHR(39) || ' AND COLUMN_POSITION = ' || P_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;
/