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.
