13. Plural SQLMurray-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 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 errorships')
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 backgrounds')
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 errorists')
GOTOwelcomeback
DBMS_OUTPUT.PUT_LINE ('2: Game overload')
DBMS_OUTPUT.PUT_LINE ('3: You will be endedments')
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 backgrounds')
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 errorists')
-- GOTOwelcomeback
GOTO gohere
DBMS_OUTPUT.PUT_LINE ('2: Game overload')
DBMS_OUTPUT.PUT_LINE ('3: You will be endedments')
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,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
