Chinaunix首页 | 论坛 | 博客
  • 博客访问: 586491
  • 博文数量: 772
  • 博客积分: 5000
  • 博客等级: 大校
  • 技术积分: 4980
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-17 13:02
文章分类

全部博文(772)

文章存档

2011年(1)

2008年(771)

我的朋友

分类:

2008-10-17 13:23:58

    一个简单的过程,传递字符串变量作为参数,传递给过程中的in 子句;但似乎不起作用 。 代码如下

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

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''' )

    当直接使用select count(*) into .. from .. where sectno in (pSectNos) 的时候,相当于where sectno = pSectNos

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.

> 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.

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

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

> 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

    使用array如上绑定变量in-list的时候,要注意sql语句的执行计划;因为在有的版本下,cbo无法估计in-list virtual table的行数,产生不正确的执行计划。

【责编:Amy】

--------------------next---------------------

阅读(591) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~