Как использовать оператор SQL IN (или ANY) с VARRAY в PL/SQL

Мой код .NET в настоящее время использует ODP.NET для многократного вызова хранимой процедуры для работы с различными строками во многих таблицах. В коде .NET есть массив строк, которые необходимо изменить. В каждом вызове изменяется только один параметр, и я хотел бы передать массив из .NET в PL/SQL для работы с несколькими строками (количество строк изменится).

Я успешно передал массив из .NET в PL/SQL, используя:

type number_arr is table of number(10) index by pls_integer;
PROCEDURE "BLAH" (foo IN number_arr);

Обратите внимание, что я считаю, что number_arr называется VARRAY, но я не уверен в этом, и если кто-то хочет меня исправить, сделайте это (в качестве комментария), но это может внести свой вклад в мое замешательство.

Но теперь в PL/SQL у меня есть много операторов обновления, которые раньше выглядели так:

UPDATE t SET a = b WHERE a = foo;

когда foo не был массивом. Я сейчас хочу написать:

UPDATE t SET a = b WHERE a IN (foo);

Но этот синтаксис, похоже, не работает. И мне не удалось найти пример для Oracle, который сочетает в себе использование VARRAY и «IN» (или «ANY» и т. д.). И я видел несколько ответов о том, как это сделать с SQL Server, но я не уверен, как перевести это на Oracle.

Конечно, если есть какой-то другой способ передать массив из .NET хранимой процедуре для этого, это также ответит на мой вопрос. Я хочу повысить эффективность с помощью IN, поэтому что-то, что перебирает массив в PL/SQL (для вызова операторов UPDATE отдельно), вероятно, не поможет.


sql oracle plsql odp.net varray
person Andy Jacobs    schedule 15.07.2011    source источник

Ответы (1)


arrow_upward
8
arrow_downward

Используемый вами массив представляет собой ассоциативный массив, а не varray. varrays и вложенные таблицы можно использовать в SQL, но ассоциативные массивы нельзя. Однако, поскольку вы пытаетесь сделать это в первую очередь на PL/SQL, вы можете использовать массовую привязку (которая будет работать с ассоциативным массивом):

PROCEDURE BLAH (foo IN number_arr) is
i number;
begin
   forall i in foo.first .. foo.last
   UPDATE t SET a = b WHERE a = foo(i);
end blah;

Если вы создали number_arr как varray в базе данных вместо ассоциативного массива, вместо этого вы можете использовать табличную функцию:

create type number_arr as varray(10) of number;

CREATE PROCEDURE BLAH (foo IN number_arr) is
begin
   UPDATE t SET a = b WHERE a in (select * from table(foo));
end blah;

Обратите внимание, что в этом случае тип должен быть определен в базе данных, а не в вашем пакете. Кроме того, этот метод не обязательно будет быстрее, чем использование forall.

person Allan    schedule 15.07.2011
comment
Я считаю, что в вашем последнем фрагменте кода table(foo) должно быть table(cast(foo as number_arr)). Но в целом хороший ответ. - person Dave Costa; 16.07.2011
comment
ты прав! Я думаю, что в какой-то момент в прошлом требовался CAST, но тестирование в 10.2.0.4 показывает, что сейчас это не так. - person Dave Costa; 18.07.2011
comment
Спасибо за ваш ответ. Знание того, что forall будет не менее быстрым, помогает, поэтому я не буду возражать против написания цикла для многих моих случаев. Тем не менее, я упростил свой вопрос из моего фактического кода, и все еще есть места, где я хотел бы использовать ассоциативный массив с оператором IN или ANY. Это возможно? - person Andy Jacobs; 25.07.2011
comment
@Andy: Нет, вы не можете использовать ассоциативный массив с in или any. Ассоциативные массивы предназначены только для PL/SQL, поэтому их нельзя использовать в SQL (даже если они выполняются в PL/SQL). - person Allan; 26.07.2011