CREATE OR REPLACE PROCEDURE DATACODE
IS
  cursor cur_table is select table_name from tab_name;
  v_tablename varchar2(20);
  v_count number(5);
  v_sql varchar2(1000);
  no_table exception;   --定义一个异常
  pragma exception_init(no_table,-00942);   --查询中如果表不存在，oracle报的错误
begin
  select count(*) into v_count from tab_name;
  open cur_table;
  fetch cur_table into v_tablename;
     for i in 1..v_count loop
         v_sql :='insert into remark SELECT A.ID "ID",A.MC "名称",A.COMP "单位代号",C.NAME "单位",'''||v_tablename||''' FROM '||v_tablename||' A,
    (SELECT COMPID FROM COMPANYINFO
      CONNECT BY PRIOR COMPID=PCOMPID
        START WITH COMPID=(SELECT MAX(CSZ) FROM XTCS WHERE CSMC=''COMPID_XNZXTOP'')) B,
        COMPANYINFO C
     WHERE A.COMPID=B.COMPID(+) AND B.COMPID IS NULL AND A.COMPID=C.COMPID';
         begin
   execute immediate v_sql;          --对于查询的多行数据，需要建立临时表保存数据，执行sql的语句与单行查询不同
       exception
                    when no_table then
                    insert into remark values(null,null,null,null,v_tablename);     --如果表不存在的话，仅仅插入表名，其他列为空值
                    commit;
             end;
             commit;
             fetch cur_table into v_tablename;
     end loop;
  close cur_table;      
end;

在这个例子中，需要注意的是两个地方:一个是如何将变量的值插入到表中；另外一个是如果处理返回多条查询的动态sql