如何摆脱动态SQL [英] How can I get rid of dynamic SQL

问题描述

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

I have the following dynamic SQL in one of my package bodies

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

sExcludeCategories将包含一组用逗号分隔的整数.我想消除此动态SQL语句.有什么聪明的方法可以做到这一点?

sExcludeCategories will contain a set of integers separated by comma. I would like to eliminate this dynamic SQL statement. Are there any smart ways to accomplish this??

推荐答案

我猜您知道可以将变量绑定到PI_N_Dept来删除那条动态sql.不幸的是，对于您的IN子句和sExcludeCategories，您无法在Oracle中为列表绑定变量(据我所知至少为9.2)

I'm guessing you know you can bind a variable to PI_N_Dept to remove that piece of dynamic sql. Unfortunately for your IN clause and sExcludeCategories you can't bind a variable for a list in Oracle (at least up to 9.2 as far as i know)

您确实有几种选择.您当前的解决方案是最简单的.另一个解决方案是将过程更改为接受多个变量并创建AND语句列表.

You do have a few choices. Your current solution is the easiest. Another solution is to change the procedure to accept multiple variables and create a list of AND statements.

'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值列表

or have a fixed list of IN values

'select c.category 
     from test_category c 
     where c.deptid= :PI_N_Dept
       and c.category not in (:sExcludeCategory1 , :sExcludeCategory2, :sExcludeCategory3)';

在只需要2个类别的情况下，您必须要小心.第三个必须设置为c.category中不存在的某个值(注意:请注意，在此处测试空值)

You'll have to be careful in the case where you only want 2 categories. The third will have to be set to some value not in c.category (NB: be careful and test null values here)

问汤姆.这看起来很简单，尽管我还没有测试过.它可以通过创建一个函数str2tbl()来工作，该函数可让您传递一系列用逗号分隔的数字，并通过对偶创建一个表"以进行IN.

Another solution is presented in Ask Tom. This looks quite simple, though i haven't tested it. It works by creating a function str2tbl() which allows you to pass a series of numbers separated by commas and creating a 'table' via dual to do a 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的类型更改为varchar2(10)或更合适.

This would only work if sExcludeCategories was a list of numbers. You'd have to change str2tbl to handle quotes if they are included in the variable (and you cant change it) plus change the type of myTableType to varchar2(10) or something more appropriate.

总体而言，如果原始sql不影响性能，那么为简单起见，我将其保留为动态SQL.保持头痛几乎没有.否则，请测试str2tbl.它应该可以在Oracle 8及更高版本中使用.

Overall, if the original sql not effecting performance then for simplicity sake i'd leave it as dynamic SQL. It's much less of a head ache to maintain. Otherwise test out the str2tbl. It should work in Oracle 8 and above.

PS :出于完整性考虑，我遇到了这篇不错的文章关于绑定变量，涵盖了一些简单的问题，例如尽管没有在IN子句中使用变量.

PS: Just for completeness, i came across this nice article on binding vars that covers the simple problems like though not using variables for IN clauses.

这篇关于如何摆脱动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！