我猜你知道你可以绑定一个变量PI_N_Dept
删除那段动态sql。不幸的是你的IN
条款和sExcludeCategories
你不能在 Oracle 中为列表绑定变量(据我所知至少最多 9.2)
你确实有几个选择。您当前的解决方案是最简单的。另一个解决方案是更改过程以接受多个变量并创建 AND 语句列表。
'select c.category
from test_category c
where c.deptid= :PI_N_Dept
and c.category <> :sExcludeCategory1
and c.category <> :sExcludeCategory2
and c.category <> :sExcludeCategory3
';
或者有一个固定的 IN 值列表
'select c.category
from test_category c
where c.deptid= :PI_N_Dept
and c.category not in (:sExcludeCategory1 , :sExcludeCategory2, :sExcludeCategory3)';
如果您只需要 2 个类别,则必须小心。第三个必须设置为 c.category 之外的某个值(注意:请小心并在此处测试空值)
另一种解决方案提出于Ask Tom http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061。这看起来很简单,虽然我还没有测试过。它的工作原理是创建一个函数 str2tbl() ,该函数允许您传递一系列以逗号分隔的数字,并通过 Dual 创建一个“表”来执行 IN 操作。
create or replace type myTableType as table of number;
create or replace function str2tbl( p_str in varchar2 ) return myTableType
as
l_str long default p_str || ',';
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
你的例子看起来像
'select c.category
from test_category c
where c.deptid= :PI_N_Dept
and c.category not in ( select * from INLIST ( select cast( str2tbl( :sExcludeCategories ) as mytableType ) from dual ) )';
这只有在以下情况下才有效sExcludeCategories
是一个数字列表。如果引号包含在变量中(并且您无法更改它),则必须更改 str2tbl 来处理引号,并更改myTableType
to varchar2(10)
或者更合适的东西。
总的来说,如果原始 SQL 不影响性能,那么为了简单起见,我会将其保留为动态 SQL。维护起来就不那么头疼了。否则测试 str2tbl。它应该在 Oracle 8 及更高版本中工作。
PS:为了完整起见,我遇到了这篇关于绑定变量的好文章 http://www.akadia.com/services/ora_bind_variables.html这涵盖了一些简单的问题,例如 IN 子句不使用变量。