DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The DBMS_SQL module provides a set of procedures for executing dynamic SQL, and therefore supports various data manipulation language (DML) or data definition language (DDL) statement.
The schema for this module is SYSIBMADM.
The DBMS_SQL module includes the following system-defined routines.
| Procedure name | Description |
|---|---|
| BIND_VARIABLE_BLOB procedure | Provides the input BLOB value for the IN or INOUT parameter; and defines the data type of the output value to be BLOB for the INOUT or OUT parameter. |
| BIND_VARIABLE_CHAR procedure | Provides the input CHAR value for the IN or INOUT parameter; and defines the data type of the output value to be CHAR for the INOUT or OUT parameter. |
| BIND_VARIABLE_CLOB procedure | Provides the input CLOB value for the IN or INOUT parameter; and defines the data type of the output value to be CLOB for the INOUT or OUT parameter. |
| BIND_VARIABLE_DATE procedure | Provides the input DATE value for the IN or INOUT parameter; and defines the data type of the output value to be DATE for the INOUT or OUT parameter. |
| BIND_VARIABLE_DOUBLE procedure | Provides the input DOUBLE value for the IN or INOUT parameter; and defines the data type of the output value to be DOUBLE for the INOUT or OUT parameter. |
| BIND_VARIABLE_INT procedure | Provides the input INTEGER value for the IN or INOUT parameter; and defines the data type of the output value to be INTEGER for the INOUT or OUT parameter. |
| BIND_VARIABLE_NUMBER procedure | Provides the input DECFLOAT value for the IN or INOUT parameter; and defines the data type of the output value to be DECFLOAT for the INOUT or OUT parameter. |
| BIND_VARIABLE_RAW procedure | Provides the input BLOB(32767) value for the IN or INOUT parameter; and defines the data type of the output value to be BLOB(32767) for the INOUT or OUT parameter. |
| BIND_VARIABLE_TIMESTAMP procedure | Provides the input TIMESTAMP value for the IN or INOUT parameter; and defines the data type of the output value to be TIMESTAMP for the INOUT or OUT parameter. |
| BIND_VARIABLE_VARCHAR procedure | Provides the input VARCHAR value for the IN or INOUT parameter; and defines the data type of the output value to be VARCHAR for the INOUT or OUT parameter. |
| CLOSE_CURSOR procedure | Closes a cursor. |
| COLUMN_VALUE_BLOB procedure | Retrieves the value of column of type BLOB. |
| COLUMN_VALUE_CHAR procedure | Retrieves the value of column of type CHAR. |
| COLUMN_VALUE_CLOB procedure | Retrieves the value of column of type CLOB. |
| COLUMN_VALUE_DATE procedure | Retrieves the value of column of type DATE. |
| COLUMN_VALUE_DOUBLE procedure | Retrieves the value of column of type DOUBLE. |
| COLUMN_VALUE_INT procedure | Retrieves the value of column of type INTEGER. |
| COLUMN_VALUE_LONG procedure | Retrieves the value of column of type CLOB(32767). |
| COLUMN_VALUE_NUMBER procedure | Retrieves the value of column of type DECFLOAT. |
| COLUMN_VALUE_RAW procedure | Retrieves the value of column of type BLOB(32767). |
| COLUMN_VALUE_TIMESTAMP procedure | Retrieves the value of column of type TIMESTAMP |
| COLUMN_VALUE_VARCHAR procedure | Retrieves the value of column of type VARCHAR. |
| DEFINE_COLUMN_BLOB procedure | Defines the data type of the column to be BLOB. |
| DEFINE_COLUMN_CHAR procedure | Defines the data type of the column to be CHAR. |
| DEFINE_COLUMN_CLOB procedure | Defines the data type of the column to be CLOB. |
| DEFINE_COLUMN_DATE procedure | Defines the data type of the column to be DATE. |
| DEFINE_COLUMN_DOUBLE procedure | Defines the data type of the column to be DOUBLE. |
| DEFINE_COLUMN_INT procedure | Defines the data type of the column to be INTEGER. |
| DEFINE_COLUMN_LONG procedure | Defines the data type of the column to be CLOB(32767). |
| DEFINE_COLUMN_NUMBER procedure | Defines the data type of the column to be DECFLOAT. |
| DEFINE_COLUMN_RAW procedure | Defines the data type of the column to be BLOB(32767). |
| DEFINE_COLUMN_TIMESTAMP procedure | Defines the data type of the column to be TIMESTAMP. |
| DEFINE_COLUMN_VARCHAR procedure | Defines the data type of the column to be VARCHAR. |
| DESCRIBE_COLUMNS procedure | Return a description of the columns retrieved by a cursor. |
| DESCRIBE_COLUMNS2 procedure | Identical to DESCRIBE_COLUMNS, but allows for column names greater than 32 characters. |
| EXECUTE procedure | Executes a cursor. |
| EXECUTE_AND_FETCH procedure | Executes a cursor and fetch one row. |
| FETCH_ROWS procedure | Fetches rows from a cursor. |
| IS_OPEN procedure | Checks if a cursor is open. |
| LAST_ROW_COUNT procedure | Returns the total number of rows fetched. |
| OPEN_CURSOR procedure | Opens a cursor. |
| PARSE procedure | Parses a DDL statement. |
| VARIABLE_VALUE_BLOB procedure | Retrieves the value of INOUT or OUT parameters as BLOB. |
| VARIABLE_VALUE_CHAR procedure | Retrieves the value of INOUT or OUT parameters as CHAR. |
| VARIABLE_VALUE_CLOB procedure | Retrieves the value of INOUT or OUT parameters as CLOB. |
| VARIABLE_VALUE_DATE procedure | Retrieves the value of INOUT or OUT parameters as DATE. |
| VARIABLE_VALUE_DOUBLE procedure | Retrieves the value of INOUT or OUT parameters as DOUBLE. |
| VARIABLE_VALUE_INT procedure | Retrieves the value of INOUT or OUT parameters as INTEGER. |
| VARIABLE_VALUE_NUMBER procedure | Retrieves the value of INOUT or OUT parameters as DECFLOAT. |
| VARIABLE_VALUE_RAW procedure | Retrieves the value of INOUT or OUT parameters as BLOB(32767). |
| VARIABLE_VALUE_TIMESTAMP procedure | Retrieves the value of INOUT or OUT parameters as TIMESTAMP. |
| VARIABLE_VALUE_VARCHAR procedure | Retrieves the value of INOUT or OUT parameters as VARCHAR. |
The following table lists the system-defined types and constants available in the DBMS_SQL module.
| Name | Type or constant | Description |
|---|---|---|
| DESC_REC | Type | A record of column information. |
| DESC_REC2 | Type | A record of column information. |
| DESC_TAB | Type | An array of records of type DESC_REC. |
| DESC_TAB2 | Type | An array of records of type DESC_REC2. |
| NATIVE | Constant | The only value supported for language_flag parameter of the PARSE procedure. |
The routines in the DBMS_SQL module are useful when you want to construct and run dynamic SQL statements. For example, you might want execute DDL or DML statements such as "ALTER TABLE" or "DROP TABLE", construct and execute SQL statements on the fly, or call a function which uses dynamic SQL from within a SQL statement.