13.PL_SQL——異常處理

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

創(chuàng)新互聯(lián)于2013年創(chuàng)立，先為黃浦等服務(wù)建站，黃浦等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為黃浦企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

 

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

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

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

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

13.PL_SQL——異常處理

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

分享文章：13.PL_SQL——異常處理
文章來源：http://bm7419.com/article34/jjeppe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計(jì)、電子商務(wù)、網(wǎng)站設(shè)計(jì)公司、、品牌網(wǎng)站制作、App開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

商城網(wǎng)站建設(shè)