13.pl_sql--Exception Handling

==================== 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 was: ' | | 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 was: ' | | 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 (' 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

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPCL3A_kKTAAHQUPtva5k952.png "style=" float: none; "title=" 1.PNG "alt=" Wkiol1qpcl3a_kktaahquptva5k952.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPCL2Tw_LTAAGaxfPuwqs298.png "style=" float: none; "title=" 2.PNG "alt=" Wkiol1qpcl2tw_ltaagaxfpuwqs298.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/1B/wKiom1QPCLGgclY9AAGafN3o1f4690.jpg "style=" float: none; "title=" 3.png "alt=" Wkiom1qpclggcly9aagafn3o1f4690.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPCL-ROaSWAAISp25tJ3Q246.png "style=" float: none; "title=" 4.PNG "alt=" Wkiol1qpcl-roaswaaisp25tj3q246.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1B/wKiom1QPCLPiENFMAALbLb-kbsM675.png "style=" float: none; "title=" 5.PNG "alt=" 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 was: ' | | 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'll be ended! ');

END;

/

Sql> @notes/s63.sql

GOTO Welcomeback;

*

ERROR at line 18:

Ora-06550:line, column 3:

Pls-00375:illegal GOTO statement; This GOTO cannot branchto label

' Welcomeback '

Ora-06550:line, 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 was: ' | | 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'll be ended! ');

END;

Sql> @notes/s63.sql

1:you Meet an error!

3:you'll be ended!

PL/SQL proceduresuccessfully completed

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/1B/wKiom1QPCTSzZ58kAAC__oKPvkc165.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1qpctszz58kaac__okpvkc165.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/1B/wKiom1QPCTahZU8TAAYPgqFCFc8427.jpg "style=" float: none; "title=" 7.png "alt=" Wkiom1qpctahzu8taaypgqfcfc8427.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPCUPixAgAAAEjj8xF7_I005.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1qpcupixagaaaejj8xf7_i005.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1B/wKiom1QPCTeDiqFOAAHlFZDUiDw931.png "style=" float: none; "title=" 9.PNG "alt=" Wkiom1qpctediqfoaahlfzduidw931.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPCUTQh2wdAAFNgfFbMwE764.png "style=" float: none; "title=" 10.PNG "alt=" Wkiol1qpcutqh2wdaafngffbmwe764.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1B/wKiom1QPCTiTPDWOAAE4n7Mxxs4930.png "style=" float: none; "title=" 11.PNG "alt=" Wkiom1qpctitpdwoaae4n7mxxs4930.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPCUWw4GPKAAHVW5RO12Y556.png "style=" float: none; "title=" 12.PNG "alt=" Wkiol1qpcuww4gpkaahvw5ro12y556.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPCUXRAHm7AADhlWrNLA8677.png "style=" float: none; "title=" 13.PNG "alt=" Wkiol1qpcuxrahm7aadhlwrnla8677.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1B/wKiom1QPCTryV4QnAAGF2Zmdh-k369.png "style=" float: none; "title=" 14.PNG "alt=" 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

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1550350

13.pl_sql--Exception Handling