▲一个简单的存储过程,传递字符串变量作为参...... 一个简单的存储过程,传递字符串变量作为参数,传递给过程中的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. 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 使用array如上绑定变量in-list的时候,要注意sql语句的执行计划;因为在有的版本下,cbo无法估计in-list virtual table的行数,产生不正确的执行计划。 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 03:24 , Processed in 0.123616 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.