5.19 Function which returns rowtype

---

5.19 Function which returns rowtype

Oracle allows result sets to be returned from functions if its return type is a REF Cursor, as illustrated in Example 5-40. You will likely convert such functions as procedures in DB2 as this minimizes the amount of application changes required (you would simply add the word CALL in front of the function call). The (less desirable) alternative is to convert this as a table function, but table functions in DB2 have limited functionality, and the application must convert the function call into a select statement.

Example 5-40: Oracle function with a REF cursor

 CREATE OR REPLACE PACKAGE ReturnRtype AS   TYPE t_RefCur IS REF CURSOR;   -- Selects from employees based on the supplied department,   -- and returns the opened cursor variable.   FUNCTION EmployeesQuery(p_Department IN VARCHAR2)     RETURN t_RefCur; END ReturnRtype; CREATE OR REPLACE PACKAGE BODY ReturnRtype AS   -- Selects from employees based on the supplied department,   -- and returns the opened cursor variable.   FUNCTION EmployeesQuery(p_Department IN VARCHAR2)     RETURN t_RefCur IS     v_ReturnCursor t_RefCur;     v_SQLStatement VARCHAR2(500);   BEGIN     v_SQLStatement := 'SELECT * FROM employees WHERE department = :m';     -- Open the cursor variable, and return it.     OPEN v_ReturnCursor FOR v_SQLStatement USING p_Department;     RETURN v_ReturnCursor;   END EmployeesQuery; END ReturnRtype; 

The converted DB2 code is shown in Example 5-41.

Example 5-41: Conversion to a procedure with a Result Set in DB2

 Create Procedure EmployeesQuery (IN p_Department varchar(30)) LANGUAGE SQL DYNAMIC RESULT SETS 1 BEGIN    DECLARE C1 cursor with return to client for    Select * from employees where department = p_Department;    OPEN C1; END! 

---