?
Oracle Exception In Loop(二)
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