如何过滤 DB2 中存储过程的结果 [英] How to a filter on the result on a stored procedure in DB2

问题描述

我在 DB2 中有一个存储过程，它返回一堆列.我需要应用WHERE"条件或对其返回的列之一进行排序.我不想在调用存储过程时触摸存储过程并进行此过滤/排序，如下所示

I have a stored procedure in DB2 which returns a bunch of columns. I need to apply a 'WHERE' condition or do a sorting on one of the columns it returns. I don't want to touch the stored procedure and do this filtering/sorting when calling the stored procedure, something like below

select * from 'call SP1()' as T where T.column1 > 10

这在 DB2 中可行吗?

Is this possible in DB2?

推荐答案

这是一个故意人为设计的流水线 UDF 示例，用于过滤 SQLPL 过程的结果集.

Here is a deliberately artificial example of a pipelined UDF that filters the result-set of an SQLPL procedure.

在现实世界的编码中，大多数程序员会避免在存储过程之外进行过滤，这仅仅是因为它更容易、性能更好，而且尽早过滤更自然.

In real world coding, most programmers will avoid filtering outside of stored-procedures, simply because it is easier and better performing, and more natural to filter at the earliest possible opportunity.

在 DB2_COMPATIBILITY_MODE=ORA 的 Db2-LUW v11.1.3.3 和 11.1.2.2 上进行了测试(或在 0x10000 中至少将 Bit-17 设置为 1，在此向 P.Vernon 致谢):

Tested on Db2-LUW v11.1.3.3 and 11.1.2.2 with DB2_COMPATIBILITY_MODE=ORA (or at least Bit-17 set to 1 as in 0x10000 , acknowledgement to P.Vernon for this clarification):

--#SET TERMINATOR @

create or replace procedure alltabs
dynamic result sets 1
language sql
specific alltabs
begin
  declare v_cur cursor with return to caller for 
      select tabschema,tabname,type  from syscat.tables ;
  open v_cur;
end@


create or replace function allstatviews()
returns table (stat_view_name varchar(80))
begin
  declare v_rs result_set_locator varying;
  declare v_tabschema varchar(128);
  declare v_tabname   varchar(128);
  declare v_type      char(1);
  declare sqlstate char(5) default '00000';
  call alltabs;
  associate result set locator (v_rs) with procedure alltabs;
  allocate v_rscur cursor for result set v_rs;
  fetch from v_rscur into v_tabschema, v_tabname, v_type;
  while ( sqlstate = '00000') do
      if v_type='V' and v_tabschema='SYSSTAT'
      then
          pipe(cast(rtrim(v_tabschema)||'.'||rtrim( v_tabname) as varchar(80)));
      end if;
      fetch from v_rscur into v_tabschema, v_tabname, v_type;
  end while;
  return;
end@

select * from table(allstatviews())
@

这篇关于如何过滤 DB2 中存储过程的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！