How to pass default Parameter in cursor

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

--Parameterized Cursors

/*

Syntax of Parameterized Cursor :-

CURSOR cur _ name (parameter list may be one, two, or more or Default ) IS SELECT statement;

OPEN cur _ name (Parameter value)

*/

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

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

--1.Example Cursor Parameters with Default Values - OPEN/FETCH/CLOSE --

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

DECLARE

V_NAME  XX_EMP_TL.ENAME%TYPE;

V_SAL XX_EMP_TL.SAL%TYPE;

   --Declare Cursor for all dept  --

  CURSOR C1(P_DEPTNO XX_EMP_TL.DEPTNO%TYPE DEFAULT 10 ) IS

  SELECT Ename, sal FROM XX_EMP_TL where DEPTNO = P_DEPTNO;

 

BEGIN

   

    OPEN C1(20);

    dbms_output.put_line ('------------  Department 20 Employee information  ------------');

        LOOP FETCH C1 INTO V_NAME, V_SAL;

        EXIT WHEN C1%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_name||'  and Your Salary is : '||V_SAL ); 

        END LOOP;

    CLOSE C1;

       

    OPEN C1(30); -- Re-Use Above cursor for 30 department --

    dbms_output.put_line ('------------  Department 30 Employee information  ------------');

        LOOP FETCH C1 INTO V_NAME, V_SAL;

        EXIT WHEN C1%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_name||'  and Your Salary is : '||V_SAL ); 

        END LOOP;

    CLOSE C1;

   

    OPEN C1;

    dbms_output.put_line ('------------  Default Department 10 Employee information  ------------');

        LOOP FETCH C1 INTO V_NAME, V_SAL;

        EXIT WHEN C1%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_name||'  and Your Salary is : '||V_SAL ); 

        END LOOP;

    CLOSE C1;   

END;

Result :-

------------  Department 20 Employee information  ------------

Sachin  and Your Salary is : 25000

Rena  and Your Salary is : 125000

Chavi  and Your Salary is : 5000

Nitin Kumar  and Your Salary is : 21000

------------  Department 30 Employee information  ------------

Vinod  and Your Salary is : 17000

Rani  and Your Salary is : 27000

------------  Default Department 10 Employee information  ------------

Sonu  and Your Salary is : 2500

Ravi  and Your Salary is : 2500

Manoj  and Your Salary is : 18000

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

--2. Example Cursor Parameters with Default Values - FOR CURSOR LOOP --

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

DECLARE

   --Declare Cursor for all dept  --

  CURSOR C1(P_DEPTNO XX_EMP_TL.DEPTNO%TYPE Default 40) IS

  SELECT * FROM XX_EMP_TL where DEPTNO = P_DEPTNO;

BEGIN

   

    dbms_output.put_line ('------------  Department 10 Employee information  ------------');

        FOR I IN C1(10) LOOP         

        DBMS_OUTPUT.PUT_LINE(I.Ename||'  and Your Salary is : '||I.SAL ); 

        END LOOP;

   

    dbms_output.put_line ('------------  Department 20 Employee information  ------------');

        FOR I IN C1(20) LOOP -- Re-Use Above cursor for 20 department --         

        DBMS_OUTPUT.PUT_LINE(I.Ename||'  and Your Salary is : '||I.SAL ); 

        END LOOP;

       

    dbms_output.put_line ('------------  Department 30 Employee information  ------------');

        FOR I IN C1(30) LOOP -- Re-Use Above cursor for 30 department --

        DBMS_OUTPUT.PUT_LINE(I.Ename||'  and Your Salary is : '||I.SAL ); 

        END LOOP;

    dbms_output.put_line ('------------ DEFAULT  Department (40) Employee information  ------------');

        FOR I IN C1 LOOP -- Re-Use Above cursor for DEFAULT (40) department --

        DBMS_OUTPUT.PUT_LINE(I.Ename||'  and Your Salary is : '||I.SAL ); 

        END LOOP;

           

END;

Result :-

------------  Department 10 Employee information  ------------

Sonu  and Your Salary is : 2500

Ravi  and Your Salary is : 2500

Manoj  and Your Salary is : 18000

------------  Department 20 Employee information  ------------

Sachin  and Your Salary is : 25000

Rena  and Your Salary is : 125000

Chavi  and Your Salary is : 5000

Nitin Kumar  and Your Salary is : 21000

------------  Department 30 Employee information  ------------

Vinod  and Your Salary is : 17000

Rani  and Your Salary is : 27000

------------ DEFAULT  Department (40) Employee information  ------------

Amit  and Your Salary is : 10000