从UDF或过程中选择多个参数 [英] Selecting multiple parameters from UDF or procedure

问题描述

我正在尝试计算多个值并在select子句中获取它们.它是通过UDF计算还是通过过程计算对我来说都无关紧要，但是我无法弄清楚如何以任何一种方式进行计算.我想这样使用它:

I'm trying to compute multiple values and fetch them in a select clause. Whether its computed via UDF or procedure does not matter to me but I can't figure out how to do it in either way. I want to use it like this:

SELECT ID, BITMAP(ID) FROM X;

然后应返回具有列ID, Bitset1, Bitset2的表.如果可能的话，它应该表现得尽可能好.

which then should return a table with columns ID, Bitset1, Bitset2. If possible it should be as performant as possible.

我目前有三个版本(简化后，实际计算量更多):

I have three versions currently (simplified, there's actual more computation):

表UDF ​​

CREATE FUNCTION TPCH.BITMAP(IN col BIGINT)
RETURNS table("BITSET1" bigint, "BITSET2" bigint)
AS BEGIN
    declare bitset1, bitset2 bigint;
    bitset1 = 1;
    bitset2 = 2;
    return select bitset1 as "BITSET1", bitset2 as "BITSET2" from sys.dummy;
END; 

标量UDF

CREATE FUNCTION BITMAP(IN col BIGINT)
RETURNS bitset1 bigint, bitset2 bigint
AS BEGIN
    declare bitset1, bitset2 bigint;
    bitset1 = 1;
    bitset2 = 2;
END; 

过程

create procedure BITMAP(in col BIGINT,
                        out bitsets table("BITSET1" bigint, "BITSET2" bigint))
as begin
    bitsets = select 1 as "BITSET1", 2 as "BITSET2" from sys.dummy;
end;

如果我从上面执行select语句，则会得到不同的异常:

If I execute the select statement from above I get different exceptions:

对于 TUDF /过程:cannot use procedure or table function in select projection column or cannot use window function w/o OVER: P_BITMAP

对于 SUDF :This user defined function has multiple outputs, but this usage requires a single output. Specify a single output.

我发现对于 SUDF ，我可以这样写:

I figured out that for SUDF I can write it like this:

select ID, BITMAP(ID).bitset1, BITMAP(ID).bitset2 from X;

但是这两次执行BITMAP(ID)，这使它变慢.我想一次性获取两个值.

But this executes BITMAP(ID) twice which makes it slow. I want to fetch both values in one go.

在哈纳还能做到吗?还是有其他选择?

Is this even possible to do in Hana or are there other options?

为清楚起见，udf和过程通常取决于输入.为了简单起见，我仅在示例中返回1和2.

EDIT 1: for clarification the udfs and procedure usually depend on the input. I just return 1 and 2 in the example for simplicity.

推荐答案

如所讨论的，在HANA 1中，存在与用户定义函数有关的以下限制:

As discussed, in HANA 1 there exist the following limitation concerning user defined functions:

• 表函数不接受联接表的值作为输入参数. IE.它们不能用于实现横向连接"
标量函数将针对语句中出现的每个实例被调用和执行.从等效调用中获取多个返回参数也将多次调用该函数.
• 表函数和过程不能在SELECT语句的投影列表中使用(非HANA专用)

• table functions don't accept values of joined tables as input parameters. I.e. they cannot be used to implement a "lateral join"
• scalar functions will be called and executed for every instance occurring in the statement. Fetching multiple return parameters from equivalent calls will also call the function multiple times.
• table functions and procedures cannot be used in the projection list of a SELECT statement (not HANA specific)

要将用户定义的函数并行应用于多个值，HANA 2提供了多种新功能，例如

To apply a user defined function to many values in parallel, HANA 2 provides multiple new features, e.g.

• MAP_MERGE函数，以实现map-reduce调用模式
• 确定性的用户定义函数，用于缓存结果，以便后续调用可以使用预先计算的结果

• MAP_MERGE function, to implement a map-reduce calling pattern
• deterministic user-defined functions, that cache results, so that sub-sequent calls can use the precomputed result

添加:可以在此处找到HANA 2中MAP_MERGE功能的一个很好的示例: https://blogs.sap.com/2016/12/01/sap-hana-2.0-sps- 0-new-developer-features-database-development/

add: a good example for the MAP_MERGE feature in HANA 2 can be found here: https://blogs.sap.com/2016/12/01/sap-hana-2.0-sps-0-new-developer-features-database-development/

这篇关于从UDF或过程中选择多个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！