Shulou(Shulou.com)06/01 Report--

The syntax of SELECT INTO is often used when programming with oracle SQL, such as 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 the query statement has and only one row of data is returned, then there is no problem with the above writing, for example, the data in the table 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

Then the result returned by the above statement block is:

Anonymous block completed Sheldon gets 100

But what if the query statement returns no data or returns more than 1 piece of data? 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 as a result:

Error report: ORA-01422: the actual number of rows returned 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 you set the VAR_ name value 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

Execute the results of the above script:

Error report: ORA-01403: no data found ORA-06512: in line 501403. 00000-"no data found" * Cause: * Action:

In fact, the exception information is already very detailed: TOO_MANY_ROWS exception is reported when more than one data is returned, that is, too much data is returned; when no data is returned, NO_DATA_FOUND exception is reported, that is, no data is returned. Now that there is an exception, you should catch him. 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 above script, if the query result has no data or more than one data, the thrown exception will be caught and the exception handling will be carried out.

If you want to query the scores of multiple individuals and rank them by score, then maybe we need to define an array and then loop through it, 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 = 100THEN 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.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 as a result:

Error report: ORA-06550: line 20, column 7: PLS-00103: the symbol "EXCEPTION" appears at one of the following times: (begin case declare end exit for goto if loop mod null pragma raise return select update while with = 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; END LOOP;END

Running result:

Anonymous block completed Sheldon: full score EXCEPTION TOO_MANY_ROWS FOR LeonardBernadette: excellent Penny: failed EXCEPTION NO_DATA_FOUND FOR Mrs. WolowitzStuart: failed Howard: good

That is, to catch an exception in a loop, you need to package the exception handling code between BEGIN and AND.

Note: the above scripts are all run in Oracle SQL Developer,oracle version: 12c

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.