Mega Code Archive
Returning Rows Call
<![CDATA[
SQL>
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor');
1 row created.
SQL>
SQL> CREATE OR REPLACE PACKAGE emp_pkg
2 AS
3 TYPE studCur IS REF CURSOR;
4 PROCEDURE Getemp(o_StudCur OUT studCur);
5 END emp_pkg;
6 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY emp_pkg
2 AS
3 PROCEDURE Getemp(o_StudCur OUT studCur)
4 IS
5 BEGIN
6 OPEN o_StudCur FOR
7 SELECT empID, Name FROM emp;
8 END Getemp;
9 END emp_pkg;
10 /
Package body created.
SQL> SET SERVEROUT ON
SQL> DECLARE
2 TYPE studCurType IS REF CURSOR;
3 mycur studCurType;
4 studrow emp%ROWTYPE;
5 BEGIN
6 emp_pkg.Getemp(mycur);
7 FETCH mycur INTO studrow;
8 WHILE mycur%FOUND
9 LOOP
10 dbms_output.put_line(studrow.empID || ' ' ||
11 studrow.Name);
12 FETCH mycur INTO studrow;
13 END LOOP;
14 END;
15 /
1 Tom
2 Jack
3 Mary
4 Bill
5 Cat
6 Victor
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
]]>