how can we return records from pl/sql stored procedure without taking out parameter

Question

My Question is "How can we return multiple records from pl/sql stored procedure without taking OUT parameter".I got this doubt because if we are using cursors or refcursor in out parameter it may degrade performance.So what is the solution??

You could populate an array I suppose, but that is likely to perform worse than a cursor. You can have [an implicit cursor](https://stackoverflow.com/a/40360471/230471) to avoid declaring one, but it's still a cursor. — William Robertson, Apr 19 '18 at 16:20
That's not really *returning* anything, but - you could populate some table with that stored procedure, and then - somewhere else (in a report, for example) select data stored in that table. — Littlefoot, Apr 19 '18 at 17:53

score 1 · Answer 1 · answered Apr 20 '18 at 09:32

As OldProgrammer wrote, i think the performance of a cursor wouldn't be you problem. But here a Solution anyway:

You can return custom types like Table of number. If it's only a list of numbers you could return a table of numbers. If you Want to return rows from a table you could return table of 'tablename'%ROWTYPE. But i guess you want to create some custom types.

CREATE OR REPLACE TYPE PUWB_INT.MyOrderType AS OBJECT
(
    OrderId NUMBER,
    OrderName VARCHAR2 (255)
)
/

CREATE OR REPLACE TYPE PUWB_INT.MyOrderListType AS TABLE OF MYORDERtype
/

Now we can use them similar to a return myNumberVariable;

Let's build a function (procedures don't have return values):

CREATE OR REPLACE FUNCTION PUWB_INT.MyFunction (SomeInput VARCHAR2)
    RETURN MyOrderListType
IS
    myOrderList   MyOrderListType := MyOrderListType ();
BEGIN
    FOR o IN (SELECT 1 AS Id, 'One' AS Name FROM DUAL
              UNION ALL
              SELECT 2 AS Id, 'Two' AS Name FROM DUAL)
    LOOP
        myOrderList.EXTEND ();
        myOrderList (myOrderList.COUNT) := MyOrderType (o.Id, o.Name || '(' || SomeInput || ')');
    END LOOP;

    RETURN myOrderList;
END MyFunction;
/

Now we can call the function and get a table of our custom-type:

DECLARE
    myOrderList   MyOrderListType;
    myOrder       MyOrderType;
BEGIN
    myOrderList := MyFunction ('test');

    FOR o IN myOrderList.FIRST .. myOrderList.LAST
    LOOP
        myOrder := myOrderList (o);
        DBMS_OUTPUT.put_line ('Id: ' || myOrder.OrderId || ', Name: ' || myOrder.OrderName);
    END LOOP;
END;

Be aware, that the calling schema, has to know the type.

how can we return records from pl/sql stored procedure without taking out parameter

1 Answers1