程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle Exception In Loop

Oracle Exception In Loop

編輯：Oracle教程

Oracle Exception In Loop

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