Table as a return value

Oracle /PL-SQL: How to return a Table from a function


With collections and the table() function, a function can return a table that can be queried in an SQL statement

Create Record type

First,create a record type. In this example, the records consist of two attributes: i , a number and n , a varchar2.

The record type is created with create type:

create or replace type t_record as object (
  i number,
  n varchar2(30)

Create Table type

Based on the record type, we can now create a table type.

create or replace type t_table as table of t_record;

Create Function

With the table type, we're ready to create a function.

The function returns a t_table.

create or replace function return_table return t_table as
  v_ret   t_table;

 -- Call constructor to create the returned
 -- variable:
    v_ret  := t_table();

 -- Add one record after another to the returned table.
 -- Note: the "table" must be extended before adding
 -- another record:
    v_ret.extend; v_ret(v_ret.count) := t_record(1, 'one'  );
    v_ret.extend; v_ret(v_ret.count) := t_record(2, 'two'  );
    v_ret.extend; v_ret(v_ret.count) := t_record(3, 'three');

 -- Return the record:
    return v_ret;

end return_table;

Using the function

In order to use the function's returned value as a table in a SQL statement, we have to enclose the function within the table() statement. From the SQL's perspective, the table(...) construct behaves as though it were an actual table.

select * from table(return_table);

Oracle SQL: table(collection)

Within an SQL statement, the expression table(collection) is treated like a table:

select *

If you like and would like to contribute, you can also write your article here or mail your article to . See your article appearing on the main page and help others to learn.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0

C++ Pointers

  • 0

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment





Forgot Password

Please enter your email address below and we will send you information to change your password.