能自动创建过程脚本,能让你直接输入源泉表名和目标表名就能创建以下链接中的全表复制过程.(第三部分) http://steven1981.itpub.net/post/7967/413828

[@more@]

--表复制过程NEW

create or replace procedure hyf_create_sql(p_sour_tab in varchar2,
p_dst_tab in varchar2) as
/*
-- 此存过能产生:一个用来从源表复制到目标表的存过;
-- 要求在目标库上布置这个代码 ;(即 p_dst_tab必须为本地表)
-- 使用:

exec hyf_create_sql('sour_table_name[@dblink_name]','dst_table_name');

Last_modifier:Steven
Last_modified:2008-08-21
*/

v_sour_domain varchar2(100);
v_dst_domain varchar2(100);
v_sour_tab varchar2(30);
v_dst_tab varchar2(30);
v_proc_name varchar2(30);
v_proc_code varchar2(32767);
v_buffer varchar2(100);
type t_cur is ref cursor;
v_cur t_cur;
v_col_num number;
v_tab_count number;
v_sum number;
E_no_table exception;
E_diff exception;

begin
--判断是否为远程表
if instr(p_sour_tab, '@') = 0 then
v_sour_domain := '';
v_sour_tab := upper(p_sour_tab);
else
v_sour_domain := substr(p_sour_tab, instr(p_sour_tab, '@'));
v_sour_tab := upper(substr(p_sour_tab, 1, instr(p_sour_tab, '@') - 1));
end if;

if instr(p_dst_tab, '@') = 0 then
v_dst_domain := '';
v_dst_tab := upper(p_dst_tab);
else
v_dst_domain := substr(p_dst_tab, instr(p_dst_tab, '@'));
v_dst_tab := upper(substr(p_dst_tab, 1, instr(p_dst_tab, '@') - 1));
end if;

--判断源表和目标表是不是都存在
execute immediate 'select count(*) from user_tables' || v_sour_domain ||
' where table_name=''' || v_sour_tab || ''''
into v_tab_count;
if v_tab_count = 0 then
raise e_no_table;
end if;

select count(*)
into v_tab_count
from user_tables
where table_name = upper(p_dst_tab);
if v_tab_count = 0 then
raise e_no_table;
end if;

--判断源表和目标表的字段是不是一致
execute immediate ' select count(*) from (select column_name, data_type, data_length from user_tab_columns' ||
v_sour_domain || ' where table_name = ''' || v_sour_tab ||
''' intersect select column_name, data_type, data_length from user_tab_columns' ||
v_dst_domain || ' where table_name = ''' || v_dst_tab ||
''')'
into v_tab_count;

execute immediate 'select count(*) from user_tab_columns' ||
v_sour_domain || ' where table_name = ''' || v_sour_tab || ''''
into v_col_num;

if v_col_num <> v_tab_count then
raise e_diff;
end if;

--检查过程名是不是有冲突
v_proc_name := 'cp_' || substr(v_sour_tab, 1, 13) || '2' ||
substr(p_dst_tab, 1, 13);
loop
select count(*)
into v_tab_count
from user_objects
where object_type = 'PROCEDURE'
and object_name = upper(v_proc_name);
exit when v_tab_count = 0;
v_sum := v_sum + 1;
v_proc_name := substr(v_proc_name, 1, 29) || v_sum;
end loop;
dbms_output.put_line(v_proc_name);
--开始组织存过代码

v_proc_code := 'create or replace procedure ' || v_proc_name || ' as ';

for x in (select 'type TYPE_' || column_name || ' is table of ' ||
table_name || '.' || column_name || '%type;' as dd
from user_tab_columns
where table_name = v_dst_tab
order by column_id asc) loop
v_proc_code := v_proc_code || x.dd;
end loop;

for x in (select 'V_' || column_name || ' TYPE_' || column_name || ';' as dd
from user_tab_columns
where table_name = v_dst_tab
order by column_id asc) loop
v_proc_code := v_proc_code || x.dd;
end loop;

v_proc_code := v_proc_code || 'type t_cur is ref cursor;';
v_proc_code := v_proc_code || 'c_table t_cur;';
v_proc_code := v_proc_code || 'v_sql varchar2(500);';
v_proc_code := v_proc_code || 'v_rows number := 10000;';
v_proc_code := v_proc_code || 'begin ';
v_proc_code := v_proc_code || 'execute immediate ''truncate table ' ||
p_dst_tab || ''';';
v_proc_code := v_proc_code || 'open c_table for';
dbms_output.put_line(p_sour_tab);
v_proc_code := v_proc_code || ' select * from ' || p_sour_tab || ';';
v_proc_code := v_proc_code || 'v_sql := ''insert /*+ APPEND*/ into ' ||
p_dst_tab || ' (';

open v_cur for
select column_name
from user_tab_columns
where table_name = v_dst_tab
order by column_id asc;

for i in 1 .. v_col_num loop
fetch v_cur
into v_buffer;
if i <> v_col_num then
v_proc_code := v_proc_code || v_buffer || ',';
else
v_proc_code := v_proc_code || v_buffer || ')';
end if;
end loop;
close v_cur;

v_proc_code := v_proc_code || ' values (';
for i in 1 .. v_col_num loop
if i <> v_col_num then
v_proc_code := v_proc_code || ':' || i || ',';
else
v_proc_code := v_proc_code || ':' || i || ')'';';
end if;
end loop;

v_proc_code := v_proc_code || 'loop ';
v_proc_code := v_proc_code || ' fetch c_table ';
v_proc_code := v_proc_code || ' bulk collect into ';

open v_cur for
select 'v_' || column_name
from user_tab_columns
where table_name = v_dst_tab
order by column_id asc;
for i in 1 .. v_col_num loop
fetch v_cur
into v_buffer;
if i <> v_col_num then
v_buffer := v_buffer || ',';
end if;
v_proc_code := v_proc_code || v_buffer;
end loop;
close v_cur;

v_proc_code := v_proc_code || ' limit v_rows;';

v_proc_code := v_proc_code || 'forall i in 1 .. ' || v_buffer ||
'.count execute immediate v_sql using ';

open v_cur for
select 'v_' || column_name || '(i)'
from user_tab_columns
where table_name = v_dst_tab
order by column_id asc;

for i in 1 .. v_col_num loop
fetch v_cur
into v_buffer;
if i <> v_col_num then
v_buffer := v_buffer || ',';
else
v_buffer := v_buffer || ';';
end if;
v_proc_code := v_proc_code || v_buffer;
end loop;
close v_cur;
v_proc_code := v_proc_code || ' commit;';
v_proc_code := v_proc_code || ' exit when c_table%notfound;';
v_proc_code := v_proc_code || 'end loop;';
v_proc_code := v_proc_code || ' close c_table;';
v_proc_code := v_proc_code || 'end;';

--产生过程
execute immediate v_proc_code;

exception
when e_no_table then
dbms_output.put_line('ERROR -- Can not found the SOURCE table or DEST table!');
when e_diff then
dbms_output.put_line('ERROR -- There is some DIFF between SOURCE table and DEST table ! ');
end;

ITPub博客

全表复制过程创建脚本

最新文章