Returning a record set from a Function in Oracle, SQL Server and DB2

In one of our previous posts, we had covered how to return record sets from a stored procedure in the case of Oracle, SQL Server and DB2 LUW. One of the readers recently asked how to do the same with a User Defined Function. We will demonstrate that in this blog post.

In Oracle, function which returns record set, requires an object type with attributes to be created first. Once object Type is created, we have to create named table type of the object type. In function we can use named table type as return type. We will show you in code snippet, how to do it but first let us create a table and populate it with some data. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE Test
(
Test_ID      NUMBER(9) NOT NULL,
Test_Desc    VARCHAR(30),
Test_DATE    DATE,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)
— TABLESPACE Clause
/

INSERT INTO Test(Test_Id, Test_Desc, Test_Date)
SELECT rownum, table_name, sysdate
FROM user_tables
WHERE rownum <= 5
/

Here is the data in the table.

SQL> select * from test;

TEST_ID TEST_DESC                      TEST_DATE
———- —————————— ———
1 DEPT                           20-FEB-07
2 EMP                            20-FEB-07
3 BONUS                          20-FEB-07
4 SALGRADE                       20-FEB-07
5 TEST                           20-FEB-07

Now let us assume, we want to return test_id and test_desc to calling application using function. First of all we will create an object type. Make sure that proper privileges are assigned to the user.

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID   NUMBER(9),
TEST_DESC VARCHAR(30)
)
/

Once object type is created, we will create named table type of above object type.

CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/

Now let us write function code to return the result set.

CREATE OR REPLACE FUNCTION FN_GET_ROWS
RETURN TEST_TABTYPE
AS
V_Test_Tabtype Test_TabType;
BEGIN
SELECT TEST_OBJ_TYPE(A.Test_Id, A.Test_Desc)
BULK COLLECT INTO V_Test_TabType
FROM
(SELECT Test_Id, Test_Desc
FROM Test
) A;
RETURN V_Test_TabType;

EXCEPTION
WHEN OTHERS THEN
v_Test_TabType.DELETE;
RETURN v_Test_TabType;
END;
/

Create above mentioned function and then run following SQL to execute it. Below is the SQL followed by result.

SQL> SELECT * FROM TABLE(FN_GET_ROWS);

TEST_ID TEST_DESC
———- ——————————
1 DEPT
2 EMP
3 BONUS
4 SALGRADE
5 TEST

Here we have shown one example of returning record set from function. There are other ways of doing this in function also by initializing named table type and then allocating space using .extend as required.

SQLServer supports scalar functions, inline table-valued functions and multi statement table-valued functions. Here we will see how we can use multi statement table-valued function to return record set. First let us create table and populate it with sample data.

CREATE TABLE Test
(
Test_ID      INT IDENTITY(1,1) NOT NULL,
Test_Desc    VARCHAR(30),
Test_DATE    DATETIME,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)
— FILEGROUP Clause
GO

INSERT INTO Test(Test_Desc, Test_Date)
SELECT TOP 5 table_name, getDate()
FROM information_Schema.tables
GO

Following is the data in the table. You may have different set of data.

Test_Id              Test_Desc     Test_Date
——-              ———     ———-
1                    syssegments   2007-02-21 23:19:11.717
2                    sysconstraints2007-02-21 23:19:11.717
3                    Invoice       2007-02-21 23:19:11.717
4                    TEMP_Invoice  2007-02-21 23:19:11.717
5                    Test          2007-02-21 23:19:11.717

Let us create function. You can run following code snippet from query analyzer.

CREATE FUNCTION DBO.FN_GET_ROWS()
RETURNS @TABLE TABLE
(
TEST_ID INT,
TEST_DESC VARCHAR(30)
)
AS
BEGIN
INSERT @TABLE
SELECT TEST_ID, TEST_DESC
FROM DBO.TEST
RETURN
END
GO

Once function is created successfully. Execute it using following sql. It is followed by the data returned by the function.

SELECT * FROM DBO.FN_GET_ROWS()
GO

Test_Id              Test_Desc
——-              ———
1             syssegments
2             sysconstraints
3             Invoice
4             TEMP_Invoice
5             Test

Table-valued functions can be used in join conditions also.

DB2 does not support multi statement table-valued function. It supports only inline table-valued function so we don’t have much flexibility in case of DB2. But we will see how we can get data using inline table-valued functions in DB2.

First we will create an empty table. Please make sure that statement terminator is defined as @.

CREATE TABLE Test
(
Test_ID      INT NOT NULL GENERATED BY DEFAULT AS
IDENTITY (START WITH +1, INCREMENT BY +1, CACHE 1000),
Test_Desc    VARCHAR(30),
Test_DATE    TIMESTAMP,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)@

Let us create some data now.

INSERT INTO Test(Test_Desc, Test_Date)
SELECT table_name, current timestamp
FROM syscat.tables
FETCH FIRST 5 ROWS ONLY
@

Following is the data in the table. You may have different set of data.

Test_Id       Test_Desc                   Test_Date
——-       ———                   ———-
1             TEST                        2007-02-22 21:53:13.237
2             ATTRIBUTES                  2007-02-22 21:53:13.237
3             BUFFERPOOLDBPARTITIONS      2007-02-22 21:53:13.237
4             BUFFERPOOLNODES             2007-02-22 21:53:13.237
5             BUFFERPOOLS                 2007-02-22 21:53:13.237

Let us create function. You can run following code snippet from command center or from command line editor.

CREATE FUNCTION FN_GET_ROWS()
RETURNS TABLE
(
TEST_ID INT,
TEST_DATE TIMESTAMP
)
LANGUAGE SQL
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
BEGIN ATOMIC
RETURN
SELECT TEST_ID, TEST_DATE
FROM TEST;
END@

Once function is created in the database, execute it using following SQL. Again SQL is followed by record set it returns.

— This is how we call table function in DB2. T is an alias.
SELECT * FROM TABLE(FN_GET_ROWS()) AS T@

Test_Id       Test_Desc
——-       ———
1             TEST
2             ATTRIBUTES
3             BUFFERPOOLDBPARTITIONS
4             BUFFERPOOLNODES
5             BUFFERPOOLS