oracle下,有两个sid 分别为orcl和neworcl
在这两个sid下分别有两个数据库,库A有一张人员表为employeeA,库B有一张人员表为employeeB
employeeA下的人员名称字段为username,employeeB下的人员名称字段为fullname
如何能够通过触发器一类的方式比较username和fullname中不一致的数据本人只初略懂得oracle的相关操作,没想到遇到了这样的问题。还请各位高手帮帮忙,能够指点一下,最好能够稍微详细一点,谢谢大家。
在这两个sid下分别有两个数据库,库A有一张人员表为employeeA,库B有一张人员表为employeeB
employeeA下的人员名称字段为username,employeeB下的人员名称字段为fullname
如何能够通过触发器一类的方式比较username和fullname中不一致的数据本人只初略懂得oracle的相关操作,没想到遇到了这样的问题。还请各位高手帮帮忙,能够指点一下,最好能够稍微详细一点,谢谢大家。
数据库A,用户USERA 密码PWDA
数据库B,用户USERB 密码PWDB 在B 的USERB上创建 dblink B.TO.A
create database link B.TO.A
connect to USERA identified by PWDA
using 'A';
create or replace procedure pro_checkcount(IN_IP VARCHAR2) AUTHID CURRENT_USER IS
/*
比对两库表,数据量不同的记录,插入表T_CHECKCOUNT中
*/
V_DBLINK_SQL VARCHAR2(100);
V_DBLINK VARCHAR2(40);
V_NUM NUMBER(2);
V_CUR_SQL VARCHAR2(2000);
V_SELECT_SQL VARCHAR2(2000);
V_CUR SYS_REFCURSOR;
V_TABLE VARCHAR2(40);
TYPE T_DATA IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
V_COL1 T_DATA;
V_COL2 T_DATA;
V_SQL VARCHAR2(2000);BEGIN
V_DBLINK_SQL := 'SELECT DB_LINK FROM USER_DB_LINKS WHERE DB_LINK LIKE ''%' ||
IN_IP || '%''';
BEGIN
EXECUTE IMMEDIATE V_DBLINK_SQL
INTO V_DBLINK;
--没有DBLINK,新建
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE 'create database link ORCL_' || IN_IP ||
' connect to CASEIM identified by CASEIM using ''(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 32.1.5.' ||
IN_IP ||
')(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)))''';
END; BEGIN
--建表语句
V_SQL:='create table T_CHECKCOUNT(TABLE_NAME VARCHAR2(40),COLONE VARCHAR2(100),COLTWO VARCHAR2(100),OPTIME DATE)';SS
END; BEGIN
EXECUTE IMMEDIATE V_DBLINK_SQL
INTO V_DBLINK;
EXECUTE IMMEDIATE 'TRUNCATE TABLE T_CHECKCOUNT';
V_CUR_SQL := 'SELECT A.TABLE_NAME
FROM (SELECT TABLE_NAME FROM USER_TABLES) A,
(SELECT TABLE_NAME FROM USER_TABLES@ORCL_' || IN_IP || ') B
WHERE A.TABLE_NAME = B.TABLE_NAME';
OPEN V_CUR FOR V_CUR_SQL;
LOOP
FETCH V_CUR
INTO V_TABLE;
EXIT WHEN V_CUR%NOTFOUND;
V_SELECT_SQL := 'SELECT (SELECT COUNT(*) FROM ' || V_TABLE ||
'),(SELECT COUNT(*) FROM ' || V_TABLE || '@' ||
V_DBLINK || ') FROM DUAL';
EXECUTE IMMEDIATE V_SELECT_SQL BULK COLLECT
INTO V_COL1, V_COL2;
--两库同张表数据量不同才插入
IF V_COL1(1) <> V_COL2(1) THEN
INSERT INTO T_CHECKCOUNT
(TABLE_NAME, COLONE, COLTWO, OPTIME)
VALUES
(V_TABLE,
'CURRENT_USERS:条数为' || V_COL1(1),
'IP_' || IN_IP || ':条数为' || V_COL2(1),
SYSDATE);
END IF;
END LOOP;
CLOSE V_CUR;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
EXECUTE IMMEDIATE V_DBLINK_SQL
INTO V_DBLINK;
--异常时,删去刚建的DBLINK
EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || V_DBLINK;
END;