Cursor operation between package functions
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>
SQL> CREATE OR REPLACE PACKAGE StudentFetch AS
2 TYPE t_lecturer IS TABLE OF lecturer%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4 PROCEDURE OpenCursor;
5 PROCEDURE CloseCursor;
6 FUNCTION FetchRows(p_BatchSize IN OUT NUMBER,p_lecturer OUT t_lecturer)
7 RETURN BOOLEAN;
8 PROCEDURE PrintRows(p_BatchSize IN NUMBER,
9 p_lecturer IN t_lecturer);
10 END StudentFetch;
11 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY StudentFetch AS
2 CURSOR myAllLecturer IS
3 SELECT *
4 FROM lecturer
5 ORDER BY ID;
6
7 PROCEDURE OpenCursor IS
8 BEGIN
9 OPEN myAllLecturer;
10 END OpenCursor;
11
12 PROCEDURE CloseCursor IS
13 BEGIN
14 CLOSE myAllLecturer;
15 END CloseCursor;
16
17 FUNCTION FetchRows(p_BatchSize IN OUT NUMBER,
18 p_lecturer OUT t_lecturer)
19 RETURN BOOLEAN IS
20 v_Finished BOOLEAN := TRUE;
21 BEGIN
22 FOR v_Count IN 1..p_BatchSize LOOP
23 FETCH myAllLecturer INTO p_lecturer(v_Count);
24 IF myAllLecturer%NOTFOUND THEN
25 v_Finished := FALSE;
26 p_BatchSize := v_Count - 1;
27 EXIT;
28 END IF;
29 END LOOP;
30 RETURN v_Finished;
31 END FetchRows;
32
33 PROCEDURE PrintRows(p_BatchSize IN NUMBER,
34 p_lecturer IN t_lecturer) IS
35 BEGIN
36 FOR v_Count IN 1..p_BatchSize LOOP
37 DBMS_OUTPUT.PUT('ID: ' || p_lecturer(v_Count).ID);
38 DBMS_OUTPUT.PUT(' Name: ' || p_lecturer(v_Count).first_name);
39 DBMS_OUTPUT.PUT_LINE(' ' || p_lecturer(v_Count).last_name);
40 END LOOP;
41 END PrintRows;
42 END StudentFetch;
43 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>