July 30, 2010, Friday, 210


Variables

From SQL PL Guide for DB2

Jump to: navigation, search

Variables in SQL PL

Any built-in data type can be used as variable type. Supported built-in data types are:

  • Datetime data types: TIME, TIMESTAMP, DATE
  • Strings: CHAR, VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, DBCLOB
  • Binaries: BLOB
  • Numeric data types: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE
  • XML data type: XML
  • New in DB2 9.5: DECFLOAT

Variables can be defined in the procedure head as IN, OUT or INOUT variable. Furthermore variables can be defined with the DECLARE statement in the procedure body. Variables can be set and modified with the SET statement.

Be careful if you've columns and variables with the same name or if you're using multiple compound statements in nested blocks with the same variable names. Details and tipps you will find in the chapter Variable name scoping.

Limits:
- At maximum a variable name can be 128 bytes. 32.767 variables can be used at maximum in a procedure.
- Variables in DDL statements (like create-table-statements) are not supported.

Retrieved from "http://sqlpl-guide.com/Variables"