189 8069 5689

OracleExceptionInLoop

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

我们提供的服务有:成都做网站、网站建设、微信公众号开发、网站优化、网站认证、翔安ssl等。为成百上千家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务,是有科学管理、有技术的翔安网站制作公司

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 
     <<
   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


网站标题:OracleExceptionInLoop
文章地址:http://cdxtjz.com/article/pjogpc.html

其他资讯