Persistance of packaged variables.
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, 'Mar', 'Wells','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, 'Man', 'Kyte','Economics', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, 'Pat', 'Poll','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, 'Tim', 'Viper','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, 'Barbara', 'Blues','Economics', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, 'David', 'Large','Music', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, 'Rose', 'Bond','Music', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE PersistPkg AS
2 TYPE t_StudentTable IS TABLE OF lecturer.ID%TYPE
3 INDEX BY BINARY_INTEGER;
4
5 v_MaxRows NUMBER := 5;
6
7 PROCEDURE Readlecturer(p_StudTable OUT t_StudentTable, p_NumRows OUT NUMBER);
8
9 END PersistPkg;
10 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY PersistPkg AS
2 CURSOR StudentCursor IS
3 SELECT ID
4 FROM lecturer
5 ORDER BY last_name;
6
7 PROCEDURE Readlecturer(p_StudTable OUT t_StudentTable,
8 p_NumRows OUT NUMBER) IS
9 v_Done BOOLEAN := FALSE;
10 v_NumRows NUMBER := 1;
11 BEGIN
12 IF NOT StudentCursor%ISOPEN THEN
13 OPEN StudentCursor;
14 END IF;
15
16 WHILE NOT v_Done LOOP
17 FETCH StudentCursor INTO p_StudTable(v_NumRows);
18 IF StudentCursor%NOTFOUND THEN
19 CLOSE StudentCursor;
20 v_Done := TRUE;
21 ELSE
22 v_NumRows := v_NumRows + 1;
23 IF v_NumRows > v_MaxRows THEN
24 v_Done := TRUE;
25 END IF;
26 END IF;
27 END LOOP;
28
29 p_NumRows := v_NumRows - 1;
30 END Readlecturer;
31 END PersistPkg;
32 /
Package body created.
SQL>
SQL> DECLARE
2 myLecturerTable PersistPkg.t_StudentTable;
3 v_NumRows NUMBER := PersistPkg.v_MaxRows;
4 myFirstName lecturer.first_name%TYPE;
5 v_LastName lecturer.last_name%TYPE;
6 BEGIN
7 PersistPkg.Readlecturer(myLecturerTable, v_NumRows);
8 DBMS_OUTPUT.PUT_LINE(' Fetched ' || v_NumRows || ' rows:');
9 FOR v_Count IN 1..v_NumRows LOOP
10 SELECT first_name, last_name
11 INTO myFirstName, v_LastName
12 FROM lecturer
13 WHERE ID = myLecturerTable(v_Count);
14 DBMS_OUTPUT.PUT_LINE(myFirstName || ' ' || v_LastName);
15 END LOOP;
16 END;
17 /
Fetched 5 rows:
Jone Bliss
Barbara Blues
Rose Bond
Sharon Clear
Chris Elegant
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>