如何在 Oracle SQL 语句中使用集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7569918/
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
How can I use a collection within an Oracle SQL statement
提问by blerontin
I want to write an Oracle function that collects some data in multiple steps into a collection variable and use that collection data within a SELECT query like in this very simplified example:
我想编写一个 Oracle 函数，该函数将多个步骤中的一些数据收集到一个集合变量中，并在 SELECT 查询中使用该集合数据，就像在这个非常简单的示例中一样：
CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS
-- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
MyList INT_LIST := INT_LIST();
MyName VARCHAR2(512);
BEGIN
MyList.Extend(3);
MyList(0) := 1;
MyList(1) := 2;
MyList(2) := 3;
SELECT Name INTO MyName
FROM Item WHERE ItemId NOT IN MyList;
RETURN MyName;
END TESTFUNC01;
Unfortunately the part "NOT IN MyList" is no valid SQL. Is there a way to achieve this?
不幸的是，“NOT IN MyList”部分不是有效的 SQL。有没有办法实现这一目标？
回答by Allan
What you're looking for is the tablefunction:
您正在寻找的是table功能：
CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS
-- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
MyList INT_LIST := INT_LIST();
MyName VARCHAR2(512);
BEGIN
MyList.Extend(3);
MyList(1) := 1;
MyList(2) := 2;
MyList(3) := 3;
SELECT Name INTO MyName
FROM Item WHERE ItemId NOT IN (select * from table(MyList));
RETURN MyName;
END TESTFUNC01;
回答by Codo
You can do it like this:
你可以这样做：
CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS
-- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
MyList INT_LIST := INT_LIST();
MyName VARCHAR2(512);
BEGIN
MyList.Extend(3);
MyList(1) := 1;
MyList(2) := 2;
MyList(3) := 3;
SELECT Name INTO MyName
FROM Item WHERE ItemId NOT IN (SELECT COLUMN_VALUE FROM TABLE(MyList));
RETURN MyName;
END TESTFUNC01;
Note that I've also changed the list indices. The start with 1 (not 0).
请注意，我还更改了列表索引。以 1（不是 0）开头。
回答by pwoodiwiss
If your using oracle 10 you could use the collections extensions:
如果您使用 oracle 10，您可以使用集合扩展：
CREATE OR REPLACE FUNCTION TESTFUNC01 RETURN VARCHAR2 AS
-- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
MyList INT_LIST := INT_LIST();
MyName VARCHAR2(512);
BEGIN
MyList.Extend(3);
MyList(1) := 1;
MyList(2) := 2;
MyList(3) := 3;
SELECT Name INTO MyName
FROM Item WHERE ItemId MEMBER OF MyList;
RETURN MyName;
END TESTFUNC01;
for more details see this post
有关更多详细信息，请参阅此帖子
回答by APC
-- INT_LIST is declared globally as "TYPE INT_LIST IS TABLE OF INTEGER"
-- INT_LIST 全局声明为“TYPE INT_LIST IS TABLE OF INTEGER”
That looks like a PL/SQL declaration. SELECT statements use the SQL engine. This means you need to declare your TYPE in SQL.
这看起来像一个 PL/SQL 声明。SELECT 语句使用 SQL 引擎。这意味着您需要在 SQL 中声明您的 TYPE。
CREATE TYPE INT_LIST AS TABLE OF NUMBER(38,0);
/
Then you can use it in a SELECT statement:
然后你可以在 SELECT 语句中使用它：
SELECT Name INTO MyName
FROM Item WHERE ItemId NOT IN (select * from table(MyList));
Of course, you need to make sure that your query returns only one row, or that your program handles the TOO_MANY_ROWS exception.
当然，您需要确保您的查询只返回一行，或者您的程序处理 TOO_MANY_ROWS 异常。
