问题描述
我在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中有可能吗?
推荐答案
下面是一个经过精心设计的流水线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(或至少将Bit-17设置为1,如0x10000所示,确认为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中存储过程的结果进行过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!