免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
› 论坛 › 数据库技术 › Oracle › 两表比较
最近访问板块 发新帖
查看: 819 | 回复: 0
打印 上一主题 下一主题

两表比较 [复制链接]

watergao

论坛徽章:
0
跳转到指定楼层
1楼 [收藏(0)] [报告]
发表于 2011-12-22 08:53 |只看该作者 |倒序浏览

1.第一版

  1. CREATE OR REPLACE PROCEDURE comparetable

  3.  IS
  4.   --SQL

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


  13.   --

  15.   rsTable_1 SYS_REFCURSOR; --新

  17.   rsTable SYS_REFCURSOR; --旧


  20.   --

  22.   recTable_1 Table_1%ROWTYPE; --新

  24.   recTable Table%ROWTYPE; --旧


  27. BEGIN

  29.   --新

  31.   sqlStr := '';
  32.   sqlStr := sqlStr || 'SELECT ';
  33.   sqlStr := sqlStr || ' * ';
  34.   sqlStr := sqlStr || 'FROM ';
  35.   sqlStr := sqlStr || ' Table_1';

  37.   --

  39.   recTable_1 := NULL;
  40.   OPEN rsTable_1 FOR sqlStr;
  41.   LOOP FETCH rsTable_1 INTO recTable_1;
  42.     EXIT WHEN rsTable_1%NOTFOUND;
  43.     
  44.     SELECT COUNT(*) INTO cnt2 FROM Table b WHERE b.prod_no = recTable_1.PROD_NO;
  45.     IF cnt2 = 0 THEN
  46.        DBMS_OUTPUT.PUT_LINE('NEW' || 'ADD;' || recTable_1.PROD_NO || ',' || recTable_1.Prod_Nm || ',' ||
  47.                            recTable_1.Prod_Plan_Purpose || ',' ||
  48.                            recTable_1.Prod_Cnt_No);
  49.     END IF;
  50.   
  51.     --旧

  53.     sqlStr := '';
  54.     sqlStr := sqlStr || 'SELECT ';
  55.     sqlStr := sqlStr || ' * ';
  56.     sqlStr := sqlStr || 'FROM ';
  57.     sqlStr := sqlStr || ' Table b where b.prod_no = ''';
  58.     sqlStr := sqlStr || recTable_1.PROD_NO;
  59.     sqlStr := sqlStr || '''';
  60.     --

  62.     recTable := NULL;
  63.   
  64.     OPEN rsTable FOR sqlStr;
  65.     LOOP FETCH rsTable INTO recTable;
  66.       EXIT WHEN rsTable%NOTFOUND;
  67.     
  68.       updateColm := '';
  69.     
  70.       IF recTable.Prod_Nm <> recTable_1.Prod_Nm THEN
  71.         updateColm := updateColm || 'Prod_Nm;';
  72.       END IF;
  73.       
  74.       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
  75.         updateColm := updateColm || 'Prod_Plan_Purpose;';
  76.       END IF;
  77.     
  78. /* IF recTable.Prod_Plan_Purpose <> recTable_1.Prod_Plan_Purpose THEN
  79.         updateColm := updateColm || 'Prod_Plan_Purpose;';
  80.       END IF;*/
  81.     
  82.       IF recTable.Prod_Cnt_No <> recTable_1.Prod_Cnt_No THEN
  83.         updateColm := updateColm || 'Prod_Cnt_No;';
  84.       END IF;
  85.     
  86.     END LOOP;
  87.     CLOSE rsTable;
  88.   END LOOP;
  89.   CLOSE rsTable_1;

  91.   IF updateColm IS NOT NULL THEN
  92.     DBMS_OUTPUT.PUT_LINE('Old' || updateColm || recTable.PROD_NO || ',' || recTable.Prod_Nm || ',' ||
  93.                          recTable.Prod_Plan_Purpose || ',' ||
  94.                          recTable.Prod_Cnt_No);
  95.     DBMS_OUTPUT.PUT_LINE('New' || updateColm || recTable.PROD_NO || ',' || recTable_1.Prod_Nm || ',' ||
  96.                          recTable_1.Prod_Plan_Purpose || ',' ||
  97.                          recTable_1.Prod_Cnt_No);
  98.   END IF;

  100.   --旧

  102.   sqlStr := '';
  103.   sqlStr := sqlStr || 'SELECT ';
  104.   sqlStr := sqlStr || ' * ';
  105.   sqlStr := sqlStr || 'FROM ';
  106.   sqlStr := sqlStr || ' Table';

  108.   --

  110.   recTable := NULL;
  111.   OPEN rsTable FOR sqlStr;
  112.   LOOP FETCH rsTable INTO recTable;
  113.     EXIT WHEN rsTable%NOTFOUND;
  114.   
  115.     --新

  117.     SELECT COUNT(*) INTO cnt1 FROM Table_1 b WHERE b.prod_no = recTable.PROD_NO;
  118.   
  119.     IF cnt1 = 0 THEN
  120.       DBMS_OUTPUT.PUT_LINE('Old' || 'DELETE;' || recTable.PROD_NO || ',' || recTable.Prod_Nm || ',' ||
  121.                            recTable.Prod_Plan_Purpose || ',' ||
  122.                            recTable.Prod_Cnt_No);
  123.     END IF;
  124.     
  125.     END LOOP;
  126.     CLOSE rsTable;
  127.   
  128. END;

2.改造后

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

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

  10.     --

  12.     TYPE colmPk IS RECORD(
  13.          COLUMN_NAME CHAR(40),
  14.          PK CHAR(20));

  16.     TYPE arrayColmPk IS TABLE OF colmPk INDEX BY BINARY_INTEGER;
  17.        arrayColmPkTemp arrayColmPk;

  19. BEGIN

  21.     --

  23.     SELECT
  24.             COL.COLUMN_NAME ,
  25.                NVL2(CONS.COLUMN_NAME, '1', NULL) PK
  26.     BULK COLLECT INTO arrayColmPkTemp
  27.     FROM USER_TAB_COLUMNS COL
  28.           LEFT JOIN (SELECT CONCOL.TABLE_NAME, CONCOL.COLUMN_NAME
  29.                            FROM USER_CONS_COLUMNS CONCOL
  30.                           INNER JOIN USER_CONSTRAINTS CON
  31.                          ON CON.OWNER = CONCOL.OWNER
  32.                         AND CON.CONSTRAINT_NAME = CONCOL.CONSTRAINT_NAME
  33.                         AND CON.CONSTRAINT_TYPE = 'P'
  34.           ) CONS
  35.         ON CONS.TABLE_NAME = COL.TABLE_NAME
  36.        AND CONS.COLUMN_NAME = COL.COLUMN_NAME
  37.     WHERE COL.TABLE_NAME ='TABLENAME'
  38.     ORDER BY COL.COLUMN_ID;

  40.     --

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




  86.   EXECUTE IMMEDIATE
  87. '
  88.   DECLARE
  89.     --
  90.     TYPE colmPk IS RECORD(
  91.          COLUMN_NAME CHAR(40),
  92.          PK CHAR(20));

  94.     TYPE arrayColmPk IS TABLE OF colmPk INDEX BY BINARY_INTEGER;
  95.        arrayColmPkTemp arrayColmPk;

  97.     --
  98.     TYPE updtRecord IS RECORD(
  99.          DATETYPE CHAR(3),' || strTemp || '

  101.        TYPE arrayUpdtRecord IS TABLE OF
  102.        updtRecord INDEX BY BINARY_INTEGER;
  103.        arrayUpdtRecordTemp arrayUpdtRecord;

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

  107.        TYPE oldTableColm IS TABLE OF
  108.        tableColm INDEX BY BINARY_INTEGER;
  109.        oldTableColmTemp oldTableColm;

  111.     TYPE newTableColm IS TABLE OF
  112.        tableColm INDEX BY BINARY_INTEGER;
  113.        newTableColmTemp newTableColm;

  115.       BEGIN

  117.     --
  118.     SELECT ' || selectTemp || '
  119.         BULK COLLECT INTO arrayUpdtRecordTemp
  120.         FROM (SELECT ' || '''' || 'NEW' || '''' ||
  121.                     ' DATETYPE, NEWTABLE.*
  122.             FROM TABLENAME_1 NEWTABLE
  123.           MINUS
  124.           SELECT ' || '''' || 'NEW' || '''' ||
  125.                     ' DATETYPE, OLDTABLE.*
  126.             FROM TABLENAME OLDTABLE
  127.           UNION
  128.           SELECT ' || '''' || 'OLD' || '''' ||
  129.                     ' DATETYPE, OLDTABLE.*
  130.             FROM TABLENAME OLDTABLE
  131.           MINUS
  132.           SELECT ' || '''' || 'OLD' || '''' ||
  133.                     ' DATETYPE, NEWTABLE.* FROM TABLENAME_1 NEWTABLE)
  134.        ORDER BY PROD_NO;

  136.     --
  137.     FOR i IN 1 .. arrayUpdtRecordTemp.count LOOP

  139.         IF (arrayUpdtRecordTemp(i).DATETYPE = ''NEW'') THEN
  140.             --
  141.              SELECT * BULK COLLECT INTO oldTableColmTemp FROM TABLENAME ' || whereTemp || ';' || '

  143.             IF oldTableColmTemp.count = 0 THEN
  144.                 --
  145.                 DBMS_OUTPUT.PUT_LINE('' OLDTABLE-ADDRECORD; '' || arrayUpdtRecordTemp(i).PROD_NO);
  146.             ELSE '
  147.             
  148.             || compareTemp || '

  150.             END IF;
  151.          ELSE
  152.             --
  153.             SELECT * BULK COLLECT INTO newTableColmTemp FROM TABLENAME_1 ' || whereTemp || ';' || '

  155.             IF newTableColmTemp.count = 0 THEN
  156.                 --
  157.                 DBMS_OUTPUT.PUT_LINE('' NEWTABLE-DELETERECORD; '' || arrayUpdtRecordTemp(i).PROD_NO);
  158.             ELSE '
  159.             
  160.             || compareOldTemp || '

  162.             END IF;
  163.          END IF;
  164.     END LOOP;
  165. END;
  166. ';
  167. END;
  168. /
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

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

清除 Cookies - ChinaUnix - Archiver - WAP - TOP