Ref cursor with function

EMPNO	ENAME	HIREDATE	DOB	GENDER	SAL	DEPTNO	JOB
1000	Sachin	11-Sep-85	01-Mar-20	M	25000	20	MANAGER
1002	Rena	01-Aug-82	11-Mar-20	F	125000	20
1008	Sonu	11-Sep-85	01-Mar-20	M	2500	10	CLERK
1003	Chavi	25-Mar-81	15-Feb-20	F	5000	20	CLERK
1006	Ravi	11-Sep-89	01-Mar-19	M	2500	10	CLERK
1005	Vinod	18-Sep-86	01-Mar-20	M	17000	30	DEVELOPER
1004	Rani	28-Jan-88	19-Jul-19	F	27000	30	MANAGER
1007	Manoj	11-Sep-85	09-Mar-18	M	18000	10	DEVELOPER
1001	Amit	11-Sep-80	01-Jan-20	M	10000	40	DEVELOPER
1009	Nitin Kumar	11-Sep-85	11-Mar-20	M	21000	20	DEVELOPER

--====================================================

--Ref Cursor with Function or procedure

--Example :- Use Function to Open Ref Cursor Variable

--====================================================

--================== Part 1

CREATE OR REPLACE FUNCTION XX_REF_FUNC ( V_QUERY_SELECTOR VARCHAR2 ) RETURN SYS_REFCURSOR AS

C_CURSOR   SYS_REFCURSOR ;

BEGIN

        IF V_QUERY_SELECTOR = 'EMP' THEN

            OPEN C_CURSOR FOR  SELECT ENAME, SAL FROM XX_EMP_TL ;

        ELSE

            OPEN C_CURSOR FOR  SELECT DNAME, DEPTNO FROM XX_DEPT_TL ;

        END IF;  

      

       RETURN   C_CURSOR;

END;

--================== Part 2

DECLARE

-- SYS_REFCURSOR  System defind ref type ----

C_W_REF  SYS_REFCURSOR; -- weak cursor variable

VAR_1 VARCHAR2(100);

VAR_2 NUMBER;

 

    BEGIN

    dbms_output.put_line ('************** EMPLOYEE DETAILS****************');     

        C_W_REF := XX_REF_FUNC('EMP') ;

            LOOP               

                FETCH C_W_REF INTO VAR_1, VAR_2;

                EXIT WHEN C_W_REF%NOTFOUND;                                

                 DBMS_OUTPUT.PUT_LINE (C_W_REF%ROWCOUNT||' - '||VAR_1||' | salary is :- '||VAR_2);                   

            END LOOP;           

        CLOSE C_W_REF;     

       

        dbms_output.put_line ('************** DEPARTMENT DETAILS****************');

        C_W_REF := XX_REF_FUNC(NULL) ;

            LOOP               

                 FETCH C_W_REF INTO VAR_1, VAR_2;

                EXIT WHEN C_W_REF%NOTFOUND;                                

                 DBMS_OUTPUT.PUT_LINE (C_W_REF%ROWCOUNT||' - '||VAR_1);                   

            END LOOP;           

        CLOSE C_W_REF;

       

    END;

OUTPUT:-

************** EMPLOYEE DETAILS****************

1 - Sachin | salary is :- 25000

2 - Rena | salary is :- 125000

3 - Sonu | salary is :- 2500

4 - Chavi | salary is :- 5000

5 - Ravi | salary is :- 2500

6 - Vinod | salary is :- 17000

7 - Rani | salary is :- 27000

8 - Manoj | salary is :- 18000

9 - Amit | salary is :- 10000

10 - Nitin Kumar | salary is :- 21000

************** DEPARTMENT DETAILS****************

1 - ACCOUNTING

2 - RESEARCH

3 - SALES

4 - OPERATIONS