Parameterized Cursors with DECLARE / OPEN / FETCH / CLOSE Method of 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
|
|
--====================================================
--Example_1 :-
--- DECLARE / OPEN / FETCH / CLOSE ----
--Requirment :-
Print employee information who belongs to department no 20, 30 and 40.
--====================================================
DECLARE
V_NAME XX_EMP_TL.ENAME%TYPE;
V_SAL XX_EMP_TL.SAL%TYPE;
--Declare Cursor for dept = 20 --
CURSOR C1 IS
SELECT Ename, sal FROM XX_EMP_TL where
DEPTNO = '20';
--Declare Cursor for dept = 30 --
CURSOR C2 IS
SELECT Ename, sal FROM XX_EMP_TL where
DEPTNO = '30';
--Declare Cursor for dept = 40 --
CURSOR C3 IS
SELECT Ename, sal FROM XX_EMP_TL where
DEPTNO = '40';
BEGIN
OPEN C1;
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 C2;
dbms_output.put_line ('------------ Department 30 Employee information ------------');
LOOP FETCH C2 INTO
V_NAME,
V_SAL;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name||' and Your Salary is : '||V_SAL
);
END LOOP;
CLOSE C2;
OPEN C3;
dbms_output.put_line ('------------ Department 40 Employee information ------------');
LOOP FETCH C3 INTO
V_NAME,
V_SAL;
EXIT WHEN C3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name||' and Your Salary is : '||V_SAL
);
END LOOP;
CLOSE C3;
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
------------ Department 40 Employee information ------------
Amit and Your Salary is : 10000
---- Example of Parameterized Cursor with Single
parameters by using 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) 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(40); --
Re-Use Above cursor for 40 department --
dbms_output.put_line ('------------ Department 40 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
------------ Department 40 Employee information ------------
Amit and Your Salary is : 10000
---- Example of Parameterized Cursor with multi
parameters by using 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,
P_SAL XX_EMP_TL.SAL%TYPE) IS
SELECT Ename, sal
FROM XX_EMP_TL
where DEPTNO =
P_DEPTNO
AND SAL >
P_SAL;
BEGIN
OPEN C1(20, 21000);
dbms_output.put_line ('------------ Department 20 Employee information where
salary is more then 21000 ------------');
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,10000); --
Re-Use Above cursor for 30 department
where salary is more then 10000
-----
dbms_output.put_line ('------------ Department 30 Employee information where
salary is more then 10000 ------------');
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(40,17000); --
Re-Use Above cursor for 40 department where salary is more then 17000--
dbms_output.put_line ('------------ Department 40 Employee information where
salary is more then 17000 ------------');
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 where
salary is more then 21000 ------------
Sachin and Your Salary is : 25000
Rena and Your Salary is : 125000
------------ Department 30 Employee information where
salary is more then 10000 ------------
Vinod and Your Salary is : 17000
Rani and Your Salary is : 27000
------------ Department 40 Employee information where
salary is more then 17000 ------------
Comments
Post a Comment