Wednesday, July 25, 2007

Create a function that returns a table

In SQL Server it is easy to create a function that returns a table when used in the from clause. In Oracle this is not as easy as it seems but since my current client uses Oracle, I was forced to find a solution so here it is.

To create a function that returns a table in Oracle you need to follow a few steps:

  • Create a type that corresponds to a single record
  • Create a type that corresponds to a table and that has the previously declared type as recordtype
  • Create the function
To use the function, you need to include it in the TABLE() function.

Here is an example on how to do it:

  • First I create a simple table that will be used for the sample.
    -- Create a dummy table to show how it works.
    --
    The table contains a hierarchy between boss
    --
    and employee.
    CREATE TABLE employee
    (
    emp_id
    NUMBER(10),
    full_name
    VARCHAR2(50),
    emp_boss_id
    NUMBER(10)
    );

    -- Insert dummy data:
    INSERT INTO employee VALUES (1, 'Geert Verhoeven', null);
    INSERT INTO employee VALUES (2, 'Ford Parker', null);
    INSERT INTO employee VALUES (3, 'Al Bino', 1);
    INSERT INTO employee VALUES (4, 'April May', 1);
    INSERT INTO employee VALUES (5, 'Abbie Birthday', 3);
    INSERT INTO employee VALUES (6, 'Gene E. Yuss', 3);
    INSERT INTO employee VALUES (7, 'Juana Bea', 3);
    INSERT INTO employee VALUES (8, 'Willie Maykit', 4);
    INSERT INTO employee VALUES (9, 'Zoltan Pepper', 4);
    INSERT INTO employee VALUES (10, 'Scott Shawn DeRocks', 4);
    COMMIT;

  • Then I create the types needed to be able to return a table
    -- Create a type that corresponds to a row that you want to have returned.
    CREATE TYPE ind_employee AS OBJECT (emp_id NUMBER(10), full_name VARCHAR2(50));
    --
    Create a type that corresponds to a table of rows with the specified type.

    CREATE TYPE ind_employee_table AS TABLE OF ind_employee;
  • After this is done, you can create a function that returns a table.
    -- Create the function.
    CREATE OR REPLACE FUNCTION FN_GET_EMPLOYEE_DESCENDANTS (
    pin_emp_id
    IN NUMBER
    )
    RETURN ind_employee_table PIPELINED
    IS

    TYPE t_ref_cursor
    IS REF CURSOR;
    lc_employees t_ref_cursor;
    lr_out_rec ind_employee :
    = ind_employee(NULL, NULL);

    BEGIN
    OPEN lc_employees FOR
    -- The connect by prior handles the hierarchy.
    SELECT
    emp_id,
    full_name
    FROM employee
    START
    WITH emp_id = pin_emp_id
    CONNECT
    BY PRIOR emp_id = emp_boss_id;

    LOOP
    FETCH lc_employees
    INTO
    lr_out_rec.emp_id,
    lr_out_rec.full_name;

    EXIT WHEN lc_employees%NOTFOUND;
    PIPE ROW(lr_out_rec);
    END LOOP;
    CLOSE lc_employees;
    RETURN;
    END;

    REMARKS: The PIPELINED command and PIPE ROW() method, work together to make sure that each row is returned to the caller immediately after it is processed. This helps to avoid having big objects in memory.

  • To use the function, you need to use the TABLE() function.
    -- Sample query:
    SELECT * FROM TABLE(FN_GET_EMPLOYEE_DESCENDANTS(1));

  • This gives the following results:
    EMP_ID FULL_NAME

    1 Geert Verhoeven
    3 Al Bino
    5 Abbie Birthday
    6 Gene E. Yuss
    7 Juana Bea
    4 April May
    8 Willie Maykit
    9 Zoltan Pepper
    10 Scott Shawn DeRocks

3 comments:

Anonymous said...

http://www.psoug.org/reference/pipelined.html

Anonymous said...

Good job Geert! When do you join the Oracle team? ;-) Thierry D

Anonymous said...

Thank you Greet! this article helps me much.

Dedy Handriyadi
~Indonesia