generic stored procedure in oracle
I want to write a PLSQL stored procedure that accepts a table name as argument. This table is source table. Now inside my procedure i want to manipulate the fields o开发者_StackOverflow中文版f that table.
EX: I want to insert the records of this source table into another target table whose name is XYZ_<source table name>
. The column names for source and target tables are the same. But there may be some extra fields in target table. How do i do it? The order of column names is not same.
You will have to build the INSERT statement dynamically.
create or replace procedure gen_insert
(p_src_table in user_tables.table_name%type
, p_no_of_rows out pls_integer)
is
col_str varchar2(16000);
begin
for rec in ( select column_name
, column_id
from user_tab_columns
where table_name = p_src_table
order by column_id )
loop
if rec.column_id != 1 then
col_str := col_str || ',' || rec.column_name;
else
col_str := rec.column_name;
end if:
end loop;
execute immediate 'insert into xyz_' || p_src_table || '('
|| col_str || ')'
|| ' select ' || col_str
|| ' from ' || p_src_table;
p_no_of_rows := sql%rowcount;
end;
/
Obviously you may want to include some error handling and other improvements.
edit
Having edited your question I see you have a special requirement for naming the target table which was obscured by the SO formatting.
You can do this using Dynamic SQL. Here's a link with basic info on Oracle Dynamic SQL
精彩评论