免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 781 | 回复: 0

两表比较 [复制链接]

论坛徽章:
0
发表于 2011-12-22 08:53 |显示全部楼层

1.第一版

  1. CREATE OR REPLACE PROCEDURE comparetable

  2.  IS
  3.   --SQL

  4.   sqlStr VARCHAR2(2000);
  5.   updateColm VARCHAR2(2000);
  6.   cnt1 NUMBER;
  7.   cnt2 NUMBER;
  8.   --deleteColm VARCHAR2(2000);


  9.   --

  10.   rsTable_1 SYS_REFCURSOR; --新

  11.   rsTable SYS_REFCURSOR; --旧


  12.   --

  13.   recTable_1 Table_1%ROWTYPE; --新

  14.   recTable Table%ROWTYPE; --旧


  15. BEGIN

  16.   --新

  17.   sqlStr := '';
  18.   sqlStr := sqlStr || 'SELECT ';
  19.   sqlStr := sqlStr || ' * ';
  20.   sqlStr := sqlStr || 'FROM ';
  21.   sqlStr := sqlStr || ' Table_1';

  22.   --

  23.   recTable_1 := NULL;
  24.   OPEN rsTable_1 FOR sqlStr;
  25.   LOOP FETCH rsTable_1 INTO recTable_1;
  26.     EXIT WHEN rsTable_1%NOTFOUND;
  27.     
  28.     SELECT COUNT(*) INTO cnt2 FROM Table b WHERE b.prod_no = recTable_1.PROD_NO;
  29.     IF cnt2 = 0 THEN
  30.        DBMS_OUTPUT.PUT_LINE('NEW' || 'ADD;' || recTable_1.PROD_NO || ',' || recTable_1.Prod_Nm || ',' ||
  31.                            recTable_1.Prod_Plan_Purpose || ',' ||
  32.                            recTable_1.Prod_Cnt_No);
  33.     END IF;
  34.   
  35.     --旧

  36.     sqlStr := '';
  37.     sqlStr := sqlStr || 'SELECT ';
  38.     sqlStr := sqlStr || ' * ';
  39.     sqlStr := sqlStr || 'FROM ';
  40.     sqlStr := sqlStr || ' Table b where b.prod_no = ''';
  41.     sqlStr := sqlStr || recTable_1.PROD_NO;
  42.     sqlStr := sqlStr || '''';
  43.     --

  44.     recTable := NULL;
  45.   
  46.     OPEN rsTable FOR sqlStr;
  47.     LOOP FETCH rsTable INTO recTable;
  48.       EXIT WHEN rsTable%NOTFOUND;
  49.     
  50.       updateColm := '';
  51.     
  52.       IF recTable.Prod_Nm <> recTable_1.Prod_Nm THEN
  53.         updateColm := updateColm || 'Prod_Nm;';
  54.       END IF;
  55.       
  56.       IF(NOT (recTable.Prod_Plan_Purpose IS NULL AND recTable_1.Prod_Plan_Purpose IS NULL OR recTable.Prod_Plan_Purpose IS NOT NULL AND recTable_1.Prod_Plan_Purpose IS NOT NULL AND recTable.Prod_Plan_Purpose = recTable_1.Prod_Plan_Purpose)) THEN
  57.         updateColm := updateColm || 'Prod_Plan_Purpose;';
  58.       END IF;
  59.     
  60. /* IF recTable.Prod_Plan_Purpose <> recTable_1.Prod_Plan_Purpose THEN
  61.         updateColm := updateColm || 'Prod_Plan_Purpose;';
  62.       END IF;*/
  63.     
  64.       IF recTable.Prod_Cnt_No <> recTable_1.Prod_Cnt_No THEN
  65.         updateColm := updateColm || 'Prod_Cnt_No;';
  66.       END IF;
  67.     
  68.     END LOOP;
  69.     CLOSE rsTable;
  70.   END LOOP;
  71.   CLOSE rsTable_1;

  72.   IF updateColm IS NOT NULL THEN
  73.     DBMS_OUTPUT.PUT_LINE('Old' || updateColm || recTable.PROD_NO || ',' || recTable.Prod_Nm || ',' ||
  74.                          recTable.Prod_Plan_Purpose || ',' ||
  75.                          recTable.Prod_Cnt_No);
  76.     DBMS_OUTPUT.PUT_LINE('New' || updateColm || recTable.PROD_NO || ',' || recTable_1.Prod_Nm || ',' ||
  77.                          recTable_1.Prod_Plan_Purpose || ',' ||
  78.                          recTable_1.Prod_Cnt_No);
  79.   END IF;

  80.   --旧

  81.   sqlStr := '';
  82.   sqlStr := sqlStr || 'SELECT ';
  83.   sqlStr := sqlStr || ' * ';
  84.   sqlStr := sqlStr || 'FROM ';
  85.   sqlStr := sqlStr || ' Table';

  86.   --

  87.   recTable := NULL;
  88.   OPEN rsTable FOR sqlStr;
  89.   LOOP FETCH rsTable INTO recTable;
  90.     EXIT WHEN rsTable%NOTFOUND;
  91.   
  92.     --新

  93.     SELECT COUNT(*) INTO cnt1 FROM Table_1 b WHERE b.prod_no = recTable.PROD_NO;
  94.   
  95.     IF cnt1 = 0 THEN
  96.       DBMS_OUTPUT.PUT_LINE('Old' || 'DELETE;' || recTable.PROD_NO || ',' || recTable.Prod_Nm || ',' ||
  97.                            recTable.Prod_Plan_Purpose || ',' ||
  98.                            recTable.Prod_Cnt_No);
  99.     END IF;
  100.     
  101.     END LOOP;
  102.     CLOSE rsTable;
  103.   
  104. END;

2.改造后

  1. CREATE OR REPLACE PROCEDURE autocomparetable(tableName IN CHAR)

  2. IS
  3.     strTemp VARCHAR2(8000);
  4.                 selectTemp VARCHAR2(8000);
  5.     whereTemp VARCHAR2(200);
  6.     compareTemp VARCHAR2(200);
  7.     compareOldTemp VARCHAR2(200);

  8.     --

  9.     TYPE colmPk IS RECORD(
  10.          COLUMN_NAME CHAR(40),
  11.          PK CHAR(20));

  12.     TYPE arrayColmPk IS TABLE OF colmPk INDEX BY BINARY_INTEGER;
  13.        arrayColmPkTemp arrayColmPk;

  14. BEGIN

  15.     --

  16.     SELECT
  17.             COL.COLUMN_NAME ,
  18.                NVL2(CONS.COLUMN_NAME, '1', NULL) PK
  19.     BULK COLLECT INTO arrayColmPkTemp
  20.     FROM USER_TAB_COLUMNS COL
  21.           LEFT JOIN (SELECT CONCOL.TABLE_NAME, CONCOL.COLUMN_NAME
  22.                            FROM USER_CONS_COLUMNS CONCOL
  23.                           INNER JOIN USER_CONSTRAINTS CON
  24.                          ON CON.OWNER = CONCOL.OWNER
  25.                         AND CON.CONSTRAINT_NAME = CONCOL.CONSTRAINT_NAME
  26.                         AND CON.CONSTRAINT_TYPE = 'P'
  27.           ) CONS
  28.         ON CONS.TABLE_NAME = COL.TABLE_NAME
  29.        AND CONS.COLUMN_NAME = COL.COLUMN_NAME
  30.     WHERE COL.TABLE_NAME ='TABLENAME'
  31.     ORDER BY COL.COLUMN_ID;

  32.     --

  33.     strTemp := '';
  34.     selectTemp := 'DATETYPE,';
  35.     whereTemp := 'WHERE ';
  36.     compareTemp := '';
  37.     compareOldTemp := '';
  38.     FOR i IN 1 .. arrayColmPkTemp.count LOOP
  39.         IF i = arrayColmPkTemp.count THEN
  40.             strTemp := strTemp || arrayColmPkTemp(i).COLUMN_NAME || 'TABLENAME.' || arrayColmPkTemp(i).COLUMN_NAME || '%TYPE);';
  41.             selectTemp := selectTemp ||arrayColmPkTemp(i).COLUMN_NAME;
  42.         ELSE
  43.             strTemp := strTemp || arrayColmPkTemp(i).COLUMN_NAME || 'TABLENAME.' || arrayColmPkTemp(i).COLUMN_NAME || '%TYPE,';
  44.             selectTemp := selectTemp ||arrayColmPkTemp(i).COLUMN_NAME || ',';
  45.         END IF;
  46.         
  47.         IF arrayColmPkTemp(i).PK IS NOT NULL THEN
  48.             IF i = 1 THEN
  49.                 whereTemp := whereTemp || arrayColmPkTemp(i).COLUMN_NAME || ' = ' || 'arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME;
  50.             ELSE
  51.                 whereTemp := whereTemp || ' AND ' || arrayColmPkTemp(i).COLUMN_NAME || ' = ' || 'arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME;
  52.             END IF;
  53.         ELSE
  54.             compareTemp := compareTemp
  55.                  || 'IF (NOT (oldTableColmTemp(1).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NULL AND '
  56.              || ' arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NULL OR '
  57.              || ' oldTableColmTemp(1).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NOT NULL AND '
  58.              || ' arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NOT NULL AND '
  59.              || ' oldTableColmTemp(1).' || arrayColmPkTemp(i).COLUMN_NAME || ' = arrayUpdtRecordTemp(i). ' || arrayColmPkTemp(i).COLUMN_NAME || ' )) THEN '
  60.                  || ' DBMS_OUTPUT.PUT_LINE('' OLDTABLE-UPDATERECORD; ''' || ''' arrayColmPkTemp(i).COLUMN_NAME; ''' || 'arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME || ');'
  61.              || ' END IF; ';
  62.             
  63.             compareOldTemp := compareOldTemp
  64.                  || 'IF (NOT (newTableColmTemp(1).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NULL AND '
  65.              || ' arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NULL OR '
  66.              || ' newTableColmTemp(1).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NOT NULL AND '
  67.              || ' arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME || ' IS NOT NULL AND '
  68.              || ' newTableColmTemp(1).' || arrayColmPkTemp(i).COLUMN_NAME || ' = arrayUpdtRecordTemp(i). ' || arrayColmPkTemp(i).COLUMN_NAME || ' )) THEN '
  69.                  || ' DBMS_OUTPUT.PUT_LINE('' NEWTABLE-UPDATERECORD; ''' || ''' arrayColmPkTemp(i).COLUMN_NAME; ''' || 'arrayUpdtRecordTemp(i).' || arrayColmPkTemp(i).COLUMN_NAME || ');'
  70.              || ' END IF; ';
  71.         END IF;
  72.     END LOOP;




  73.   EXECUTE IMMEDIATE
  74. '
  75.   DECLARE
  76.     --
  77.     TYPE colmPk IS RECORD(
  78.          COLUMN_NAME CHAR(40),
  79.          PK CHAR(20));

  80.     TYPE arrayColmPk IS TABLE OF colmPk INDEX BY BINARY_INTEGER;
  81.        arrayColmPkTemp arrayColmPk;

  82.     --
  83.     TYPE updtRecord IS RECORD(
  84.          DATETYPE CHAR(3),' || strTemp || '

  85.        TYPE arrayUpdtRecord IS TABLE OF
  86.        updtRecord INDEX BY BINARY_INTEGER;
  87.        arrayUpdtRecordTemp arrayUpdtRecord;

  88.     TYPE tableColm IS RECORD( ' || strTemp || '

  89.        TYPE oldTableColm IS TABLE OF
  90.        tableColm INDEX BY BINARY_INTEGER;
  91.        oldTableColmTemp oldTableColm;

  92.     TYPE newTableColm IS TABLE OF
  93.        tableColm INDEX BY BINARY_INTEGER;
  94.        newTableColmTemp newTableColm;

  95.       BEGIN

  96.     --
  97.     SELECT ' || selectTemp || '
  98.         BULK COLLECT INTO arrayUpdtRecordTemp
  99.         FROM (SELECT ' || '''' || 'NEW' || '''' ||
  100.                     ' DATETYPE, NEWTABLE.*
  101.             FROM TABLENAME_1 NEWTABLE
  102.           MINUS
  103.           SELECT ' || '''' || 'NEW' || '''' ||
  104.                     ' DATETYPE, OLDTABLE.*
  105.             FROM TABLENAME OLDTABLE
  106.           UNION
  107.           SELECT ' || '''' || 'OLD' || '''' ||
  108.                     ' DATETYPE, OLDTABLE.*
  109.             FROM TABLENAME OLDTABLE
  110.           MINUS
  111.           SELECT ' || '''' || 'OLD' || '''' ||
  112.                     ' DATETYPE, NEWTABLE.* FROM TABLENAME_1 NEWTABLE)
  113.        ORDER BY PROD_NO;

  114.     --
  115.     FOR i IN 1 .. arrayUpdtRecordTemp.count LOOP

  116.         IF (arrayUpdtRecordTemp(i).DATETYPE = ''NEW'') THEN
  117.             --
  118.              SELECT * BULK COLLECT INTO oldTableColmTemp FROM TABLENAME ' || whereTemp || ';' || '

  119.             IF oldTableColmTemp.count = 0 THEN
  120.                 --
  121.                 DBMS_OUTPUT.PUT_LINE('' OLDTABLE-ADDRECORD; '' || arrayUpdtRecordTemp(i).PROD_NO);
  122.             ELSE '
  123.             
  124.             || compareTemp || '

  125.             END IF;
  126.          ELSE
  127.             --
  128.             SELECT * BULK COLLECT INTO newTableColmTemp FROM TABLENAME_1 ' || whereTemp || ';' || '

  129.             IF newTableColmTemp.count = 0 THEN
  130.                 --
  131.                 DBMS_OUTPUT.PUT_LINE('' NEWTABLE-DELETERECORD; '' || arrayUpdtRecordTemp(i).PROD_NO);
  132.             ELSE '
  133.             
  134.             || compareOldTemp || '

  135.             END IF;
  136.          END IF;
  137.     END LOOP;
  138. END;
  139. ';
  140. END;
  141. /
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP