DB2 Arrays, functions

One. Arrays

In DB2, creating an array will generate two objects under functions: The SYS type and the user type array

/* Create an array */ Create  as varchar (+) array[]; /* Delete an array */ drop type arrname;

Two. Functions

1. Basic syntax

Create functionDB2INST.FUNC1 (param1integer, param2varchar(Ten))/*input Parameters except Boolean and cursor*/returns varchar( $)/*must return type, optional scalar,row,table*/specific "func1" language SQL notdeterministicexternal actionmodifies SQL databegin    DeclareVarNamevarchar( $); /*use of custom arrays*/    Declarearr1 Arrname; SetArr1=Array[' 2 ']; returnVarName;End

View Code

　

2. Common functions

cardinality (ARR1): Returns the number of elements in an array locate (ARG1,ARG2,<pos>) : Find where Arg1 first appears in Arg2, and if POS is specified, find the location where ARG1 first appears, starting at POS at arg2. COALESCE (Arg1,arg2 ...) : Returns the first non-null parameter in a parameter set. Usage is similar to the value function. substr (Arg1,pos,<length>): Returns the length character at the beginning of the POS position in arg1, if no length is specified, The remaining characters are returned. Integer (ARG1): Returns the converted integer cast (<expression><DataType  >[length]): Data type conversion function sum (ARG1): The value of the column is counted

DB2 Array, function