oracle 10g: PL/SQL User's Guide and Reference
---> 10 Handling PL/SQL Errors
---> Summary of Predefined PL/SQL Exceptions
系统预定义异常(有名字的错误代码):
TOO_MANY_ROWS : SELECT INTO返回多行
INVALID_CURSOR :非法指针操作(关闭已经关闭的游标)
ZERO_DIVIDE :除数等于零
DUP_VAL_ON_INDEX :违反唯一性约束
ACCESS_INTO_NULL: 未定义对象
CASE_NOT_FOUND: CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL: 集合元素未初始化
CURSER_ALREADY_OPEN: 游标已经打开
DUP_VAL_ON_INDEX: 唯一索引对应的列上有重复的值
INVALID_NUMBER: 内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND: 使用 select into 未返回行，或应用索引表未初始化的元素时
SUBSCRIPT_BEYOND_COUNT:元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT: 使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR: 赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED: PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON: PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR: PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH: 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL: 使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR: 运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID: 无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE: Oracle 在等待资源时超时
oracl 11g: Pl/SQL LANGUAGE REFERENCE
----> 11 PL/SQL error handling
----> Predefined Exceptions
练习 1:捕获预定义异常
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
end;
/
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
exception
when TOO_MANY_ROWS then
dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/
declare
v_ename employees.last_name%type;
begin
select last_name into v_ename from employees;
exception
when too_many_rows then
dbms_output.put_line('too many rows,you should add a condition');
end;
/
练习 2:捕获预定义异常
declare
v1 emp.sal%type;
begin
select sal into v1 from emp where empno=7777;
exception
when TOO_MANY_ROWS then
dbms_output.put_line('more person !');
-- when NO_DATA_FOUND then
-- dbms_output.put_line('no rows selected!');
when others then --other执行器
dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/
declare
v_sal hr.employees.salary%type;
begin
select salary into v_sal from hr.employees where department_id= &ID;
dbms_output.put_line(v_sal);
exception
WHEN TOO_MANY_ROWS then
dbms_output.put_line('there are too many rows to return.');
when NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('there is no data to return.');
end;
declare
v_ename employees.last_name%type;
begin
select last_name into v_ename from employees;
exception
when too_many_rows then
dbms_output.put_line('sqlcode :'||sqlcode ||chr(10)||'sqlerrm :'||sqlerrm);
end;
/
sqlcode :-1422
sqlerrm :ORA-01422: exact fetch returns more than requested number of rows
练习 3:捕获错误代码和错误描述,借助预定义函数sqlcode(ERROR代码),sqlerrm(ERROR文本)
begin
update emp set deptno=60;
dbms_output.put_line('ok');
exception
when TOO_MANY_ROWS then
dbms_output.put_line('more person !');
when NO_DATA_FOUND then
dbms_output.put_line('no person !');
when others then --other执行器
dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
/
练习 4:捕获非预定义异常(捕获oracle错误代码)
declare
fk_error exception;--声明异常
pragma exception_init(fk_error,-2292);--使用编译指示器将异常名称和oracle的错误代码绑定
begin
delete dept; --oracle自动传播错误（fk_error）
dbms_output.put_line('ok');
exception
when TOO_MANY_ROWS then
dbms_output.put_line('more person ');
when NO_DATA_FOUND then
dbms_output.put_line('no person ');
when fk_error then
dbms_output.put_line('infringe forign key !');
end;
/
declare
pk_fk_error exception;
pragma exception_init(pk_fk_error,-2292);
begin
delete hr.departments;
dbms_output.put_line('It had been deleted.');
exception
when pk_fk_error then
dbms_output.put_line('It can not delete.');
end;
/
It can not delete.
用户自定义异常的sqlcode代码段：-20001 ~ -20999
练习 5:捕获用户自定义的异常:
declare
my_error EXCEPTION;
PRAGMA EXCEPTION_INIT(my_error, -20001);--编译指示,将命名的异常与ORACLE ERROR关联
BEGIN
raise_application_error(-20001,'工资不能被改动!');--将异常传送到环境
UPDATE e SET SAL=1000;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('未检索到数据!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('SELECT返回多行数据!');
WHEN MY_ERROR THEN
DBMS_OUTPUT.PUT_LINE('E表工资不可以被修改!');
end;
/
declare
my_excep EXCEPTION;
PRAGMA EXCEPTION_init(my_excep,-20001);
v_did hr.employees.department_id%type := &id;
begin
if v_did != 10 then
raise_application_error(-20001,'my own exception.');
else
update hr.employees set salary=salary+1 where department_id=v_did;
dbms_output.put_line('ok');
end if;
end;
练习 6:捕获用户自定义的异常
declare
my_error EXCEPTION;
PRAGMA EXCEPTION_INIT(my_error, -20001);
v_empno number(4):=&p_empno;
begin
IF TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '14' OR TO_CHAR (SYSDATE, 'DY') IN ('星期六', '星期日') THEN
RAISE my_error;
else
insert into e(empno) values (v_empno);
dbms_output.put_line('insert 成功!');
END IF;
exception
when my_error then
dbms_output.put_line('该时间段不能向E表插入数据!');
end;
/
declare
v_raise_sal number := &raise_sal;
my_error EXCEPTION;
PRAGMA exception_init(my_error,-20001);
begin
if v_raise_sal > 1000 then
raise_application_error(-20001,'can not raise so much money.');
else
update employees set salary=salary+v_raise_sal;
end if;
--exception
-- when my_error then
-- dbms_output.put_line('you can not raise.');
end;
/
错误报告:
ORA-20001: can not raise so much money.
ORA-06512: at line 7
****************************************************************************************************
练习 7:打印 ORA-##### 错误编号和描述:
SPOOL D:ORACLE_ERROR.TXT
SET SERVEROUTPUT ON
DECLARE
ERR_MSG VARCHAR2(4000);
ERR_CODE NUMBER(10);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
FOR ERR_NUM IN 20000..20999
LOOP
ERR_CODE:=sqlcode;
ERR_MSG := SQLERRM(-ERR_NUM);
IF ERR_MSG NOT LIKE '%Message '||ERR_NUM||' not found%' then
dbms_output.put_line(ERR_MSG);
END IF;
END LOOP;
END;
/
SPOOL OFF;
--嵌套的PL/SQL代码段的异常处理
declare
v_ename varchar2(10);
begin
select ename into v_ename from emp where empno=7839;
dbms_output.put_line(v_ename);
declare
v1 emp.sal%type;
begin
select sal into v1 from emp;
exception
when TOO_MANY_ROWS then
dbms_output.put_line(sqlcode||';'||sqlerrm);
end;
dbms_output.put_line('ok');
end;
/
--内部pl/sql代码段，进行了异常处理，是所有外部程序可以正常执行；
declare
v_name employees.last_name%type;
begin
select last_name into v_name from hr.employees
where employee_id=100;
declare
v_no departments.department_id%type;
begin
select department_id into v_no from departments;
exception
when OTHERS then
dbms_output.put_line('too may rows');
end;
dbms_output.put_line(v_name);
end;
/
too may rows
King
--内部pl/sql代码段异常
declare
v_name hr.employees.last_name%type;
begin
select last_name into v_name from hr.employees where employee_id=100;
dbms_output.put_line(v_name);
declare
v_sal hr.employees.salary%type;
begin
select salary into v_sal from employees;
dbms_output.put_line('inner pl/sql block');
-- exception
-- when too_many_rows then
-- dbms_output.put_line('inner exception capured.');
end;
dbms_output.put_line('outer block');
exception
when too_many_rows then
dbms_output.put_line('outer exception captured.');
end;