Oracle Exception in Loop

When programming with Oracle SQL, the syntax for SELECT into is often used, such as the following simple example:

SET serveroutput ondeclare Var_score INTEGER; Var_name VARCHAR2 (): = ' Sheldon ';    BEGIN SELECT score to Var_score from STUDENT1 WHERE NAME = var_name; SYS. Dbms_output. Put_Line (var_name| | ' gets ' | | Var_score); END;

If a query statement has and only one row of data is returned, the above notation is no problem, such as the data in table STUDENT1:

Idnamescore1sheldon1002leonard953penny504howard885rajesh 908bernadette966barry957amy999stuart011leonard67

Then the above statement block returns the result:

Anonymous block completed Sheldon gets 100

But what if a query statement doesn't have data to return or return more than 1 data? Set the Var_name value to Leonard:

SET serveroutput ondeclare Var_score INTEGER; Var_name VARCHAR2 (): = ' Leonard ';    BEGIN SELECT score to Var_score from STUDENT1 WHERE NAME = var_name; SYS. Dbms_output. Put_Line (var_name| | ' gets ' | | Var_score); END;

Run the above script results:

Error Report: ORA-01422: The number of rows actually returned exceeds the number of rows requested ORA-06512: On line 501422. 00000-"Exact fetch returns more than requested number of rows" *cause:the number specified in exact fetch was less th The the the rows returned.*action:rewrite the query or change number of rows requested

If the Var_name value is set to Mrs. Wolowitz:

SET serveroutput ondeclare Var_score INTEGER; Var_name VARCHAR2 (): = ' Mrs Wolowitz ';    BEGIN SELECT score to Var_score from STUDENT1 WHERE NAME = var_name; SYS. Dbms_output. Put_Line (var_name| | ' gets ' | | Var_score); END;

Execute the above script result:

Error Report: ORA-01403: No data found ORA-06512: On line 501403. 00000-"No Data Found" *cause: *action:

In fact, the exception information is very detailed: when the return of more than one data reported Too_many_rows exception, that is, the return of too much data, when no data returned when the report No_data_found exception, that is, no data returned. Since there is an exception, then you should capture him, the sample code is as follows:

SET serveroutput ondeclare Var_score INTEGER; Var_name VARCHAR2 (): = ' Leonard ';    BEGIN SELECT score to Var_score from STUDENT1 WHERE NAME = var_name; SYS. Dbms_output. Put_Line (var_name| | ' gets ' | |  Var_score); EXCEPTION when Too_many_rows and then Dbms_output.    Put_Line (' EXCEPTION too_many_rows '); When No_data_found and then Dbms_output.    Put_Line (' EXCEPTION no_data_found '); When OTHERS and then SYS. Dbms_output. Put_Line (' unkown Exception '); END;

Run the above script, if the query results have no data or more than one piece of data, the thrown exception will be caught, followed by exception handling.

If you want to query scores for multiple people and grade by fractions, then maybe we need to define an array and loop through the array, for example:

Set serveroutput ondeclare  var_score integer;  type t_varray is  varray ( OF VARCHAR2),   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) | | : Out of ');      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. Dbms_output. Put_Line (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 results:

Error Report: ORA-06550: Line 20th, column 7th: PLS-00103: the symbol "EXCEPTION" appears when you need one of the following: (Begin case declare end exit for goto if Loop mod n ull pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> &L  T;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 above script as:

Set serveroutput ondeclare  var_score integer;  type t_varray is  varray ( OF VARCHAR2),   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) | | : Out of ');      elsif var_score >= 90 then         sys. Dbms_output. Put_Line (NAMES (I) | | ': excellent   ');       elsif var_score >= 80  then     &nbSp;  sys. Dbms_output. Put_Line (NAMES (I) | | ': good   ');       elsif var_score >= 60  then        sys. Dbms_output. Put_Line (NAMES (I) | | ': pass   ');      else          sys. Dbms_output. Put_Line (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;  end loop; END;

Operation Result:

Anonymous block completed Sheldon: Out of EXCEPTION too_many_rows for Leonardbernadette: Excellent Penny: Failed EXCEPTION no_data_found for Mrs. Wolowitzstuart: Failed Howard: Good

That is, catching an exception in a loop requires the exception handling code to be wrapped between begin and and.

Note: The above scripts are run on the Oracle SQL developer,oracle version: 12c

This article is from the "Evan" blog, be sure to keep this source http://wenshengzhu.blog.51cto.com/5151285/1709422

Oracle Exception in Loop