# [PL/SQL] Oracle Records and Collections

Collection : components have the same data type, and are called

elements.

Record : components can have the different data types, and are calledfields.

# 1. Record

**1.1 Type and %type**

You could force fields as NOT NULL constraints

**1.2 ROWTYPE**

*1.2.1 Table%ROWTYPE variable*

*1.2.2 Cursor%ROWTYPE variable*

# 2. Collection

**2.1 VARRAY**

**VARRAY **has a pre-defined size, for using **VARRAY**, we need to follow steps :

Define → Declare → Initialize → Assign → Access

You might face below exceptions when you work with **VARRAY**

ORA-06531: Reference to uninitialized collection

ORA-06533: Subscript beyond count

ORA-06533: Subscript beyond count

ORA-06532: Subscript outside of limit

**Methods**

LIMIT : returns the size of VARRAY

COUNT : returns the number of initilazed elements

FIRST : always return 1, since in VARRAY we can’t delete any specific elements, we just can delete the last elements

LAST : returns the index of the last initilazed elements

EXTEND(n) : extends n memory locations (limited by the size of VARRAY)

TRIM(n) : removes the the last n elements

DELETE : delete ALL elements in VARRAY

PRIOR(n-th) : returns the previous index of n-th element

NEXT(n-th) : returns the next index of n-th element

**2.2 NESTED TABLE**

The same with VARRAY, for using **NESTED TABLE**, we need to follow steps :

Define → Declare → Initialize → Assign → Access

The main different between VARRAY and Nested Table is in nested table, we don’t have a pre-defined size, so we could extend() any much we want. And the nested table is not continuous, since we could delete any specific elements we want. So we need to add checking condition “EXISTS” before accessing to avoid “ORA-01403: no data found”

**Methods**

LIMIT : returns NULL, since in NESTED TABLE, we don’t have pre-defined size

COUNT : returns the number of initilazed elements

FIRST : returns the index of the first initilazed elements

LAST : returns the index of the last initilazed elements

EXTEND(n) : extend n memory locations

TRIM(n) : remove the the last n elements

DELETE(n-th) : delete the n-th element

PRIOR(n-th) : returns the previous index of n-th element

NEXT(n-th) : returns the next index of n-th elementEXISTS(n-th) : check if n-th exists before accessing to avoid “ORA-01403: no data found”

You can use TABLE() to transfor nested table to normal table

**2.3 ASSOCIATIVE ARRAY (MAP)**

With Associative array, we don’t need to initialized

**Methods**

COUNT : returns the number of initilazed elements

FIRST : returns the index of the first initilazed elements

LAST : returns the index of the last initilazed elements

TRIM(n) : remove the the last n elements

DELETE(n-th) : delete the n-th element

PRIOR(n-th) : returns the previous index of n-th element

NEXT(n-th) : returns the next index of n-th element

EXISTS(n-th) : check if n-th exists before accessing to avoid “ORA-01403: no data found”

# Loop

# Multiset Operators

The “MULTISET” operators are very similiar with the “SET” operators.

It has “ALL” hint by default i.e: MULTISET UNION = MULTISET UNION ALL

The differences between “MULTISET” and “SET” are :

- “MULTISET” has default (ALL) while SET has default(DISTINCT)
- “MULTISET” when using “DISTINCT” keyword, it
**doesn’t sort**the collection.