Check SQL%ROWCOUNT after update statement

  
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> 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.

SQL>

Related examples in the same category

1.	Cursor attributes: FOUND NOTFOUND, ROWCOUNT
2.	modified the cursor FOR LOOP presented earlier to include %ROWCOUNT:
3.	IF csr_org%ROWCOUNT = 1 THEN
4.	Implicit Cursor Attributes: If the previous UPDATE statement didn't match any rows, insert a new row into the place table
5.	Demonstrates the %ROWCOUNT cursor attribute by using a single-row implicit cursor based on the DUAL pseudotable
6.	sql%rowcount macro

Check SQL%ROWCOUNT after update statement : Cursor ROWCOUNT « Cursor « Oracle PL / SQL

Related examples in the same category