Oracle PL/SQL/PL SQL/Exception Range
Содержание
Add a WHEN OTHERS handler to catch all runtime exceptions.
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
SQL> DECLARE
2 myException EXCEPTION;
3
4 studentCount NUMBER(3);
5
6 studentMax NUMBER(3);
7 BEGIN
8 SELECT current_lecturer, max_lecturer
9 INTO studentCount, studentMax
10 FROM session
11 WHERE department = "HIS" AND course = 101;
12
13 IF studentCount > studentMax THEN
14 RAISE myException;
15 END IF;
16 EXCEPTION
17 WHEN myException THEN
18 INSERT INTO myLogTable (info)
19 VALUES ("History 101 has " || studentCount ||
20 "lecturer: max allowed is " || studentMax);
21 WHEN OTHERS THEN
22 INSERT INTO myLogTable (info) VALUES ("Another error occurred");
23 END;
24 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from myLogTable;
no rows selected
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL> drop table session;
Table dropped.
A WHEN OTHERS handler that records which runtime error occurred.
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL> CREATE TABLE myLogTable (
2 code NUMBER,
3 message VARCHAR2(200),
4 info VARCHAR2(100)
5 );
Table created.
SQL>
SQL> DECLARE
2 myException EXCEPTION;
3
4 studentCount NUMBER(3);
5
6 studentMax NUMBER(3);
7
8 v_ErrorCode myLogTable.code%TYPE;
9 v_ErrorText myLogTable.message%TYPE;
10 BEGIN
11 SELECT current_lecturer, max_lecturer
12 INTO studentCount, studentMax
13 FROM session
14 WHERE department = "HIS" AND course = 101;
15
16 IF studentCount > studentMax THEN
17 RAISE myException;
18 END IF;
19 EXCEPTION
20 WHEN myException THEN
21 INSERT INTO myLogTable (info)
22 VALUES ("History 101 has " || studentCount ||
23 "lecturer: max allowed is " || studentMax);
24 WHEN OTHERS THEN
25 v_ErrorCode := SQLCODE;
26 v_ErrorText := SUBSTR(SQLERRM, 1, 200);
27 INSERT INTO myLogTable (code, message, info) VALUES
28 (v_ErrorCode, v_ErrorText, "Oracle error occurred");
29 END;
30 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> select * from myLogTable;
no rows selected
SQL>
SQL> drop table myLogTable;
Table dropped.
SQL> drop table session;
Table dropped.
Exception Range in action
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> begin
2 begin
3 begin
4 begin
5 begin
6 declare
7 myFirstName employee.first_name%type;
8 begin
9 select first_name into myFirstName from employee where 1=2;
10 -- exception
11 -- when NO_DATA_FOUND then
12 -- dbms_output.put_line("block #6");
13 end;
14 exception
15 when NO_DATA_FOUND then
16 dbms_output.put_line("block #5");
17 end;
18 exception
19 when NO_DATA_FOUND then
20 dbms_output.put_line("block #4");
21 end;
22 exception
23 when NO_DATA_FOUND then
24 dbms_output.put_line("block #3");
25 end;
26 exception
27 when NO_DATA_FOUND then
28 dbms_output.put_line("block #2");
29 end;
30 exception
31 when NO_DATA_FOUND then
32 dbms_output.put_line("block #1");
33 end;
34 /
block #5
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Inner exception and outter exception
SQL>
SQL> -- Inner exception and outter exception
SQL>
SQL>
SQL> begin
2 declare
3 l_number number default "MY NUMBER";
4 begin
5 null;
6 exception
7 when OTHERS then
8 dbms_output.put_line("Exception caught in inner block");
9 end;
10 exception
11 when others then
12 dbms_output.put_line("Exception caught in outer block");
13 raise;
14 end;
15 /
Exception caught in outer block
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 13
SQL>
Raising a dynamic exception without previously declaring a user-defined EXCEPTION variable:
SQL>
SQL> BEGIN
2 RAISE_APPLICATION_ERROR(-20001,"A not too original message.");
3 EXCEPTION
4 WHEN others THEN
5 dbms_output.put_line(SQLERRM);
6 END;
7 /
ORA-20001: A not too original message.
PL/SQL procedure successfully completed.