Re: Is it possible to use a variable (table of numbers) with 'in' operator?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 19 Mar 2002 07:57:31 -0800
Message-ID: <a77n5b0fb@drn.newsguy.com>

In article <8a47e227.0203190524.eda74aa_at_posting.google.com>, kazelot_at_thenut.eti.pg.gda.pl says...
>
>I have a table MOZ_WYP:
> CREATE TABLE moz_wyp (
> fk1 NUMBER NOT NULL,
> fk2 NUMBER NOT NULL);
>
>Let's say it containst this data:
> FK1,FK2
> 1,0
> 1,3
> 2,1
> 2,2
> 2,3
> 3,0
> 3,1
> 3,2
>
>The task is to find all FK1 that have given set of FK2 values.
>I do this with the following SQL:
> -- we look for fk1's that are connected with both 0 and 3 FK2 values
> SELECT fk1
> FROM moz_wyp t
> WHERE fk2 IN (0, 3)
> GROUP BY fk1
> HAVING COUNT (*) >= 2
>FK1
>---
>1
>
>So far so good.
>
>Now I'd like to have a function that would take a table of numbers as input
>parameter, return result set (cursor reference).
>
>function get_fk1(at_fk2s in <table_of_number_type>, ac_cur out
><cursor_ref_type>)
>
>It should do something like this
>
> -- pseudo-code
> SELECT fk1
> FROM moz_wyp t
> WHERE fk2 IN (at_fk2s)
> GROUP BY fk1
> HAVING COUNT (*) >= at_fk2s.count
>
>Is it possible to use a variable (table of numbers) with 'in' operator?
>Or should I use dynamic SQL?
>
>TIA,
>kazelot

It'll look like this:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace type myTableType   2 as table of number
  3 /

Type created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace

  2 function in_list( p_string in varchar2 )   3 return myTableType
  4 as

  5      l_string        long default p_string || ',';
  6      l_data          myTableType := myTableType();
  7      n               number;
  8  begin
  9      loop
 10          exit when l_string is null;
 11          n := instr( l_string, ',' );
 12          l_data.extend;
 13                  l_data(l_data.count) := substr( l_string, 1, n-1 );
 14          l_string := substr( l_string, n+1 );
 15      end loop;
 16  
 17      return l_data;

 18 end;
 19 /

Function created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable str varchar2(4000);
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec :str := '1,3,5'

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select *   2 from TABLE( cast( in_list(:str) as myTableType ) )   3 /

COLUMN_VALUE

---

           1
           3
           5

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select *   2 from all_users
3 where user_id in ( select * from TABLE( cast( in_list(:str) as myTableType ) ) )
  4 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 28-AUG-01

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Mar 19 2002 - 09:57:31 CST