PL/SQL Collections Flashcards
Which collections allow holes?
AA, NT
Which collections do not allow holes?
VA
Which collections are empty at declaration?
AA
Which collections are NULL at declaration?
NT, VA
Which collections have a fixed capacity?
VA
Which collections have a capacity that’s not set?
AA, NT
What types of indexing do each collection have?
AA: Integers, Strings
NT: Integers
VA: Integers
What is an associative array (AA)?
A set of key-value pairs where each key is unique and is used to locate the corresponding value
What is the basic syntax of creating an AA?
TYPE type_name IS TABLE OF element_type INDEX BY index_type;
table_name type_name;
What is the syntax for adding an element to an AA?
table_name(‘key’) := value;
What is a nested table (NT)?
A one-dimensional array where the array size is dynamic
What is the basic syntax of creating an NT?
TYPE type_name IS TABLE OF element_type;
table_name type_name;
What is the syntax for adding elements to a NT?
table_name := type_name(‘elem1’, ‘elem2’);
What are some of the collection methods PL/SQL provides for AA’s and NT’s?
- FIRST
- LAST
- PRIOR(i)
- NEXT(i)
- DELETE(i)
- COUNT
- EXISTS(I)
- EXTEND
What does the FIRST function do?
Returns the index of the first element
What does the LASTfunction do?
Returns the index of the last element
What does the PRIOR(i) function do?
Returns the index of the element that comes before index i
What does the NEXT(i) function do?
Returns the index of the element that comes
after index i
What does the DELETE(i) function do?
Deletes the element at index i
What does the COUNT function do?
Returns the number of elements
What does the EXISTS(i) function do?
Returns true/false if element i exists
Which PL/SQL collection has sparse indexing?
AAs
Which PL/SQL collection starts with dense indexing (at 1)?
NTs
What does the EXTEND function do?
Appends one null element to a collection
What is are Varrays?
A 1D array whose number of elements can vary from zero (empty) to the declared maximum size
What is the basic syntax of creating a Varrays?
TYPE type_name IS VARRAY(size) OF element_type;
table_name type_name;
What is the syntax for adding elements to a Varray?
table_name.EXTEND;
table_name (index) := value;
