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