Close a cursor and open it again with another query

  
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> set serveroutput on
SQL>
SQL> DECLARE
  2    TYPE roomCursor IS REF CURSOR RETURN place%ROWTYPE;
  3    roomCursorV roomCursor;
  4    v_Rooms place%ROWTYPE;
  5  BEGIN
  6    OPEN roomCursorV FOR SELECT * FROM place WHERE building = 'Building 7';
  7    LOOP
  8      FETCH roomCursorV INTO v_Rooms;
  9      EXIT WHEN roomCursorV%NOTFOUND;
 10      DBMS_OUTPUT.PUT_LINE('Fetched Room #' || v_Rooms.room_number ||' in Building 7 from roomCursorV');
 11    END LOOP;
 12    CLOSE roomCursorV;
 13    OPEN roomCursorV FOR SELECT * FROM place WHERE number_seats > 100;
 14    LOOP
 15      FETCH roomCursorV INTO v_Rooms;
 16      EXIT WHEN roomCursorV%NOTFOUND;
 17      DBMS_OUTPUT.PUT_LINE('Fetched ' || v_Rooms.building || ',' ||' Room #' || v_Rooms.room_number || ' from roomCursorV');
 18    END LOOP;
 19
 20    CLOSE roomCursorV;
 21  END;
 22  /
Fetched Room #201 in Building 7 from roomCursorV
Fetched Room #300 in Building 7 from roomCursorV
Fetched Room #310 in Building 7 from roomCursorV
Fetched Building 7, Room #201 from roomCursorV
Fetched Building 6, Room #101 from roomCursorV
Fetched Music Building, Room #200 from roomCursorV

PL/SQL procedure successfully completed.

SQL>
SQL> drop table place;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Below is a function that demonstrates how to use the CLOSE statement
2.	Closing the Cursor Variable
3.	Close a cursor after looping
4.	After the cursor is opened, the book table is dropped prior to looping through the cursor.
5.	Close cursor after while loop
6.	Close cursor in excpetion handler

Close a cursor and open it again with another query : Close Cursor « Cursor « Oracle PL / SQL

Related examples in the same category