[bbk5300]第17集 - Chapter 08 - Handling Exceptions(00)

Objectives

After completing this lesson,you should be able to do the following:

• Define PL/SQL execeptions
• Recognize unhandled exceptions
• List and use different types of PL/SQL execption handlers
• Trap unanticipated errors.
• Describe the effect of exception propagation in nested blocks
• Customize PL/SQL exception messages

Agenda

• Understanding PL/SQL exceptions
• Trapping exceptions

What is an exception ? 

DECLARE
        v_lname VARCHAR(35);
BEGIN
        SELECT last_name INTO v_lname
        FROM employees
        WHERE first_name = 'John';

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

/
SQL> @1.sql
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

SET SERVEROUT ON;
DECLARE
        v_lname VARCHAR(35);
BEGIN
        SELECT last_name INTO v_lname
        FROM employees
        WHERE first_name = 'John';

        DBMS_OUTPUT.PUT_LINE('John`s last name is :' || v_lname);
EXCEPTION
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('ERROR OCCURED : returned more than one rows!');
END;

/
SQL> @1.sql
ERROR OCCURED : returned more than one rows!

PL/SQL procedure successfully completed.

SET SERVEROUT ON;
DECLARE
        v_lname VARCHAR(35);
BEGIN
        SELECT last_name INTO v_lname
        FROM employees
        WHERE first_name = 'John';

        DBMS_OUTPUT.PUT_LINE('John`s last name is :' || v_lname);
EXCEPTION
        WHEN TOO_MANY_ROWS THEN
                DBMS_OUTPUT.PUT_LINE('Your select statement retrieved multiple rows.Consider using a cursor.!');
END;

/
SQL> @1_1.sql
Your select statement retrieved multiple rows.Consider using a cursor.!

PL/SQL procedure successfully completed.

SET SERVEROUT ON;
DECLARE
        v_lname VARCHAR(15);
BEGIN
        SELECT last_name INTO v_lname
        FROM emp
        WHERE first_name = 'John';

        DBMS_OUTPUT.PUT_LINE('John`s last name is :' || v_lname);
EXCEPTION
        WHEN TOO_MANY_ROWS THEN
                DBMS_OUTPUT.PUT_LINE('Your select statement retrieved multiple rows.Consider using a cursor.!');
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('You meet an error!');
END;

/
SQL> @1_1
You meet an error!

PL/SQL procedure successfully completed.

Handling the exception : Example(up look)

TOO_MANY_ROWS等异常类型总结.

PL/SQL Block Structure

DECLARE(optional)

　　-Variables,cursors,user-defined exceptions

BEGIN(mandatory)

　　-SQL statements

　　-PL/SQL statements

EXCEPTION(optional)

　　-Action to perform when execptions occur

END;(mandatory)

Understanding Exceptions with PL/SQL

• An exception is a PL/SQL error that is raised during program execution.
• An exception can be raised:
  • -Implicitly by the Oracle Server
  • -Explicitly by the program
• An exception can be handled:
  • -By trapping it with a handler
  • -By propagating it to the calling enviroment

Handing Exceptions

Understanding Exceptions with PL/SQL

• When an error occurs in PL/SQL,an exception is raised.The processing in the current PL/SQL block`s execution section halt,and control is transferred to the separate exception section of the current block,if one exists,to handle the exception.
• You cannot return to that block after you finish handling the exception.Instead,control is passed to the enclosing block,if any.

 图解：

DECLARE
        v_lname VARCHAR2(15);
BEGIN
        SELECT last_name INTO v_lname
        FROM emp
        WHERE first_name = 'John';

        DBMS_OUTPUT.PUT_LINE('John`s last name is :' || v_lname);
        <<welcomback>>
        DBMS_OUTPUT.PUT_LINE('Welcom back!');
EXCEPTION
        WHEN TOO_MANY_ROWS THEN
                DBMS_OUTPUT.PUT_LINE('Your select statement retrieved multiple rows.Consider using a cursor.');
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('1:You meet an error!');
                --GOTO welcomback;
                GOTO gohere;
                DBMS_OUTPUT.PUT_LINE('2:You are to die!');
                <<gohere>>
                DBMS_OUTPUT.PUT_LINE('3:You will be ended!');
END;

/