Working with REF CURSOR in PL/SQL - Dealing with REF CURSOR in the sub-programs of a PL/SQL block (
Page 4 of 4 )
Sub-programs can also be called sub-routines. These are nothing but the divisions of the main program. These divisions are named and are executed when they are called by name from the main program. They will not get executed unless they are called.
The following is an example:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
procedure PrintEmployeeDetails is
begin
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails;
close c_emp;
end loop;
end;
In the above program, the sub-routine is named "PrintEmployeeDetails." You can observe that I am executing (or calling) the sub-routine from within the loop as follows:
for i in (select deptno,dname from dept)
loop
.
.
PrintEmployeeDetails;
.
.
end loop;
According to the above loop, the sub-routine gets executed for every iteration, which displays the employee information for the respective department.
Passing REF CURSOR as parameters to sub-programs
In the previous section, we already started working with sub-programs (or sub-routines). In this section, I shall extend the same with the concept of "parameters" (or arguments). Every sub-program (or sub-routine) can accept values passed to it in the form of "parameters" (or arguments). Every parameter is very similar to a variable, but gets declared as part of a sub-program.
Let us consider the following program:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
procedure PrintEmployeeDetails(p_emp r_cursor) is
er rec_emp;
begin
loop
fetch p_emp into er;
exit when p_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails(c_emp);
close c_emp;
end loop;
end;
From the above program, you can observe the following declaration:
procedure PrintEmployeeDetails(p_emp r_cursor) is
In the above declaration, "PrintEmployeeDetails" is the name of the sub-routine which accepts "p_emp" as a parameter (of type "r_cursor") and we can use that parameter throughout that sub-routine.
I hope you enjoyed the article and any comments, suggestions, feedback, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com
| | Discuss Working with REF CURSOR in PL/SQL | | | | | | | This is a simple introduction to REF CURSOR in PL/SQL. Hope you will enjoy it. If... | | | | | | dear sir,
this article is very use full for the beginner like me.
Thanks a... | | | | | | It is very good article for the starters in PL/SQL | | | | | | thanks!
this was a great article for me and gave me new insights. | | | | | | I would like to know how to handle data from a ref cursor to a record type when i... | | | | | | I liked they way,all your topics are put in.This one was also very easy to... | | | | | | This is really to good explanation with examples. I was able to understand it in a... | | | | | | very good one | | | | | | great way of explaining the concept | | | | | | Its really a good way of explaining ina simple way..
Thanks
Jeg | | | | | | Thanks Jag for the enlightening article on Reference cursors...it was a lot more... | | | | | | Thanks allot for providing us a very good description on Ref Cursors. I found this... | | | | | | Excellent steps of understanding REFCURSOR concept with simple example... | | | | | | This is an example of telling a complex thing in a SIMPLE way. Hats off to you.... | | | | | | Thanks a lot for the post. This is good article and keep posting things with similar... | | | | | | The Content is Very Useful for my Knowledge and more than This is Really a Good Work... | | | | | | Itīs only confused to use ref cursor in page 1-3.
The only reason to use ref cursor... | | | | | | has anybody tried using ref cursor in 10g. Because I have a proc which passes ref... | | | | | | The examples posted here are really helpful and can be easily comprehended.
If you... | | | | | | this topic civering ref cursors was very good. especially for beginners!!! | | | | | | its great to peek into it for 2 minutes and to get in hand all the imp points reg... | | | | | | Excellent article! Thanks. | | | | | | Really helpful. In less than 15 min I could get everything I ever wanted to know... | | | | | | its great to peek into it for 2 minutes and to get in hand all the imp points reg... | | | | | | Dear Sir,
Realy its very easy to study and understand...Thanx for... | | | | | | This is really good presentation for understanding the ref_cursors topic .
this is... | | | | | | Very nice artical. It gives very clear idea of ref cursor. Thank you for this. | | | | | | very helpfull | | | | | | You are amazing. If all programming books were written the way you do, learning to... | | | | | | Dear Jagadish,
I wish other authors 'learned' the flow and style that you use.
It... | | | | | | Hi sir,
Basically before i read this article i have a little idea on REF... | | | | | | How do you use a ref cursor,in PL/SQL, when you don't know the number of columns... | | | | | | It seems like you could do it in PL/SQL, but you can't before version 11g. Variable... | | | | | | Good explanation, however just like to point out one flaw with the... | | | | | | Hi Sir
This article is very good and very usefull to me, the way of... | | | | | | explanation of this topic is awesome.....and I enjoyed working with this ref... | | | | | | Its really a best example for understanding a REF Cursor in PL/SQL.I am sure if any... | | | | | | Excellent article. The author can read minds of his readers. | | | | | | This is v.good example for the beginners | | | | | | Hi jagadish,
Very good explanation.It gave a clear understanding of... | | | | | | gud job:)) | | | | | | This article is very useful for me as a beginner.Thank u very much for your good... | | | | | | Excellent explanation!!! Whenever I go through any article in DevShed regarding... | | | | | | Thank you very much for this sort of step by step deliberation. I hope it will help... | | | | | | dear sir,
this article is very use full for me.
Thanks a lot.
from,... | | | | | | >>> Post your comment now! | | | | | |
|
 |