Oracle Exception In Loop

Oracle Exception In Loop
When oracle SQL is used for programming, the SELECT INTO syntax is often used. For example, the following simple example: 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; if a query statement has only one row of data returned, the preceding statement is correct, for example, the data in STUDENT1 is:

ID  NAME        SCORE1   Sheldon     1002   Leonard     953   Penny       504   Howard      885   Rajesh     908   Bernadette  966   Barry       957   Amy     999   Stuart      011  Leonard     67

 

The returned result of the preceding statement block is: the anonymous block has completed Sheldon gets 100, but what if no data is returned in the query statement or more than one data record is returned? Set the VAR_NAME value to 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;

 

Run the above script result: Error Report: ORA-01422: the actual number of returned rows exceeds the number of requested rows ORA-06512: In 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. If VAR_NAME is set to 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;

 

Run the above script result: Error Report: ORA-01403: No data ORA-06512 found: In line 501403. 00000-"no data found" * Cause: * Action: in fact, the exception information is already very detailed: when more than one data entry is returned, the TOO_MANY_ROWS exception is reported, that is, too much data is returned; when no data is returned, the NO_DATA_FOUND exception is reported, that is, no data is returned. If an exception occurs, capture it. The sample code is as follows:

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;

 

Run the preceding script. If no data or more than one data record exists in the query result, all thrown exceptions are caught and processed accordingly. If you want to query scores of multiple people and calculate scores by score level, you may need to define an array and then loop through this array, for example:

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) | ': full score'); ELSIF VAR_SCORE> = 90 THEN SYS. DBMS_OUTPUT.PUT_LINE (NAMES (I) | ': Excellent'); ELSIF VAR_SCORE> = 80 THEN SYS. DBMS_OUTPUT.PUT_LINE (NAMES (I) | ': Good'); ELSIF VAR_SCORE> = 60 THEN SYS. DBMS_OUTPUT.PUT_LINE (NAMES (I) | ': pass'); ELSE SYS. values (NAMES (I) | ': fail'); 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;

 

Run the above script result: Error Report:

ORA-06550: 20th rows, 7th columns: PLS-00103: The symbol "EXCEPTION" appears when one of the following is required: (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:

 

Modify the preceding script:

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) | ': full score'); ELSIF VAR_SCORE> = 90 THEN SYS. DBMS_OUTPUT.PUT_LINE (NAMES (I) | ': Excellent'); ELSIF VAR_SCORE> = 80 THEN SYS. DBMS_OUTPUT.PUT_LINE (NAMES (I) | ': Good'); ELSIF VAR_SCORE> = 60 THEN SYS. DBMS_OUTPUT.PUT_LINE (NAMES (I) | ': pass'); ELSE SYS. values (NAMES (I) | ': fail'); 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;

 

Running result: the anonymous block has been Sheldon: full score EXCEPTION TOO_MANY_ROWS FOR LeonardBernadette: Excellent Penny: fail EXCEPTION NO_DATA_FOUND FOR Mrs. wolowitzStuart: fail Howard: Good, that is, to catch exceptions in a loop, you need to package the exception handling code between begin and. Note: The above scripts run on Oracle SQL Developer. The oracle version is 12c.