Server-side cursor variables.
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('HIS', 101, 'History 101', 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('HIS', 301, 'History 301', 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('CS', 101, 'Computer Science 101', 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('ECN', 203, 'Economics 203', 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('CS', 102, 'Computer Science 102', 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('MUS', 410, 'Music 410', 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('ECN', 101, 'Economics 101', 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('NUT', 307, 'Nutrition 307', 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ('MUS', 100, 'Music 100', 100, 0, 3, NULL);
1 row created.
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> select * from session;
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
HIS 101.00
History 101
30.00 11.00 4.00 ########
HIS 301.00
History 301
30.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
CS 101.00
Computer Science 101
50.00 .00 4.00 ########
ECN 203.00
Economics 203
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
15.00 .00 3.00 ########
CS 102.00
Computer Science 102
35.00 3.00 4.00 ########
MUS 410.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
Music 410
5.00 4.00 3.00 ########
ECN 101.00
Economics 101
50.00 .00 4.00 ########
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
NUT 307.00
Nutrition 307
20.00 2.00 4.00 ########
MUS 100.00
Music 100
100.00 .00 3.00
DEP COURSE
--- --------
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
MAX_LECTURER CURRENT_LECTURER NUM_CREDITS ROOM_ID
------------ ---------------- ----------- --------
9 rows selected.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE ShowCursorVariable
2 (p_Table IN VARCHAR2) AS
3
4 TYPE myRoomType IS REF CURSOR;
5
6 cursorValue myRoomType;
7
8 v_Department session.department%TYPE;
9 v_Course session.course%TYPE;
10 v_RoomID place.room_id%TYPE;
11 v_Description place.description%TYPE;
12 BEGIN
13 IF p_Table = 'session' THEN
14 OPEN cursorValue FOR
15 SELECT department, course
16 FROM session;
17 ELSIF p_table = 'place' THEN
18 OPEN cursorValue FOR
19 SELECT room_id, description
20 FROM place;
21 ELSE
22 RAISE_APPLICATION_ERROR(-20000,'Input must be ''session'' or ''place''');
23 END IF;
24
25 LOOP
26 IF p_Table = 'session' THEN
27 FETCH cursorValue INTO
28 v_Department, v_Course;
29 EXIT WHEN cursorValue%NOTFOUND;
30
31 INSERT INTO MyTable (num_col, char_col)
32 VALUES (v_Course, v_Department);
33 ELSE
34 FETCH cursorValue INTO
35 v_RoomID, v_Description;
36 EXIT WHEN cursorValue%NOTFOUND;
37
38 INSERT INTO MyTable (num_col, char_col)
39 VALUES (v_RoomID, SUBSTR(v_Description, 1, 60));
40 END IF;
41 END LOOP;
42
43 CLOSE cursorValue;
44
45 COMMIT;
46 END ShowCursorVariable;
47 /
Procedure created.
SQL>
SQL>
SQL> drop table place;
Table dropped.
SQL> drop table session;
Table dropped.
SQL> drop table MyTable;
Table dropped.
SQL>
Related examples in the same category