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中的数据为：

ID	NAME		SCORE
1	Sheldon		100
2	Leonard		95
3	Penny		50
4	Howard		88
5	Rajesh 	90
8	Bernadette	96
6	Barry		95
7	Amy		99
9	Stuart		0
11	Leonard		67

那么上面的语句块返回的结果为：

匿名块已完成
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 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   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