13.PL_SQL——异常处理

zhouu

　　==================== 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);
　　
　　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!');
　　
　　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);
　　
　　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!');
　　
　　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