oracle 11g PL/SQL Programming学习三



-------------------------------------------------------------
-----------------Cryking 学习笔记系列-----------------
------------------转载请注明出处,谢谢!---------------- 

 

                          第二部分 PL/SQL编程

第四章  控制结构(也比较基础,就只做例子了)

--MEMBER OF的使用
23:14:07 SCOTT@orcl> DECLARE
23:14:09   2    TYPE LIST IS TABLE OF VARCHAR2(10);
23:14:09   3    N VARCHAR2(10) := 'One';
23:14:09   4    A LIST := LIST('One', 'Two', 'Three');
23:14:09   5  BEGIN
23:14:09   6    IF N MEMBER OF A THEN
23:14:09   7      DBMS_OUTPUT.PUT_LINE('"n" is member.');
23:14:09   8    END IF;
23:14:09   9  END;
23:14:11  10  /
"n" is member.


PL/SQL 过程已成功完成。

--SUBMULTISET的使用
23:14:13 SCOTT@orcl> DECLARE
23:15:33   2    TYPE LIST IS TABLE OF INTEGER;
23:15:33   3    A LIST := LIST(1, 2, 3);
23:15:33   4    B LIST := LIST(1, 2, 3, 4);
23:15:33   5  BEGIN
23:15:33   6    IF A SUBMULTISET B THEN
23:15:33   7      DBMS_OUTPUT.PUT_LINE('Subset.');
23:15:33   8    END IF;
23:15:33   9  END;
23:15:34  10  /
Subset.


PL/SQL 过程已成功完成。

1.IF语句
格式:
IF [NOT] {comparison_expression | boolean_value} [[AND | OR]
         {comparison_expression | boolean_value}] THEN
         true_execution_block;
  [ELSE
         false_execution_block;]
END IF;

23:19:29 SCOTT@orcl> DECLARE
23:19:35   2    ONE_THING VARCHAR2(5) := 'Three';
23:19:35   3    FUNCTION ORDINAL(N NUMBER) RETURN VARCHAR2 IS
23:19:35   4      TYPE ORDINAL_TYPE IS TABLE OF VARCHAR2(5);
23:19:35   5      ORDINAL ORDINAL_TYPE := ORDINAL_TYPE('One', 'Two', 'Three', 'our');
23:19:35   6    BEGIN
23:19:35   7      RETURN ORDINAL(N);
23:19:35   8    END;
23:19:35   9  BEGIN
23:19:35  10    IF ONE_THING = ORDINAL(3) THEN
23:19:35  11      DBMS_OUTPUT.PUT_LINE('[' || ORDINAL(3) || ']');
23:19:35  12    END IF;
23:19:35  13  END;
23:19:36  14  /
[Three]

PL/SQL 过程已成功完成。

2.CASE语句
CASE [ TRUE | [selector_variable]]
  WHEN [criterion1 | expression1] THEN
   criterion1_statements;
  WHEN [criterion2 | expression2] THEN
   criterion2_statements;
  WHEN [criterion(n+1) | expression(n+1)] THEN
   criterion(n+1)_statements;
ELSE
   block_statements;
END CASE;


3.迭代语句

23:24:02 SCOTT@orcl> DECLARE
23:24:04   2    COUNTER NUMBER;
23:24:04   3    FIRST   BOOLEAN;
23:24:04   4  BEGIN
23:24:04   5    LOOP
23:24:04   6      -- Loop index management.
23:24:04   7      IF NVL(COUNTER, 1) >= 1 THEN
23:24:04   8        IF NOT NVL(FIRST, TRUE) THEN
23:24:04   9          COUNTER := COUNTER + 1;
23:24:04  10        ELSE
23:24:04  11          COUNTER := 1;
23:24:04  12          FIRST   := FALSE;
23:24:04  13        END IF;
23:24:04  14      END IF;
23:24:04  15      -- Exit management.
23:24:04  16      EXIT WHEN NOT COUNTER < 3;
23:24:04  17      DBMS_OUTPUT.PUT_LINE('Iteration [' || COUNTER || ']');
23:24:04  18    END LOOP;
23:24:04  19  END;
23:24:04  20  /
Iteration [1]
Iteration [2]


PL/SQL 过程已成功完成。

23:24:05 hr@orcl> DECLARE
23:26:06   2    COUNTER NUMBER;
23:26:06   3    FIRST   BOOLEAN;
23:26:06   4  BEGIN
23:26:06   5    LOOP
23:26:06   6      -- Loop index management.
23:26:06   7      IF NVL(COUNTER, 1) >= 1 THEN
23:26:06   8        IF NOT NVL(FIRST, TRUE) THEN
23:26:06   9          COUNTER := COUNTER + 1;
23:26:06  10        ELSE
23:26:06  11          COUNTER := 1;
23:26:06  12          FIRST   := FALSE;
23:26:06  13        END IF;
23:26:06  14      END IF;
23:26:06  15      DBMS_OUTPUT.PUT_LINE('Iteration [' || COUNTER || ']');
23:26:06  16      -- Exit management.
23:26:06  17      EXIT WHEN NOT COUNTER < 3;
23:26:06  18    END LOOP;
23:26:06  19  END;
23:26:07  20  /
Iteration [1]
Iteration [2]
Iteration [3]


PL/SQL 过程已成功完成。

--CONTINUE的使用
23:35:55 hr@orcl> DECLARE
23:35:57   2    COUNTER NUMBER;
23:35:57   3    FIRST   BOOLEAN;
23:35:57   4  BEGIN
23:35:57   5    LOOP
23:35:57   6      -- Loop index management.
23:35:57   7      IF NVL(COUNTER, 1) >= 1 THEN
23:35:57   8        IF NOT NVL(FIRST, TRUE) THEN
23:35:57   9          COUNTER := COUNTER + 1;
23:35:57  10        ELSE
23:35:57  11          COUNTER := 1;
23:35:57  12          FIRST   := FALSE;
23:35:57  13        END IF;
23:35:57  14      END IF;
23:35:57  15      -- Exit management.
23:35:57  16      EXIT WHEN NOT COUNTER < 3;
23:35:57  17      IF COUNTER = 2 THEN
23:35:57  18        CONTINUE;
23:35:57  19      ELSE
23:35:57  20        DBMS_OUTPUT.PUT_LINE('Index [' || COUNTER || '].');
23:35:57  21      END IF;
23:35:57  22    END LOOP;
23:35:57  23  END;
23:35:57  24  /
Index [1].


PL/SQL 过程已成功完成。

4.FOR循环语句
格式:
FOR range_index IN range_bottom..range_top LOOP
  repeating_statements;
END LOOP;

23:35:59 hr@orcl> BEGIN
23:38:44   2  FOR i IN 1..3 LOOP
23:38:44   3  dbms_output.put_line('Iteration ['||i||']');
23:38:44   4  END LOOP;
23:38:44   5  END;
23:38:46   6  /
Iteration [1]
Iteration [2]
Iteration [3]


PL/SQL 过程已成功完成。

--游标FOR循环
BEGIN
  FOR I IN (SELECT COUNT(*) AS ON_HAND, ITEM_TITLE, ITEM_RATING
              FROM ITEM
             WHERE ITEM_TITLE LIKE 'Harry Potter%'
               AND ITEM_RATING_AGENCY = 'MPAA'
             GROUP BY ITEM_TITLE, ITEM_RATING) LOOP
    DBMS_OUTPUT.PUT('(' || I.ON_HAND || ') ');
    DBMS_OUTPUT.PUT(I.ITEM_TITLE || ' ');
    DBMS_OUTPUT.PUT_LINE('[' || I.ITEM_RATING || ']');
  END LOOP;
END;


5.WHILE循环语句
格式:
WHILE entry_condition LOOP
  [counter_management_statements;]
  repeating_statements;
END LOOP;
例:

23:38:47 hr@orcl> DECLARE
23:40:35   2    COUNTER NUMBER := 1;
23:40:35   3  BEGIN
23:40:35   4    WHILE (COUNTER < 3) LOOP
23:40:35   5      DBMS_OUTPUT.PUT_LINE('Index [' || COUNTER || '].');
23:40:35   6      IF COUNTER >= 1 THEN
23:40:35   7        COUNTER := COUNTER + 1;
23:40:35   8      END IF;
23:40:35   9    END LOOP;
23:40:35  10  END;
23:40:35  11  /
Index [1].
Index [2].


PL/SQL 过程已成功完成。

--goto的使用
23:40:36 hr@orcl> DECLARE
23:41:34   2    COUNTER NUMBER := 1;
23:41:34   3  BEGIN
23:41:34   4    WHILE (COUNTER < 3) LOOP
23:41:34   5      IF COUNTER = 2 THEN
23:41:34   6        GOTO LOOPINDEX;
23:41:34   7      ELSE
23:41:34   8        DBMS_OUTPUT.PUT_LINE('Index [' || COUNTER || '].');
23:41:34   9      END IF;
23:41:34  10      <<LOOPINDEX>>
23:41:34  11      IF COUNTER >= 1 THEN
23:41:34  12        COUNTER := COUNTER + 1;
23:41:34  13      END IF;
23:41:34  14    END LOOP;
23:41:34  15  END;
23:41:35  16  /
Index [1].


PL/SQL 过程已成功完成。

6.游标结构
  隐式游标

--隐式游标的%ROWCOUN属性
23:41:35 hr@orcl> DECLARE
23:43:06   2    N NUMBER;
23:43:06   3  BEGIN
23:43:06   4    SELECT 1 INTO N FROM DUAL;--单行隐式游标
23:43:06   5    DBMS_OUTPUT.PUT_LINE('Selected [' || SQL%ROWCOUNT || ']');
23:43:06   6  END;
23:43:06   7  /
Selected [1]


PL/SQL 过程已成功完成。

--记录类型的使用
DECLARE
  TYPE ITEM_RECORD IS RECORD(
    ID       ITEM.ITEM_ID%TYPE,
    TITLE    ITEM.ITEM_TITLE%TYPE,
    SUBTITLE ITEM.ITEM_SUBTITLE%TYPE);
  DATASET ITEM_RECORD;
BEGIN
  SELECT ITEM_ID, ITEM_TITLE, ITEM_SUBTITLE
    INTO DATASET
    FROM ITEM
   WHERE ROWNUM < 2;
  DBMS_OUTPUT.PUT_LINE('Selected [' || DATASET.TITLE || ']');
END;


--多行隐式游标
BEGIN
  UPDATE SYSTEM_USER SET LAST_UPDATE_DATE = SYSDATE;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Updated [' || SQL%ROWCOUNT || ']');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Nothing updated!');
  END IF;
END;


--多行隐式游标
BEGIN
  FOR I IN (SELECT ITEM_ID, ITEM_TITLE FROM ITEM) LOOP--这种隐式游标的SQL%ROWCOUNT属性将返回NULL
    DBMS_OUTPUT.PUT_LINE('Item #[' || I.ITEM_ID || '][' || I.ITEM_TITLE || ']');
  END LOOP;
END;
  
  显示游标
静态显示游标
DECLARE
  ID    ITEM.ITEM_ID%TYPE;
  TITLE VARCHAR2(60);
  CURSOR C IS
    SELECT ITEM_ID, ITEM_TITLE FROM ITEM;
BEGIN
  OPEN C;
  LOOP
    FETCH C
      INTO ID, TITLE;
    EXIT WHEN C%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Title [' || TITLE || ']');
  END LOOP;
  CLOSE C;
END;
等价于=>
DECLARE
  CURSOR C IS
    SELECT ITEM_ID AS ID, ITEM_TITLE AS TITLE FROM ITEM;
BEGIN
  FOR I IN C LOOP
    DBMS_OUTPUT.PUT_LINE('Title [' || I.TITLE || ']');
  END LOOP;
END;


--显示游标中使用记录类型
DECLARE
  TYPE ITEM_RECORD IS RECORD(
    ID    NUMBER,
    TITLE VARCHAR2(60));
  ITEM ITEM_RECORD;
  CURSOR C IS
    SELECT ITEM_ID, ITEM_TITLE FROM ITEM;
BEGIN
  OPEN C;
  LOOP
    FETCH C
      INTO ITEM;
    EXIT WHEN C%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Title [' || ITEM.TITLE || ']');
  END LOOP;
END;
等价于=>
DECLARE
  TYPE ITEM_RECORD IS RECORD(
    ID    NUMBER,
    TITLE VARCHAR2(60));
  EXPLICIT_ITEM ITEM_RECORD;
  CURSOR C IS
    SELECT ITEM_ID AS ID, ITEM_TITLE AS TITLE FROM ITEM;
BEGIN
  FOR I IN C LOOP
    EXPLICIT_ITEM := I;
    DBMS_OUTPUT.PUT_LINE('Title [' || EXPLICIT_ITEM.TITLE || ']');
  END LOOP;
END;


--有条件的退出循环,这种显示游标无法用FOR循环改写
DECLARE
  TYPE ITEM_RECORD IS RECORD(
    ID    NUMBER,
    TITLE VARCHAR2(60));
  ITEM ITEM_RECORD;
  CURSOR C IS
    SELECT ITEM_ID, ITEM_TITLE FROM ITEM WHERE ITEM_ID = -1;
BEGIN
  OPEN C;
  LOOP
    FETCH C
      INTO ITEM;
    IF C%NOTFOUND THEN
      IF C%ROWCOUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No Data Found');
      END IF;
      EXIT;
    ELSE
      DBMS_OUTPUT.PUT_LINE('Title [' || ITEM.TITLE || ']');
    END IF;
  END LOOP;
END;
 
动态显示游标
DECLARE
  LOWEND  NUMBER;
  HIGHEND NUMBER;
  ITEM_ID NUMBER := 1012;
  TYPE ITEM_RECORD IS RECORD(
    ID    NUMBER,
    TITLE VARCHAR2(60));
  ITEM ITEM_RECORD;
  CURSOR C(LOW_ID NUMBER, HIGH_ID NUMBER) IS--带参游标
    SELECT ITEM_ID, ITEM_TITLE
      FROM ITEM
     WHERE ITEM_ID BETWEEN LOW_ID AND HIGH_ID;
BEGIN
  LOWEND  := TO_NUMBER(NVL(&1, 1005));
  HIGHEND := TO_NUMBER(NVL(&2, 1021));
  OPEN C(LOWEND, HIGHEND);
  LOOP
    FETCH C
      INTO ITEM;
    EXIT WHEN C%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Title [' || ITEM.TITLE || ']');
  END LOOP;
END;
使用部分改写为FOR循环为:
FOR i IN c (lowend,highend) LOOP
item := i;
dbms_output.put_line('Title ['||item.title||']');
END LOOP;



7.BULK语句
BULK语句让你从表或视图查询、插入、更新、删除大数据集.
它有2个游标属性:
  %BULK_ROWCOUNT(I):查看元素是否被更改
  %BULK_EXCEPTION(I):查看某行是否遇到错误
格式:
SELECT column1 [, column2 [, column(n+1)]]
       COLLECT BULK INTO collection1 [, collection2 [, collection(n+1)]]
FROM table_name
[WHERE where_clause_statements];


FETCH cursor_name [(parameter1 [, parameter2 [, parameter(n+1)]])]
  BULK COLLECT INTO collection1 [, collection2 [, collection(n+1)]]
[LIMIT rows_to_return];
--隐式BULK游标,SELECT BULK是并行执行的
DECLARE
  TYPE TITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  TYPE SUBTITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  TITLE    TITLE_COLLECTION;
  SUBTITLE SUBTITLE_COLLECTION;
BEGIN
  SELECT ITEM_TITLE, ITEM_SUBTITLE BULK COLLECT
    INTO TITLE, SUBTITLE
    FROM ITEM;
  -- Print one element of one of the parallel collections.
  FOR I IN 1 .. TITLE.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Title [' || TITLE(I) || ']');
  END LOOP;
END;


--BULK中使用记录变量
DECLARE
  TYPE TITLE_RECORD IS RECORD(
    TITLE    VARCHAR2(60),
    SUBTITLE VARCHAR2(60));
  TYPE COLLECTION IS TABLE OF TITLE_RECORD;
  FULL_TITLE COLLECTION;
BEGIN
  SELECT ITEM_TITLE, ITEM_SUBTITLE BULK COLLECT INTO FULL_TITLE FROM ITEM;
  -- Print one element of a structure.
  FOR I IN 1 .. FULL_TITLE.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Title [' || FULL_TITLE(I).TITLE || ']');
  END LOOP;
END;


--并行行数限制
DECLARE
  -- Define scalar datatypes.
  TYPE TITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  TYPE SUBTITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  -- Define local variables.
  TITLE    TITLE_COLLECTION;
  SUBTITLE SUBTITLE_COLLECTION;
  -- Define a static cursor.
  CURSOR C IS
    SELECT ITEM_TITLE, ITEM_SUBTITLE FROM ITEM;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT
      INTO TITLE, SUBTITLE LIMIT 10;--限制每10个一次
    EXIT WHEN TITLE.COUNT = 0;
    FOR I IN 1 .. TITLE.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Title [' || TITLE(I) || ']');
    END LOOP;
  END LOOP;
END;


8. FORALL语句
FORALL语句设计用来处理集合类型的.
--使用FORALL进行批量插入
DECLARE
  TYPE ID_COLLECTION IS TABLE OF NUMBER;
  TYPE TITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  TYPE SUBTITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  ID       ID_COLLECTION;
  TITLE    TITLE_COLLECTION;
  SUBTITLE SUBTITLE_COLLECTION;
  CURSOR C IS
    SELECT ITEM_ID, ITEM_TITLE, ITEM_SUBTITLE FROM ITEM;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT
      INTO ID, TITLE, SUBTITLE LIMIT 10;
    EXIT WHEN TITLE.COUNT = 0;
    FORALL I IN ID.FIRST .. ID.LAST --FORALL的使用
      INSERT INTO ITEM_TEMP VALUES (ID(I), TITLE(I), SUBTITLE(I));
  END LOOP;
  FOR I IN ID.FIRST .. ID.LAST LOOP
    DBMS_OUTPUT.PUT('Inserted [' || ID(I) || ']');
    DBMS_OUTPUT.PUT_LINE('[' || SQL%BULK_ROWCOUNT(I) || ']');
  END LOOP;
END;


--使用FORALL进行批量更新
DECLARE
  TYPE ID_COLLECTION IS TABLE OF NUMBER;
  TYPE TITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  ID    ID_COLLECTION;
  TITLE TITLE_COLLECTION;
  CURSOR C IS
    SELECT ITEM_ID, ITEM_TITLE FROM ITEM;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT
      INTO ID, TITLE LIMIT 10;
    EXIT WHEN TITLE.COUNT = 0;
    FORALL I IN ID.FIRST .. ID.LAST
      UPDATE ITEM_TEMP SET TITLE = TITLE(I) || ': ' WHERE ID = ID(I);
  END LOOP;
END;


--使用FORALL进行批量删除
DECLARE
  TYPE ID_COLLECTION IS TABLE OF NUMBER;
  TYPE TITLE_COLLECTION IS TABLE OF VARCHAR2(60);
  ID    ID_COLLECTION;
  TITLE TITLE_COLLECTION;
  CURSOR C IS
    SELECT ITEM_ID, ITEM_TITLE FROM ITEM;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT
      INTO ID, TITLE LIMIT 10;
    EXIT WHEN TITLE.COUNT = 0;
    FORALL I IN ID.FIRST .. ID.LAST
      DELETE FROM ITEM_TEMP
       WHERE SUBTITLE IS NULL
         AND ID = ID(I);
  END LOOP;

END;

第5章 错误管理(Error Management)

异常类型和异常处理范围
有两种类型的错误:编译错误和运行时错误.编译错误在语法解析的时候就会发现,运行时错误是当实际数据在程序中按
预定规则处理的时候发生的错误.
运行时错误一般都会在当前块被异常处理捕捉到(声明块的异常无法被当前异常处理块捕捉),编译错误(如声明处变量定义错误)当前块无法捕捉.
编译错误
编译错误很常见,如果你输错关键字,忘记分号,忘记括号等等,都会引发编译错误.
如:

--少分号的
22:11:57 hr@orcl> BEGIN
22:12:19   2   dbms_output.put_line('Hello World.')
22:12:19   3  END;
22:12:21   4  /
END;
*
第 3 行出现错误:
ORA-06550: 第 3 行, 第 1 列:
PLS-00103: 出现符号 "END"在需要下列之一时：
:= . ( % ;
符号 ";" 被替换为 "END" 后继续。
--未定义类型长度
22:12:22 hr@orcl> DECLARE
22:13:51   2    A CHAR := 'AB';--未定义类型长度
22:13:51   3  BEGIN
22:13:51   4    DBMS_OUTPUT.PUT_LINE('[' || A || ']');
22:13:51   5  END;
22:13:52   6  /
DECLARE
*
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 line 2

综上可以看出编译错误,给出的错误提示一般是不准确的,需要自己去判断.


运行时错误
运行时错误可以发生在声明块、执行块、异常块.声明块及异常块发生的运行时错误只有外部异常块才能捕捉.
异常块格式:
WHEN {predefined_exception | user_defined_exception | OTHERS} THEN
     exception_handling_statement;
[RETURN | EXIT ];
异常块处理的异常分为系统预定义异常、用户自定义异常及其他异常.
系统预定义异常定义在SYS.STANDARD包里,异常块里常用SQLCODE和SQLERRM两个函数输出错误信息.

                            系统预定义异常对照表

SQLCODE输出一个oracle预定义的负数,此负数对应oracle系统一系列预定义异常.
注意:有一个异常是例外的NO_DATA_FOUND异常返回的SQLCODE是100.
SQLERRM返回错误信息及定义的错误代码.
例:

--系统预定义异常VALUE_ERROR
22:25:51 hr@orcl> DECLARE
22:25:59   2    A VARCHAR2(1);
22:25:59   3    B VARCHAR2(2) := 'AB';
22:25:59   4  BEGIN
22:25:59   5    A := B;--这里发生运行时错误
22:25:59   6  EXCEPTION
22:25:59   7    WHEN VALUE_ERROR THEN
22:25:59   8      DBMS_OUTPUT.PUT_LINE('You can''t put [' || B ||'] in a one character string.');
22:25:59  10  END;
22:25:59  11  /
You can't put [AB] in a one character string.

PL/SQL 过程已成功完成。

--用WHEN OHTERS捕捉系统预定义异常NO_DATA_FOUND
22:26:00 hr@orcl> DECLARE
22:28:44   2    A NUMBER;
22:28:44   3  BEGIN
22:28:44   4    DECLARE
22:28:44   5      B VARCHAR2(2);
22:28:44   6    BEGIN
22:28:44   7      SELECT 1 INTO B FROM DUAL WHERE 1 = 2;--此处发生运行时错误
22:28:44   8      A := B;
22:28:44   9    EXCEPTION--里层异常处理
22:28:44  10      WHEN VALUE_ERROR THEN
22:28:44  11        DBMS_OUTPUT.PUT_LINE('You can''t put [' || B ||
22:28:44  12                             '] in a one character string.');
22:28:44  13    END;
22:28:44  14  EXCEPTION--外层异常处理
22:28:44  15    WHEN OTHERS THEN
22:28:44  16      DBMS_OUTPUT.PUT_LINE('Caught in outer block [' || SQLERRM || '].');
22:28:44  17  END;
22:28:46  18  /
Caught in outer block [ORA-01403: 未找到任何数据].


PL/SQL 过程已成功完成。

--用户自定义异常
22:28:47 hr@orcl> DECLARE
22:31:24   2    A NUMBER;
22:31:24   3    E EXCEPTION;
22:31:24   4  BEGIN
22:31:24   5    DECLARE
22:31:24   6      B VARCHAR2(2) := 'AB';
22:31:24   7    BEGIN
22:31:24   8      RAISE E;--主动抛出异常,注意此异常变量是在外层定义的,所以只能被外层捕捉
22:31:24   9    EXCEPTION
22:31:24  10      WHEN OTHERS THEN
22:31:24  11        A := B;
22:31:24  12        DBMS_OUTPUT.PUT_LINE('Does not reach this line.');
22:31:24  13    END;
22:31:24  14  EXCEPTION
22:31:24  15    WHEN OTHERS THEN --此处捕捉异常
22:31:24  16      DBMS_OUTPUT.PUT_LINE('Caught in outer block [' || SQLCODE || '].');
22:31:24  17  END;
22:31:25  18  /
Caught in outer block [-6502].


PL/SQL 过程已成功完成。

--声明处的运行时错误,当前的异常块是无法捕捉当前声明块的运行时错误的
22:34:33 hr@orcl> DECLARE
22:34:35   2    A VARCHAR2(1) := 'a1';
22:34:35   3  BEGIN
22:34:35   4    DBMS_OUTPUT.PUT_LINE('Substituted variable value [' || A || ']');
22:34:35   5  EXCEPTION
22:34:35   6    WHEN OTHERS THEN
22:34:35   7      DBMS_OUTPUT.PUT_LINE('Local exception caught.');
22:34:35   8  END;
22:34:35   9  /
DECLARE
*
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 line 2

--外部异常块捕捉声明处运行时错误
22:36:42 hr@orcl> BEGIN
22:37:21   2    DECLARE
22:37:21   3      A VARCHAR2(1) := 'a1';--内部声明块发生运行时错误
22:37:21   4    BEGIN
22:37:21   5      DBMS_OUTPUT.PUT_LINE('Substituted variable value [' || A || ']');
22:37:21   6    EXCEPTION
22:37:21   7      WHEN OTHERS THEN
22:37:21   8        DBMS_OUTPUT.PUT_LINE('Local exception caught.');
22:37:21   9    END;
22:37:21  10  EXCEPTION
22:37:21  11    WHEN OTHERS THEN--外部异常块捕捉到
22:37:21  12      DBMS_OUTPUT.PUT_LINE('Outer exception caught.');
22:37:21  13  END;
22:37:21  14  /
Outer exception caught.


PL/SQL 过程已成功完成。

--函数中的声明块异常处理
--注:不建议在声明处使用动态赋值
22:41:39 hr@orcl> CREATE OR REPLACE FUNCTION RUNTIME_ERROR(VARIABLE_IN VARCHAR2)
22:41:43   2    RETURN VARCHAR2 IS
22:41:43   3    A VARCHAR2(1) := VARIABLE_IN;
22:41:43   4  BEGIN
22:41:43   5    RETURN NULL;
22:41:43   6  EXCEPTION
22:41:43   7    WHEN OTHERS THEN
22:41:43   8      DBMS_OUTPUT.PUT_LINE('Function error.');
22:41:43   9  END;
22:41:44  10  /


函数已创建。
--发生声明处错误,异常处理块未捕捉到
22:41:45 hr@orcl> SELECT runtime_error ('AB') FROM dual;
SELECT runtime_error ('AB') FROM dual
       *
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 "GW.RUNTIME_ERROR", line 3

用户自定义异常
两种类型的用户自定义异常:
1.在声明处声明EXCEPTION类型的变量;
2.使用函数RAISE_APPLICATION_ERROR在执行块动态建立异常(使用的异常号范围为-20000到-20999)
例：

--第一类用户自定义异常
22:57:37 hr@orcl> DECLARE
22:57:41   2    E EXCEPTION;
22:57:41   3  BEGIN
22:57:41   4    RAISE E;
22:57:41   5    DBMS_OUTPUT.PUT_LINE('Can''t get here.');
22:57:41   6  EXCEPTION
22:57:41   7    WHEN OTHERS THEN
22:57:41   8      IF SQLCODE = 1 THEN--所有用户自定义异常的SQLCODE都为1
22:57:41   9        DBMS_OUTPUT.PUT_LINE('This is a [' || SQLERRM || '].');
22:57:41  10      END IF;
22:57:41  11  END;
22:57:41  12  /
This is a [User-Defined Exception].


PL/SQL 过程已成功完成。
--第一类用户自定义异常,绑定了系统的预定义错误号-2003
22:57:42 hr@orcl> DECLARE
22:59:54   2    A VARCHAR2(20);
22:59:54   3    INVALID_USERENV_PARAMETER EXCEPTION;
22:59:54   4    PRAGMA EXCEPTION_INIT(INVALID_USERENV_PARAMETER, -2003);
22:59:54   5  BEGIN
22:59:54   6    A := SYS_CONTEXT('USERENV', 'PROXY_PUSHER');
22:59:54   7  EXCEPTION
22:59:54   8    WHEN INVALID_USERENV_PARAMETER THEN
22:59:54   9      DBMS_OUTPUT.PUT_LINE(SQLERRM);
22:59:54  10  END;
22:59:55  11  /
ORA-02003: 无效的 USERENV 参数


PL/SQL 过程已成功完成。


--第二类用户自定义异常
22:59:56 hr@orcl> BEGIN
23:01:34   2    RAISE_APPLICATION_ERROR(-20001, 'A not too original message.');
23:01:34   3  EXCEPTION
23:01:34   4    WHEN OTHERS THEN
23:01:34   5      DBMS_OUTPUT.PUT_LINE(SQLERRM);
23:01:34   6  END;
23:01:35   7  /
ORA-20001: A not too original message.


PL/SQL 过程已成功完成。


--一、二类用户自定义异常结合使用
23:01:35 hr@orcl> DECLARE
23:03:20   2    E EXCEPTION;--定义异常变量
23:03:20   3    PRAGMA EXCEPTION_INIT(E, -20001);--绑定错误号-20001
23:03:20   4  BEGIN
23:03:20   5    RAISE_APPLICATION_ERROR(-20001, 'A less than original message.');--抛出异常
23:03:20   6  EXCEPTION
23:03:20   7    WHEN E THEN--捕捉异常,-20001和E已经绑定
23:03:20   8      DBMS_OUTPUT.PUT_LINE(SQLERRM);
23:03:20   9  END;
23:03:20  10  /
ORA-20001: A less than original message.


PL/SQL 过程已成功完成。