oracle处理例外
/*
预定义例外
*/
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('雇员名：'||v_ename);
exception
when TOO_MANY_ROWS THEN
dbms_output.put_line('查询只能返回单行');
when NO_DATA_FOUND then
dbms_output.put_line('雇员号不正确');
end;
--ACCESS_INTO_NULL
create type emp_type as object
(name varchar2(10),sal number(6,2));
GO
declare
emp emp_type;
begin
emp.name:='scott';
exception
when access_into_null then
dbms_output.put_line('首先初始化对象emp');
end;
--CASE_NOT_FOUND
undef no
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=&&no;
dbms_output.put_line('v_sal=='||v_sal);
case
when v_sal<1000 then
dbms_output.put_line('小于1000');
when v_sal<2000 then
dbms_output.put_line('小于2000');
when v_sal<3000 then
dbms_output.put_line('小于3000');
end case;
exception
when case_not_found then
dbms_output.put_line('在Case语句中缺少与'||v_sal||'相关的事件');
end;
--COLLECTION_IS_NULL
declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
select ename into ename_table(2) from emp where empno=&no;
dbms_output.put_line('雇员名：'||ename_table(2));
exception
when collection_is_null then
dbms_output.put_line('必须使用构造方法初始化集合元素');
end;
--CURSOR_ALREADY_OPEN
declare
cursor emp_cursor is select * from emp;
begin
open emp_cursor;
for emp_record in emp_cursor loop
dbms_output.put_line('雇员：'||emp_record .ename);
end loop;
exception
when cursor_already_open then
dbms_output.put_line('游标已打开');
end;
--DUM_VAL_ON_INDEX
begin
update dept set deptno=&new_no where deptno=&old_no;
exception
when dup_val_on_index then
dbms_output.put_line('在deptno上不能出现重复值');
end;
--INVALID_CURSOR
declare
cursor emp_cursor is select * from emp;
emp_record emp_cursor%rowtype;
begin
fetch emp_cursor into emp_record;
exception
when invalid_cursor then
dbms_output.put_line('请检查游标是否已经打开');
end;
--INVALID_NUMBER
begin
update emp set sal=sal+'1b';
exception
when invalid_number then
dbms_output.put_line('输入的数字不正确');
end;
--NO_DATA_FOUND
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where lower(ename)=lower('&name');
exception
when no_data_found then
dbms_output.put_line('不存在该雇员');
end;
--TOO_MANY_ROWS
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where sal>2000;
exception
when too_many_rows then
dbms_output.put_line('返回多行，需用集合变量');
end;
--ZERO_DIVIDE
declare
num1 int:=100;
num2 int:=0;
num3 number(6,2);
begin
num3:=num1/num2;
exception
when zero_divide then
dbms_output.put_line('分母不能为零');
end;
--SUBSCRIPT_BEYOND_COUNT
declare
type emp_array_type is varray(20) of varchar2(10);
emp_array emp_array_type;
begin
emp_array:=emp_array_type('scott','marry');
dbms_output.put_line(emp_array(3));
exception
when subscript_beyond_count then
dbms_output.put_line('超出下标范围');
end;
--SUBSCRIPT_OUTSIDE_LIMIT
declare
type emp_array_type is varray(20) of varchar2(10);
emp_array emp_array_type;
begin
emp_array:=emp_array_type('scott','marry');
dbms_output.put_line(emp_array(-1));
exception
when subscript_outside_limit then
dbms_output.put_line('下标不能为负');
end;
--VALUE_ERROR
declare
v_ename varchar2(2);
begin
select ename into v_ename from emp where empno=&&no;
dbms_output.put_line(v_ename);
exception
when value_error then
dbms_output.put_line('变量尺寸不足');
end;
/*
非预定义例外
*/
--
declare
e_integrity exception;
pragma exception_init(e_integrity,-2291);
begin
update emp set deptno=&detpno where empno=&empno;
exception
when e_integrity then
dbms_output.put_line('该部门不存在');
end;
/*
自定义例外
*/
declare
e_integrity exception;
pragma exception_init(e_integrity,-2291);
e_no_employee exception;
begin
update emp set deptno=&detpno where empno=&empno;
if sql%notfound then
raise e_no_employee;
end if;
exception
when e_integrity then
dbms_output.put_line('该部门不存在');
when e_no_employee then
dbms_output.put_line('该雇员不存在');
end;
/**
例外函数
*/
-- SQLCODE,SQLERRM
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where sal=&&v_sal;
dbms_output.put_line('雇员：'+v_ename);
exception
when no_data_found then
dbms_output.put_line('不存在工资为'||&v_sal||'的雇员');
when others then
dbms_output.put_line('错误号：'||sqlcode);
dbms_output.put_line(sqlerrm);
end;
--RAISE_APPLICATION_ERROR
create or replace procedure raise_comm
(eno number,commission number)
is
v_comm emp.comm%type;
begin
select comm into v_comm from emp where empno=eno;
if v_comm is null then
raise_application_error(-20001,'该雇员无补助');
end if;
exception
when no_data_found then
dbms_output.put_line('该雇员不存在');
end;
/*
编译警告
*/
create or replace procedure dead_code as
x number:=10;
begin
if x=10 then
x:=20;
else
x:=100;--(死代码，永远不执行)
end if;
end dead_code ;
alter session set plsql_warnings='enable:informational';
alter procedure dead_code compile;
show errors;
