CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
Rey Libutan
Rey Libutan

Reputation: 5314

What is the standard way to return records from an Oracle function?

I really avoided the "best" word for my question but it really is the most suitable word for it.

What's the best(most efficient) way of returning records from a function?

Currently I have something like:

  FUNCTION myFunct(param1 VARCHAR2) RETURN SYS_REFCURSOR AS
    myCursor SYS_REFCURSOR;
  BEGIN
    OPEN myCursor FOR
    SELECT *
    FROM myTable
    WHERE field = param1;

    RETURN(myCursor);
  END myFunct;

I can run this fine but with everything else I am reading like (TABLE type, implicit cursor, etc) I am really confused about what is most suitable.

P.S. how can I loop over this cursor after I call it from a proc?

EDIT: I've read that I can only iterate through cursors ONCE (forums.oracle.com/thread/888365) but in reality I want to loop contents several times. Does this mean that I am opt to use associative arrays instead?

Upvotes: 1

Views: 2839

Answers (1)

the_slk
the_slk

Reputation: 2182

create or replace 
PACKAGE example_pkg AS

    /*
    ** Record and nested table for "dual" table
    ** It is global, you can use it in other packages
    */
    TYPE g_dual_ntt IS TABLE OF SYS.DUAL%ROWTYPE;
    g_dual  g_dual_ntt;

    /*
    ** procedure is public. You may want to use it in different parts of your code
    */
    FUNCTION myFunct(param1 VARCHAR2) RETURN SYS_REFCURSOR;

    /*
    ** Example to work with a cursor
    */
    PROCEDURE example_prc;

END example_pkg;

create or replace 
PACKAGE BODY example_pkg AS

    FUNCTION myFunct(param1 VARCHAR2) RETURN SYS_REFCURSOR
    AS
        myCursor SYS_REFCURSOR;
    BEGIN
        OPEN myCursor FOR
            SELECT  dummy
            FROM    dual
            WHERE   dummy = param1;

        RETURN(myCursor);
    END myFunct;

    PROCEDURE example_prc
    AS
        myCursor SYS_REFCURSOR;
        l_dual   g_dual_ntt; /* With bulk collect there is no need to initialize the collection */
    BEGIN
        -- Open cursor
        myCursor := myFunct('X');
        -- Fetch from cursor  /  all at onece
        FETCH myCursor BULK COLLECT INTO l_dual;
        -- Close cursor
        CLOSE myCursor;

        DBMS_OUTPUT.PUT_LINE('Print: ');
        FOR indx IN 1..l_dual.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('element: ' || l_dual(indx).dummy );
        END LOOP;
    END example_prc;

END example_pkg;

EXECUTE example_pkg.example_prc();

/*
Print: 
element: X
*/

Please take a look at this link: http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

You might find it useful...

Upvotes: 1

Related Questions