我怎样才能摆脱动态SQL

我的一个包体中有以下动态SQL

 OPEN ccur for
    'select c.category 
     from test_category c 
     where c.deptid='||PI_N_Dept ||
     ' and c.category not in ('|| sExcludeCategories ||')';

sExcludeCategories将包含一组用逗号分隔的整数.我想消除这种动态SQL语句.有没有聪明的方法来实现这一目标？

我猜你知道你可以绑定一个变量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提出了另一种解决方案.这看起来很简单,虽然我还没有测试过.它的工作原理是创建一个函数str2tbl(),它允许你传递一系列用逗号分隔的数字,并通过dual创建一个'table'来做一个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以处理引号,并更改myTableTypeto 的类型varchar2(10)或更合适的内容.

总的来说,如果原始的sql不影响性能那么为了简单起见我将它留作动态SQL.维持头脑的痛苦要小得多.否则测试出str2tbl.它应该在Oracle 8及更高版本中运行.

PS:为了完整起见,我遇到了这篇关于绑定变量的好文章,它涵盖了简单的问题,比如不使用IN子句的变量.