Re: Using SQL IN predicate in PL/SQL

Hi Barry thanks for the info, very helpful! If you don't mind I have a question very relevent to this: Is there a way to use IN and a variable in a cursor declaration ? Like:

v_pool := ('ABC','DEF','GHI');

cursor mycur is
select

    col1
from mytable
where
coltest IN v_pool;

Re your remark about the IN clause may have a cost on performance...what's the better alternative then ? ... EXISTS ?

I am re-coding my proc to use IN against a 4-Mil-row table so to use the index on that column.

Thanks !
Robert

  Barry P. Grove wrote in message <3C7EE5F3.8DCB6854_at_istar.ca>...   Hi,
  You can use instr as suggested by Terje, but to make sure the string can be
  matched correctly in more general cases, then follow one of these examples:

1. If the v_Test and pool values are exactly one character:

      v_Pool := 'ABCDEF123456';
      if instr(v_Pool, v_Test) > 0 then
      ....;
      end if;


  2) If the v_Test and pool values are all the same fixed length and do not
      contain the delimiter char used in v_Pool:

      v_Pool := 'ABC,DEF,GHI';
      if instr(v_Pool, vTest) > 0 then


      ...;

      end if;


  3) If the v_Test and pool values are arbitrary length but do not contain
      the delimiter char used in v_Pool:

      v_Pool := ',ABC,AB,CDEF,GH,EFG,';
      if instr(v_Pool, ','||v_Test||',') > 0 then


      ...;

      end if;

      In this last case, you need delimiters at beginning and end of v_Pool
and
      also concatenated to beginning and end of v_Test.  This prevents false
      matches of test strings eg:
         BC
         EF
         A
         B
         CD
       etc.

  If this clause were used in SQL, then consider that
     v_Test in ('ABC','DEF','GHI')
  is actually transformed in the optimizer to
     v_Test = 'ABC'  or  v_Test = 'DEF'  or  v_Test = 'GHI'
  In a complex SQL query this may be transformed again to
     SELECT fields FROM tables WHERE v_test = 'ABC'
     UNION
     SELECT fields FROM tables WHERE v_test = 'DEF'
     UNION
     SELECT fields FROM tables WHERE v_test = 'GHI'

  In a simple PL/SQL expression as you have shown, this shouldn't   be a problem.

  Hope this helps. Received on Mon Mar 04 2002 - 18:52:13 CET