Netezza Stored Procedure ARRAY Variables and Examples

  • Post author:
  • Post last modified:June 6, 2018
  • Post category:Netezza
  • Reading time:4 mins read

The ARRAY data type is a composite data value that consists of zero or more elements of a specified specific data type. Netezza nzplsql allows you to define ARRAY types along with other scalar variables. In this article, we will check Netezza stored procedure ARRAY variables, declaration and examples.

Netezza Stored Procedure ARRAY Variables

You can define and use VARRAY in Netezza stored procedures. You can insert values to array variables, increase array size in case if length is exceeded and remove elements from array. ARRAY variables are allowed anywhere scalar variables are allowed with some restrictions.

VARRAY data type allows the size of an individual element value to exceed its declared size as long as the total size of the array is smaller than the array’s SQL width.

Netezza Stored Procedure ARRAY Variables Declaration

To declare an array variable, use the following syntax:

array_name VARRAY(size) OF type;

All the elements of the array are initially set to the SQL NULL value for the declared type. You can assign the value to array elements using array index. You can use below syntax:

array_name(idx) := value;

The above syntax raises an exception if the index (idx) is out of bounds.

Netezza Stored Procedure ARRAY Variables Methods

Following methods are supported for ARRAY variable in Netezza stored procedures:

Increase Array Variable Size

The EXTEND method extends the array by the specified size. If size is omitted, the default is 1.

array_name.EXTEND(size);

Count Items in an Array

COUNT method returns the number of elements in the array.

array_name.COUNT;

Delete items from an ARRAY

TRIM deletes the last size elements in the array (the default is 1).

array_name.TRIM(size);

TRIM and EXTEND methods raise an exception if size is out of bounds. 

Netezza Stored Procedure ARRAY Variable Example

Below Netezza stored procedure demonstrates Netezza Array variables and its methods:

CREATE OR REPLACE PROCEDURE nzplsql_arrayType_chk()
LANGUAGE NZPLSQL RETURNS varchar(255) AS
BEGIN_PROC

DECLARE

int_array VARRAY(10) OF INTEGER;
cnt int;

BEGIN
int_array(1) := 100;
int_array(2) := 200;
int_array(3) := 300;

-- Count item in an array
cnt := int_array.COUNT;

RAISE NOTICE 'Array has, % items', cnt;

-- increase array size
int_array.EXTEND(2);
cnt := int_array.COUNT;

RAISE NOTICE 'Array has, % items', cnt;

-- Trim array 
int_array.TRIM(3);
cnt := int_array.COUNT;

RAISE NOTICE 'Array has, % items', cnt;
END;
END_PROC;

Below is the screenshot of the output:

Netezza Stored Procedure ARRAY Variables and Examples

Netezza Stored Procedure ARRAY Variable Restrictions

Array references are allowed almost anywhere variable references are allowed. Following are some of restrictions:

• You can’t use ARRAY variable as an argument to RAISE statement.
• You can’t use ARRAY variable as an INTO variable.
• ARRAY Variables are NOT allowed in FOR LOOP.
• You can’t use ARRAY variable as a part of default value.

Related readings:

Tags: ,