能自動建立過程指令碼,能讓你直接輸入源泉表名和目標表名就能建立以下連結中的全表複製過程.(第三部分) 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;

全表複製過程建立指令碼

相關文章