×Ô¼ºÐ´µÄoracle´æ´¢¹ý³Ì»áÓõ½

create or replace procedure prc_statistic_declare(table_name varchar2 ,table_name_pass varchar2 ,not_exist varchar2,not_exist_record varchar2)
--eg:'t_statistic_bianyuanhu_month',t_statistic_bianyuanhu_month,('YEAR','STATISTIC_ID')','YESR'
is
   v_sql_column varchar2(1000);
   v_sql_record varchar2(1000);
   get_declare  varchar2(9000);
begin
  declare
 
     type v_column is record(c_value varchar2(200));
     dec_column v_column;
    
     type my_cursor is ref cursor;
     v_statistic_cur my_cursor;
    
  begin
     v_sql_column :=' select column_name  from user_tab_columns c WHERE c.TABLE_name = upper('||table_name||')
                      and c.column_name not in   ('||not_exist||')';
     dbms_output.put_line(v_sql_column);
     get_declare :=' ';
     open v_statistic_cur for v_sql_column;
     fetch v_statistic_cur into dec_column;
     while v_statistic_cur%found loop----------»ñµÃdeclareÊý×éÓï¾ä
            get_declare :='type '||dec_column.c_value||'  is table of '||table_name_pass||'.'||dec_column.c_value||'%type index by pls_integer;';
            dbms_output.put_line('type DEC_'||dec_column.c_value||'  is table of '||table_name_pass||'.'||dec_column.c_value||'%type index by pls_integer;');
            fetch v_statistic_cur into dec_column; 
     end loop;