Oracle PL/SQL for DBAs

The Function Header

I’ll start things off by explaining the header for the function I’ll write.

    1 CREATE FUNCTION repeat_order_finder ( p_curs cursors.repeat_orders_curs )
    2                 RETURN repeat_region_location_t
    3                 PIPELINED
    4                 PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(region_id) )
    5                 ORDER p_curs BY (location_id, order_date) IS

As far as function headers go, this one has a lot to say, so let’s look at it line by line.

Line 1

States the function name and its parameter—a strongly typed REF cursor declared in another package like this:

CREATE OR REPLACE PACKAGE cursors AS
  TYPE repeat_orders_rec IS RECORD (order_number NUMBER,
                                    order_date   DATE,
                                    region_id    NUMBER,
                                    type_id      NUMBER,
                                    location_id  NUMBER );

  TYPE repeat_orders_curs IS REF CURSOR RETURN repeat_orders_rec;
END;

When the function is executed, I’ll be passing it a SELECT statement getting all orders from the past 30 days.

Line 2

Explains the structure of the rows this function will return. It was created using the following SQL defining an object and a collection:

CREATE TYPE repeat_region_location_o AS OBJECT ( region_id      NUMBER,
                                                 location_id    NUMBER,
                                                 first_type_id  NUMBER,
                                                 repeat_type_id NUMBER );
/
CREATE TYPE repeat_region_location_t AS TABLE OF repeat_region_location_o;
/

Line 3

Explains that this function will pipe rows back upstream as soon as they are calculated.

Line 4

Defines the way that records from the passed-in REF cursor will be partitioned across the multiple parallel instances of this function. They are to be partitioned by values ...