oracle exception 唯一性错误,oralce 异常处理 exception

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;