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