Geek Answers Handbook

How to use SQL 'IN' (or 'ANY') statement with VARRAY in PL / SQL

Currently, my .NET code uses ODP.NET to call a stored procedure multiple times to work with different rows in many tables. .NET code has an array of strings to modify. Only one parameter is changed in each call, and I would like to pass an array from .NET to PL / SQL to work with several rows (the number of rows will change).

I successfully passed an array from .NET to PL / SQL using:

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

Please note that I believe that number_arr is called VARRAY, but I'm not sure about it, and if someone wants to correct me, please do (as a comment), but this may affect my confusion.

But now, in PL / SQL, I have many update statements that look like this:

UPDATE t SET a = b WHERE a = foo;

when foo was not an array. Now I want to write:

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

But this syntax does not work. And I could not find an example for Oracle that combines the use of VARRAY and "IN" (or "ANY", etc.). And I saw some answers on how to do this with SQL Server, but I'm not sure how to translate this to Oracle.

Of course, if there is some other way to get an array from .NET for a stored procedure to do this, this will also answer my question. I want to improve performance with IN, so something that iterating through an array in PL / SQL (to invoke the UPDATE statements separately) probably won't help.

+5
source share
1 answer

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.

+8

More articles:


All Articles