oracle 检测表字段区别

胥2772

DECLARE

  V_COUNT NUMBER;

  V_SQL VARCHAR2(1000);

BEGIN

  --drop database link LINK_SRC;

  --create database link LINK_SRC connect to xzgl identified by "xzgl" using '10.114.72.168/orclrep';

  --drop database link LINK_TARGET;

  --create database link LINK_TARGET connect to xzgl identified by "XZgl123456" using '10.36.10.105/scshdb';

  ---------------初始化结果表部分--------------------------------------------------------------

  SELECT COUNT(*) INTO V_COUNT FROM USER_TABLES WHERE TABLE_NAME='DIFFERENCE';

  IF V_COUNT>0 THEN--已经存在差异结果表

    V_SQL := 'TRUNCATE TABLE DIFFERENCE';

  ELSE--未存在

    V_SQL := 'CREATE TABLE DIFFERENCE(DIFF_TYPE VARCHAR2(20),MARK VARCHAR(2),DIFF_VALUE VARCHAR2(200),

      SRC_FIELD_TYPE VARCHAR2(50),TARGET_FIELD_TYPE VARCHAR2(50))';

  END IF;

  EXECUTE IMMEDIATE(V_SQL);

  ------------------------------------------------------------------------------------------------

  --------------开始交叉比较，将数据插入以上定义的表中----------------

  --①源比目标多的表

  FOR T IN(SELECT TABLE_NAME FROM USER_TABLES@link_src WHERE TABLE_NAME NOT LIKE 'BIN$%') LOOP

    SELECT COUNT(*) INTO V_COUNT FROM USER_TABLES@link_target WHERE TABLE_NAME=T.TABLE_NAME;

    IF V_COUNT=0 THEN

      V_SQL := 'INSERT INTO DIFFERENCE VALUES(''表'',''+'','''||T.TABLE_NAME||''',NULL,NULL)';

      EXECUTE IMMEDIATE(V_SQL);

    END IF;

  END LOOP;

  --②源比目标少的表

  FOR T IN(SELECT TABLE_NAME FROM USER_TABLES@link_target WHERE TABLE_NAME NOT LIKE 'BIN$%') LOOP

    SELECT COUNT(*) INTO V_COUNT FROM USER_TABLES@link_src WHERE TABLE_NAME=T.TABLE_NAME;

    IF V_COUNT=0 THEN

      V_SQL := 'INSERT INTO DIFFERENCE VALUES(''表'',''-'','''||T.TABLE_NAME||''',NULL,NULL)';

      EXECUTE IMMEDIATE(V_SQL);

    END IF;

  END LOOP;

  --③源比目标多的字段

  FOR T IN(SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS@link_src WHERE TABLE_NAME NOT LIKE 'BIN$%') LOOP

    SELECT COUNT(*) INTO V_COUNT FROM USER_TAB_COLUMNS@link_target WHERE TABLE_NAME=T.TABLE_NAME

      AND COLUMN_NAME=T.COLUMN_NAME;

    IF V_COUNT=0 THEN

      V_SQL := 'INSERT INTO DIFFERENCE VALUES(''字段'',''+'','''||T.TABLE_NAME||'->'||T.COLUMN_NAME||''',NULL,NULL)';

      EXECUTE IMMEDIATE(V_SQL);

    END IF;

  END LOOP;

  --④源比目标少的字段

  FOR T IN(SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS@link_target WHERE TABLE_NAME NOT LIKE 'BIN$%') LOOP

    SELECT COUNT(*) INTO V_COUNT FROM USER_TAB_COLUMNS@link_src WHERE TABLE_NAME=T.TABLE_NAME

      AND COLUMN_NAME=T.COLUMN_NAME;

    IF V_COUNT=0 THEN

      V_SQL := 'INSERT INTO DIFFERENCE VALUES(''字段'',''-'','''||T.TABLE_NAME||'->'||T.COLUMN_NAME||''',NULL,NULL)';

      EXECUTE IMMEDIATE(V_SQL);

    END IF;

  END LOOP;

  --⑤源和目标字段相同但数据类型不同

  FOR T IN(SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS@link_src WHERE TABLE_NAME NOT LIKE 'BIN$%') LOOP

    SELECT COUNT(*) INTO V_COUNT FROM USER_TAB_COLUMNS@link_target WHERE TABLE_NAME=T.TABLE_NAME

      AND COLUMN_NAME=T.COLUMN_NAME AND DATA_TYPE<>T.DATA_TYPE ;

    IF V_COUNT>0 THEN

      V_SQL := 'INSERT INTO DIFFERENCE VALUES(''数据类型'',''<>'','''||T.TABLE_NAME||'->'||T.COLUMN_NAME||''',NULL,NULL)';

      EXECUTE IMMEDIATE(V_SQL);

    END IF;

  END LOOP;

END;