异常处理总结
来源:互联网 发布:深入浅出node.js完整版 编辑:程序博客网 时间:2024/06/05 10:34
Exception
例1:zero_divide ----除数为0
declare
v_a number := &A;
v_b number := &B;
v_result number;
begin
v_result := v_a / v_b;
dbms_output.put_line(v_result);
exception
when zero_divide then
dbms_output.put_line('0');
end;
/
0
PL/SQL procedure successfully completed.
例2:value_error ----比如sqrt平方根不能接受负数
SQL> declare
2 v_a number := &A;
3 v_result number;
4 begin
v_result := sqrt(v_a);
6 dbms_output.put_line(v_result);
7 end;
8 /
Enter value for a: -2
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
declare
v_a number := &A;
v_result number;
begin
v_result := sqrt(v_a);
dbms_output.put_line(v_result);
exception
when value_error then
dbms_output.put_line('enter the right type');
end;
/
enter the right type
PL/SQL procedure successfully completed.
declare
v_a number := &A;
v_result number;
error_v exception;
begin
case when v_a < 0 then
raise error_v;
end case;
v_result := sqrt(v_a);
dbms_output.put_line(v_result);
exception
when error_v then
dbms_output.put_line('enter the right type');
end;
/
例3:no_data_found
DECLARE
v_ename emp.ename%TYPE;
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno=3000;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE(err_num||'----'||err_msg);
END;
/
100----ORA-01403: no data found
PL/SQL procedure successfully completed.
这里使用了SQLCODE和SQLERRM函数
例4:再次抛出异常
SQL> declare
2 v_course_no number := 430;
3 v_total number;
4 e_no_sections exception;
5 begin
6 begin
7 select count(*) into v_total from section where course_no = v_course_no;
8 if v_total=0 then
9 raise e_no_sections;
10 else
11 DBMS_OUTPUT.PUT_LINE('course, '||v_course_no||' has '||v_total||' section');
12 end if;
13 exception
14 when e_no_sections then
15 DBMS_OUTPUT.PUT_LINE('inner exception');
16 raise;
17 end;
18 DBMS_OUTPUT.PUT_LINE('done..');
19 exception
20 when e_no_sections then
21 DBMS_OUTPUT.PUT_LINE('outer exception');
22 end;
23 /
inner exception
outer exception
PL/SQL procedure successfully completed.
内部的PLSQL块的exception中使用了raise,唤起外部plsql块的异常
所以没有执行DBMS_OUTPUT.PUT_LINE('done..');
而是直接进入了异常部分
SQL> declare
2 v_course_no number := 430;
3 v_total number;
4 e_no_sections exception;
5 begin
6 begin
7 select count(*) into v_total from section where course_no = v_course_no;
8 if v_total=0 then
9 raise e_no_sections;
10 else
11 DBMS_OUTPUT.PUT_LINE('course, '||v_course_no||' has '||v_total||' section');
12 end if;
13 exception
14 when e_no_sections then
15 DBMS_OUTPUT.PUT_LINE('inner exception');
16 /* raise;*/
17 end;
18 DBMS_OUTPUT.PUT_LINE('done..');
19 exception
20 when e_no_sections then
21 DBMS_OUTPUT.PUT_LINE('outer exception');
22 end;
23 /
inner exception
done..
PL/SQL procedure successfully completed.
没有raise再次抛出异常的例子
用户定义异常
通过raise唤起用户定义异常
declare
v_a number := &enter_value_for_a;
v_err exception;
begin
if v_a > 2 then
dbms_output.put_line(v_a);
elsif v_a < 2 then
raise v_err;
end if;
exception
when v_err then
dbms_output.put_line('wrong!!! a < 2');
end;
/
Enter value for enter_value_for_a: 1
wrong!!! a < 2
PL/SQL procedure successfully completed.
RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR是oracle提供的一种特殊的内置过程,允许程序员为特定应用程序创建有意义的错误消息。
RAISE_APPLICATION_ERROR过程适用于用户定义异常。它的语法为:
RAISE_APPLICATION_ERROR(error_number,error_message);
或者
RAISE_APPLICATION_ERROR(error_number,error_message,keep_errors);
正如所看到的那样,RAISE_APPLICATION_ERROR过程存在两种形式。第一种形式包含两个参数:error_number和erroe_message。
error_number是与特定错误消息相关联的错误编号。这个编号的范围在-20999和-20000之间。error_message是错误的文本,最多包含2048个字符。
RAISE_APPLICATION_ERROR过程第二种形式还多包含一个参数:keep_errors,这是可选的Boolean参数。如果keep_errors被设置为True,新错误会被添加到已经被抛出的错误列表中.
这种错误列表被称为错误栈。如果keep_errors被设置为FALSE,新错误会替换已经被抛出的错误栈。keep_errors参数的默认值是FALSE。
目的是自定义一个错误编号和异常message,和oracle一起抛出错误
declare
v_a number := &A;
v_result number;
begin
if v_a < 0 then
raise_application_error(-20000,'wrong value');
end if;
v_result := sqrt(v_a);
dbms_output.put_line(v_result);
end;
/
输入 a 的值: -1
原值 2: v_a number := &A;
新值 2: v_a number := -1;
declare
*
第 1 行出现错误:
ORA-20000: wrong value
ORA-06512: 在 line 6
EXCEPTION_INIT
目的是把错误编号和我们自定义的异常关联起来,让用户体验更好
违反外键约束
DECLARE
v_zip zipcode.zip%type := '&sv_zip';
BEGIN
DELETE FROM zipcode WHERE zip = v_zip;
DBMS_OUTPUT.PUT_LINE('Zip ' || v_zip || ' has been deleted');
COMMIT;
END;
/
Enter value for sv_zip: 06870
DECLARE
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.STU_ZIP_FK) violated - child record
found
ORA-06512: at line 4
现在我们通过PRAGMA EXCEPTION_INIT()捕获这个异常,并给出我们更友好的提示
DECLARE
v_zip zipcode.zip%type := '&sv_zip';
e_child_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(e_child_exists, -2292);
BEGIN
DELETE FROM zipcode WHERE zip = v_zip;
DBMS_OUTPUT.PUT_LINE('Zip ' || v_zip || ' has been deleted');
COMMIT;
EXCEPTION
WHEN e_child_exists THEN
DBMS_OUTPUT.PUT_LINE('Delete students for this ' || 'zipcode first');
END;
/
Enter value for sv_zip: 06870
Delete students for this zipcode first
PL/SQL procedure successfully completed.
再来一个例子
insert数据,not null的列不插入任何值,会报错
begin
insert into emp(empno,ename) values(null,'fan');
commit;
end;
/
SQL> begin
2 insert into emp(empno,ename) values(null,'fan');
commit;
end;
5 /
begin
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."EMP"."EMPNO")
ORA-06512: 在 line 2
然后我们关联ORA-01400这个错误
declare
v_err exception;
pragma exception_init(v_err,-1400);
begin
insert into emp(empno,ename) values(null,'fan');
commit;
exception
when v_err then
dbms_output.put_line('你插了空值到非空列');
end;
/
注意
no_data_found -1403不可以用,因为no_data_found的sqlcode函数返回时100
SQL> declare
2 v_err exception;
3 pragma exception_init(v_err,-01403);
4 v_number number;
5 begin
6 select empno into v_number from emp where empno=7777;
7 exception
8 when v_err then
9 dbms_output.put_line('no data found');
10 end;
11 /
v_err exception;
*
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
3 DBMS_OUTPUT.PUT_LINE('Error message1: ' || SQLERRM(SQLCODE));
4 DBMS_OUTPUT.PUT_LINE('Error message2: ' || SQLERRM(100));
5 DBMS_OUTPUT.PUT_LINE('Error message3: ' || SQLERRM(200));
6 DBMS_OUTPUT.PUT_LINE('Error message4: ' || SQLERRM(-20000));
7 END;
8 /
Error code: 0
Error message1: ORA-0000: normal, successful completion
Error message2: ORA-01403: no data found
Error message3: -200: non-ORACLE exception
Error message4: ORA-20000:
PL/SQL procedure successfully completed.
declare
v_a number := &enter_value_for_a;
v_err exception;
v_err_no number;
v_err_msg varchar2(200);
begin
if v_a > 2 then
dbms_output.put_line(v_a);
elsif v_a < 2 then
raise v_err;
end if;
exception
when v_err then
dbms_output.put_line('a < 2');
v_err_no := sqlcode;
v_err_msg := sqlerrm;
dbms_output.put_line(v_err_no);
dbms_output.put_line(v_err_msg);
end;
/
Enter value for enter_value_for_a: 1
a < 2
1
User-Defined Exception
PL/SQL procedure successfully completed.
同常SQLCODE函数会返回错误编号的复数。凡是也存在一些例外情况
当在异常部分的外部引用SQLCODE是,所返回的错误编号是0。错误编号是0意味着成功结束
当与用户定义异常一起使用SQLCODE函数时,返回的错误编号是+1.
当排除NO_DATA_FOUND异常时,SQLCODE函数返回100
1 0
- 错误异常处理总结
- Java异常处理总结
- Java异常处理总结
- 异常处理总结
- Java异常处理总结
- C++异常处理总结
- ORACLE异常处理总结
- Java异常处理总结
- java 异常处理总结
- Java异常处理总结
- struts2 异常处理总结
- Oracle异常处理总结
- struts2 异常处理总结
- struts2 异常处理总结
- Java异常处理总结
- java异常处理总结
- struts2 异常处理总结
- Java异常处理总结
- 包含min函数的栈 ---- 利用栈
- _StringAPI
- Spring MVC POST请求转到GET
- 内存泄露分析
- web前端开发的MVC与MVP初探
- 异常处理总结
- ReactiveCocoa 学习心得 -- 1
- Java 体系总预览图
- 大小写转换
- 跨国银行卡
- 整数转换
- NSUserDefaults 简单的用户名密码持久化存储 自动登录实现
- 九度OJ 1531-1540(7/10)
- super那点事