OracleExceptionInLoop-创新互联

  在使用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       <<    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

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

名称栏目：OracleExceptionInLoop-创新互联
文章分享：http://cqcxhl.cn/article/digccd.html