Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs - Mixing PROCEDURE and FUNCTION
(Page 2 of 5 )
Can we mix PROCEDURE and FUNCTION together in a single program? Why not? After all, any of those two belongs to “sub-program.” And a PL/SQL program can include any number of sub-programs (regardless of whether the sub-program involves PROCEDURE or FUNCTION). Let us look at a practical example of this issue:
declare
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;
procedure dispEmp is
CURSOR c_emp is
select ename,deptno from emp;
begin
for r_emp in c_emp
loop
dbms_output.put_line(r_emp.ename || ',' || getDname(r_emp.deptno));
end loop;
end;
BEGIN
dispEmp;
END;
I just modified the program given in the previous section to follow this topic. From the above program, first of all there exist no variable declarations in the main program. The declaration of CURSOR as well as the FOR loop to the CURSOR have been encapsulated into a separate procedure named ‘dispEmp’. So, there exists literally no processing to deal with at the main program, apart from calling the procedure ‘dispEmp’.
Next: Using RECORD with sub-programs >>
More Oracle Articles
More By Jagadish Chatarji
/ 17 
