V_RUNFREQUENCY := '''D'''||','||'''M'''||','||'''W'''||','||'''Q'''||','||'''Y'''; FOR SP_SN IN (
SELECT TA.*
FROM TA
, TB
WHERE TA.SPNAME = TB.PROCNAME
AND TA.FREQUENCY IN ( V_RUNFREQUENCY )
-- AND TA.FREQUENCY IN ( 'D','M','W','Q','Y' )
ORDER BY STEP
) LOOP 但在存储过程中 FOR里面的 V_RUNFREQUENCY 并不是 'D','M','W','Q','Y' 请问各位达人
应该是咋样才正确?
SELECT TA.*
FROM TA
, TB
WHERE TA.SPNAME = TB.PROCNAME
AND TA.FREQUENCY IN ( V_RUNFREQUENCY )
-- AND TA.FREQUENCY IN ( 'D','M','W','Q','Y' )
ORDER BY STEP
) LOOP 但在存储过程中 FOR里面的 V_RUNFREQUENCY 并不是 'D','M','W','Q','Y' 请问各位达人
应该是咋样才正确?
create procedure .......
cur sys_refcursor;
begin
open cur for 'SELECT TA.* FROM TA , TB WHERE TA.SPNAME = TB.PROCNAME
AND TA.FREQUENCY IN ('||V_RUNFREQUENCY||')'
loop
fetch cur into .....;
exit when cur%notfound;
end loop;
close cur;
..........
end;
DINGJUN123>declare
2 v_condition varchar2(100);
3 v_sql varchar2(1000);
4 v_count number(10);
5 begin
6 v_condition := '''TEST''' || ',' || '''EMP'''; --拼凑'TEST','EMP'
7 select count(*)
8 into v_count
9 from all_obj_test
10 where object_name in (v_condition);
11 --打印结果
12 dbms_output.put_line(v_count);
13 --打印SQL
14 v_sql := 'select count(*) from all_obj_test where object_name in(' ||
15 v_condition || ')';
16 dbms_output.put_line(v_sql);
17 end;
18 /
0
select count(*) from all_obj_test where object_name
in('TEST','EMP')PL/SQL 过程已成功完成。
我们看到,通过拼凑的SQL条件,我们打印出来的SQL是select count(*) from all_obj_test where object_name in('TEST','EMP'),但是为什么执行结果是0呢?我们将打印出的SQL单独执行一下看看:DINGJUN123>select count(*) from all_obj_test
2 where object_name in('TEST','EMP'); COUNT(*)
--------------------
3
select count(*) into v_count from all_obj_test where object_name in (v_condition);
中的v_condition是一个varchar2类型,在静态SQL中拼凑的条件相当于一个整体,'TEST','EMP'是一个字符串,在SQL中相当于'''TEST'',''EMP''',因此实际执行的SQL是:
select count(*) from object_name where name in('''TEST'',''EMP''');而不是
select count(*) from all_obj_test where object_name in('TEST','EMP');
因此没有找到数据。
1.可以用动态SQL,缺点明显,无法使用绑定变量,效率不好
2.10g可以使用正则表达式
3.非10g可以自定义函数,返回集合类型变量,传入的有分隔符的字符串。然后使用table函数转column_value查询到list列表
OK?你的缺点很明显,你穿的根本不是in list,而是一个字符串,不是多个字符串