Mega Code Archive

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>