Named Cursor Vs REF Cursor in Oracle

Named Cursor Vs REF Cursor in Oracle

--------------------------------------------------------------------
set serveroutput on
DECLARE
    CURSOR lc_emp_name IS SELECT ename 
                          FROM emp 
                          WHERE deptno=10;
                          
    lv_emp_name varchar2(30);
BEGIN
    OPEN lc_emp_name;
    LOOP
        FETCH lc_emp_name into lv_emp_name;
        EXIT WHEN lc_emp_name%notfound;
        dbms_output.put_line(lv_emp_name);
    END LOOP;
    CLOSE lc_emp_name;
END;
/

--------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PRINT_LIST(p_in varchar2) as
    TYPE REF_CUR_TYPE IS REF CURSOR;
    lv_ref_cur REF_CUR_TYPE;
    lv_name   VARCHAR2(30);
BEGIN

    IF p_in='ENAME' THEN
       OPEN lv_ref_cur FOR SELECT ename FROM emp;
    ELSIF p_in='DNAME' THEN
       OPEN lv_ref_cur FOR 'SELECT dname FROM dept';
    ELSIF  p_in='JOB' THEN
       OPEN lv_ref_cur FOR SELECT distinct job FROM emp;
    ELSE
       OPEN lv_ref_cur FOR SELECT 'INVALID INPUT' FROM dual;
    END IF;
    
    LOOP
        FETCH lv_ref_cur INTO lv_name;
        EXIT WHEN lv_ref_cur%notfound;        
        dbms_output.put_line(lv_name);
    END LOOP;
END;
/

set serveroutput on
EXEC PRINT_LIST('ENAME');
EXEC PRINT_LIST('DNAME');
EXEC PRINT_LIST('JOB');
EXEC PRINT_LIST('ABC');

--------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE PROC_GET_LIST(p_in varchar2,p_out_cur out SYS_REFCURSOR) as
    lv_name   VARCHAR2(30);
BEGIN

    IF p_in='ENAME' THEN
       OPEN p_out_cur FOR SELECT ename FROM emp;
    ELSIF p_in='DNAME' THEN
       OPEN p_out_cur FOR 'SELECT dname FROM dept';
    ELSIF  p_in='JOB' THEN
       OPEN p_out_cur FOR SELECT distinct job FROM emp;
    ELSE
       OPEN p_out_cur FOR SELECT 'INVALID INPUT' FROM dual;
    END IF;

END;
/

CREATE OR REPLACE PROCEDURE PROC_PRINT_LIST(p_in varchar2) AS
    TYPE lc_ref_cur_type IS REF CURSOR;
    lc_ref_cur  lc_ref_cur_type;
    lv_name     VARCHAR2(30);
BEGIN
    PROC_GET_LIST(p_in,lc_ref_cur);
    
    LOOP
        FETCH lc_ref_cur INTO lv_name;
        EXIT WHEN lc_ref_cur%notfound;        
        dbms_output.put_line(lv_name);
    END LOOP;
END;
/

set serveroutput on
exec PROC_PRINT_LIST('ENAME');
exec PROC_PRINT_LIST('DNAME');
exec PROC_PRINT_LIST('JOB');
exec PROC_PRINT_LIST('ABC');

--------------------------------------------------------------------

CREATE OR REPLACE PACKAGE emp_pkg AS
    CURSOR emp_list IS SELECT *
                       FROM emp;

    CURSOR emp_list_10 IS SELECT *
                          FROM emp
                          WHERE deptno = 10;

    CURSOR emp_list_20 IS SELECT *
                          FROM emp
                          WHERE deptno = 20;

END emp_pkg;
/

DECLARE
    lv_emp_rec   emp%rowtype;
BEGIN
    OPEN emp_pkg.emp_list;
    FETCH emp_pkg.emp_list into lv_emp_rec;
    dbms_output.put_line('ENAME = '||lv_emp_rec.ename);
    dbms_output.put_line('SAL = '||lv_emp_rec.sal);
END;

--------------------------------------------------------------------