过程中一段:
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;请问这么写哪不对?或者应该怎么写?
解决方案 »
- 请教一个异常:ORA-03113: 通信通道的文件结束
- 求助undo表空间被损坏
- ----------------------------java与oracle通过数组交互 出现问题,求助--------------------------
- 谁看到过和知道的,请指点一下
- 自增字段用"序列"一定要用"触发器"吗?用"触发器"和不用"触发器"有什么不同啊?
- PL/SQL 遍历某一行数据的每一列
- 哪里有oracle 8.05下载
- [问题]如何用jsp来连oracle?
- SSRS中oracle查询应该怎么写查询语句? 求帮助
- Create directory xmldir 問題
- 如何在trigger 中执行动态sql
- oracle for update
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;
/