如何摆脱动态SQL

只需要一个理由

我的包主体之一包含以下动态SQL
" [- r6 g: h& S! O3 ~    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）
# m) ]$ x# d* |# Y, Q/ G* |' P你确实有几个选择。你目前的解决方案是最简单的。另一个解决方案是将过程改为接受多个变量并创建AND语句列表。
'select c.category      from test_category c      where c.deptid= I_N_Dept       and c.category  :sExcludeCategory        and c.category  :sExcludeCategory2        and c.category  :sExcludeCategory3';或者有固定的IN值列表
' p/ F0 G) R; _3 A'select c.category      from test_category c      where c.deptid= I_N_Dept       and c.category not in (:sExcludeCategory1 ,:sExcludeCategory2,:sExcludeCategory3)';你必须小心，只需要两个类别。第三个必须设置为不在c.category某一值(注:请注意，在这里测试null值）
# p. j4 H) r* BAsk
Tom它提供了另一个解决方案。虽然我还没有测试过，但这看起来很简单。它创建了一个函数str2tbl()来工作，这个函数允许你通过对偶创建一个表来传递一系列用逗号分隔的数字IN。
' {) w6 N. N# Kcreate 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-))))；                                                               l_str := substr( l_str,l_n        end loop;      return l_data;  end;您的示例看起来像
$ e" ^0 E. u+ k5 v$ y! u# b'select c.category      from test_category c      where c.deptid= I_N_Dept       and c.category not in ( select * from INLIST ( select cast( str2tbl( :sExcludeCategories  ) as mytableType ) from dual )；仅当sExcludeCategories这种方法只有在有数字列表的情况下才有效。如果引号包含在变量中(而且不能更改)，则必须更改str2tbl处理引号并更改myTableTypeto的类型varchar2(10)或更合适的名称。
# }' V$ h$ t" p5 A一般来说，如果是原始的sql不影响性能，所以为了简单起见，我把它保持为动态SQL。几乎没有头痛。否则，请测试str2tbl。它应该在Oracle
在更高的版本中工作。
PS
2 O5 b% R7 \( s8 x! ^: e：出于完整性，我遇到了一篇关于绑定的文章var这篇好文章涵盖了一些简单的问题，比如虽然不对IN变量用于子句。