how to bind in list in Oracle7/8 (zt from asktom)

Mark -- Thanks for the question regarding "In list question with bind variables.", version
originally submitted on 2-May-2000 13:21 Eastern US time, last updated 12-Apr-2005 21:02Tom's latest followup | GOTO a Bookmarkable Page | Bottom
You Asked (Jump to Tom's latest followup)

Hi Tom,
I am using Pro*C and I need to send an array of strings
in a "where in :" is this possible?
And if not what should I do?

 

 
and we said...

In Oracle7, this is rather difficult.  You cannot bind in a SINGLE value and
expect it to be treated as MANY values (think about how hard it would be to find
something in an IN LIST that had a "comma" in it for example).

In Oracle8.0 and up, this is easy with object types and nested tables.  Just to
see what that will look like, I'll show that - then I'll show the v7
implementation.

ops$tkyte@8i> create or replace type myTableType
              as table of number;
  2  /
Type created.

ops$tkyte@8i> create or replace function in_list
                     ( p_string in varchar2 ) return myTableType
  2  as
  3      l_data             myTableType := myTableType();
  4      l_string           long default p_string || ',';
  5      l_n                number;
  6  begin
  7 
  8    loop
  9      exit when l_string is null;
 10      l_data.extend;
 11      l_n := instr( l_string, ',' );
 12      l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
 13      l_string := substr( l_string, l_n+1 );
 14    end loop;
 15    return l_data;
 16  end;
 17  /

Function created.


So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function
returns a SQL Table type as follows:

ops$tkyte@8i>
ops$tkyte@8i> select *
  2    from THE ( select cast( in_list('1,2,3,5,12')
                        as mytableType ) from dual ) a
  3  /

COLUMN_VALUE
------------
           1
           2
           3
           5
          12

Which means we can do that in a subquery:

ops$tkyte@8i> select *
  2    from all_users
  3   where user_id in
  4      ( select * from THE (
               select cast( in_list('1,2,3,5,12')
                      as mytableType ) from dual ) a )
  5  /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 20-APR-99




In v7, we can do something very similar (works in 8.0 and up as well) with plsql
tables.  Its a little more work but not too much.  It would look like this:


ops$tkyte@8i> create or replace package my_pkg
  2  as
  3          function in_list( p_string in varchar2,
                            p_idx in number ) return varchar2;
  4          pragma restrict_references( in_list,
                        wnds, rnds, wnps, rnps );
  5 
  6          function get_list_count(p_string in varchar2)
                      return number;
  7          pragma restrict_references( get_list_count,
                            wnds, rnds, wnps, rnps );
  8 
  9          pragma restrict_references( my_pkg,
                            wnds, rnds, wnps, rnps );
 10  end;
 11  /

Package created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body my_pkg
  2  as
  3  function in_list( p_string in varchar2,
                       p_idx in number ) return varchar2
  4  is
  5          l_start number;
  6          l_stop  number;
  7  begin
  8    if ( p_idx = 1 ) then
  9         l_start := 1;
 10    else
 11         l_start := instr( p_string, ',', 1, p_idx-1 )+1;
 12    end if;
 13 
 14    l_stop := instr( p_string ||',', ',', 1, p_idx );
 15 
 16    return ltrim( rtrim( substr( p_string, l_start,
                                       l_stop-l_start ) ) );
 17  end;
 18 
 19  function get_list_count( p_string in varchar2 )
             return number
 20  is
 21          l_cnt number default 0;
 22  begin
 23     for i in 1 .. 1000 loop
 24        exit when nvl( instr( p_string, ',', 1, i ), 0 ) = 0;
 25        l_cnt := i+1;
 26     end loop;
 27 
 28     return l_cnt;
 29  end;
 30 
 31  end;
 32  /

Package body created.



So, now I can code a query like:

ops$tkyte@8i> variable bindVar varchar2(255)
ops$tkyte@8i>
ops$tkyte@8i> exec :bindVar := '1, 3, 44, 1000'

PL/SQL procedure successfully completed.

ops$tkyte@8i>
ops$tkyte@8i> select my_pkg.in_list( :bindVar, rownum )
  2    from all_objects
  3   where rownum <=
         ( select my_pkg.get_list_count( :bindVar ) from dual )
  4  /

MY_PKG.IN_LIST(:BINDVAR,ROWNUM)
---------------------------------------
1
3
44
1000


All we need for that to work is a table with MORE rows then we have IN LIST
items -- all_objects is generally a good candidate.  It works by using rownum as
an "index" into the plsql table. 

So, now we can simply:


ops$tkyte@8i> select *
  2    from all_users
  3   where user_id in (select my_pkg.in_list(:bindVar,rownum)
  4                       from all_objects
  5                      where rownum <=
          ( select my_pkg.get_list_count( :bindVar ) from dual )
  6                                    )
  7  /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WEB$AZEILMAN                         1000 24-JUN-99

 [@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-795879/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-795879/