Oracle匿名块中使用子过程

/**
* Note：按模式分别获取表及字段
* 1、子过程：获取所有表
* 2、子过程：获取表的所有字段
* 3、组成insert into select 语句
**/
declare
  --定义模式数组
  type t_arr_schema is varray(3) of varchar2(20);
  v_scm_arr t_arr_schema;
  
  /**
  * 子过程：获取表的所有字段
  **/
  procedure deal_cols(p_schema varchar2, p_table varchar2) is
      --定义游标
      type ref_col is ref cursor;
      c_col ref_col;

      --获取表所有字段SQL
      sql_all_cols varchar(300);
      v_col varchar(100);
      --拼接字段
      v_cols_sql varchar(2000);
  begin
      --内循环前，清空变量
      v_cols_sql := '';
      sql_all_cols := 'select COLUMN_NAME from all_tab_columns where OWNER='''||p_schema||''''
          ||' and TABLE_NAME='''||p_table||''' order by column_id';
      --内循环获取表字段
      open c_col for sql_all_cols;
      LOOP
        fetch c_col into v_col;
        exit when c_col%notfound;
        IF v_cols_sql is null THEN
          v_cols_sql := v_col;
        ELSE
          v_cols_sql := v_cols_sql||','||v_col;
        END IF;
      END LOOP;
      close c_col;
      --输出 拼接SQL
      DBMS_OUTPUT.PUT_LINE('insert into '||p_schema||'.'||p_table||' ('||v_cols_sql||')');
      DBMS_OUTPUT.PUT_LINE('select '||v_cols_sql||' from '||p_schema||'.'||p_table||'_OLD;');
      DBMS_OUTPUT.PUT_LINE('commit;');
      DBMS_OUTPUT.PUT_LINE('');
  end deal_cols;

  /**
  * 子过程：获取所有表
  **/  
  procedure deal_tables(p_schema varchar2) is
      --定义游标
      CURSOR c_allTables IS
        select t.OWNER,t.TABLE_NAME from all_tab_comments t
        where t.OWNER=p_schema and rownum<6;
      --定义游标：获取表的所有字段
      type ref_col is ref cursor;
      c_col ref_col;
  begin
      --外循环获取表
      FOR tabs IN c_allTables LOOP
        deal_cols(tabs.OWNER, tabs.TABLE_NAME);
      END LOOP;
  end deal_tables;
  
begin
  --不限制 缓冲大小
  DBMS_OUTPUT.ENABLE (buffer_size=>null);
  --模式变量初始化
  v_scm_arr := t_arr_schema('XXDB','XXXX','XXXX');
  for i in 1..v_scm_arr.count loop
    deal_tables(v_scm_arr(i));
  end loop;
  
exception
  when others then
    dbms_output.put_line(SQLCODE||' : '||SQLERRM);
end;