Database Interaction with PL/SQL: Explict Cursors in Depth - Can we use JOINS in cursors?
(Page 6 of 6 )
Why not? We can use almost any type of query (including joins, sub-queries etc) with cursor declarations. If you have any expressions as columns, then you are required to provide an alias for each of them. The following program is a demonstration:
declare
cursor c_emp is
select ename,sal*12 as annsal,dname
from emp,dept
where emp.deptno = dept.deptno;
r_emp c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into r_emp;
exit when c_emp%notfound;
dbms_output.put_line(r_emp.ename || ' - ' || r_emp.annsal || ' - ' || r_emp.dname);
end loop;
close c_emp;
end;
In the above program, “sal*12” is an expression provided with an alias “annsal” (annual salary). The beauty of ‘r_emp’ is that it automatically contains ‘annsal’ as a member without having any declaration. And I provide the cursor FOR loop version for the above program as follows:
declare
cursor c_emp is
select ename,sal*12 as annsal,dname
from emp,dept
where emp.deptno = dept.deptno;
begin
for r_emp in c_emp
loop
dbms_output.put_line(r_emp.ename || ' - ' || r_emp.annsal || ' - ' || r_emp.dname);
end loop;
end;
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 23 
