首页 > 代码库 > 13.PL_SQL——异常处理

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


wKioL1QPCL3A_kKTAAHQUPtva5k952.png

wKioL1QPCL2Tw_LTAAGaxfPuwqs298.png

wKiom1QPCLGgclY9AAGafN3o1f4690.jpg

wKioL1QPCL-ROaSWAAISp25tJ3Q246.png

wKiom1QPCLPiENFMAALbLb-kbsM675.png

==================== 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


wKiom1QPCTSzZ58kAAC__oKPvkc165.jpg

wKiom1QPCTahZU8TAAYPgqFCFc8427.jpg

wKioL1QPCUPixAgAAAEjj8xF7_I005.jpg

wKiom1QPCTeDiqFOAAHlFZDUiDw931.png

wKioL1QPCUTQh2wdAAFNgfFbMwE764.png

wKiom1QPCTiTPDWOAAE4n7Mxxs4930.png

wKioL1QPCUWw4GPKAAHVW5RO12Y556.png

wKioL1QPCUXRAHm7AADhlWrNLA8677.png

wKiom1QPCTryV4QnAAGF2Zmdh-k369.png

==================== 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


本文出自 “重剑无锋 大巧不工” 博客,请务必保留此出处http://wuyelan.blog.51cto.com/6118147/1550350

13.PL_SQL——异常处理