过程中一段:
select count(*) into v_count from A where 1>2;
if v_count = 0 then raise_application_error(-20001,'未找到产品');
select count(*) into v_count from B where 1>2;
if v_count = 0 then raise_application_error(-20001,'无此合同);如何用EXCEPTION实现?自定义的EXCEPTION不怎么会用Exception e_a,
Exception e_b,
.............
select a into v_a from A where 1>2;
if sql%notfound then raise e_a end if;
select b into v_b from B where 1>2;
if sql%notfound then raise e_b end if;
............
Exception
when e_a then raise_application_error(-20001,'未找到产品');
when e_b then raise_application_error(-20001,'无此合同');
end;请问这么写哪不对?或者应该怎么写?
select count(*) into v_count from A where 1>2;
if v_count = 0 then raise_application_error(-20001,'未找到产品');
select count(*) into v_count from B where 1>2;
if v_count = 0 then raise_application_error(-20001,'无此合同);如何用EXCEPTION实现?自定义的EXCEPTION不怎么会用Exception e_a,
Exception e_b,
.............
select a into v_a from A where 1>2;
if sql%notfound then raise e_a end if;
select b into v_b from B where 1>2;
if sql%notfound then raise e_b end if;
............
Exception
when e_a then raise_application_error(-20001,'未找到产品');
when e_b then raise_application_error(-20001,'无此合同');
end;请问这么写哪不对?或者应该怎么写?
after insert or update on test_3
for each row
declare
a exception;
--pragma exception_init(a,-1476);
v number;
pragma autonomous_transaction;
begin
select count(1) into v from test_3;
dbms_output.put_line(v||'---'||:new.name||'---'||:new.ida||'---'||:new.idb);
--raise_application_error(-11111, 'error');
commit;
Exception
When others
then
ROLLBACK;
--raise a;
raise;
end;
raise_application_error(-11111, 'error');这样的方式抛一个自己定义的异常。
Exception e_b,要放在定义段中
后面的逗号要改成分号if sql%notfound then raise e_a end if;
可以直接去掉,如果找不到数据的话直接就发生异常,下面的不会执行
可以用select count(*) into v_count from A where 1>2;来代替
判断若count为0,出发异常
declare
v_a A.a%type;
v_b B.b%type;
begin
begin
select a into v_a from A where 1>2;
Exception
when NO_DATA_FOUND then raise_application_error(-20001,'未找到产品');
end;
begin
select b into v_b from B where 1>2;
Exception
when NO_DATA_FOUND then raise_application_error(-20001,'无此合同');
end;
end;
/