如何在 Oracle SQL 语句中使用集合

Question

我想编写一个 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;

不幸的是，“NOT IN MyList”部分不是有效的 SQL。有办法实现这一点吗？

Answer 1

您正在寻找的是 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;

Answer 2

如果您使用 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;

有关更多详细信息，请参阅这篇文章< /a>

Answer 3

您可以这样做：

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;

请注意，我还更改了列表索引。从 1（不是 0）开始。

Answer 4

-- INT_LIST 全局声明为“TYPE INT_LIST IS TABLE OF
整数”

这看起来像一个 PL/SQL 声明。SELECT 语句使用 SQL 引擎。这意味着您需要在 SQL 中声明您的 TYPE。

CREATE TYPE INT_LIST AS TABLE OF NUMBER(38,0);
/

然后您可以在 SELECT 语句中使用它：

SELECT Name INTO MyName
FROM Item WHERE ItemId NOT IN (select * from table(MyList));

---

当然，您需要确保您的查询仅返回一行，或者您的程序处理 TOO_MANY_ROWS 异常。