How to use a nested table in a cursor

问题:

CREATE PROCEDURE( p_cur  OUT a_cur)
IS
type rec is record( a varchar2(2), b number, c number);
type tab is table of rec;
tab1 tab:=tab();
begin

tab1.extend;
tab1(tab1.last).a:='as';
tab1(tab1.last).b:=2;
tab1(tab1.last).c:=3;
tab1.extend;
tab1(tab1.last).a:='jj';
tab1(tab1.last).b:=2;
tab1(tab1.last).c:=3;

--??---
end;

I have created a nested table here tab1 ,but my issue is that i want to use this nested table in a cursor and want to return whole records using this nested table ,limitation is that i dont want to use any temporary table . I am using RDBMS as ORACLE

回答1:

If you want to use a collection as if it were a table then you'll nedd to look a the TABLE() function:

There is an example here: http://www.dobosz.at/oracle/select-from-plsql-table/

And another good resource here: http://www.databasejournal.com/features/oracle/article.php/2222781/Returning-Rows-Through-a-Table-Function-in-Oracle.htm

You'll need to declare the collection type in the database before then populating it in your procedure and then selecting from it.

I answered a question using this method here: Can a table variable be used in a select statement where clause?

Take a look as it should help you with what you are trying to achieve.

Hope it helps...

EDIT: In response to your question this code should do what you want it to. I haven't tested it but it should be very close to what you need and you can debug it if needed.

-- Create the relevent Object
CREATE TYPE data_obj_type AS OBJECT (
   a VARCHAR2(2),
   b NUMBER,
   c NUMBER
);

Type Created

-- Create the collection to hold the objects
CREATE TYPE table_obj_type IS TABLE OF data_obj_type;

Type Created

CREATE OR REPLACE
PROCEDURE cursor_values(
   p_cur OUT sys_refcursor
)
IS
   -- Create a variable and initialise it
   tab1 table_obj_type := table_obj_type();
BEGIN
   -- Populate the tab1 collection
   tab1.extend;
   tab1(tab1.last) := data_obj_type('as', 2, 3);
   tab1.extend;
   tab1(tab1.last) := data_obj_type('jj', 2, 3);
   --
   -- Open ref_cursor for output
   OPEN p_cur FOR
      SELECT a,
             b,
             c
        FROM TABLE(CAST(tab1 AS table_obj_type));
END cursor_values; 

N.B.: This is code amended from this page: http://www.akadia.com/services/ora_return_result_set.html