Oracle存储过程中EXECUTE IMMEDIATE 执行结果是空时怎么继续执行
无查询结果
- 创建测试表和模拟数据
create table test1(
name varchar2(10)
);
insert into test1 values ('aaa');
insert into test1 values ('bbb');
insert into test1 values ('ccc');
create table test2(
name varchar2(10),
n number
);
insert into test2 values ('aaa',111);
insert into test2 values ('ddd',444);
insert into test2 values ('ccc',333);
create table test3(
name varchar2(10),
value varchar2(10)
)
- 创建存储过程
需求:对test1
表结果进行遍历,将遍历到的name值,在test2
中搜索,如果匹配到数据则将结果存放到test3
中
create or replace procedure test_p() is
declare
cursor datas is select name from test1;
v_sql varchar2(100);
value varchar2(50);
begin
v_sql:= 'delete from test3';
execute immediate v_sql;
for data_item in datas LOOP
v_sql:= 'select n from test2 where name='''||data_item.name||'''';
execute immediate v_sql into value;
v_sql:= 'insert into test3 values('''||data_item.name||''','''||value||''')';
execute immediate v_sql;
end loop;
end;
上面执行存储过程,test3
表收集到的数据只有一条。
这是因为在执行execute immediate v_sql into xxx
的时候存储过程出现异常了,导致后面的循环终止了。
我们可以采用异常处理来解决。
需求将没有匹配到的值,打个标识来标记。
create or replace procedure test_p() is
declare
cursor datas is select name from test1;
v_sql varchar2(100);
value varchar2(50);
begin
v_sql:= 'delete from test3';
execute immediate v_sql;
for data_item in datas LOOP
begin
v_sql:= 'select n from test2 where name='''||data_item.name||'''';
execute immediate v_sql into value;
v_sql:= 'insert into test3 values('''||data_item.name||''','''||value||''')';
execute immediate v_sql;
Exception when others then
value:= '没有匹配到值'; -- 打个标识
v_sql:= 'insert into test3 values('''||data_item.name||''','''||value||''')';
execute immediate v_sql;
end;
end loop;
end;
上面表示当value变量赋值不成功时会自动跳到异常处理代码块执行。
但是上面我们只是为某个变量重新赋值,然后又需要复制N多行重复的代码,这样不易于维护,我们可以修改指针的指向。
create or replace procedure test_p() is
declare
cursor datas is select name from test1;
v_sql varchar2(100);
value varchar2(50);
begin
v_sql:= 'delete from test3';
execute immediate v_sql;
for data_item in datas LOOP
begin
v_sql:= 'select n from test2 where name='''||data_item.name||'''';
execute immediate v_sql into value;
<<next1>>
v_sql:= 'insert into test3 values('''||data_item.name||''','''||value||''')';
execute immediate v_sql;
Exception when others then
value:= '没有匹配到值';
goto next1;
end;
end loop;
end;
通过goto 标记
来指定跳转的位置,这样就不需要写重复的代码啦