Mega Code Archive
NO_DATA_FOUND vs %NOTFOUND
<![CDATA[
SQL>
SQL>
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, 'Building 7', 201, 1000, 'Large Lecture Hall');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, 'Building 6', 101, 500, 'Small Lecture Hall');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, 'Building 6', 150, 50, 'Discussion Room A');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, 'Building 6', 160, 50, 'Discussion Room B');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, 'Building 6', 170, 50, 'Discussion Room C');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, 'Music Building', 100, 10, 'Music Practice Room');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, 'Music Building', 200, 1000, 'Concert Room');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, 'Building 7', 300, 75, 'Discussion Room D');
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, 'Building 7', 310, 50, 'Discussion Room E');
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
CREATE TABLE MyTable (
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
SQL>
SQL> DECLARE
2 v_RoomData place%ROWTYPE;
3 BEGIN
4 SELECT *
5 INTO v_RoomData
6 FROM place
7 WHERE room_id = -1;
8
9 IF SQL%NOTFOUND THEN
10 INSERT INTO MyTable (char_col)
11 VALUES ('Not found!');
12 END IF;
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 INSERT INTO MyTable (char_col)
16 VALUES ('Not found, exception handler');
17 END;
18 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
Not found, exception handler
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table place;
Table dropped.
SQL>
SQL>
]]>