用Oracle表函数解决绑定变量集合问题

声明：该方法是受到工作中同事案例启发，在此感谢。

 

绑定变量是我们经常使用的PL/SQL编码技术。通过在SQL语句中加入绑定变量，可以提高SQL语句共享程度，降低硬解析。

 

绑定变量是一种占位符技术，通过指定位置的替换，实现语句优化。如下示例：

 

vc_sql := 'select count(*) from emp where empno=:1';

 

execute immediate vc_sql into n_count  using 7844;

 

 

但是，如果我们在语句中使用in等集合操作符，括号中使用绑定变量是有些困难的。

 

vc_sql := 'select count(*) from emp where ename in (:1)';

 

//虽然不会报错，但是不会查询出正确的结果；

execute immediate vc_sql into n_count using 'Tom, FORD';

 

 

解决：可以借助表函数table的特性，将字符串转化为内存中的虚拟表。将in集合转化为子查询。

 

Table函数的作用是在PL/SQL中，将一个REF CURSOR或者集合转化为数据集。使用嵌套表table的时候，存在具体化的问题。在9i之后，引入了管道函数pipelined，简化了数据返回操作和效率。

 

首先，需要构建对象和对象集合。

 

//构建每个单元中的对象类型，与查询in的列类型一致；

//此外，此处要建立object对象，不要建立record。

create or replace type t_rec_test as object(id number);

 

//对象集合

create or replace type t_rec_table as table of t_rec_test;

 

其次，定义函数，实现字符串到对象列表的转换。

 

//实现字符串到对象列表的转换，使用i_vc_dil作为切分

create or replace function F_SPILE(i_vc_str varchar2, i_vc_dil varchar2)

return t_rec_table pipelined //指定返回列表类型外，使用pipelined关键字；

is

  dil varchar2(10);

  l_string varchar2(100);

  l_num number;

 begin

  dil := i_vc_dil;

  if (dil is null) then

      dil := ',';

  end if;

 

  l_string := i_vc_str;

 

  LOOP

    l_num := instr(l_string,dil);

EXIT WHEN(nvl(l_num,0) = 0);   

 

    //使用pipe row，将列表中的一个对象返回

    pipe row(t_rec_test(to_number(substr(l_string,1,l_num - 1))));   

    l_string := substr(l_string,l_num + length(dil));    

  END LOOP; 

 

  pipe row(t_rec_test(to_number(l_string)));

 

  return; //使用pipelined关键字声明的函数，最后返回空即可；

end F_SPILE;

 

说明：使用pipelined关键字之后，其中的对象列表t_rec_table无须专门定义一个实例。只要每次将分析创建好的对象pipe出就可以了。

 

定义函数F_SPILE之后，就可以实现将字符串转化为对象列表。

 

最后，调用方法，使用table函数调用分割函数，作为一个独立的数据集合进行组织。

 

declare 

  vc_sql varchar2(1000);

  n_count number;

begin

  //SQL语句调用书写，先函数处理，再用table函数包裹为数据表；

vc_sql := 'select count(*) from emp where empno in (select * from table(f_spile(:1, '','')))'; 

 

execute immediate vc_sql into n_count using '434,7782,7788,7900';                   

  dbms_output.put_line(to_char(n_count));

end;

/

//返回结果

SQL>

 

3

 

PL/SQL procedure successfully completed

 

 

那么，这样的处理方法效率如何？table函数相当于构建了一张内存数据表，相对于其他类型的数据表，访问和读写效率是相当高的。所以，及时使用了函数处理以及in操作，这种方法的效率也是相当高的。

 

 

另一个问题是pipelined关键字和pipe管道函数。从资料上看，引入这个特定的目的更多是从性能和写法上的优化，能够更快的将数据集返回。同样的例子，如果我们不使用pipelined，函数也是可以实现，如下：

 

//非pipeline版本

create or replace function F_SPILE(i_vc_str varchar2, i_vc_dil varchar2)

return t_rec_table //无pipelined关键字

is

  dil varchar2(10);

  l_string varchar2(100);

  l_num number;

  Result t_rec_table; //显示定义结果集

begin

  dil := i_vc_dil;

  if (dil is null) then

      dil := ',';

  end if;

 

  l_string := i_vc_str;

  Result := t_rec_table(); //初始化结果集

 

  LOOP

    l_num := instr(l_string,dil);

    EXIT WHEN(nvl(l_num,0) = 0); 

    //手工的拓展管理和索引脚标管理

Result.EXTEND;   

Result(Result.COUNT) := t_rec_test(to_number(substr(l_string,1,l_num - 1)));

 

    l_string := substr(l_string,l_num + length(dil));

    

  END LOOP;

 

  Result.EXTEND;   

  Result(Result.COUNT) := t_rec_test(l_string);

 

  return(Result);

 

end F_SPILE;

 

没有使用Pipelined，我们的功能同样可以实现。只是写法上要更加复杂一些，性能上在大数据量时稍差。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-682291/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-682291/