SQL 嵌入函数
SQLScript 允许将表函数嵌入到 SQL 查询中，而无需创建任何额外的元数据。HANA SQL 查询现在接受 SQL FUNCTION 块作为表，该表可以将必要的 SQLScript 逻辑嵌入到单个查询中。
语法
= FROM
= | ‘,’
= | | |
= | ….. |
= SQL FUNCTION BEGIN END
= (empty string) | ‘(‘ ‘)’ | ‘(‘ ‘)’
= ARG_ASSIGN_OP
= RETURNS | RETURNS TABLE ‘(‘ ‘)’ | RETURNS proc_param_name func_data_type
描述
可以创建一次性 SQLScript 函数，该函数可以将必要的 SQLScript 逻辑嵌入到 SQL 查询中。以前，需要创建 SQLScript 函数作为元数据对象并在单个查询中使用。与匿名过程块 DO BEGIN…END 类似， SQL FUNCTION RETURNS… BEGIN… END 块支持该类型的一次性表函数。
示例
用户的原始意图使用 SQLScript TUDF 查询SQL 嵌入式 SQLScript 表函数SELECT A, B, SUM(C)FROM (SELECT 1 as A, 2 as B, 3 as C FROM DUMMYUNION ALL SELECT 1 as A, 2 as B, 4 as C FROM DUMMYUNION ALL SELECT 2 as A, 3 as B, 2 as C FROM DUMMYUNION ALL SELECT 2 as A, 3 as B, 4 as C FROM DUMMYUNION ALL SELECT 2 as A, 5 as B, 7 as C FROM DUMMY)GROUP BY A, BORDER BY A, B;CREATE FUNCTION TEMP_FUNC()RETURNS TABLE (A INT, B INT, C INT)AS BEGIN DECLARE buffer TABLE (A INT, B INT, C INT); :buffer.insert((1, 2, 3)); :buffer.insert((1, 2, 4)); :buffer.insert((2, 3, 2)); :buffer.insert((2, 3, 4)); :buffer.insert((2, 5, 7)); RETURN :buffer;END;SELECT A, B, SUM(C)FROM TEMP_FUNC()GROUP BY A, BORDER BY A, B;SELECT A, B, SUM(C)FROM SQL FUNCTION RETURNS TABLE (A INT, B INT, C INT) BEGIN DECLARE buffer TABLE (A INT, B INT, C INT); :buffer.insert((1, 2, 3)); :buffer.insert((1, 2, 4)); :buffer.insert((2, 3, 2)); :buffer.insert((2, 3, 4)); :buffer.insert((2, 5, 7)); RETURN :buffer; ENDGROUP BY A, BORDER BY A, B; 示例代码select sum(a) fromsql functionreturns table (a int, b int)begin declare t table(a int, b int); :t.insert((1, 2)); :t.insert((1, 3)); :t.insert((2, 2)); :t.insert((3, 3)); return :t;end -- fails, because it is read-onlyselect a fromsql functionreturns table (a int)begin create column table temptable(a int); return select 1 as a from dummy;end 示例代码-- input parameterselect a from sql function (in a int => 1) returns table (a int) begin return select :a as a from dummy; end; -- nested SQL FUNCTION clauseselect a from sql function returns table (a int) begin return select * from sql function returns table (a int) begin return select 1 as a from dummy; end; end;
限制
如果 SQL FUNCTION 子句嵌套在另一个 SQLScript 对象中，则大多数 SQLScript 系统变量不可用（如果未将其定义为 INPUT 参数）。
- ROWCOUNT 不在调用者对象和 SQL
FUNCTION 之间共享，但它仍然可以显示从 SELECT 语句本身中选择的 ROWCOUNT。
- 不会继承 SQL_ERROR_CODE 和 SQL_ERROR_MESSAGE，尽管可以在 SQL
FUNCTION 中显式定义它们