DB2 Version 10.1 for Linux, UNIX, and Windows

CREATE TYPE (Nested table) statement (PL/SQL)

The CREATE TYPE (Nested table) statement defines an associative array indexed by INTEGER data type.

Invocation

This statement can be executed from the DB2® command line processor (CLP), any supported interactive SQL interface, an application, or a routine.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

If the schema name of the nested table type does not exist, IMPLICIT_SCHEMA authority on the database
If the schema name of the nested table type refers to an existing schema, CREATEIN privilege on the schema
DBADM authority

Syntax


>>-CREATE--+------------+--TYPE--assocarray--+-IS-+------------->
           '-OR REPLACE-'                    '-AS-'   

>--TABLE OF--datatype------------------------------------------><

Description

OR REPLACE: Indicates that if a user-defined data type with the same name already exists in the schema, the new data type is to replace the existing one. If this option is not specified, the new data type cannot replace an existing one with the same name in the same schema.
assocarray: Specifies an identifier for the associative array type.
datatype: Specifies a supported data type, such as NUMBER, VARCHAR2, RECORD, VARRAY, or associative array type.

Example

The following example reads the first ten employee names from the EMP table, stores them in a nested table, and then displays its contents:

SET SERVEROUTPUT ON
/

CREATE OR REPLACE TYPE emp_arr_typ IS TABLE OF VARCHAR2(10)
/

DECLARE
   emp_arr         emp_arr_typ;
   CURSOR emp_cur IS SELECT ename FROM emp WHERE ROWNUM <= 10;     
   i               INTEGER := 0; 
BEGIN     
   FOR r_emp IN emp_cur LOOP         
      i := i + 1;         
      emp_arr(i) := r_emp.ename;     
   END LOOP;     
   FOR j IN 1..10 LOOP         
      DBMS_OUTPUT.PUT_LINE(emp_arr(j));     
   END LOOP; 
END 
/