Returning a RECORD from FUNCTION - Oracle

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