create or replace procedure proc_acct(in_table in varchar2 default 'ACCT',o_str out varchar2)
as
v_reccnt integer;
v_colcnt integer;
v_str varchar2(3000):='';
v_col user_tab_columns.COLUMN_NAME%type;
v_data_type user_tab_columns.data_type%type;
v_data_length user_tab_columns.DATA_LENGTH%type;
cursor cur_acct is select infortyp,addr from acct;

v_infortyp acct.infortyp%type;
v_addr acct.addr%type;
begin

select count(1) into v_reccnt from acct;
for i in 1..v_reccnt loop
   for x in (select column_name,data_type,data_length
            from user_tab_columns
            where table_name=upper(in_table) and column_name not in('ACCTNBR')) loop

v_str:=v_str||x.column_name||i||' '||x.data_type||''||'('||x.data_length||'),';
end loop;
end loop;
v_str:=substr(v_str,1,length(v_str)-1);
v_str:='acctnbr number(22),'||v_str;
v_str:='create table v_t('||v_str;
v_str:=v_str||')';
o_str:=v_str;
execute immediate o_str;

ITPub博客

csii_根据acct表同一个acctnbr（列转行)动态sql_存储过程

最新文章