PLSQL Programming Flashcards
Composite Data Types and type names
Stores values that have internal components. You can pass entire composite variables to sub programs and parameters. Can access internal components of composite variables individually
Collection
Record
Collection Data Type (composite)
Internal components always have the same data types and are called (elements). You can access each element of a collection variable by its unique index. Variable_name (index).
Create a collection
Create a variable of that type or use %TYPE
Record Data Type (composite)
Data Type can be visualized as a row of data. It can contain all the contents of a row.
Collection Types (composite). Variable (index) 3 Types
Associative Array
Nested Tables
Varray (Variable Size Arrays)
Dense & Sparse
Are the values of performance settings of the underlying data structure called dimensions.
Dense
If all the rows between the first and last row are defined and given a value (including nulls)
Sparse
Not defined or populated sequentially Gaps between defined rows.
Associative Array - collection –> composite
Sparse or dense index by integer or string. Originally only one and table or index by table
Example of Associative array
Creating an index by table keys of subscript type and associated values element type
Syntax
TYPE Type_name is TABLE OF element_type (NOT NULL)
INDEX BY subscript_type
Table_name type_name;
Nested Tables - collection –> composite
This is like a one dimensional array with an arbitrary number of elements.
Differs from an array in the following aspects
- Array has a declared number of elements Nested doesn’t. Nested can increase dynamically.
- Array is always dense, consecutive scripts. Nested dense initially but can become sparse when elements are deleted.
Nested table is similar to declaration of an index-by table but no index by.
Can store in a DB column used for simplifying
Join a single column table with a larger table
Associative array can’t be stored in DB
Syntax
TYPE Type_name is Table OF element_type (NOT NULL)
Table_name type_name;
Varrays (variable size array) - collection –> composite
Always dense, can’t delete values in between the values only delete from the end.
Similar to a nested table except must specify upper bound in declaration. Can store in DB
When should you use VARRAY
Know the max number of elements
Usually access the elements sequentially
Have to store or retrieve all elements at same time, might be impractical for large numbers of elements.
Syntax
TYPE Type_name is VARRAY(size) OF element_type (NOT NULL)
Table_name type_name := type_name();
Characteristics of Collection
Collection # of Subscript Dense or Where Obj type
elements type Sparse Created Attribute
———————————————————————-
Associative -unbound -string -Either -PLSQL No
Array or integer block
———————————————————————-
Nested -unbound -integer -starts dense -PLSQL Yes
Table can become block
sparse or at
Schema level
———————————————————————-
Varray -bounded -integer -dense -PLSQL or Yes
Schema level
Farrah
Collection Methods (15)
E C L F L P N E E E T T D D D
EXISTS(n) - rtn TRUE if specified element exists
COUNT - rtn number of elements in collection
LIMIT - rtn max number of elements for VARRAY or null for nest tables
FIRST - rtn index of the first element in the collection
LAST - rtn index of the last element in the collection
PRIOR(n) - rtn index of the the element prior to specified element
NEXT (n) - rtn index of the next element after the specified element
EXTEND - append a single null element to the collection
EXTEND(n) - appends n null elements to the collection
EXTEND (n1,n2) - appends n1 copies of the n2th element of the collec
TRIM - removes a single element from the end of the collection
TRIM(n) -removes n elements from the collection
DELETE -removes all elements from the collection
DELETE(n) -removes element n from the collection
DELETE(n1,n2) -removes all elements from n1 to n2 from the collection
