Как я могу использовать коллекцию в выражении Oracle SQL

Я хочу написать функцию 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. Есть ли способ достичь этого?

Ответы

Ответ 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;

Ответ 2

Вы можете сделать это следующим образом:

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

Ответ 3

Если вы используете 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;

Подробнее см. этот пост

Ответ 4

- INT_LIST объявляется глобально как "TYPE INT_LIST IS TABLE OF INTEGER"

Это похоже на объявление PL/SQL. Операторы SELECT используют механизм SQL. Это означает, что вам нужно объявить свой ТИП в SQL.

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.