Oracle Exception In Loop

標籤：異常   loop   exception   into   oracle   

   在使用oracle SQL進行編程的時候，SELECT INTO 的文法經常被使用，例如下面這個簡單的樣本：

SET SERVEROUTPUT ONDECLARE  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中的資料為：

IDNAMESCORE1Sheldon1002Leonard953Penny504Howard885Rajesh 908Bernadette966Barry957Amy999Stuart011Leonard67

那麼上面的語句塊返回的結果為：

匿名塊已完成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 501422. 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 501403. 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 LeonardBernadette:優秀 Penny:不及格 EXCEPTION NO_DATA_FOUND FOR Mrs. WolowitzStuart:不及格 Howard:良好

也就是說在迴圈中捕獲異常需要將異常處理程式碼封裝在BEGIN和AND之間。

註：以上指令碼均運行於Oracle SQL Developer，oracle版本為：12c

本文出自 “埃文” 部落格，請務必保留此出處http://wenshengzhu.blog.51cto.com/5151285/1709422

Oracle Exception In Loop