Oracle Exception In Loop

来源:互联网 时间:1970-01-01


在使用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 SCORE1 Sheldon 1002 Leonard 953 Penny 504 Howard 885 Rajesh 908 Bernadette 966 Barry 957 Amy 999 Stuart 011 Leonard 67

 


 
那么上面的语句块返回的结果为:
 
匿名块已完成
Sheldon gets 100
 
但是如果查询语句没有数据返回或者返回大于1条数据会怎么样呢?把VAR_NAME值设置为Leonard:
 
SET SERVEROUTPUT ONDECLARE 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 ONDECLARE 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 ONDECLARE 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 ONDECLARE 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 purge06550. 00000 - "line %s, column %s:/n%s"*Cause: Usually a PL/SQL compilation error.*Action:

 


修改以上脚本为:
 
SET SERVEROUTPUT ONDECLARE 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



相关阅读:
Top