SQL & PL/SQL

Hi all,

I am intending to use A CURSOR OF A PROCEDURE to get RECORDSET that means a collection of many records. After that, depending on my different purposes, I will use different fields of that cursor. For example, in this case I just need to print out "employee_id". Following is my code

===================================================================
CREATE OR REPLACE PACKAGE Types AS
TYPE cursor_type IS REF CURSOR RETURN employees%rowtype;
END Types;
/
-------------------------------------------------------------------------------------------
CREATE OR REPLACE
PROCEDURE GetEmpRS ( p_recordset OUT Types.cursor_type) AS
BEGIN
OPEN p_recordset FOR
select * from employees
where rownum < 10;
END GetEmpRS;
/
-------------------------------------------------------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
v_cursor Types.cursor_type;
person employees%rowtype;
BEGIN
------- GetEmpRS ( p_recordset => v_cursor); *(line a)*
LOOP
GetEmpRS ( p_recordset => v_cursor); *(line b)*
FETCH v_cursor INTO person;
-- EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(person.employee_id); **TROUBLE HERE: Either using code as "line a" or "line b",*
it prints out 10 lines with the same value of employee_id
*( it can be the first value or last value of employee_id column)*
END LOOP;
CLOSE v_cursor;
END;
/
===================================================================

I don't know how to have my cursor to ptint out all values of employee_id col.

I will be very happy if there is someone would help me!

Thank you

Edited by: Mai Phuong on Jun 24, 2010 12:26 AM