DECLARE TYPE
Defining a new data type in SQL Script is effectively a way to create an alias for a data type. The declaration can be used to make a custom string, such as aliasing FirstName to VARCHAR(24), or (more likely) for making an alias for a column set, such as aliasing ResponseCursorType to ROW(col1 VARCHAR(40), col2 INTEGER).
The data types supported in SQL Script are listed in the section
Data Types.
You can also declare a new data type.
Syntax
DECLARE [PUBLIC] TYPE <typeName> <dataType>
The <dataType> can be a ROW type or regular data type.
Remarks
• You can use DECLARE TYPE on CURSOR types, as in
DECLARE PUBLIC TYPE cursor_datatype_exampleA
CURSOR (fieldA INTEGER, fieldB VARCHAR(255), fieldC DATE)
• If you alias ID to be of type INTEGER, it is a distinct type and is no longer a plain integer.
• To make the data types visible outside of a procedure, the PUBLIC keyword can only be used in the root compound statement of a procedure.
Examples
PROCEDURE p ( )
BEGIN
DECLARE TYPE name_type VARCHAR(50);
DECLARE TYPE money_type DECIMAL(18, 2);
DECLARE TYPE id_type BIGINT;
DECLARE a name_type DEFAULT 'Joe';
DECLARE b money_type DEFAULT 12.34;
DECLARE c id_type DEFAULT 1234567890;
...
END
PROCEDURE p ( )
BEGIN
DECLARE TYPE r_type ROW (i INTEGER, name VARCHAR, birthdate DATE);
DECLARE r r_type;
DECLARE s r_type;
SET r.id = 123;
SET r.name = '5';
SET r.birthdate = '1990-10-31';
...
END