NO_DATA_FOUND vs. %NOTFOUND : Cursor Not Found « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Cursor Not Found

NO_DATA_FOUND vs. %NOTFOUND

  

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>

   
  

Related examples in the same category