从 Oracle 表变量/数组中选择值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1573877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting Values from Oracle Table Variable / Array?
提问by Sonny Boy
Following on from my last question (Table Variables in Oracle PL/SQL?)...
继我的最后一个问题（Oracle PL/SQL 中的表变量？）...
Once you have values in an array/table, how do you get them back out again? Preferably using a select statement or something of the like?
一旦数组/表中有值，您如何将它们再次取出？最好使用选择语句或类似的东西？
Here's what I've got so far:
这是我到目前为止所得到的：
declare
type array is table of number index by binary_integer;
pidms array;
begin
for i in (
select distinct sgbstdn_pidm
from sgbstdn
where sgbstdn_majr_code_1 = 'HS04'
and sgbstdn_program_1 = 'HSCOMPH'
)
loop
pidms(pidms.count+1) := i.sgbstdn_pidm;
end loop;
select *
from pidms; --ORACLE DOESN'T LIKE THIS BIT!!!
end;
I know I can output them using dbms_output.putline(), but I'm hoping to get a result set like I would from selecting from any other table.
我知道我可以使用 dbms_output.putline() 输出它们，但我希望得到一个结果集，就像我从任何其他表中选择一样。
Thanks in advance, Matt
提前致谢，马特
回答by PaulJ
You might need a GLOBAL TEMPORARY TABLE.
您可能需要一个全局临时表。
In Oracle these are created once and then when invoked the data is private to your session.
在 Oracle 中，这些创建一次，然后在调用时数据对您的会话是私有的。
Try something like this...
尝试这样的事情......
CREATE GLOBAL TEMPORARY TABLE temp_number
( number_column NUMBER( 10, 0 )
)
ON COMMIT DELETE ROWS;
BEGIN
INSERT INTO temp_number
( number_column )
( select distinct sgbstdn_pidm
from sgbstdn
where sgbstdn_majr_code_1 = 'HS04'
and sgbstdn_program_1 = 'HSCOMPH'
);
FOR pidms_rec IN ( SELECT number_column FROM temp_number )
LOOP
-- Do something here
NULL;
END LOOP;
END;
/
回答by Dave Costa
In Oracle, the PL/SQL and SQL engines maintain some separation. When you execute a SQL statement within PL/SQL, it is handed off to the SQL engine, which has no knowledge of PL/SQL-specific structures like INDEX BY tables.
在 Oracle 中，PL/SQL 和 SQL 引擎保持一定的分离。当您在 PL/SQL 中执行 SQL 语句时，它被移交给 SQL 引擎，该引擎不了解 PL/SQL 特定结构（如 INDEX BY 表）。
So, instead of declaring the type in the PL/SQL block, you need to create an equivalent collection type within the database schema:
因此，您不需要在 PL/SQL 块中声明类型，而是需要在数据库模式中创建一个等效的集合类型：
CREATE OR REPLACE TYPE array is table of number;
/
Then you can use it as in these two examples within PL/SQL:
然后您可以像在 PL/SQL 中的这两个示例中那样使用它：
SQL> l
1 declare
2 p array := array();
3 begin
4 for i in (select level from dual connect by level < 10) loop
5 p.extend;
6 p(p.count) := i.level;
7 end loop;
8 for x in (select column_value from table(cast(p as array))) loop
9 dbms_output.put_line(x.column_value);
10 end loop;
11* end;
SQL> /
1
2
3
4
5
6
7
8
9
PL/SQL procedure successfully completed.
SQL> l
1 declare
2 p array := array();
3 begin
4 select level bulk collect into p from dual connect by level < 10;
5 for x in (select column_value from table(cast(p as array))) loop
6 dbms_output.put_line(x.column_value);
7 end loop;
8* end;
SQL> /
1
2
3
4
5
6
7
8
9
PL/SQL procedure successfully completed.
Additional example based on comments
基于评论的附加示例
Based on your comment on my answer and on the question itself, I think this is how I would implement it. Use a package so the records can be fetched from the actual table once and stored in a private package global; and have a function that returns an open ref cursor.
根据您对我的回答和问题本身的评论，我认为这就是我的实施方式。使用一个包，这样记录就可以从实际表中提取一次并存储在一个私有包全局中；并有一个函数返回一个打开的引用游标。
CREATE OR REPLACE PACKAGE p_cache AS
FUNCTION get_p_cursor RETURN sys_refcursor;
END p_cache;
/
CREATE OR REPLACE PACKAGE BODY p_cache AS
cache_array array;
FUNCTION get_p_cursor RETURN sys_refcursor IS
pCursor sys_refcursor;
BEGIN
OPEN pCursor FOR SELECT * from TABLE(CAST(cache_array AS array));
RETURN pCursor;
END get_p_cursor;
-- Package initialization runs once in each session that references the package
BEGIN
SELECT level BULK COLLECT INTO cache_array FROM dual CONNECT BY LEVEL < 10;
END p_cache;
/
回答by Robert Giesecke
The sql array type is not neccessary. Not if the element type is a primitive one. (Varchar, number, date,...)
sql 数组类型不是必需的。如果元素类型是原始类型，则不会。（变量，数字，日期，...）
Very basic sample:
非常基本的示例：
declare
type TPidmList is table of sgbstdn.sgbstdn_pidm%type;
pidms TPidmList;
begin
select distinct sgbstdn_pidm
bulk collect into pidms
from sgbstdn
where sgbstdn_majr_code_1 = 'HS04'
and sgbstdn_program_1 = 'HSCOMPH';
-- do something with pidms
open :someCursor for
select value(t) pidm
from table(pidms) t;
end;
When you want to reuse it, then it might be interesting to know how that would look like. If you issue several commands than those could be grouped in a package. The private package variable trick from above has its downsides. When you add variables to a package, you give it state and now it doesn't act as a stateless bunch of functions but as some weird sort of singleton object instance instead.
当您想重用它时，了解它的外观可能会很有趣。如果您发出多个命令，那么这些命令可以分组在一个包中。上面的私有包变量技巧有其缺点。当你向一个包添加变量时，你给了它状态，现在它不是作为无状态的一堆函数而是作为某种奇怪的单例对象实例。
e.g. When you recompile the body, it will raise exceptions in sessions that already used it before. (because the variable values got invalided)
例如，当您重新编译主体时，它会在之前已经使用过它的会话中引发异常。（因为变量值无效）
However, you could declare the type in a package (or globally in sql), and use it as a paramter in methods that should use it.
但是，您可以在包中（或在 sql 中全局）声明类型，并将其用作应该使用它的方法中的参数。
create package Abc as
type TPidmList is table of sgbstdn.sgbstdn_pidm%type;
function CreateList(majorCode in Varchar,
program in Varchar) return TPidmList;
function Test1(list in TPidmList) return PLS_Integer;
-- "in" to make it immutable so that PL/SQL can pass a pointer instead of a copy
procedure Test2(list in TPidmList);
end;
create package body Abc as
function CreateList(majorCode in Varchar,
program in Varchar) return TPidmList is
result TPidmList;
begin
select distinct sgbstdn_pidm
bulk collect into result
from sgbstdn
where sgbstdn_majr_code_1 = majorCode
and sgbstdn_program_1 = program;
return result;
end;
function Test1(list in TPidmList) return PLS_Integer is
result PLS_Integer := 0;
begin
if list is null or list.Count = 0 then
return result;
end if;
for i in list.First .. list.Last loop
if ... then
result := result + list(i);
end if;
end loop;
end;
procedure Test2(list in TPidmList) as
begin
...
end;
return result;
end;
How to call it:
如何调用它：
declare
pidms constant Abc.TPidmList := Abc.CreateList('HS04', 'HSCOMPH');
xyz PLS_Integer;
begin
Abc.Test2(pidms);
xyz := Abc.Test1(pidms);
...
open :someCursor for
select value(t) as Pidm,
xyz as SomeValue
from table(pidms) t;
end;
