EXCEPTION抛出异常
处理除数为零异常
declare
varA number;
begin
varA:=10/0;
dbms_output.put_line('IT WILL NOT WORK');
EXCEPTION
when zero_divide then --when 后面加的是异常名称
dbms_output.put_line('it can not be zero');
dbms_output.put_line('SQLCODE= '||SQLCODE);--异常编号
END;
/
it can not be zero
SQLCODE= -1476
PL/SQL procedure successfully completed.
处理赋值异常
declare
varA varchar2(1);
varB varchar2(6):='oracle';
begin
varA:=varB;
dbms_output.put_line('wrong value');
exception
when value_error then
dbms_output.put_line('Wrong');
dbms_output.put_line('SQLCODE= '||SQLCODE);
END;
/
Wrong
SQLCODE= -6502
PL/SQL procedure successfully completed.
WHEN后面若无捕获，则交由程序默认处理
找不到数据
declare
eno emp.empno%TYPE;
ename emp.ename%TYPE;
begin
eno:=&empno;
select ename into ename from emp where empno=eno;
dbms_output.put_line('eno:'||eno||' ename:'||ename);
exception
when no_data_found then
dbms_output.put_line('not found empno');
end;
/
Enter value for empno: 9999
old 5: eno:=&empno;
new 5: eno:=9999;
not found empno
返回多条结果
declare
dno emp.deptno%TYPE;
ename emp.ename%TYPE;
begin
dno:=&deptno;
select ename into ename from emp where deptno=dno;
exception
when too_many_rows then
dbms_output.put_line('too many data');
dbms_output.put_line('SQLCODE '||SQLCODE);
END;
/
Enter value for deptno: 10
old 5: dno:=&deptno;
new 5: dno:=10;
too many data
SQLCODE -1422
使用OTHERS来捕获所有异常
declare
result number;
title varchar2(50):='www.vastdata.com.cn';
begin
result:=title;
exception
when others then
dbms_output.put_line('SQLCODE= '||SQLCODE);
dbms_output.put_line('SQLERRM= '||SQLERRM);
END;
/
SQLCODE= -6502
SQLERRM= ORA-06502: PL/SQL: numeric or value error: character to number
conversion error
使用用户定义异常
declare
data number;
myexp exception;
Pragma exception_init(myexp,-20789);
begin
data:=&inputData;
if data>10 and data<100 then
raise myexp;
end if;
exception
when others then
dbms_output.put_line('---------Wrong---------');
dbms_output.put_line('sqlcode= '||sqlcode);
dbms_output.put_line('sqlerrm= '||sqlerrm);
end;
/
构建动态异常
declare
data number;
myexp exception;
pragma exception_init(myexp,-20789);
begin
dbms_output.put_line('input number:');
data:=&inputData;
if data>10 and data<100 then
raise_application_error(-20789,'values can not be 10-100');
end if;
exception
when others then
dbms_output.put_line('sqlcode= '||sqlcode);
dbms_output.put_line('sqlerrm= '||sqlerrm);
end;
/
Enter value for inputdata: 11
old 7: data:=&inputData;
new 7: data:=11;
input number:
sqlcode= -20789
sqlerrm= ORA-20789: values can not be 10-100