通过dblink抽取数据处理代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】通过dblink抽取数据处理

作者:Parker / 发布于2015/10/13/ 1312

就是通过select * from user_sequence和select * from all_tables 来获取sequence和table的名字进行拼串最怕的就是当前账号的权限不够


	create or replace package extraction_data is
	 -- Author : ADMINISTRATOR
	 -- Created : 2010-12-15 下午 03:36:28
	 -- Purpose : 抽取数据
	
	 Cursor TNAME is SELECT table_name AS table_name FROM all_tables@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM where owner =upper('tieba');
	
	 PROCEDURE EXT_DATA;
	
	 PROCEDURE EXT_SEQ;
	end extraction_data;
	/
	create or replace package body extraction_data is

	PROCEDURE EXT_DATA IS
	BEGIN
	FOR T IN TNAME LOOP
	/* dbms_output.put_line('执行insert过程'||T.table_name);*/
	 --执行表的删除操作，防止重复插入数据
	 begin
	 EXECUTE IMMEDIATE 'drop table '||T.table_name ;
	 EXCEPTION WHEN OTHERS THEN NULL;
	 end;
	
	 --输出要执行的sql
	 dbms_output.put_line('create table tieba.'||T.table_name||' as select * from '||T.table_name||'@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM');
	 dbms_output.put_line('commit;');
	 --将远程的表的数据拿到本地，可能执行不了呀
	 /*EXECUTE IMMEDIATE 'create table tieba.'||T.table_name||' as select * from '||T.table_name||'@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM';*/
	
	END LOOP;
	END EXT_DATA;
	--此过程没有成功执行。如果需要可以拼装sequence串
	PROCEDURE EXT_SEQ IS
	BEGIN
	-- Create sequence
	--create sequence ADMIN_MESSAGE_SEQ minvalue 1 maxvalue 999999999 start with 100000 increment by 1 cache 20;
	--select * from user_sequence;
	FOR T IN (select * from user_sequences@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM) LOOP
	/* dbms_output.put_line('执行insert过程'||T.table_name);*/
	 --执行表的删除操作，防止重复插入数据
	 begin
	 EXECUTE IMMEDIATE 'drop sequence '||T.sequence_name ;
	 EXCEPTION WHEN OTHERS THEN NULL;
	 end;
	
	 --输出要执行的sql
	 dbms_output.put_line('create sequence tieba.'||T.sequence_name||' minvalue 1 maxvalue 999999999 start with '||T.LAST_NUMBER||' increment by 1 cache 20;');
	/* EXECUTE IMMEDIATE 'create sequence tieba.'||T.sequence_name||' minvalue 1 maxvalue 999999999 start with '||T.LAST_NUMBER||' increment by 1 cache 20;';*/
	END LOOP;
	dbms_output.put_line('commit;');
	END EXT_SEQ;
	end extraction_data;
	/

试试其它关键字

　dblink　　数据处理　

同语言下

可能有用的

Parker贡献的其它代码(3)