The DEFINE_ARRAY stored procedure defines the collection into which the row values are fetched, with a FETCH_ROWS function call, for a given column.
The DEFINE_ARRAY stored procedure can use a single SELECT statement to retrieve multiple rows and pass them to COLUMN_VALUE as parameters.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Scalar and LOB types of the collection
You can declare local variables as the following types and use DBMS_SQL to fetch any number of rows into them. These types can be the same as those specified by the BIND_ARRAY stored procedure.
TYPE binary_double_table
IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;
TYPE binary_float_table
IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;
TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;
TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
TYPE date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE interval_day_to_second_Table
IS TABLE OF dsinterval_unconstrained
INDEX BY BINARY_INTEGER;
TYPE interval_year_to_MONTH_Table
IS TABLE OF yminterval_unconstrained
INDEX BY BINARY_INTEGER;
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE time_table IS TABLE OF time_unconstrained
INDEX BY BINARY_INTEGER;
TYPE time_with_time_zone_table
IS TABLE OF time_tz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE timestamp_table
IS TABLE OF timestamp_unconstrained
INDEX BY BINARY_INTEGER;
TYPE timestamp_with_ltz_Table
IS TABLE OF timestamp_ltz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE timestamp_with_time_zone_Table
IS TABLE OF timestamp_tz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
Syntax
DBMS_SQL.DEFINE_ARRAY (
c IN INTEGER,
position IN INTEGER,
<table_variable> IN <datatype>
cnt IN INTEGER,
lower_bnd IN INTEGER);
<table_variable> and the corresponding <datatype> can be combined into any of the following matching pairs, and DEFINE_ARRAY is reloaded to accept different data types:
<n_tab> Number_Table
<c_tab> Varchar2_Table
<d_tab> Date_Table
<bl_tab> Blob_Table
<cl_tab> Clob_Table
Parameters
| Parameter | Description |
|---|---|
| c | The ID of the cursor to be bound to the array. |
| position | The relative position of the column in the defined array. This value starts at 1. |
| table_variable | The local variable that has been declared as <datatype>. |
| cnt | The number of rows to be fetched. |
| lower_bnd | The lower-bound index, starting from which the results are copied to the collection. |
Considerations
The value of
(cnt)must be an integer greater than 0; otherwise, an exception will be thrown.The value of
lower_bndcan be a positive number, a negative number, or 0.Queries that call
DEFINE_ARRAYcannot contain array bindings.