Oracle PL/SQL/Data Type/EXCEPTION
Add an exception handler for myException.
<source lang="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 );
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 END;
22 /
PL/SQL procedure successfully completed. SQL> SQL> select * from myLogTable;
CODE MESSAGE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INFO
ORA-1400 occurred
SQL> SQL> drop table myLogTable; Table dropped. SQL> drop table session; Table dropped. SQL> SQL>
</source>
User defined exceptions.
<source lang="sql">
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> 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 END; 17 /
PL/SQL procedure successfully completed. SQL> SQL> drop table session; Table dropped. SQL>
</source>
