Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page