Student fetch package
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 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 NUMBER := 5,p_lecturer OUT t_lecturer)RETURN BOOLEAN;
7 PROCEDURE PrintRows(p_BatchSize IN NUMBER,p_lecturer IN t_lecturer);
8 END StudentFetch;
9 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY StudentFetch AS
2 CURSOR myAllLecturer IS SELECT * FROM lecturer ORDER BY ID;
3 PROCEDURE OpenCursor IS
4 BEGIN
5 OPEN myAllLecturer;
6 END OpenCursor;
7
8 PROCEDURE CloseCursor IS
9 BEGIN
10 CLOSE myAllLecturer;
11 END CloseCursor;
12
13 FUNCTION FetchRows(p_BatchSize IN NUMBER := 5,p_lecturer OUT t_lecturer)
14 RETURN BOOLEAN IS
15 v_Finished BOOLEAN := TRUE;
16 BEGIN
17 FOR v_Count IN 1..p_BatchSize LOOP
18 FETCH myAllLecturer INTO p_lecturer(v_Count);
19 IF myAllLecturer%NOTFOUND THEN
20 v_Finished := FALSE;
21 EXIT;
22 END IF;
23 END LOOP;
24 RETURN v_Finished;
25 END FetchRows;
26
27 PROCEDURE PrintRows(p_BatchSize IN NUMBER,
28 p_lecturer IN t_lecturer) IS
29 BEGIN
30 FOR v_Count IN 1..p_BatchSize LOOP
31 DBMS_OUTPUT.PUT('ID: ' || p_lecturer(v_Count).ID);
32 DBMS_OUTPUT.PUT(' Name: ' || p_lecturer(v_Count).first_name);
33 DBMS_OUTPUT.PUT_LINE(' ' || p_lecturer(v_Count).last_name);
34 END LOOP;
35 END PrintRows;
36 END StudentFetch;
37 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_BatchSize NUMBER := 5;
3 v_lecturer StudentFetch.t_lecturer;
4 BEGIN
5 StudentFetch.OpenCursor;
6 WHILE StudentFetch.FetchRows(v_BatchSize, v_lecturer) LOOP
7 StudentFetch.PrintRows(v_BatchSize, v_lecturer);
8 END LOOP;
9 StudentFetch.CloseCursor;
10 END;
11 /
ID: 10001 Name: Scott Lawson
ID: 10002 Name: Mar Wells
ID: 10003 Name: Jone Bliss
ID: 10004 Name: Man Kyte
ID: 10005 Name: Pat Poll
ID: 10006 Name: Tim Viper
ID: 10007 Name: Barbara Blues
ID: 10008 Name: David Large
ID: 10009 Name: Chris Elegant
ID: 10010 Name: Rose Bond
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL>
Related examples in the same category