1 引言

      在某些应用场景下,针对表的一些复杂操作无法用标准SQL的函数完成。这时我们可以使用游标获取表中的每一行数据,进而实现需要的操作。但是,游标所带来的性能损耗使得数据库开发人员对其避而远之。SAP HANA ARRAY为操作表的每一行数据提供了另一种方式。本文首先介绍SAP HANA ARRAY的使用方法,然后通过一个示例展示ARRAYCURSOR的性能差异。

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;

结果如下:

/wp-content/uploads/2014/06/1_481024.png

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;

结果如下:

/wp-content/uploads/2014/06/2_481034.png

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

本小节分别使用ARRAYCURSOR实现一个简单的功能。表结构如下:

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的运行结果。将上述两个procedureRSinsert到两张不同的表中,然后用以下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;

结果如下:

/wp-content/uploads/2014/06/3_481035.png

可见,结果一致。

然后,二者的性能比较,TSTTAB_FOR_ARRAY表含10,000,000行记录:

/wp-content/uploads/2014/06/4_481036.png

两个procedure耗时如下:

/wp-content/uploads/2014/06/5_481037.png

/wp-content/uploads/2014/06/6_481038.png

可见, ARRAY实现的性能较CURSOR提高了5倍多。

总结

      本文简单介绍了如何使用SAP HANA数组,并比较了ARRAYCURSOR的性能。

      想获取更多SAP HANA学习资料或有任何疑问,请关注新浪微博@HANAGeek!我们欢迎你的加入! 转载本文章请注明作者和出处<文章url>,请勿用于任何商业用途。

参考文献

      SAP HANA SQL Script Reference

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply