oracle 具有异库查询的功能. 可以通过建立dblink,从而可以在不同的数据库之间执行sql脚本
方法如下:
1, 建立dblink
DROP PUBLIC DATABASE LINK "NET3_TO_ECP.US.ORACLE.COM";
CREATE PUBLIC DATABASE LINK "NET3_TO_ECP.US.ORACLE.COM"
CONNECT TO NET3_TEST
IDENTIFIED BY <PWD>//这里替换成要连接的数据库密码
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 131.1.2.82)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = HOTYEDB)
)
)';
2. 由此可以通过dblink的方式查询异库的数据
select * from point@NET3_TO_ECP.US.ORACLE.COM
通过dblink查找异库数据并循环插入到本地的pl/sql语句
DECLARE
BEGIN
FOR aa IN (SELECT hu.user_name, pj.institute_trace
FROM point_journal@net3_to_ecp.us.oracle.com pj, hotye_user@net3_to_ecp.us.oracle.com hu, user_point@net3_to_ecp.us.oracle.com UP
WHERE hu.user_id = UP.user_id
AND UP.ID = pj.user_point_id
AND pj.institute_trace IS NOT NULL
AND pj.trans_time BETWEEN TO_DATE ('2008-10-01',
'yyyy-mm-dd')
AND TO_DATE ('2008-10-12',
'yyyy-mm-dd'))
LOOP
UPDATE t_transline
SET useraccount = aa.user_name
WHERE systemtrace = aa.institute_trace;
END LOOP;
COMMIT;
END;
/