use cursor attributes on the implicit SQL cursor. : Implicit Cursor « Cursor « Oracle PL / SQL






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

use cursor attributes on the implicit SQL cursor.

  
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> BEGIN
  2    UPDATE place
  3      SET number_seats = 100
  4      WHERE room_id = 99980;
  5    IF SQL%NOTFOUND 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>
SQL> drop table place;

Table dropped.

SQL>








Related examples in the same category

1.Use implicit cursor
2.Implicit cursors: SQL%FOUND returns TRUE if SQL statement found one or more records
3.use for loop cursor
4.Implicit Cursors: sql%rowcount
5.Cursor for loop
6.Reference implicit in insert statement
7.Implicit cursor by for loop
8.Test cursor attributes with an implicit cursor
9.Use implicit or explicit cursor to insert 50000 rows to a table
10.Use passed in value with creating a cursor
11.Write an implicit cursor in a FOR loop and use the data
12.explicit cursor with cursor variable
13.for data in ( select * from tableName )