IBM PureData System for Analytics, Version 7.1

Variables and constants

All of the variables, rows, and records that are used in a block or its sub-blocks must be declared in the declarations section of a block. There are some exceptions, namely the loop variable of a FOR loop that iterates over a range of integer values, and some built-in variables like FOUND, ROW_COUNT, and LAST_OID.

NZPLSQL variables can have any SQL data type, such as INTEGER, VARCHAR, and CHAR. Some sample variable declarations follow:

user_id INTEGER;
quantity NUMERIC(5,2);
url VARCHAR;

A variable declaration has the following syntax:

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } value ];

The DEFAULT clause, if included, specifies the initial value assigned to the variable when the block is entered. If a DEFAULT clause is not specified, the variable uses the SQL NULL value as its default.
The CONSTANT option means that the variable cannot be changed; its value remains constant for the duration of the block.
If NOT NULL is specified, an assignment of a NULL value results in a runtime error. Since the default value of all variables is the SQL NULL value, all variables declared as NOT NULL must also specify a non-null default value.

When specifying types in declarations, NUMERIC can be specified with or without a precision and scale. CHAR, NCHAR, VARCHAR, and NVARCHAR can be specified with or without a size. When these types are specified with a size or a precision/scale, assignment to the variable follows normal cast rules. If they are specified without sizes, assignment preserves the original source size or precision/scale.

The default value is evaluated each time the procedure is called. So assigning now() to a variable of type timestamp causes the variable to be set to the time of the actual procedure call, not the time when the procedure was precompiled into its bytecode.

Some examples of variable assignments follow:

quantity INTEGER := 32;
url varchar := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;

By using the %TYPE and %ROWTYPE attributes, you can declare variables with the same data type or structure of another database item (for example, a table field).

The %TYPE attribute provides the data type of a variable or database column. You can use this attribute to declare variables that hold database values. For example, if you have a column named user_id in your users table, you can declare a variable with the same data type as user_id as follows:

user_id users.user_id%TYPE;

By using %TYPE, you do not have to know the data type of the structure that you are referencing. Also, if the data type of the referenced item changes in the future (for example, you change your table definition of user_id to become a REAL), you do not have to change your procedure definition.

You can declare a row with the structure of a table, as follows:

name table%ROWTYPE;

The table value must be an existing table or view name of the database. The fields of the row are accessed by using the dot notation.

Only the user attributes of a table row are accessible in the row. You cannot access an OID or other system attribute because the row could be from a view. The fields of the row type inherit the sizes or precision for CHAR, NCHAR, and NUMERIC data types from the table.