第四章 控制结构(也比较基础,就只做例子了)
--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 过程已成功完成。