Parameterized cursor : Declaring the cursor with a parameter is called parameterized cursor
Syntax : Declare
Cursor Cursor_name(parameter datatype) is select col1,col2… where col=parameter;
Example: Show deptwise employee information
declare
cursor dept_cur is select * from dept;
cursor emp_cur(p_deptid number) is select * from emp where dept_id = p_deptid;
begin
for dept_rec in dept_cur
loop
dbms_output.put_line (‘—————————————–‘);
dbms_output.put_line (dept_Rec.dept_id ||’ ‘||dept_rec.dept_name);
dbms_output.put_line (‘——————————————‘);
for emp_rec in emp_cur(dept_Rec.dept_id)
loop
dbms_output.put_line (emp_Rec.eno||' '||emp_Rec.ename||' '||emp_rec.sal||' '||emp_rec.dept_id);
end loop;
end loop;end;
SQL> @ filename.sql
Output
100 IT
800 raju 15000 100
801 ramu 25000 100
802 venu 35000 100
101 Sales
803 krishna 10000 101
804 kiran 25000 101
102 Finance
805 chandu 14000 102
806 kumar 14050 102
807 omar 95000 102
