--在存储过程执行的用户下,新建两个个表
--建表代码
drop table tab_name;
create table tab_name
(table_name varchar2(20));
drop table tab_remark;
create table tab_remark
(
table_name varchar2(20),
remark varchar2(20)
);
--tab_name存放代码表的名称,先执行获取表名称的语句,
insert into tab_name
select table_name from user_tables;
--tab_remark存放核对的结果;
CREATE OR REPLACE PROCEDURE "DATACODE" (MYARG IN varchar2)
IS
v_num number(5);
cursor cur_table is select table_name from tab_name;
v_tablename varchar2(20);
v_count number(5);
v_sql varchar2(400);
v_tybm varchar2(36);
v_unitid varchar2(6);
no_table exception;
pragma exception_init(no_table,-00904);
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 :='select tybm,unitid from '|| v_tablename || ' where rownum<2';
begin
execute immediate v_sql into v_tybm,v_unitid;
insert into tab_remark values(v_tablename,'cunzai');
commit;
exception
when no_table then
insert into tab_remark values(v_tablename,'bucunzai');
commit;
when no_data_found then
insert into tab_remark values(v_tablename,'kongzhi');
commit;
end;
fetch cur_table into v_tablename;
end loop;
close cur_table;
end;
两种异常的处理方法。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28803801/viewspace-768477/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28803801/viewspace-768477/