传递参数为in('','','') 不定数据时

Thomas -- Thanks for the question regarding "How can I do a variable "in list"", version 8.1.5
Submitted on 2-May-2000 15:57 Central time zoneTom's latest followup | Bookmark | BottomLast updated 15-Jul-2009 16:57
You Asked
I have a simple stored procedure, that I would like to have a passed in string(varchar2)
for used in select from where col1 in (var1) in a stored procedure.  I've tried
everything but doesn't work.  Followed is my proc.

Thanks

CREATE OR REPLACE PROCEDURE WSREVSECT_5

pSectNos varchar2,
  pRetCode OUT varchar2
)
AS
nCount number;

BEGIN

SELECT count(fksrev) into nCount
FROM SREVSECT
WHERE sectno IN  (pSectNos )  /* as in 'abc', 'xyz', '012' */
;
pRetCode:=to_char(ncount);

End;




and we said...
it works -- the above is the same as

where sectno = pSectNos

though, not what you want.  You want it to be:

where sectno in ( 'abc', 'xyz', '012' )

NOT:

where sectno in ( '''abc'', ''xyz'', ''012''' )

which is effectively is (else you could never search on a string with commas and quotes
and so on -- it is doing the only logical thing right now).

You can do this:

SQL> create or replace type myTableType as table
     of varchar2 (255);
  2  /

Type created.

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

Function created.

ops$tkyte@dev8i> select *
  2    from THE
        ( select cast( in_list('abc, xyz, 012') as
                              mytableType ) from dual ) a
  3  /

COLUMN_VALUE
------------------------
abc
xyz
012

ops$tkyte@dev8i> select * from all_users where username in
  2  ( select *
  3    from THE ( select cast( in_list('OPS$TKYTE, SYS, SYSTEM')
                         as mytableType ) from dual ) )
  4  /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
OPS$TKYTE                           23761 02-MAY-00
SYS                                     0 20-APR-99
SYSTEM                                  5 20-APR-99