Return multiple value from a function in oracle plsql

Normally when we came across the difference between function and procedure, we say that function always returns value(e.g single) where as procedure may or may not. But we can use OUT parameter to return multiple value from a procedure. Similarly we can also return multiple value from a function by using TABLE type object.

TABLE type objects are defined from a user defined collection/object type. We can also say that collection type object can be made as TABLE type object in oracle plsql.

Here we are going to returns two column’s value from our function. So it is necessary to define our user type collection object having two data types.

STEP – 1 Create Object Type (e.g COLLECTION)/Defining Object Type:

An object type is a kind of data type. It can be used like our standard data type such as NUMBER,VARCHAR. You can also specify an object type as a column type of the database table.

Here we are considering the EMP table for our below example process.

Create or Replace type emp_type_o as Object

(

ename varchar2(15), //You may define other name rather than ename/job but the data type

job varchar2(12) should be same as the existing EMP table’s ename/job column.

)

Once it is executed, “emp_type_o” type is created as a collection type object having two parameter. Then we have to define a table type of the same object type Bcoz function can return multiple value with the help of table type object.

STEP – 2 Create Type as TABLE:

Create type emp_type_t is table of emp_type_o ;

It shows that “emp_type_t” is a table type object of emp_type_o object.

STEP – 3 Create Function to Return the Type as table:

Create or Replace function get_emp_list (id number) return emp_type_t

as

v_ename emp_type_t; // Define a variable of the same table object type

begin

select emp_type_o(ename,job) bulk collect

into v_ename from emp

where deptno= id;

return v_ename; // Returned the table type object variable.

end;

The above function takes “id” as a parameter. Bcoz it fetches the related employee’s ename and job based on that id(e.g deptno). The variable “v_ename” is a variable of that table type object and it is used in the sql query to keep all the returned o/p value sets. The object “emp_type_o” in the select query helps only to fetch the name and job column values from the emp table into the table type variable [e.g v_ename]. To returned a bulk/mass data from database table we normally use the BULK COLLECT concept. It is a technique that reduces the multiple Context Switches. So less Context Switch occurs in the plsql program, the execution is more faster.

What is Context Switch:

When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine, but passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary.

This transfer of control between the PL/SQL and SQL engines is called a context switch. Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades.

STEP – 4 Call Function which Returns Data Set:

select * from table ( get_emp_list (20));

This SQL query is used to fetch the exact data set. But to do this, it must use the oracle inbuilt table function. This function takes our above defined function [e.g get_emp_list] as a parameter. So from this example it’ll show all employee’s ename and job for department no 20.

 

Ref : https://www.linkedin.com/pulse/20140930092306-121069460-return-multiple-value-from-a-function-in-oracle-plsql