如何在游标中使用嵌套表 [英] 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;

我在这里创建了一个嵌套表tab1，但我的问题是，我想使用这个嵌套表在游标中并且想要使用这个嵌套表返回整个记录，限制是我不想使用任何临时表。
我使用RDBMS作为ORACLE

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

推荐答案

如果你想使用集合作为一个表， 'nedd查看 TABLE（）函数：

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

这里有一个例子： http://www.dobosz.at/oracle/select-from-plsql-table/ a>

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

这里还有一个很好的资源： http://www.databasejournal.com/features/oracle/article.php/2222781/Returning-Rows-Through-a- Table-Function-in-Oracle.htm

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.

希望它有助于...

编辑：为了回答你的问题，这段代码应该做你想要的。我没有测试它，但它应该非常接近你需要的，你可以调试它，如果需要。

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
);

类型已创建

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

类型已创建

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; 

注意：这是从此页面修改的代码：
http://www.akadia.com/services/ora_return_result_set.html

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

这篇关于如何在游标中使用嵌套表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！