oracle exception 循环,Oracle Exception In Loop

在使用oracle SQL进行编程的时候，SELECT INTO 的语法经常被使用，例如下面这个简单的示例：SET SERVEROUTPUT ON

DECLARE

VAR_SCORE INTEGER;

VAR_NAME VARCHAR2(50):='Sheldon';

BEGIN

SELECT SCORE INTO VAR_SCORE FROM STUDENT1 WHERE NAME = VAR_NAME;

SYS.DBMS_OUTPUT.PUT_LINE(VAR_NAME||' gets '||VAR_SCORE);

END;

如果查询语句有且仅有一行数据返回，那么上面的写法是没有问题的，例如表STUDENT1中的数据为：IDNAMESCORE

1Sheldon100

2Leonard95

3Penny50

4Howard88

5Rajesh 90

8Bernadette96

6Barry95

7Amy99

9Stuart0

11Leonard67

那么上面的语句块返回的结果为：匿名块已完成

Sheldon gets 100

但是如果查询语句没有数据返回或者返回大于1条数据会怎么样呢？把VAR_NAME值设置为Leonard：SET SERVEROUTPUT ON

DECLARE

VAR_SCORE INTEGER;

VAR_NAME VARCHAR2(50):='Leonard';

BEGIN

SELECT SCORE INTO VAR_SCORE FROM STUDENT1 WHERE NAME = VAR_NAME;

SYS.DBMS_OUTPUT.PUT_LINE(VAR_NAME||' gets '||VAR_SCORE);

END;

运行以上脚本结果：错误报告:

ORA-01422: 实际返回的行数超出请求的行数

ORA-06512: 在 line 5

01422. 00000 -  "exact fetch returns more than requested number of rows"

*Cause:    The number specified in exact fetch is less than the rows returned.

*Action:   Rewrite the query or change number of rows requested

如果把VAR_NAME值设置为Mrs. Wolowitz：SET SERVEROUTPUT ON

DECLARE

VAR_SCORE INTEGER;

VAR_NAME VARCHAR2(50):='Mrs. Wolowitz';

BEGIN

SELECT SCORE INTO VAR_SCORE FROM STUDENT1 WHERE NAME = VAR_NAME;

SYS.DBMS_OUTPUT.PUT_LINE(VAR_NAME||' gets '||VAR_SCORE);

END;

执行以上脚本结果：错误报告:

ORA-01403: 未找到任何数据

ORA-06512: 在 line 5

01403. 00000 -  "no data found"

*Cause:

*Action:

其实异常信息已经很详细了：当返回超过一条数据就报TOO_MANY_ROWS异常，即返回了太多的数据；当没有数据返回的时候就报NO_DATA_FOUND异常，即没有数据返回。既然有异常了，那么就应该捕获他，示例代码如下：SET SERVEROUTPUT ON

DECLARE

VAR_SCORE INTEGER;

VAR_NAME VARCHAR2(50):='Leonard';

BEGIN

SELECT SCORE INTO VAR_SCORE FROM STUDENT1 WHERE NAME = VAR_NAME;

SYS.DBMS_OUTPUT.PUT_LINE(VAR_NAME||' gets '||VAR_SCORE);

EXCEPTION

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION TOO_MANY_ROWS');

WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND');

WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE('Unkown Exception');

END;

运行以上脚本，如果查询结果没有数据或者多于一条数据，抛出的异常都会被捕获，继而进行异常处理。

如果想要查询多个人的分数并且按照分数分等级，那么可能我们需要定义一个数组，然后循环这个数组，例如：SET SERVEROUTPUT ON

DECLARE

VAR_SCORE INTEGER;

TYPE T_VARRAY IS VARRAY(10) OF VARCHAR2(20);

NAMES T_VARRAY := T_VARRAY('Sheldon','Leonard','Bernadette','Penny','Mrs. Wolowitz','Stuart','Howard');

BEGIN

FOR I IN 1.. NAMES.COUNT LOOP

SELECT SCORE INTO VAR_SCORE FROM STUDENT1 WHERE NAME = NAMES(I);

IF VAR_SCORE = 100 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||'：满分');

ELSIF VAR_SCORE >= 90 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':优秀 ');

ELSIF VAR_SCORE >= 80 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':良好 ');

ELSIF VAR_SCORE >= 60 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':及格 ');

ELSE

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':不及格 ');

END IF;

EXCEPTION

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION TOO_MANY_ROWS FOR '||NAMES(I));

WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND FOR '||NAMES(I));

WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE('Unkown Exception FOR '||NAMES(I));

END LOOP;

END;

运行以上脚本结果：错误报告:

ORA-06550: 第 20 行, 第 7 列:

PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：

( begin case declare

end exit for goto if loop mod null pragma raise return select

update while with 

  <<

continue close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe purge

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

修改以上脚本为：SET SERVEROUTPUT ON

DECLARE

VAR_SCORE INTEGER;

TYPE T_VARRAY IS VARRAY(10) OF VARCHAR2(20);

NAMES T_VARRAY := T_VARRAY('Sheldon','Leonard','Bernadette','Penny','Mrs. Wolowitz','Stuart','Howard');

BEGIN

FOR I IN 1.. NAMES.COUNT LOOP

BEGIN

SELECT SCORE INTO VAR_SCORE FROM STUDENT1 WHERE NAME = NAMES(I);

IF VAR_SCORE = 100 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||'：满分');

ELSIF VAR_SCORE >= 90 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':优秀 ');

ELSIF VAR_SCORE >= 80 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':良好 ');

ELSIF VAR_SCORE >= 60 THEN

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':及格 ');

ELSE

SYS.DBMS_OUTPUT.PUT_LINE(NAMES(I)||':不及格 ');

END IF;

EXCEPTION

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION TOO_MANY_ROWS FOR '||NAMES(I));

WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND FOR '||NAMES(I));

WHEN OTHERS THEN SYS.DBMS_OUTPUT.PUT_LINE('Unkown Exception FOR '||NAMES(I));

END;

END LOOP;

END;

运行结果：匿名块已完成

Sheldon：满分

EXCEPTION TOO_MANY_ROWS FOR Leonard

Bernadette:优秀

Penny:不及格

EXCEPTION NO_DATA_FOUND FOR Mrs. Wolowitz

Stuart:不及格

Howard:良好

也就是说在循环中捕获异常需要将异常处理代码包在BEGIN和AND之间。

注：以上脚本均运行于Oracle SQL Developer，oracle版本为：12c