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

Returning a RECORD from 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

In the previous section, we have seen how to deal with RECORD using procedures. Now we shall see the same, using FUNCTION. Instead of passing a RECORD to the FUNCTION, we shall return a RECORD from the FUNCTION back to the calling program. Consider the following program:

declare

TYPE t_deptrec IS RECORD

(

name dept.dname%type,

location dept.loc%type

);

CURSOR c_emp is

select ename,deptno from emp;

r_dept t_deptrec;

function getDept(p_deptno dept.deptno%type) return t_deptrec is

r_dept t_deptrec;

begin

select dname,loc into r_dept

from dept where deptno = p_deptno;

return r_dept;

end;

BEGIN

for r_emp in c_emp

loop

r_dept := getDept(r_emp.deptno);

dbms_output.put_line(r_emp.ename || ',' || r_dept.name || ',' || r_dept.location);

end loop;

END;

Let me explain part by part. Consider the following:

TYPE t_deptrec IS RECORD

(

name dept.dname%type,

location dept.loc%type

);

The above defines a new data type named ‘t_deptrec’ (just like %ROWTYPE with limited specified fields) which can hold only two fields, namely ‘name’ and ‘location’.

CURSOR c_emp is

select ename,deptno from emp;

The above statement defined a cursor ‘c_emp’ based on the SELECT statement provided.

r_dept t_deptrec;

The above statement declares a variable ‘r_dept’ based on the datatype ‘t_deptrec’. This means ‘r_dept’ internally contains the fields ‘name’ and ‘location’. All of the above declarations are available at the main program level. So, according to logic, we can use them throughout our program (including sub-programs).

function getDept(p_deptno dept.deptno%type) return t_deptrec is

r_dept t_deptrec;

begin

select dname,loc into r_dept

from dept where deptno = p_deptno;

return r_dept;

end;

The above FUNCTION receives only a department number as a parameter and retrieves the department name along with the location. The values retrieved get gathered into the ‘r_dept’ variable of type ‘t_deptrec’ (which is local to ‘getDept’ only). And finally we return ‘r_dept’ (which is a RECORD type of variable) back to the main program.

for r_emp in c_emp

loop

r_dept := getDept(r_emp.deptno);

dbms_output.put_line(r_emp.ename || ',' || r_dept.name || ',' || r_dept.location);

end loop;

The main program uses the above loop to display all employee details along with their department names and locations. In the above loop, you should note that we are assigning the value returned by ‘getDept’ into a variable named ‘r_dept’, which is declared in the main program. This has no relation to ‘r_dept’ in ‘getDept’.



 
 
>>> 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: