plsql基础(我们似曾相识系列4)

共勉赠言：人间最美的是欢喜，最贵的善举是结缘，最宽的心胸是忍耐，最强的力量是定力。

 

1，异常

　　用来处理正常执行过程中未预料的事件 。

①、预定义例外处理常见的oracle错误；
②、非预定义例外处理预定义例外不能处理的例外；
③、自定义例外处理与oracle无关的其他情况；

格式：

　　异常处理部分一般放在 PL/SQL 程序体的后半部 , 结构为 :
　　EXCEPTION
　　　　W HEN first_exception THEN <code to handle first exception >
　　　　WHEN second_exception THEN <code to handle second exception >
　　　　WHEN OTHERS THEN <code to handle others exception >
　　END;
　　异常处理可以按任意次序排列 , 但 OTHERS 必须放在最后 .

 

2，预定义异常

oracle 预定义异常如下表格：

预定义例外名	解释
ACCESS_INTO_NULL	为对象赋值前必需初始化对象。对应ORA-06530错误。
CASE_NOT_FOUND	使用CASE语句时在WHEN子句中没有包含必需的条件分支，并且没有包含ELSE语句。对应ORA-06592错误。
COLLECTION_IS_NULL	给集合元素赋值前，必需初始化集合元素。对应ORA-06531错误。
CURSOR_ALRADY_OPEN	重新打开已经打开的游标。对应ORA-06511错误。
DUP_VAL_ON_INDEX	在惟一索引所对应的列上键入重复值。对应ORA-00001错误。
INVALID_CURSOR	试图操作不合法的游标。对应ORA-01001错误。
INVALID_NUMBER	内嵌SQL语句不能有效的将字符转换成数字。对应ORA-01722错误。
NO_DATA_FOUND	执行SELECT INTO未返回行。对应ORA-01403错误。
TOO_MANY_ROWS	执行SELECT INTO语句时，返回超过一行。对应ORA-01422错误。
ZERO_DIVIDE	PL/SQL块中，使用数字除0，对应ORA-01476错误。
SUBSCRIPT_BEYOND_COUNT	元素下标超出嵌套表或VARRAY元素的范围。对应ORA-06533错误。
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或VARRAY元素时，元素下标为负。对应ORA-06532错误。
VALUE_ERROR	PL/SQL中赋值操作时，变量长度不足以容纳实际数据。对应ORA-06502错误。
LOGIN_DENIED	连接到ORACLE数据库时，用户名/密码不正确。对应ORA-01017错误。
NOT_LOGGED_ON	应用程序没有连接到数据库。对应ORA-01012错误。
PROGRAM_ERROR	PL/SQL内部问题。对应ORA-06510错误。
ROWTYPE_MISMATCH	执行赋值操作时，宿主游标变量和PL/SQL游标变量返回类型不兼容。对应ORA-06504错误。
SELF_IS_NULL	在NULL实例上调用成员方法。对应ORA-30625错误。
STORAGE_ERROR	PL/SQL块运行时超出了内存空间或者内存被破坏。
SYS_INVALID_ROWID	将字符串转变为ROWID时没有使用有效的字符串。对应ORA-01410错误。
TIMEOUT_ON_RESOURCE	ORACLE在等待资源时超时。对应ORA-00051错误。

 

这些异常都是可以直接拿来用的，编程的时候应该经常会用到吧，自己备一份吧。

 

 

例：

DECLARE
v_empno emp.empno%TYPE :=&empno;
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
IF v_sal<=1500 THEN
UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE(' 编码为 '||v_empno||' 员工工资已更新 !');
ELSE
DBMS_OUTPUT.PUT_LINE(' 编码为 '||v_empno||' 员工工资已经超过规定值 !');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(' 数据库中没有编码为 '||v_empno||' 的员工 ');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(' 程序运行错误 ! 请使用游标 ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' 发生其它错误 !');
END;

3，非预定义异常

对于这类异常情况的处理，首先必须对非定义的 错误进行定义。步骤如下：
1. 在 PL/SQL 块的定义部分定义异常情况：
　　< 异常情况 > EXCEPTION;
2. 将其定义好的异常情况，与标准的 ORACLE 错误联系起来，使用 EXCEPTION_INIT
语句：
　　PRAGMA EXCEPTION_INIT(< 异常情况 >, < 错误代码 >) ；
3. 在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

　　前面以提到。

 

先看例子：

DECLARE
v_deptno dept.deptno%TYPE :=&deptno;
e_deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deptno_remaining, -2292);
/* -2292 是违反一致性约束的错误代码 */
BEGIN
DELETE FROM dept WHERE deptno=v_deptno;
EXCEPTION
WHEN e_deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE(' 违反数据完整性约束 !');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' 发生其它错误 !');
END;

 

代码中的语句：PRAGMA EXCEPTION_INIT(e_deptno_remaining, -2292);

解释如下：

类似于这样的格式：PRAGMA EXCEPTION_INIT(错误名, -错误号); （EXCEPTION的初始化）

用来将所谓的非预定义的一个异常，和oracle自己内部错误的绑定起来，那么其实前面提到的预定义的异常其实只是orcale自己预先绑定好错误之后的一个非预定义异常罢了。（这样来看，我还需要知道oracle的错误号啊，看来还是要上网查查，估计也要备一份。）

 

4，自定义异常

当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。

 

对于这类异常情况的处理，步骤如下：
1 ． 在 PL/SQL 块的定义部分定义异常情况：
　　< 异常情况 > EXCEPTION;
2 ． RAISE < 异常情况 > ；
3 ． 在 PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

 

还是看例子：

DECLARE
v_empno emp.empno%TYPE :=&empno;
no_result EXCEPTION; --和非预定义一样
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
IF SQL%NOTFOUND THEN
RAISE no_result; --所谓的触发异常
END IF;
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE(' 你的数据更新语句失败了 !');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' 发生其它错误 !');
END;

 

 

5，关于SQLCODE, SQLERRM

由于 ORACLE 的错信息最大长度是 512 字节，为了得到完整的错误提示信息，我们 可
用 SQLERRM 和 SUBSTR 函数一起得到错误提示信息。
SQLCODE 返回错误代码数字 .
SQLERRM 返回错误信息 .

 

例：

BEGIN
INSERT INTO emp(empno, ename, hiredate, deptno)
VALUES(2222, ‘ Jerry ’ , SYSDATE, 20);
DBMS_OUTPUT.PUT_LINE(' 插入数据记录成功 !');
INSERT INTO emp(empno, ename, hiredate, deptno)
VALUES(2222, ‘ Jerry ’ , SYSDATE, 20);
DBMS_OUTPUT.PUT_LINE(' 插入数据记录成功 !');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE|| ’ --- ‘ ||SQLERRM);
END;

 

 

6，关于RAISE_APPLICATION_ERROR

调用 DBMS_STANDARD(ORACLE 提供的包) 包所定义的 RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与 ORACLE 交互的方法。
RAISE_APPLICATION_ERROR 的语法如下：
　　RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors] ) ;
这里的 error_number 是从 – 20,000 到 – 20,999 之间的参数，
error_message 是相应的提示信息 (< 2048 字节即2kb ) ，
keep_errors 为可选，如果 keep_errors =TRUE , 则新错误将被添加到已经引发的错误
列表中。如果 keep_errors=FALSE( 缺省 ), 则新错误将替换当前的错误列表。

 

其实 RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)

 

例：

CREATE OR REPLACEPROCEDURE HR.secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOTBETWEEN'08:00'AND'18:00'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
            RAISE_APPLICATION_ERROR (-20205,'You may only make changes during normal office hours');
ENDIF;
END

 

7，异常的传播

由于异常错误可以在声明部分和执行部分以及异常错误部分出现，因而在不同部分引发的异常错误也不一样。

 

在执行部分引发异常错误

当一个异常错误在执行部分引发时，有下列情况：

l. 如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。

2. 如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。然后对该包含块执行步骤1)。

以上的做法和java很像吧。

 

在声明部分引发异常错误

网上的例子说明下：

DECLARE
Abc number(3):= ’ abc ’ ;
其它语句
BEGIN
其它语句
EXCEPTION
WHEN OTHERS THEN
其它语句
END;
例子中，由于 Abc number(3)= ’ abc ’ ; 出错，尽管在 EXCEPTION 中说明了 WHEN OTHE RS
THEN 语句，但 WHEN OTHERS THEN 也不会被执行。 但是如果在该错误语句块的外部
有一个异常错误，则该错误能被抓住，如：

BEGIN
DECLARE
Abc number(3) : = ’ abc ’ ;
其它语句
BEGIN
其它语句
EXCEPTION
WHEN OTHERS THEN
其它语句
END;
EXCEPTION
WHEN OTHERS THEN
其它语句
END;

 

今日无附加。

 

 

低头做事，抬头做人