Implicit Cursor Attributes: If the previous UPDATE statement didn't match any rows, insert a new row into the place table : Cursor ROWCOUNT « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Cursor ROWCOUNT

Implicit Cursor Attributes: If the previous UPDATE statement didn't match any rows, insert a new row into the place table

  

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> BEGIN
  2    UPDATE place
  3      SET number_seats = 100
  4      WHERE room_id = 99980;
  5    IF SQL%ROWCOUNT = 0 THEN
  6      INSERT INTO place (room_id, number_seats)
  7        VALUES (99980, 100);
  8    END IF;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table place;

Table dropped.

   
  

Related examples in the same category