[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 called fields.

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 element
EXISTS(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.

“Developer never die” 😎🇻🇳