Cursor, ref cursor and SYS_Ref cursor.

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno    IN  emp.DEPARTMENT_ID%TYPE, p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
     OPEN p_recordset FOR
SELECT
     EMPLOYEE_ID,
     FIRST_NAME,
     LAST_NAME
     EMAIL,
     PHONE_NUMBER,
     HIRE_DATE,
     JOB_ID,
     SALARY,
     COMMISSION_PCT,
     MANAGER_ID,
     DEPARTMENT_ID
FROM   emp
     WHERE  DEPARTMENT_ID = p_deptno
     ORDER BY FIRST_NAME;
END get_emp_rs;
/

===============================================================================
print  out put :
SQL> exec get_emp_rs(p_deptno=>10,p_recordset=>:X);

PL/SQL procedure successfully completed.

SQL> set serveroutput on;SQL> exec get_emp_rs(p_deptno=>10,p_recordset=>:X);

PL/SQL procedure successfully completed.

SQL> print x;

EMPLOYEE_ID FIRST_NAME           EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    515.123.4444         17-SEP-87 AD_ASST          4400                       101            10
========================================================================



LOOP:

declare
n number (4):=1;
begin
loop
dbms_output.put_line(n);
exit when n>=11;
n:=n+1;
end loop;
end;
/




Cursor;

declare
cursor c1 is select first_name from employees;
X employees.first_name%type;
begin
open c1;
if c1%isopen then
dbms_output.put_line('XXXXcursor opend');
loop
fetch c1  into X ;
if C1%found then
dbms_output.put_line(X||', '||C1%rowcount);
dbms_output.put_line(C1%rowcount);
else
exit;
end if;
end loop;
end if;
close c1;
end;
/


======================

declare
cursor c1 (employee_no number ) is select * from employees where employee_id=employee_no;
X employees%ROWtype;
begin
open c1(&employee_no);
if c1%isopen then
dbms_output.put_line('XXXXcursor opend');
loop
fetch c1  into X ;
if C1%found then
dbms_output.put_line(X.employee_id||X.first_name);
dbms_output.put_line(C1%rowcount);
else
exit;
end if;
end loop;
end if;
close c1;
end;
/
======================================================