Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs (
Page 1 of 5 )
This is part 14 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on some tips for using procedures together with the introduction to functions. In this article we will look through several examples that cover using sub-programs in a very effective manner. I suggest you to go through part two in order to follow this article properly.Please note that all the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.
More about FUNCTION
My previous article actually stopped at a simple example on FUNCTION. Now we shall extend its limit. Consider the following example:
declare
CURSOR c_emp is
select ename,deptno from emp;
function getDname(p_deptno dept.deptno%type) return varchar2 is
v_dname dept.dname%type;
begin
select dname into v_dname
from dept where deptno = p_deptno;
return v_dname;
end;
begin
for r_emp in c_emp
loop
dbms_output.put_line(r_emp.ename || ',' || getDname(r_emp.deptno));
end loop;
end;
The above program really makes use of FUNCTION properly. We are sending ‘deptno’ as a parameter to the function ‘getDname’ which returns the department name back to the main program. Actually we can store the value returned back by ‘getDname’ (demonstrated in part 13), but it would be easy to write directly within the DBMS_OUTPUT statement in the main program as above.
/ 17 
