Arrays in SAP HANA

ARRAYS:

An array is an indexed collection of elements of a single data type. In the following section we explore the varying ways to define and use arrays in SQLScript.

UNNEST FUNCTION

The UNNEST function converts one or many arrays into a table. The result table includes a row for each element of the specified array. The result of the UNNEST function needs to be assigned to a table variable.

DECLARE ARRAY-TYPED VARIABLE

An array-typed variable will be declared by using the keyword ARRAY.

Syntax:

DECLARE <variable_name> <sql_type> ARRAY;

You can declare an array <variable_name> with the element type <sql_type>.

The following SQL types are supported:

•          DATE
•          TIME
•          TIMESTAMP
•          SECONDDATE
•          TINYINT
•          SMALLINT
•          INTEGER
•          BIGINT
•          DECIMAL
•          SMALLDECIMAL  
•          REAL
•          DOUBLE
•          VARCHAR
•          NVARCHAR  
•          ALPHANUM  
•          VARBINARY  
•          CLOB
•          NCLOB
•          BLOB

You can declare the arr array of type INTEGER as follows:

DECLARE arr INTEGER ARRAY;

To Create a Column:

create column table "KABIL_PRACTICE"."arrayproc_tbl" ("a" nvarchar(50),"b" nvarchar(50));

To Create a Procedure:

CREATE PROCEDURE "KABIL_PRACTICE"."ARRAYPROC"

(IN a NVARCHAR(20), IN b NVARCHAR(20))

AS

BEGIN

DECLARE arrayNvarchar NVARCHAR(20) ARRAY;

arrayNvarchar := ARRAY(:a,:b);

insert into "KABIL_PRACTICE"."arrayproc_tbl" values (:arrayNvarchar[1],:arrayNvarchar[2]);

END;

To call a Procedure:                                                         

            call "KABIL_PRACTICE"."ARRAYPROC"(1,2);

                       

To Drop a Procedure:       

            drop procedure "KABIL_PRACTICE"."ARRAYPROC";

                       

To see the output:

                 

            select * from "KABIL_PRACTICE"."arrayproc_tbl";

Result:       

Example 2:   

To Create a table:          

create column table "KABIL_PRACTICE"."Array_Proc_Dec"("A" nvarchar(10), "B" nvarchar(10));

To Create a Procedure:      

create procedure "KABIL_PRACTICE"."Array_Proc_Declaration"

as

begin

DECLARE array_int INTEGER ARRAY := ARRAY(3, 4);

--array_int := ARRAY(:A,:B);

insert into "KABIL_PRACTICE"."Array_Proc_Dec" values(:array_int[1],:array_int[2]);

END;

To Call a Procedure:        

call "KABIL_PRACTICE"."Array_Proc_Declaration"();

(OR)

call "KABIL_PRACTICE"."Array_Proc_Declaration";

To see the output:           

select * from "KABIL_PRACTICE"."Array_Proc_Dec"

Result: