SAP HANA ARRAY与CUSOR（学习备份）
内容来源于网络 https://blogs.sap.com/2014/06/21/sap-hana-array与cusor的性能比较/
在某些应用场景下，针对表的一些复杂操作无法用标准SQL的函数完成。这时我们可以使用游标获取表中的每一行数据，进而实现需要的操作。但是，游标所带来的性能损耗使得数据库开发人员对其避而远之。SAP HANA ARRAY为操作表的每一行数据提供了另一种方式。本文首先介绍SAP HANA ARRAY的使用方法，然后通过一个示例展示ARRAY和CURSOR的性能差异。
2 SAP HANA ARRAY使用方法简介
本节用两个简单示例介绍如何在SAP HANA SQL Script中使用数组。
2.1 示例1：从表中取数据到数组
CREATE COLUMN TABLE TSTTAB(
“ID” INTEGER,
“VALUE” DECIMAL
);
INSERT INTO SYSTEM.TSTTAB VALUES(1,10);
INSERT INTO SYSTEM.TSTTAB VALUES(2,20);
INSERT INTO SYSTEM.TSTTAB VALUES(3,30);
CREATE PROCEDURE TEST_ARRAY_1()
AS
ARRAY_VALUE DECIMAL ARRAY;
V_INDEX INTEGER;
SUM_VALUE DECIMAL:=0;
BEGIN
V_TSTTAB = SELECT “VALUE” FROM SYSTEM.TSTTAB;
ARRAY_VALUE := ARRAY_AGG(:V_TSTTAB.VALUE);
FOR V_INDEX IN 1 .. CARDINALITY(:ARRAY_VALUE) DO
SUM_VALUE :=:SUM_VALUE + :ARRAY_VALUE[:V_INDEX];
END FOR;
SELECT :SUM_VALUE AS “SUM” FROM DUMMY;
END;
CALL TEST_ARRAY_1;
结果如下：
2.2 示例2： 数组合并成表变量
CREATE PROCEDURE TEST_ARRAY_2()
AS
ARRAY_ID INTEGER ARRAY;
ARRAY_VALUE DECIMAL ARRAY;
V_INDEX INTEGER;
BEGIN
FOR V_INDEX IN 1 .. 5 DO
ARRAY_ID[:V_INDEX]:=:V_INDEX;
ARRAY_VALUE[:V_INDEX]:=:V_INDEX*10;
END FOR;
RS = UNNEST(:ARRAY_ID,:ARRAY_VALUE) AS (“ID”,“VALUE”); SELECT * FROM :RS;
END;
CALL TEST_ARRAY_2;
结果如下：
2.3 数组知识点总结
1. 数组变量的定义：<array_name> <type> ARRAY [:= <array_constructor>]
2. ARRAY_AGG函数从表变量取数据到数组，语法是: ARRAY_AGG”(“:<table_variable>.<column_name> [<order_by_clause>]”)”
注意：ARRAY_AGG的参数只支持表变量，不支持物理表。
3. CARDINALITY函数返回数组的长度。注意，CARDINALITY返回的是数组下标的最大值，不一定每个下标对于的元素都有值。
例如：ARRAY_ID[1]:=1;ARRAY_ID[100]:=2; 那么CARDINALITY返回的是100，而不是2。
4. UNNEST函数将一个或多个数组合并成一个表变量，语法是：
UNNEST(:<array_variable> [ {, array_variable} …] )
[WITH ORDINALITY] [AS <return_table_specification>)]
3 ARRAY VS CURSOR
本小节分别使用ARRAY和CURSOR实现一个简单的功能。表结构如下：
CREATE COLUMN TABLE TSTTAB_FOR_ARRAY(
“ID” INTEGER,
“VALUE” DECIMAL
);
要实现的功能就是将TSTTAB_FOR_ARRAY表每条记录的VALUE值都加上它前一条记录(由ID列决定)的VALUE值。
3.1 用CURSOR实现
CREATE PROCEDURE DO_WITH_CURSOR()
LANGUAGE SQLSCRIPT
AS
ID_ARRAY INTEGER ARRAY;
VALUE_ARRAY DECIMAL ARRAY;
CURSOR CUR_TSTTAB FOR SELECT * FROM SYSTEM.TSTTAB_FOR_ARRAY ORDER BY ID;
V_INDEX INTEGER := 1;
PRE_VALUE DECIMAL :=0;
BEGIN
FOR CUR AS CUR_TSTTAB DO
ID_ARRAY[:V_INDEX]:=CUR.ID;
VALUE_ARRAY[:V_INDEX]:=CUR.VALUE + :PRE_VALUE;
PRE_VALUE :=CUR.VALUE;
V_INDEX :=:V_INDEX+1;
END FOR;
RS = UNNEST(:ID_ARRAY,:VALUE_ARRAY) AS (“ID”,“VALUE”);
SELECT * FROM :RS;
END;
CALL DO_WITH_CURSOR;
3.2 用ARRAY实现
CREATE PROCEDURE DO_WITH_ARRAY()
LANGUAGE SQLSCRIPT
AS
ID_ARRAY INTEGER ARRAY;
VALUE_ARRAY DECIMAL ARRAY;
V_INDEX INTEGER := 1;
PRE_VALUE DECIMAL :=0;
TMP DECIMAL :=0;
BEGIN
TSTTAB = SELECT * FROM SYSTEM.TSTTAB_FOR_ARRAY ORDER
BY ID;
ID_ARRAY := ARRAY_AGG(:TSTTAB.ID);
VALUE_ARRAY:=ARRAY_AGG(:TSTTAB.VALUE);
FOR V_INDEX IN 1 .. CARDINALITY(:ID_ARRAY) DO
TMP:=:VALUE_ARRAY[:V_INDEX];
VALUE_ARRAY[:V_INDEX]:=:VALUE_ARRAY[:V_INDEX]
+ :PRE_VALUE;
PRE_VALUE:=:TMP;
END FOR;
RS = UNNEST(:ID_ARRAY,:VALUE_ARRAY) AS (“ID”,“VALUE”);
SELECT * FROM :RS;
END;
CALL DO_WITH_ARRAY;
3.3 对比
首先，对比两个procedure的运行结果。将上述两个procedure中RS的insert到两张不同的表中，然后用以下SQL语句验证：
SELECT SUM(ABS(A.VALUE-B.VALUE))
FROM SYSTEM.TSTTAB_FOR_ARRAY_RESULT A INNER JOIN SYSTEM. TSTTAB_FOR_ARRAY_RESULT_2 B ON A.ID=B.ID;
结果如下：
可见，结果一致。
然后，二者的性能比较，TSTTAB_FOR_ARRAY表含10,000,000行记录：
两个procedure耗时如下：
可见， ARRAY实现的性能较CURSOR提高了5倍多。
总结
本文简单介绍了如何使用SAP HANA数组，并比较了ARRAY和CURSOR的性能。
