oracle 检测表字段区别

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;