Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs

Mixing PROCEDURE and FUNCTION - Oracle

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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, RECORD and TABLE in Sub-programs
  2. Mixing PROCEDURE and FUNCTION
  3. Using RECORD with sub-programs
  4. Returning a RECORD from FUNCTION
  5. Working with PL/SQL TABLE and sub-programs
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
August 23, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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’.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: