- create or replace PROCEDURE "SYNC_DATA_FROM_DBLINK_DB" AS
- BEGIN
- DECLARE
- CURSOR c_TabNames IS
- SELECT TNAME FROM TAB;
- v_TabName c_TabNames%ROWTYPE;
- v_SQL VARCHAR2(500);
- v_rowcount NUMBER;
- v_rc NUMBER:=0;
- v_tab NUMBER :=0;
- BEGIN
- Dbms_Output.put_line(to_char(SYSDATE,'yy-mm-dd hh24:mi:ss')||' 开始从远程的TEST2实例导入表.');
- FOR v_TabName in c_TabNames LOOP
- v_SQL := 'DELETE '||v_TabName.TNAME;
- EXECUTE IMMEDIATE v_sql;
- v_sql := 'INSERT INTO '|| v_tabname.TNAME||' SELECT * FROM '|| v_tabname.TNAME||'@TEST2';
- EXECUTE IMMEDIATE v_sql;
- COMMIT;
- v_SQL :='SELECT COUNT(*) FROM '|| v_tabname.TNAME;
- EXECUTE IMMEDIATE v_sql INTO v_rowcount;
- v_tab := v_tab +1;
- v_rc := v_rc + v_rowcount;
- END LOOP;
- Dbms_Output.put_line(to_char(SYSDATE,'yy-mm-dd hh24:mi:ss')||' 导入完成,共导入表'||to_char(v_tab)||'张,总记录数'||to_char(v_rc)||'条记录。');
- END;
- END SYNC_DATA_FROM_DBLINK_DB;
- --该片段来自于http://www.codesnippet.cn/detail/250320132540.html
来源: http://www.codesnippet.cn/detail/250320132540.html