CODEWITHSUNDEEP
sqloracle-database
RNJ
RNJ

Reputation: 15552

Array within in clause

I have several sqls that I am running as part of a large db script. There are often conditions which are repeated over several of the sqls. For example

update phone_numbers set num = 'abc' where id not in (1,2,4,5,6,8);
update fax_numbers set num = 'abc' where id not in (1,2,4,5,6,8);
update email_add set val = 'abc' where id not in (1,2,4,5,6,8);

My problem is that I am constantly repeating the values in the not in clause.

how can i move these numbes into a variable and then apply to each sql. Something like

my_var = (1,2,4,5,6,8);
update phone_numbers set num = 'abc' where id not in @my_var;
update fax_numbers set num = 'abc' where id not in @my_var;
update email_add set val = 'abc' where id not in @my_var;

The SQL is running on Oracle if that helps? Ive done this before with one parameter but not with an array.

Ive seen this Oracle PL/SQL - How to create a simple array variable? but that doesnt apply to an in

Ive searched a few places but nothing seems to be quite what I want

Thanks

Upvotes: 2

Views: 274

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

If you're using SQL*Plus or SQL Developer, you could define a substitution variable:

define my_var = '1,2,4,5,6,8'
update phone_numbers set num = 'abc' where id not in (&my_var);
update fax_numbers set num = 'abc' where id not in (&my_var);
update email_add set val = 'abc' where id not in (&my_var);

Each statement will do a simple substitution of the value (as the name suggests); if you have set verify on then you'd see that happening:

old:update phone_numbers set num = 'abc' where id not in (&my_var)
new:update phone_numbers set num = 'abc' where id not in (1,2,4,5,6,8)

You could also wrap your statements in a PL/SQL block and use a built-in collection type to hold the values:

declare
  my_var sys.odcinumberlist;
begin
  my_var := sys.odcinumberlist(1,2,4,5,6,8);

  update phone_numbers set num = 'abc'
  where id not in (select column_value from table (my_var));
  update fax_numbers set num = 'abc'
  where id not in (select column_value from table (my_var));
  update email_add set val = 'abc'
  where id not in (select column_value from table (my_var));
end;
/

... which is longer and hides update counts unless you display SQL%ROWCOUNT yourself, but would work from any client.

Because this is using a varray type you can use collection methods. So if you currently had something like this in a PL/SQL block::

if x in (1,2,4,5,6,8) then

you could instead do;

if my_var.exists(x) the

Upvotes: 4

Related Questions