13.PL_SQL——異常處理

==================== Example 1====================

SQL> create table emp_tmp as select * from employees;

Table created.

SQL> select last_name from emp_tmp wherefirst_name='John';

LAST_NAME

-------------------------

Chen

Seo

Russell

SQL> edit

DECLARE

    v_lnameVARCHAR2(15);

BEGIN

    SELECT last_nameINTO v_lname

    FROM emp_tmp

    WHERE first_name ='John';

   DBMS_OUTPUT.PUT_LINE('John''s last name is: ' || v_lname);

END;

/

SQL> @notes/s62.sql

DECLARE

*

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number ofrows

ORA-06512: at line 4

SQL> edit

DECLARE

    v_lnameVARCHAR2(15);

BEGIN

    SELECT last_nameINTO v_lname

    FROM emp_tmp

    WHERE first_name ='John';

   DBMS_OUTPUT.PUT_LINE('John''s last name is: ' || v_lname);

EXCEPTION

    WHEN TOO_MANY_ROWSTHEN

       DBMS_OUTPUT.PUT_LINE('Your select statement retrieved multiple rows.Condider using a cursor.');

    WHEN OTHERS THEN

       DBMS_OUTPUT.PUT_LINE('You meet an error!');

END;

/

SQL> @notes/s62.sql

Your select statement retrieved multiple rows. Condiderusing a cursor.

PL/SQL procedure successfully completed.

SQL> truncate table emp_tmp;

Table truncated.

SQL> @notes/s62.sql

You meet an error!

PL/SQL proceduresuccessfully completed

==================== Example 2====================

SQL> edit

DECLARE

    v_lnameVARCHAR2(15);

BEGIN

    SELECT last_nameINTO v_lname

    FROM emp_tmp

    WHERE first_name ='John';

   DBMS_OUTPUT.PUT_LINE('John''s last name is: ' || v_lname);

   <<welcomeback>>

   DBMS_OUTPUT.PUT_LINE('Welcome back!');

EXCEPTION

    WHEN TOO_MANY_ROWSTHEN

       DBMS_OUTPUT.PUT_LINE('Your select statement retrieved multiple rows.Condider using a cursor.');

    WHEN OTHERS THEN

       DBMS_OUTPUT.PUT_LINE('1: You meet an error!');

        GOTOwelcomeback;

       DBMS_OUTPUT.PUT_LINE('2: Game Over!');

       <<gohere>>

       DBMS_OUTPUT.PUT_LINE('3: You will be ended!');

END;

/

SQL> @notes/s63.sql

                GOTO welcomeback;

                *

ERROR at line 18:

ORA-06550: line 18, column 3:

PLS-00375: illegal GOTO statement; this GOTO cannot branchto label

'WELCOMEBACK'

ORA-06550: line 18, column 3:

PL/SQL: Statement ignored

SQL> edit

DECLARE

    v_lname VARCHAR2(15);

BEGIN

    SELECT last_nameINTO v_lname

    FROM emp_tmp

    WHERE first_name ='John';

   DBMS_OUTPUT.PUT_LINE('John''s last name is: ' || v_lname);

   <<welcomeback>>

   DBMS_OUTPUT.PUT_LINE('Welcome back!');

EXCEPTION

    WHEN TOO_MANY_ROWSTHEN

       DBMS_OUTPUT.PUT_LINE('Your select statement retrieved multiple rows.Condider using a cursor.');

    WHEN OTHERS THEN

       DBMS_OUTPUT.PUT_LINE('1: You meet an error!');

        --GOTOwelcomeback;

        GOTO gohere;

       DBMS_OUTPUT.PUT_LINE('2: Game Over!');

       <<gohere>>

       DBMS_OUTPUT.PUT_LINE('3: You will be ended!');

END;

SQL> @notes/s63.sql

1: You meet an error!

3: You will be ended!

PL/SQL proceduresuccessfully completed

==================== Example 3====================

SQL> edit

DECLARE

        e_insert_excepEXCEPTION;

        PRAGMAEXCEPTION_INIT(e_insert_excep, -01400);

BEGIN

        INSERT INTOdepartments(department_id, department_name) VALUES (280, NULL);

EXCEPTION

        WHENe_insert_excep THEN

                DBMS_OUTPUT.PUT_LINE('InsertOperation Failed!');

               DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

SQL> @notes/s64.sql

Insert Operation Failed!

ORA-01400: cannot insert NULL into("HR"."DEPARTMENTS"."DEPARTMENT_NAME")

PL/SQL proceduresuccessfully completed