Database Interaction with PL/SQL: Explict Cursors in Depth - Further approaches to the above program
(Page 4 of 6 )
The above program can be solved using just the cursor FOR loop as follows (which is best shown with simplicity):
declare
cursor c_emp is
select ename,deptno from emp;
cursor c_dept is
select deptno,dname from dept;
begin
for r_dept in c_dept
loop
dbms_output.put_line(r_dept.dname);
dbms_output.put_line('-----------------');
for r_emp in c_emp
loop
if r_emp.deptno = r_dept.deptno then
dbms_output.put_line(r_emp.ename);
end if;
end loop;
end loop;
end;
I hope will agree with me that the above approach is very simple when compared with the program demonstrated in the first section.
Cursors with parameters – a simple example
In all of the above programs, the ‘if’ statement looks bit awkward. If no employee exists in a particular department, still the inner loop iterates through all employees, which is totally unnecessary. That means I need to retrieve only the employees I want (instead of all employees). This is where the concept of “cursors with parameters” comes in. First of we shall see a very simple example of using “cursors with parameters”:
declare
cursor c_emp(v_deptno emp.deptno%type) is
select ename from emp where deptno=v_deptno;
v_ename emp.ename%type;
begin
open c_emp(10);
loop
fetch c_emp into v_ename;
exit when c_emp%notfound;
dbms_output.put_line(v_ename);
end loop;
close c_emp;
end;
Within the above program, the cursor ‘c_emp’ is declared with a parameter ‘v_deptno’. That means we need to provide a value to that variable (v_deptno) at the time of opening the cursor, which gets directly substituted within the WHERE condition of the SELECT statement of cursor. The cursor FOR loop version of the same program as above will be as follows:
declare
cursor c_emp(v_deptno emp.deptno%type) is
select ename from emp where deptno=v_deptno;
begin
for v_emp in c_emp(10)
loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
Next: Cursors with parameters – a complicated example >>
More Oracle Articles
More By Jagadish Chatarji
/ 23 
