Using "IN" instead of "FOR" loop with a collection

Question

I have a plsql procedure which takes an array of data and updates bunch of records, I am able to do this with a for loop. I could really use some help figuring out to do this without a loop.

Package spec and body:

create or replace PACKAGE ARRAY_EXAMPLE AS
  type arrtype is table of test_table.name%TYPE index by pls_integer;

  PROCEDURE PROCESS_ARRAY( stringArrayIn IN arrtype
                         , p_city           varchar2
                         , p_phone          number);
END;
/

create or replace PACKAGE BODY ARRAY_EXAMPLE AS

  PROCEDURE PROCESS_ARRAY( stringArrayIn IN arrtype
                         , p_city           varchar2
                         , p_phone          number) IS   
  BEGIN
   FOR i IN 1..stringArrayIn.Count
   LOOP
      update test_table t
         set t.city = p_city 
       where t.name = stringArrayIn(i)
         and t.phone = p_phone;
   END LOOP;
  END;
END;
/

What I want to have:

create or replace PACKAGE BODY ARRAY_EXAMPLE AS

  PROCEDURE PROCESS_ARRAY( stringArrayIn IN arrtype
                           , p_city           varchar2
                           , p_phone          number) IS   
  BEGIN
     update test_table t
        set t.city = p_city
      where t.phone = p_phone
        and t.name in (stringArrayIn);
  END;
END;

I get error when I try the above, please help. thank you very much in advance.

MT0 · Accepted Answer · 2016-02-18T11:11:23.377

2

You will need to define the collection in SQL rather than PL/SQL:

create type arrtype is table of VARCHAR2(100);

create or replace PACKAGE BODY ARRAY_EXAMPLE AS
  PROCEDURE PROCESS_ARRAY(
    stringArrayIn IN arrtype
  , p_city        IN varchar2
  , p_phone       IN number
  )
  IS 
  BEGIN
    update test_table t
    set    t.city  = p_city
    where  t.phone = p_phone
    and    t.name  MEMBER OF stringArrayIn
  END;
END;
/

Update

Initialise an array on instantiation:

DECLARE
  t_names ARRTYPE := t_names( 'Alice', 'Bob', 'Charlie' );
BEGIN
  ARRAY_EXAMPLE.PROCESS_ARRAY(
    t_names,
    'New York City',
    '555-2368'
  );
END;
/

Populate an array later:

DECLARE
  t_names ARRTYPE;
BEGIN
  t_names := ARRTYPE();
  t_names.EXTEND(3);
  t_names(1) := 'Alice';
  t_names(2) := 'Bob';
  t_names(3) := 'Charlie';

  ARRAY_EXAMPLE.PROCESS_ARRAY(
    t_names,
    'New York City',
    '555-2368'
  );
END;
/

You can see from the second example that the array elements are still indexed.

edited Feb 18 '16 at 11:11

answered Feb 18 '16 at 10:39

MT0

143,790
11
59
117

thanks for your response, but i cant get the indexing if i declare it outside the package body, is there a way around it? – Radan Feb 18 '16 at 10:42
You appear to be using the associative array as a normal array (i.e. indexed from 1 to n rather than as a sparse array). Why do you need the index? – MT0 Feb 18 '16 at 11:04
I decided to go with this approach, used this http://www.oracle.com/technetwork/articles/fuecks-sps-095636.html – Radan Feb 18 '16 at 14:05

score 1 · Answer 2 · answered Feb 18 '16 at 10:26

1

if your type arrtype is created on database level you may use:

t.name in (select column_value from table(stringArrayIn))

answered Feb 18 '16 at 10:26

SkyWalker

494
2
7

thank you for your response but If I create it on database level, then I cant do "index by pls_integer" for some reason its required for the php application I am using. – Radan Feb 18 '16 at 10:34

score 1 · Answer 3 · answered Feb 18 '16 at 10:34

If you can define your type at SQL level, you can do something like this:

create type  SQL_arrtype is table of varchar2(50) /* for example, it is the type of your name field */

CREATE OR REPLACE PACKAGE ARRAY_EXAMPLE AS

    PROCEDURE PROCESS_ARRAY(
                            stringArrayIn    IN SQL_arrtype,
                            p_city              VARCHAR2,
                            p_phone             NUMBER
                           );
END;
CREATE OR REPLACE PACKAGE BODY ARRAY_EXAMPLE AS
    PROCEDURE PROCESS_ARRAY(
                            stringArrayIn    IN SQL_arrtype,
                            p_city              VARCHAR2,
                            p_phone             NUMBER
                           ) IS
    BEGIN
        forall i in 1 .. stringArrayIn.COUNT
        UPDATE test_table t
               SET t.city    = p_city
             WHERE     t.name = stringArrayIn(i)
                   AND t.phone = p_phone;
    END;
END;

FORALL will be much more efficient than single updates in a loop.

good point, i can keep this as my last resort. if i cant get this working with IN. Thank you. — Radan, Feb 18 '16 at 10:37

Using "IN" instead of "FOR" loop with a collection

3 Answers3