001：テーブル内容比較のパッケージ
同じテーブル定義のテーブルのデータ内容を比較して違いを出力する
テーブルＡ(TEST_A)とテーブルＢ(TEST_B)を比較します
| TEST_AとTEST_Bの定義 |
DROP TABLE TEST_A CASCADE CONSTRAINTS; CREATE TABLE TEST_A ( FLD001 NUMBER (10,0) NOT NULL, FLD002 VARCHAR2 (32) , CONSTRAINT PK_TEST_A PRIMARY KEY (FLD001) USING INDEX ); DROP TABLE TEST_B CASCADE CONSTRAINTS; CREATE TABLE TEST_B ( FLD001 NUMBER (10,0) NOT NULL, FLD002 VARCHAR2 (32) , CONSTRAINT PK_TEST_B PRIMARY KEY (FLD001) USING INDEX ); |
| 比較に使用するテーブルの定義 |
DROP TABLE INF_COMPARE_INPUT CASCADE CONSTRAINTS; CREATE TABLE INF_COMPARE_INPUT ( SEQ NUMBER (10,0) NOT NULL, USERID_A VARCHAR2 (32) NOT NULL, TABLE_A VARCHAR2 (32) NOT NULL, USERID_B VARCHAR2 (32) NOT NULL, TABLE_B VARCHAR2 (32) NOT NULL, FIELD_B VARCHAR2 (32) NOT NULL, FLG NUMBER (1,0) DEFAULT 0, CONSTRAINT PK_INF_COMPARE_INPUT PRIMARY KEY (SEQ) USING INDEX ); COMMENT ON TABLE INF_COMPARE_INPUT IS '比較テーブル'; COMMENT ON COLUMN INF_COMPARE_INPUT.SEQ IS '順番'; COMMENT ON COLUMN INF_COMPARE_INPUT.USERID_A IS '比較元ユーザー名'; COMMENT ON COLUMN INF_COMPARE_INPUT.TABLE_A IS '比較元テーブル名'; COMMENT ON COLUMN INF_COMPARE_INPUT.USERID_B IS '比較先ユーザー名'; COMMENT ON COLUMN INF_COMPARE_INPUT.TABLE_B IS '比較先テーブル名'; COMMENT ON COLUMN INF_COMPARE_INPUT.FIELD_B IS '比較フィールド'; COMMENT ON COLUMN INF_COMPARE_INPUT.FLG IS '比較対象フラグ(0:対象外,1:対象)'; |
| サンプルデータをセット |
TRUNCATE TABLE TEST_A; INSERT INTO TEST_A VALUES(001,'001'); INSERT INTO TEST_A VALUES(002,'002'); INSERT INTO TEST_A VALUES(003,'003'); COMMIT; TRUNCATE TABLE TEST_B; INSERT INTO TEST_B VALUES(001,'001'); INSERT INTO TEST_B VALUES(003,'333'); INSERT INTO TEST_B VALUES(004,'004'); COMMIT; TRUNCATE TABLE INF_COMPARE_INPUT; INSERT INTO INF_COMPARE_INPUT VALUES(001,'USER_A','TEST_A','USER_B','TEST_B','FLD002',1); COMMIT; |
| パッケージ |
-- **************
-- Package Header
-- **************
CREATE OR REPLACE PACKAGE PK_COMPARE_TOOL
AS
--Global変数
gintRet PLS_INTEGER; --関数の戻り値の格納用
gdteDate DATE; --パッケージの実行開始時刻を保持し、エラー発生時にテーブルに書き込む
gintSeqErr PLS_INTEGER; --エラー情報書き込みごとに＋１のインクリメントを行い、重複を防ぐ
gstrErr VARCHAR2(1000); --エラー発生時の文字列情報を保持
gcon_table_all VARCHAR2(4) := '_ALL';
gcon_table_del VARCHAR2(4) := '_DEL';
gcon_table_upd VARCHAR2(4) := '_UPD';
PROCEDURE PR_COMPARE_TABLE
(
in_owner1 IN VARCHAR2,
in_table1 IN VARCHAR2,
in_owner2 IN VARCHAR2,
in_table2 IN VARCHAR2
);
PROCEDURE PR_CREATE_TEMP_TABLE
(
in_owner IN VARCHAR2,
in_table IN VARCHAR2
);
PROCEDURE PR_ERR_INSERT
(
in_err IN VARCHAR2
);
PROCEDURE PR_INPUT_DATA_CHECK;
PROCEDURE PR_TABLE_DATA_CHECK;
PROCEDURE PR_MAIN;
END PK_COMPARE_TOOL;
/
SHOW ERRORS
-- ************
-- Package Body
-- ************
CREATE OR REPLACE PACKAGE BODY PK_COMPARE_TOOL
IS
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--
--COMPARE実行
--
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PROCEDURE PR_COMPARE_TABLE
(
in_owner1 IN VARCHAR2, --元OWNER
in_table1 IN VARCHAR2, --元TABLE
in_owner2 IN VARCHAR2, --先OWNER
in_table2 IN VARCHAR2 --先TABLE
)
IS
strSQL DBMS_SQL.VARCHAR2S; --動的SQL発行のためのSQL文字列を格納
intSQL PLS_INTEGER; --上の変数のPL/SQL表の添え字
intCur PLS_INTEGER; --カーソル情報を保持
intRet PLS_INTEGER; --関数の戻り値を保持
intRecordCnt PLS_INTEGER; --レコード数を保持
strOwner1 ALL_INDEXES.TABLE_OWNER%TYPE;
strTable1 ALL_INDEXES.TABLE_NAME%TYPE;
strOwner2 ALL_INDEXES.TABLE_OWNER%TYPE;
strTable2 ALL_INDEXES.TABLE_NAME%TYPE;
blnFlg BOOLEAN;
--フィールド名を格納するPL/SQL表の定義
TYPE arr IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
col_arr arr; --フィールド名を格納するPL/SQL表の実体
BEGIN
DBMS_OUTPUT.PUT_LINE('START:[PR_COMPARE_TABLE]');
--_DELと_UPD(更新後以外)を作成
FOR intCnt IN 1 .. 2 LOOP
--ループ①：元と先を比べて、元にあり、先にないレコードを取得する(DELのパターン)
--INSERT INTO TEST_A_DEL
--SELECT * FROM TEST_A A
--WHERE NOT EXISTS (SELECT * FROM TEST_B B WHERE A.FLD001 = B.FLD001);
--ループ②：元と先を比べて、先にあり、元にないレコードを取得する(INSのパターン)
--INSERT INTO TEST_A_INS
--SELECT * FROM TEST_B B
--WHERE NOT EXISTS (SELECT * FROM TEST_A A WHERE A.FLD001 = B.FLD001);
--テーブル名とフィールド情報を生成
IF intCnt = 1 THEN
strOwner1 := in_owner1;
strTable1 := in_table1;
strOwner2 := in_owner2;
strTable2 := in_table2;
ELSE
strOwner1 := in_owner2;
strTable1 := in_table2;
strOwner2 := in_owner1;
strTable2 := in_table1;
END IF;
strSQL.DELETE();
intSQL := 1;
IF intCnt = 1 THEN
strSQL(intSQL) := 'INSERT INTO ' || in_owner2 || '.' || in_table2 || gcon_table_del;
ELSE
strSQL(intSQL) := 'INSERT INTO ' || in_owner2 || '.' || in_table2 || gcon_table_upd;
END IF;
intSQL := intSQL + 1;
strSQL(intSQL) := ' SELECT * FROM ' || strOwner1 || '.' || strTable1 || ' A';
intSQL := intSQL + 1;
strSQL(intSQL) := ' WHERE NOT EXISTS (SELECT * FROM ' || strOwner2 || '.' || strTable2 || ' B';
intSQL := intSQL + 1;
--WHERE句で使用するPrimaryKey情報を取得
FOR rec IN
(
SELECT IND.*
FROM ALL_IND_COLUMNS IND,
ALL_CONSTRAINTS CST
WHERE IND.TABLE_OWNER = strOwner2
AND IND.TABLE_NAME = strTable2
AND IND.TABLE_OWNER = CST.OWNER
AND IND.TABLE_NAME = CST.TABLE_NAME
AND IND.INDEX_NAME = CST.CONSTRAINT_NAME
AND CST.CONSTRAINT_TYPE = 'P'
ORDER BY
IND.COLUMN_POSITION
) LOOP
IF rec.COLUMN_POSITION = 1 THEN
strSQL(intSQL) := ' WHERE';
intSQL := intSQL + 1;
ELSE
strSQL(intSQL) := ' AND';
intSQL := intSQL + 1;
END IF;
strSQL(intSQL) := ' A.' || rec.COLUMN_NAME || ' = B.' || rec.COLUMN_NAME;
intSQL := intSQL + 1;
END LOOP;
strSQL(intSQL) := ')';
intSQL := intSQL + 1;
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL,strSQL.FIRST,strSQL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_DELと_UPD(更新後以外)を作成)');
FOR intLoopCnt IN 1 .. intSQL - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_DELと_UPD(更新後以外)を作成)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
END LOOP;
--_DELを_ALLに入れる
--INSERT INTO TEST_A_ALL SELECT 'DEL',A.* FROM TEST_A_DEL A
strSQL.DELETE();
intSQL := 1;
strSQL(intSQL) := 'INSERT INTO ' || in_owner2 || '.' || in_table2 || gcon_table_all;
intSQL := intSQL + 1;
strSQL(intSQL) := ' SELECT ''DEL'',A.* FROM ' || in_owner2 || '.' || in_table2 || gcon_table_del || ' A';
intSQL := intSQL + 1;
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL,strSQL.FIRST,strSQL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_DELを_ALLに入れる)');
FOR intLoopCnt IN 1 .. intSQL - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_DELを_ALLに入れる)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
--_UPDを_ALLに入れる
--INSERT INTO TEST_A_ALL SELECT 'INS',A.* FROM TEST_A_UPD A
strSQL.DELETE();
intSQL := 1;
strSQL(intSQL) := 'INSERT INTO ' || in_owner2 || '.' || in_table2 || gcon_table_all;
intSQL := intSQL + 1;
strSQL(intSQL) := ' SELECT ''INS'',A.* FROM ' || in_owner2 || '.' || in_table2 || gcon_table_upd || ' A';
intSQL := intSQL + 1;
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL,strSQL.FIRST,strSQL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_UPDを_ALLに入れる)');
FOR intLoopCnt IN 1 .. intSQL - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_UPDを_ALLに入れる)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
--_ALL(UPDATEのみ)を作成
strSQL.DELETE();
intSQL := 1;
FOR LOOP_CNT IN 1 .. 2 LOOP
blnFlg := FALSE;
IF LOOP_CNT = 1 THEN
strSQL(intSQL) := 'INSERT INTO ' || in_owner2 || '.' || in_table2 || gcon_table_all;
intSQL := intSQL + 1;
strSQL(intSQL) := ' (SELECT ''UPD_1'',A.* FROM ' || in_owner1 || '.' || in_table1 || ' A,';
intSQL := intSQL + 1;
strSQL(intSQL) := in_owner2 || '.' || in_table2 || ' B WHERE ';
intSQL := intSQL + 1;
ELSE
strSQL(intSQL) := 'UNION';
intSQL := intSQL + 1;
strSQL(intSQL) := 'SELECT ''UPD_2'',B.* FROM ' || in_owner1 || '.' || in_table1 || ' A,';
intSQL := intSQL + 1;
strSQL(intSQL) := in_owner2 || '.' || in_table2 || ' B WHERE ';
intSQL := intSQL + 1;
END IF;
FOR rec IN
(
SELECT IND.COLUMN_NAME
FROM ALL_IND_COLUMNS IND,
ALL_CONSTRAINTS CST
WHERE IND.TABLE_OWNER = in_owner1
AND IND.TABLE_NAME = in_table1
AND IND.INDEX_NAME = CST.INDEX_NAME
AND IND.INDEX_NAME = CST.CONSTRAINT_NAME
AND CST.CONSTRAINT_TYPE = 'P'
ORDER BY
IND.COLUMN_POSITION
) LOOP
col_arr(rec.COLUMN_NAME) := rec.COLUMN_NAME;
IF blnFlg = TRUE THEN
strSQL(intSQL) := ' AND ';
intSQL := intSQL + 1;
END IF;
strSQL(intSQL) := 'A.' || rec.COLUMN_NAME || ' = B.' || rec.COLUMN_NAME;
intSQL := intSQL + 1;
blnFlg := TRUE;
END LOOP;
strSQL(intSQL) := ' AND (';
intSQL := intSQL + 1;
blnFlg := FALSE;
FOR rec IN
(
SELECT FIELD_B
FROM INF_COMPARE_INPUT
WHERE USERID_B = in_owner2
AND TABLE_B = in_table2
AND FLG = 1
ORDER BY
SEQ
) LOOP
IF col_arr.EXISTS(rec.FIELD_B) = FALSE THEN
IF blnFlg = TRUE THEN
strSQL(intSQL) := ' OR ';
intSQL := intSQL + 1;
END IF;
strSQL(intSQL) := 'A.' || rec.FIELD_B || ' != B.' || rec.FIELD_B;
intSQL := intSQL + 1;
blnFlg := TRUE;
END IF;
END LOOP;
IF LOOP_CNT = 1 THEN
strSQL(intSQL) := ')';
ELSE
strSQL(intSQL) := '))';
END IF;
intSQL := intSQL + 1;
END LOOP;
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL,strSQL.FIRST,strSQL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_ALL(UPDATEのみ)を作成)');
FOR intLoopCnt IN 1 .. intSQL - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_ALL(UPDATEのみ)を作成)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
--_ALLのUPD_2を_UPDに入れる
--INSERT INTO TEST_A_UPD SELECT ---- FROM TEST_A_ALL A WHERE FIELD_FLG = 'UPD_2'
strSQL.DELETE();
intSQL := 1;
strSQL(intSQL) := 'INSERT INTO ' || in_owner2 || '.' || in_table2 || gcon_table_upd;
intSQL := intSQL + 1;
strSQL(intSQL) := ' SELECT';
intSQL := intSQL + 1;
blnFlg := FALSE;
FOR rec IN
(
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = in_owner2
AND TABLE_NAME = in_table2
ORDER BY
COLUMN_ID
) LOOP
IF rec.COLUMN_NAME != 'FIELD_FLG' THEN
IF blnFlg = FALSE THEN
strSQL(intSQL) := ' ' || rec.COLUMN_NAME;
blnFlg := TRUE;
ELSE
strSQL(intSQL) := ',' || rec.COLUMN_NAME;
END IF;
intSQL := intSQL + 1;
END IF;
END LOOP;
strSQL(intSQL) := ' FROM ' || in_owner2 || '.' || in_table2 || gcon_table_all;
intSQL := intSQL + 1;
strSQL(intSQL) := ' WHERE FIELD_FLG = ''UPD_2''';
intSQL := intSQL + 1;
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL,strSQL.FIRST,strSQL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_ALLのUPD_2を_UPDに入れる)');
FOR intLoopCnt IN 1 .. intSQL - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_ALLのUPD_2を_UPDに入れる)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
COMMIT;
/*
FOR intLoopCnt IN 1 .. 3 LOOP
CASE intLoopCnt
WHEN 1 THEN
strTable0 := gcon_table_all;
WHEN 2 THEN
strTable0 := gcon_table_del;
WHEN 3 THEN
strTable0 := gcon_table_upd;
END CASE;
strSQL.DELETE();
intSQL := 1;
strSQL(intSQL) := 'SELECT COUNT(*) FROM ' || in_owner2 || '.' || in_table2 || strTable0;
intSQL := intSQL + 1;
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL,strSQL.FIRST,strSQL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.DEFINE_COLUMN(intCur,1,intRecordCnt);
DBMS_SQL.CLOSE_CURSOR(intCur);
DBMS_OUTPUT.PUT_LINE(strSQL(1));
DBMS_OUTPUT.PUT_LINE('レコード数(' || in_owner2 || '.' || in_table2 || strTable0 || '):[' || TO_CHAR(intRecordCnt,'00000') || ']');
END LOOP;
*/
DBMS_OUTPUT.PUT_LINE('END :[PR_COMPARE_TABLE]');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
END PR_COMPARE_TABLE;
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--
--格納用テーブル作成
--
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PROCEDURE PR_CREATE_TEMP_TABLE
(
in_owner IN VARCHAR2,
in_table IN VARCHAR2
)
IS
strTruncTable VARCHAR2(100);
strSQL_ALL DBMS_SQL.VARCHAR2S;
intIdx_ALL PLS_INTEGER;
strSQL_DEL DBMS_SQL.VARCHAR2S;
intIdx_DEL PLS_INTEGER;
strSQL_UPD DBMS_SQL.VARCHAR2S;
intIdx_UPD PLS_INTEGER;
strTmpField VARCHAR2(100);
strTmpIdx_ALL VARCHAR2(100);
strTmpIdx_DEL VARCHAR2(100);
strTmpIdx_UPD VARCHAR2(100);
intCur PLS_INTEGER;
intRet PLS_INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('START:[PR_CREATE_TEMP_TABLE]');
--CREATE TABLE TEST001
--(
-- FIELD001 NUMBER(3,0) NOT NULL,
-- FIELD002 NUMBER(5,0) NOT NULL,
-- FIELD003 VARCHAR2(32),
-- FIELD004 VARCHAR2(32),
-- FIELD005 DATE,
--)
--テーブル名とフィールド情報を生成
FOR rec IN
(
SELECT *
FROM ALL_TAB_COLUMNS
WHERE OWNER = in_owner
AND TABLE_NAME = in_table
ORDER BY
COLUMN_ID
) LOOP
IF rec.COLUMN_ID = 1 THEN
--EXECUTE IMMEDIATE 'DROP TABLE TEST001_0 CASCADE CONSTRAINTS';
BEGIN
strTruncTable := 'DROP TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || gcon_table_all || ' CASCADE CONSTRAINTS';
--DBMS_OUTPUT.PUT_LINE(strTruncTable);
EXECUTE IMMEDIATE strTruncTable;
EXCEPTION
WHEN OTHERS THEN
gstrErr := '削除すべきテーブルがないが、問題なし[' || strTruncTable || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END;
BEGIN
strTruncTable := 'DROP TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || gcon_table_del || ' CASCADE CONSTRAINTS';
--DBMS_OUTPUT.PUT_LINE(strTruncTable);
EXECUTE IMMEDIATE strTruncTable;
EXCEPTION
WHEN OTHERS THEN
gstrErr := '削除すべきテーブルがないが、問題なし[' || strTruncTable || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END;
BEGIN
strTruncTable := 'DROP TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || gcon_table_upd || ' CASCADE CONSTRAINTS';
--DBMS_OUTPUT.PUT_LINE(strTruncTable);
EXECUTE IMMEDIATE strTruncTable;
EXCEPTION
WHEN OTHERS THEN
gstrErr := '削除すべきテーブルがないが、問題なし[' || strTruncTable || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END;
strSQL_ALL.DELETE();
strSQL_DEL.DELETE();
strSQL_UPD.DELETE();
intIdx_ALL := 1;
intIdx_DEL := 1;
intIdx_UPD := 1;
strSQL_ALL(intIdx_ALL) := 'CREATE TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || gcon_table_all;
intIdx_ALL := intIdx_ALL + 1;
strSQL_ALL(intIdx_ALL) := '( ';
intIdx_ALL := intIdx_ALL + 1;
strSQL_ALL(intIdx_ALL) := ' FIELD_FLG VARCHAR2(10) NOT NULL,';
intIdx_ALL := intIdx_ALL + 1;
strSQL_DEL(intIdx_DEL) := 'CREATE TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || gcon_table_del;
intIdx_DEL := intIdx_DEL + 1;
strSQL_DEL(intIdx_DEL) := '( ';
intIdx_DEL := intIdx_DEL + 1;
strSQL_UPD(intIdx_UPD) := 'CREATE TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME || gcon_table_upd;
intIdx_UPD := intIdx_UPD + 1;
strSQL_UPD(intIdx_UPD) := '( ';
intIdx_UPD := intIdx_UPD + 1;
END IF;
strTmpField := '';
strTmpField := strTmpField || ' ' || rec.COLUMN_NAME;
CASE rec.DATA_TYPE
WHEN 'NUMBER' THEN
strTmpField := strTmpField || ' ' || rec.DATA_TYPE;
strTmpField := strTmpField || ' (' || rec.DATA_PRECISION;
strTmpField := strTmpField || '.' || rec.DATA_SCALE;
strTmpField := strTmpField || ')';
WHEN 'VARCHAR2' THEN
strTmpField := strTmpField || ' ' || rec.DATA_TYPE;
strTmpField := strTmpField || ' (' || rec.DATA_LENGTH;
strTmpField := strTmpField || ')';
WHEN 'DATE' THEN
strTmpField := strTmpField || ' ' || rec.DATA_TYPE;
ELSE
gintRet := 1;
gstrErr := 'テーブルの型情報の対応が足りていません！！型情報：[' || rec.DATA_TYPE || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END CASE;
IF rec.NULLABLE = 'N' THEN
strTmpField := strTmpField || ' NOT NULL';
END IF;
strTmpField := strTmpField || ',';
strSQL_ALL(intIdx_ALL) := strTmpField;
intIdx_ALL := intIdx_ALL + 1;
strSQL_DEL(intIdx_DEL) := strTmpField;
intIdx_DEL := intIdx_DEL + 1;
strSQL_UPD(intIdx_UPD) := strTmpField;
intIdx_UPD := intIdx_UPD + 1;
END LOOP;
--CONSTRAINT PK_TEST001 PRIMARY KEY (FIELD001, FIELD002) USING INDEX
--インデックス情報を生成
FOR rec IN
(
SELECT ICM.*
FROM ALL_IND_COLUMNS ICM,
ALL_INDEXES IND
WHERE IND.TABLE_OWNER = in_owner
AND IND.TABLE_NAME = in_table
AND IND.UNIQUENESS = 'UNIQUE'
AND IND.TABLE_OWNER = ICM.TABLE_OWNER
AND IND.TABLE_NAME = ICM.TABLE_NAME
AND IND.INDEX_NAME = ICM.INDEX_NAME
ORDER BY
ICM.COLUMN_POSITION
) LOOP
IF rec.COLUMN_POSITION = 1 THEN
strTmpIdx_ALL := '';
strTmpIdx_ALL := strTmpIdx_ALL || ' CONSTRAINT';
strTmpIdx_ALL := strTmpIdx_ALL || ' ' || rec.INDEX_NAME || gcon_table_all;
strTmpIdx_ALL := strTmpIdx_ALL || ' PRIMARY KEY';
strTmpIdx_ALL := strTmpIdx_ALL || ' (';
strTmpIdx_DEL := '';
strTmpIdx_DEL := strTmpIdx_DEL || ' CONSTRAINT';
strTmpIdx_DEL := strTmpIdx_DEL || ' ' || rec.INDEX_NAME || gcon_table_del;
strTmpIdx_DEL := strTmpIdx_DEL || ' PRIMARY KEY';
strTmpIdx_DEL := strTmpIdx_DEL || ' (';
strTmpIdx_UPD := '';
strTmpIdx_UPD := strTmpIdx_UPD || ' CONSTRAINT';
strTmpIdx_UPD := strTmpIdx_UPD || ' ' || rec.INDEX_NAME || gcon_table_upd;
strTmpIdx_UPD := strTmpIdx_UPD || ' PRIMARY KEY';
strTmpIdx_UPD := strTmpIdx_UPD || ' (';
ELSE
strTmpIdx_ALL := strTmpIdx_ALL || ',';
strTmpIdx_DEL := strTmpIdx_DEL || ',';
strTmpIdx_UPD := strTmpIdx_UPD || ',';
END IF;
strTmpIdx_ALL := strTmpIdx_ALL || rec.COLUMN_NAME;
strTmpIdx_DEL := strTmpIdx_DEL || rec.COLUMN_NAME;
strTmpIdx_UPD := strTmpIdx_UPD || rec.COLUMN_NAME;
END LOOP;
strTmpIdx_ALL := strTmpIdx_ALL || ',FIELD_FLG';
strTmpIdx_ALL := strTmpIdx_ALL || ')';
strTmpIdx_ALL := strTmpIdx_ALL || ' USING INDEX';
strTmpIdx_ALL := strTmpIdx_ALL || ')';
strTmpIdx_DEL := strTmpIdx_DEL || ')';
strTmpIdx_DEL := strTmpIdx_DEL || ' USING INDEX';
strTmpIdx_DEL := strTmpIdx_DEL || ')';
strTmpIdx_UPD := strTmpIdx_UPD || ')';
strTmpIdx_UPD := strTmpIdx_UPD || ' USING INDEX';
strTmpIdx_UPD := strTmpIdx_UPD || ')';
strSQL_ALL(intIdx_ALL) := strTmpIdx_ALL;
intIdx_ALL := intIdx_ALL + 1;
strSQL_DEL(intIdx_DEL) := strTmpIdx_DEL;
intIdx_DEL := intIdx_DEL + 1;
strSQL_UPD(intIdx_UPD) := strTmpIdx_UPD;
intIdx_UPD := intIdx_UPD + 1;
--_ALL作成
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL_ALL,strSQL_ALL.FIRST,strSQL_ALL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_ALL作成)');
FOR intLoopCnt IN 1 .. intIdx_ALL - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL_ALL(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_ALL作成)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
--_DEL作成
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL_DEL,strSQL_DEL.FIRST,strSQL_DEL.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_DEL作成)');
FOR intLoopCnt IN 1 .. intIdx_DEL - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL_DEL(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_DEL作成)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
--_UPD作成
BEGIN
intCur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(intCur,strSQL_UPD,strSQL_UPD.FIRST,strSQL_UPD.LAST,TRUE,DBMS_SQL.NATIVE);
intRet := DBMS_SQL.EXECUTE(intCur);
DBMS_SQL.CLOSE_CURSOR(intCur);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL文字列生成エラー(_UPD作成)');
FOR intLoopCnt IN 1 .. intIdx_UPD - 1 LOOP
DBMS_OUTPUT.PUT_LINE(strSQL_UPD(intLoopCnt));
END LOOP;
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
gintRet := 1;
gstrErr := 'SQL文字列生成エラー(_UPD作成)[ERROR-Str :' || SQLERRM || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
RETURN;
END;
DBMS_OUTPUT.PUT_LINE('END :[PR_CREATE_TEMP_TABLE]');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
END PR_CREATE_TEMP_TABLE;
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--
--テーブルデータのチェックのメインルーチン
--
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PROCEDURE PR_TABLE_DATA_CHECK
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('START:[PR_TABLE_DATA_CHECK]');
FOR rec IN
(
SELECT DISTINCT USERID_A,TABLE_A,USERID_B,TABLE_B
FROM INF_COMPARE_INPUT
WHERE FLG = 1
) LOOP
--差分格納用テーブルを作成
--DBMS_OUTPUT.PUT_LINE('TABLE_NAME:[' || rec.TABLE_B || ']');
PR_CREATE_TEMP_TABLE(rec.USERID_B,rec.TABLE_B);
IF gintRet != 0 THEN
RETURN;
END IF;
--データを比較する
PR_COMPARE_TABLE(rec.USERID_A,rec.TABLE_A,rec.USERID_B,rec.TABLE_B);
IF gintRet != 0 THEN
RETURN;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('END :[PR_TABLE_DATA_CHECK]');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
END PR_TABLE_DATA_CHECK;
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--
--エラー情報書き込み
--
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PROCEDURE PR_ERR_INSERT
(
in_err IN VARCHAR2
)
IS
BEGIN
gintSeqErr := gintSeqErr + 1;
INSERT INTO INF_COMPARE_ERROR
(
START_DATE,
SEQ,
ERROR_STRING
) VALUES
(
gdteDate,
gintSeqErr,
in_err
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
END PR_ERR_INSERT;
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--
--入力情報として渡されるINF_COMPARE_INPUTの内容を精査
--
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PROCEDURE PR_INPUT_DATA_CHECK
IS
strOwner0 ALL_TABLES.OWNER%TYPE;
strTable0 ALL_TABLES.TABLE_NAME%TYPE;
rowRecord1 ALL_TAB_COLUMNS%ROWTYPE;
rowRecord2 ALL_TAB_COLUMNS%ROWTYPE;
intColCnt1 PLS_INTEGER;
intColCnt2 PLS_INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('START:[PR_INPUT_DATA_CHECK]');
--テーブル名チェック
FOR rec IN
(
SELECT DISTINCT USERID_A,
TABLE_A,
USERID_B,
TABLE_B
FROM INF_COMPARE_INPUT
WHERE FLG = 1
) LOOP
strOwner0 := '';
strTable0 := '';
BEGIN
--テーブルが存在するかどうかをチェック
SELECT OWNER,
TABLE_NAME
INTO strOwner0,
strTable0
FROM ALL_TABLES
WHERE OWNER = rec.USERID_A
AND TABLE_NAME = rec.TABLE_A;
EXCEPTION
WHEN OTHERS THEN
gintRet := 1;
gstrErr := '元テーブルの指定を間違えています！！テーブル情報：[' || rec.USERID_A || '.' || rec.TABLE_A || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END;
strOwner0 := '';
strTable0 := '';
BEGIN
--テーブルが存在するかどうかをチェック
SELECT OWNER,
TABLE_NAME
INTO strOwner0,
strTable0
FROM ALL_TABLES
WHERE OWNER = rec.USERID_B
AND TABLE_NAME = rec.TABLE_B;
EXCEPTION
WHEN OTHERS THEN
gintRet := 1;
gstrErr := '先テーブルの指定を間違えています！！テーブル情報：[' || rec.USERID_B || '.' || rec.TABLE_B || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END;
--元テーブルのカラム数を数える
SELECT COUNT('X')
INTO intColCnt1
FROM ALL_TAB_COLUMNS
WHERE OWNER = rec.USERID_A
AND TABLE_NAME = rec.TABLE_A;
--先テーブルのカラム数を数える
SELECT COUNT('X')
INTO intColCnt2
FROM ALL_TAB_COLUMNS
WHERE OWNER = rec.USERID_B
AND TABLE_NAME = rec.TABLE_B;
IF intColCnt1 != intColCnt2 THEN
gintRet := 1;
gstrErr := 'フィールド数が違います！！テーブル情報：[' || rec.USERID_A || '.' || rec.TABLE_A || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END IF;
END LOOP;
IF gintRet != 0 THEN
RETURN;
END IF;
--フィールドチェック
FOR rec IN
(
SELECT *
FROM INF_COMPARE_INPUT
WHERE FLG = 1
) LOOP
BEGIN
SELECT *
INTO rowRecord1
FROM ALL_TAB_COLUMNS
WHERE OWNER = rec.USERID_A
AND TABLE_NAME = rec.TABLE_A
AND COLUMN_NAME = rec.FIELD_B;
EXCEPTION
WHEN OTHERS THEN
gintRet := 1;
gstrErr := '元テーブルのフィールド名指定が間違えています！！フィールド情報：[' || rec.USERID_A || '.' || rec.TABLE_A || '.' || rec.FIELD_B || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END;
BEGIN
SELECT *
INTO rowRecord2
FROM ALL_TAB_COLUMNS
WHERE OWNER = rec.USERID_B
AND TABLE_NAME = rec.TABLE_B
AND COLUMN_NAME = rec.FIELD_B;
EXCEPTION
WHEN OTHERS THEN
gintRet := 1;
gstrErr := '先テーブルのフィールド名指定が間違えています！！フィールド情報：[' || rec.USERID_B || '.' || rec.TABLE_B || '.' || rec.FIELD_B || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END;
IF rowRecord1.DATA_TYPE != rowRecord2.DATA_TYPE THEN
gintRet := 1;
gstrErr := '比較するフィールドのデータ型が違います！！フィールド情報：[' || rec.USERID_A || '.' || rec.TABLE_A || '.' || rec.FIELD_B || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END IF;
IF rowRecord1.DATA_LENGTH != rowRecord2.DATA_LENGTH THEN
gintRet := 1;
gstrErr := '比較するフィールドのデータ長が違います！！フィールド情報：[' || rec.USERID_A || '.' || rec.TABLE_A || '.' || rec.FIELD_B || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END IF;
IF rowRecord1.COLUMN_ID != rowRecord2.COLUMN_ID THEN
gintRet := 1;
gstrErr := '比較するフィールドのフィールド位置が違います！！フィールド情報：[' || rec.USERID_A || '.' || rec.TABLE_A || '.' || rec.FIELD_B || ']';
DBMS_OUTPUT.PUT_LINE(gstrErr);
PR_ERR_INSERT(gstrErr);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('END :[PR_INPUT_DATA_CHECK]');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
END PR_INPUT_DATA_CHECK;
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
--
--メインロジック(ここから開始)
--
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
PROCEDURE PR_MAIN
IS
dteDate1 TIMESTAMP;
dteDate2 TIMESTAMP;
dteDate3 INTERVAL DAY TO SECOND;
BEGIN
dteDate1 := CURRENT_TIMESTAMP;
gdteDate:= SYSDATE;
DBMS_OUTPUT.PUT_LINE('START:[' || TO_CHAR(dteDate1,'YYYY/MM/DD HH24:MI:SS.FF3') || ']');
DBMS_OUTPUT.PUT_LINE('START:[PR_MAIN]');
gintRet := 0;
gintSeqErr := 0;
--入力情報のチェック
PR_INPUT_DATA_CHECK;
IF gintRet != 0 THEN
DBMS_OUTPUT.PUT_LINE('入力情報に誤りがあります');
END IF;
--データチェック
IF gintRet = 0 THEN
PR_TABLE_DATA_CHECK;
END IF;
DBMS_OUTPUT.PUT_LINE('END :[PR_MAIN]');
dteDate2 := CURRENT_TIMESTAMP;
DBMS_OUTPUT.PUT_LINE('END :[' || TO_CHAR(dteDate2,'YYYY/MM/DD HH24:MI:SS.FF3') || ']');
dteDate3 := dteDate2 - dteDate1;
DBMS_OUTPUT.PUT_LINE('DIFF :[' || TO_CHAR(dteDate3,'YYYY/MM/DD HH24:MI:SS.FF3') || ']');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR-Code:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR-Str :' || SQLERRM);
END PR_MAIN;
END PK_COMPARE_TOOL;
/
SHOW ERRORS
|
| sqlplusから実行 |
@PK_COMPARE_TOOL SET SERVEROUTPUT ON SIZE 1000000 EXEC PK_COMPARE_TOOL.PR_MAIN; |
実行結果
SQL> @start パッケージが作成されました。 エラーはありません。 パッケージ本体が作成されました。 エラーはありません。 START:[2006/04/03 16:41:16.484] START:[PR_MAIN] START:[PR_INPUT_DATA_CHECK] END :[PR_INPUT_DATA_CHECK] START:[PR_TABLE_DATA_CHECK] START:[PR_CREATE_TEMP_TABLE] END :[PR_CREATE_TEMP_TABLE] START:[PR_COMPARE_TABLE] END :[PR_COMPARE_TABLE] END :[PR_TABLE_DATA_CHECK] END :[PR_MAIN] END :[2006/04/03 16:41:17.234] DIFF :[+000000 00:00:00.750000000] PL/SQLプロシージャが正常に完了しました。 SQL> |
正常終了すると[TEST_B_ALL],[TEST_B_DEL],[TEST_B_UPD]という３つのテーブルが作成されて、その中にそれぞれ値が格納されている