Use of RECORD, VARRAY, and TABLE in ORACLE

Use of RECORD, VARRAY, and TABLE in ORACLE

1 Description

1.1 RECORD

Define the record data type. It is similar to the STRUCTURE in C language. PL/SQL provides a holistic approach to combine several related, separated, and basic data type variables, that is, the RECORD compound data type. When using a record data type variable, you must first define the record composition and record variables in the Declaration section, and then reference the record variable itself or its members in the execution section.

The syntax for defining the record data type is as follows:

TYPE RECORD_NAME is record (

V1 DATA_TYPE1 [not null] [: = DEFAULT_VALUE],

V2 DATA_TYPE2 [not null] [: = DEFAULT_VALUE],

VN DATA_TYPEN [not null] [: = DEFAULT_VALUE]);

1.2 VARRAY

An array is a set of group members of the same data type. Each member has a unique subscript, which depends on the position of the Member in the array. In PL/SQL, the array data type is VARRAY (variable array, that is, variable array ).

The syntax for defining the VARRAY data type is as follows:

TYPE VARRAY_NAMEIS VARRAY (SIZE) OF ELEMENT_TYPE [not null];

Where, varray_name is the name of the VARRAY data type, and size is a positive integer, indicating the maximum number of members that can be accommodated. When the data type of each member is element_typeo by default, the member can take null values, otherwise, use not null.

1.3 TABLE

Defines the Data Type of the record table (or index table. It is similar to the record type, but it is an extension of the record type. It can process multiple rows of records, similar to the two-dimensional array in C language, so that it can imitate the tables in the database in PL/SQL.

The syntax for defining the record table type is as follows:

Type table name is table of ELEMENT_TYPE [not null]

Index by [BINARY_INTEGER | PLS_INTEGER | VARRAY2];

The keyword index by indicates that a primary key INDEX is created to reference a specific row in the record table variable.

Description of BINARY_INTEGER

For example, the statement "type numbers is table of number index by BINARY_INTEGER" adds "INDEX BYBINARY_INTEGER", and the subscript OF the numbers type is auto-increment. When the numbers type inserts an element, no Initialization is required, and no EXTEND space is required each time.

Without this statement "INDEXBY BINARY_INTEGER", you must display the initialization of the pair. EXTEND is required for each element inserted into a NUMBERS-type TABLE.

2. Example

2.1 create a table structure and prepare data

[SQL] view plain copy

-- Organization Structure table

Create table SF_ORG

(

ORG_ID int not null, -- Organization primary key ID

ORG_NAME VARCHAR2 (50), -- Organization Name

PARENT_ID INT -- parent level of the organization

)

-- Level-1 Organizational Unit

Insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (1, 'level 1 Department 1', 0 );

-- Level 2 Department

Insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (2, 'department 2 2', 1 );

Insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (3, 'level 2 Department 3', 1 );

Insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (4, 'level 2 Department 4', 1 );

2.2 example of using RECORD

First, define a record data type TYPE_ORG_RECORD with the same data type as several columns in the SF_ORG table, and then declare a record variable V_ORG_RECORD of this data type, finally, use the replace variable & ORG_ID to accept the input employee code, query and display the information in these columns of the employee. Note: When using a RECORD data type variable, use the "." operator to specify the RECORD variable name qualifier.

A variable of the record type can only store one row of records queried from the database. If multiple rows of records are queried, an error occurs.

[SQL] view plain copy

DECLARE

TYPE TYPE_ORG_RECORD is record (

V_ORG_NAME SF_ORG.ORG_NAME % TYPE,

V_PARENT_ID SF_ORG.PARENT_ID % TYPE );

V_ORG_RECORD TYPE_ORG_RECORD;

BEGIN

SELECT ORG_NAME, PARENT_ID INTO V_ORG_RECORD

FROM SF_ORG SO

Where so. ORG_ID = & ORG_ID;

DBMS_OUTPUT.PUT_LINE ('department name: '| V_ORG_RECORD.V_ORG_NAME );

DBMS_OUTPUT.PUT_LINE ('superior department code: '| TO_CHAR (V_ORG_RECORD.V_PARENT_ID ));

END;

2.3 examples of VARRAY

First define a VARRAY data type ORG_VARRAY_TYPE that can save five Members of the VARCHAR2 (25) data type, and then declare a VARRAY variable V_ORG_VARRAY of the data type, finally, use the constructor syntax with the same name as ORG_VARRAY_TYPE to assign the initial value to the V_ORG_VARRAY variable and display the value assignment result.

Note: When referencing members in an array, you must use the sequential subscript in a pair of parentheses. The subscript starts from 1 rather than from 0.

[SQL] view plain copy

DECLARE

TYPE ORG_VARRAY_TYPE is varray (5) OF VARCHAR2 (25 );

V_ORG_VARRAY ORG_VARRAY_TYPE;

BEGIN

V_ORG_VARRAY: = ORG_VARRAY_TYPE ('1', '2', '3', '4', '5 ');

DBMS_OUTPUT.PUT_LINE ('output 1: '| V_ORG_VARRAY (1) |', '| V_ORG_VARRAY (2) |', '| V_ORG_VARRAY (3) | ',' | V_ORG_VARRAY (4 ));

DBMS_OUTPUT.PUT_LINE ('output 2: '| V_ORG_VARRAY (5 ));

V_ORG_VARRAY (5): = '20140901 ';

DBMS_OUTPUT.PUT_LINE ('output 3: '| V_ORG_VARRAY (5 ));

END;

2.4 TABLE example

2.4.1 store a single column and multiple rows

This is similar to VARRAY. However, the assignment method is slightly different. You cannot assign values using constructors of the same name. The details are as follows:

[SQL] view plain copy

DECLARE

TYPE ORG_TABLE_TYPE is table of VARCHAR2 (25)

Index by BINARY_INTEGER;

V_ORG_TABLE ORG_TABLE_TYPE;

BEGIN

V_ORG_TABLE (1): = '1 ';

V_ORG_TABLE (2): = '2 ';

V_ORG_TABLE (3): = '3 ';

V_ORG_TABLE (4): = '4 ';

V_ORG_TABLE (5): = '5 ';

DBMS_OUTPUT.PUT_LINE ('output 1: '| V_ORG_TABLE (1) |', '| V_ORG_TABLE (2) |', '| V_ORG_TABLE (3) | ',' | V_ORG_TABLE (4 ));

DBMS_OUTPUT.PUT_LINE ('output 2: '| V_ORG_TABLE (5 ));

END;

[SQL] view plain copy

DECLARE

TYPE T_TYPE is table of SF_ORG % ROWTYPE;

V_TYPE T_TYPE;

BEGIN

SELECT ORG_ID, ORG_NAME, PARENT_ID bulk collect into V_TYPE

FROM SF_ORG

WHERE SF_ORG.ORG_ID <= 3;

FOR V_INDEX IN V_TYPE.FIRST... V_TYPE.LAST LOOP

DBMS_OUTPUT.PUT_LINE (V_TYPE (V_INDEX). C1 | ''| V_TYPE (V_INDEX). C2 );

End loop;

END;

2.4.3 use multiple columns, multiple rows, and RECORD together

Bulkcollect can be used to load the query results to collections at a time. Instead of processing it one by one Using cursor.

[SQL] view plain copy

DECLARE

TYPE TEST_EMP IS RECORD

(

C1 SF_ORG.ORG_NAME % TYPE,

C2 SF_ORG.PARENT_ID % TYPE

);

TYPE T_TYPE is table of TEST_EMP;

V_TYPE T_TYPE;

BEGIN

SELECT ORG_NAME, PARENT_ID bulk collect into V_TYPE

FROM SF_ORG

WHERE SF_ORG.ORG_ID <= 3;

FOR V_INDEX IN V_TYPE.FIRST... V_TYPE.LAST LOOP

DBMS_OUTPUT.PUT_LINE (V_TYPE (V_INDEX). C1 | ''| V_TYPE (V_INDEX). C2 );

End loop;

END;