心得笔记网站首页 心得笔记

oracle-异常处理

发布时间：2017-11-23 15:15:27编辑：雪饮阅读（）

创建带有异常处理的存储过程：

SQL> edit

Wrote file afiedt.buf

1 create procedure p15(dpnum int)

2 is

3 dp dept%rowtype;

4 dn dept.dname%type;

5 begin

6 select deptno,dname,loc into dp.deptno,dn,dp.loc from dept where deptno>dpnum;

7 dbms_output.put_line(dp.deptno||'_'||dn||'_'||dp.loc);

8 exception

9 when NO_DATA_FOUND then

10 dbms_output.put_line('sorry,can not found any row');

11 when TOO_MANY_ROWS then

12 dbms_output.put_line('ooh! so much');

13 when others then

14 dbms_output.put_line('sorry,dont know');

15* end;

SQL> /

Procedure created.

测试效果：

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> call p15(30);

40_OPERATIONS_BOSTON

Call completed.

SQL> call p15(20)

2 /

ooh! so much

Call completed.

SQL> call p15(50);

sorry,can not found any row

Call completed.

关键字词：oracle,异常处理