Parameterized Cursors with For Loop Method
--====================================================
--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)
FOR I IN C1 ( 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 :-
--- FOR LOOP----
--Requirment :-
Print employee information who belongs to department no 20, 30 and 40.
--====================================================
DECLARE
--Declare Cursor for dept = 10 --
CURSOR C1 IS
SELECT Ename, sal FROM XX_EMP_TL where
DEPTNO = '10';
--Declare Cursor for dept = 20 --
CURSOR C2 IS
SELECT Ename, sal FROM XX_EMP_TL where
DEPTNO = '20';
--Declare Cursor for dept = 30 --
CURSOR C3 IS
SELECT Ename, sal FROM XX_EMP_TL where
DEPTNO = '30';
BEGIN
dbms_output.put_line ('------------ Department 10 Employee information ------------');
FOR I IN C1 LOOP
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 C3 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 C2 LOOP
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 30 Employee information ------------
Vinod and Your Salary is : 17000
Rani and Your Salary is : 27000
------------ 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
---- Example of Parameterized Cursor with Single
parameters by using FOR LOOP ----
DECLARE
--Declare Cursor for all dept --
CURSOR C1(P_DEPTNO
XX_EMP_TL.DEPTNO%TYPE) 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;
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
---- Example of Parameterized Cursor with multi
parameters by using OPEN/FETCH/CLOSE
----
DECLARE
--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
dbms_output.put_line ('------------ Department 10 Employee information where
salary is more then 2500 ------------');
FOR I IN C1(10, 2500) LOOP
DBMS_OUTPUT.PUT_LINE(I.Ename||' and Your Salary is : '||I.SAL );
END LOOP;
-- Re-Use Above
cursor for 30 department where salary is
more then 10000 -----
dbms_output.put_line ('------------ Department 20 Employee information where
salary is more then 5000 ------------');
FOR I IN C1(20,5000) LOOP
DBMS_OUTPUT.PUT_LINE(I.Ename||' and Your Salary is : '||I.SAL );
END LOOP;
-- Re-Use Above
cursor for 40 department where salary is more then 17000--
dbms_output.put_line ('------------ Department 30 Employee information where
salary is more then 17000 ------------');
FOR I IN C1(30,17000) LOOP
DBMS_OUTPUT.PUT_LINE(I.Ename||' and Your Salary is : '||I.SAL );
END LOOP;
END;
Result:-
------------ Department 10 Employee information where
salary is more then 2500 ------------
Manoj and Your Salary is : 18000
------------ Department 20 Employee information where
salary is more then 5000 ------------
Nitin Kumar and Your Salary is : 21000
Sachin and Your Salary is : 25000
Rena and Your Salary is : 125000
------------ Department 30 Employee information where
salary is more then 17000 ------------
Rani and Your Salary is : 27000
Comments
Post a Comment