inlist oracle,sql中一个字符串的in-list判断问题

你同事的方法没用上绑定变量。

转帖：

http://tkyte.blogspot.com/2006/06/varying-in-lists.html

......

If you are in 9iR2 and above

Then we can skip the function all together and just use DUAL to generate rows and parse the string. Consider:

SQL> select level l

2    from dual

3  connect by level <= 5;

L

----------

1

2

3

4

5

So, we can use DUAL to generate rows and then using substr/instr – effectively parse the bind variable and return the i'th element from it. For example:

SQL> exec :txt := 'SYS, SYSTEM'

PL/SQL procedure successfully completed.

SQL> with data

2  as

3  (

4  select

5    trim( substr (txt,

6          instr (txt, ',', 1, level  ) + 1,

7          instr (txt, ',', 1, level+1)

8             - instr (txt, ',', 1, level) -1 ) )

9      as token

10    from (select ','||:txt||',' txt

11            from dual)

12  connect by level <=

13     length(:txt)-length(replace(:txt,',',''))+1

14  )

15  select * from data;

TOKEN

----------------------------------

SYS

SYSTEM

Once we have that accomplished – the rest is easy:

SQL> with data

2  as

3  (

4  select

5    trim( substr (txt,

6          instr (txt, ',', 1, level  ) + 1,

7          instr (txt, ',', 1, level+1)

8             - instr (txt, ',', 1, level) -1 ) )

9      as token

10    from (select ','||:txt||',' txt

11            from dual)

12  connect by level <=

13     length(:txt)-length(replace(:txt,',',''))+1

14  )

15  select *

16    from all_users

17   where username in (select * from data);

USERNAME      USER_ID CREATED

---------- ---------- ---------

SYSTEM              5 30-JUN-05

SYS                 0 30-JUN-05

Now, some people look at that “with data” bit and say “that is too much, too hard to code that every time”. We can use a VIEW to hide the complexity here – and use a stored procedure as our way to “bind to the view” (this is sort of a parameterized view in effect). It would look like this:

SQL> create or replace context my_ctx using my_ctx_procedure

2  /

Context created.

SQL> create or replace

2  procedure my_ctx_procedure

3  ( p_str in varchar2 )

4  as

5  begin

6          dbms_session.set_context

7          ( 'my_ctx', 'txt', p_str );

8  end;

9  /

Procedure created.

SQL> create or replace view IN_LIST

2  as

3  select

4    trim( substr (txt,

5          instr (txt, ',', 1, level  ) + 1,

6          instr (txt, ',', 1, level+1)

7             - instr (txt, ',', 1, level) -1 ) )

8      as token

9    from (select ','||sys_context('my_ctx','txt')||',' txt

10            from dual)

11  connect by level <=

12     length(sys_context('my_ctx','txt'))

13       -length(replace(sys_context('my_ctx','txt'),',',''))+1

14  /

View created.

SQL> exec my_ctx_procedure( :txt )

PL/SQL procedure successfully completed.

SQL> select *

2    from all_users

3   where username in

4   (select * from IN_LIST);

USERNAME      USER_ID CREATED

---------- ---------- ---------

SYSTEM              5 30-JUN-05

SYS                 0 30-JUN-05

Now, you “bind” your queries in-list by calling MY_CTX_PROCEDURE and the view does the rest.