PL/SQL provides two composite datatypes: TABLE and RECORD.
Objects of the type table are called PL/SQL tables, which are modeled as database tables.
PL/SQL tables use a primary key for array-like access to rows. These tables can have one column and a primary key. Neither the column nor the primary key can be user defined. The size of a PL/SQL table is unconstrained; that is, the table size grows dynamically as new rows are added.
Using PL/SQL tables
Declaration is done in two steps. First, define a table type, then declare PL/SQL tables of that type.
Table types can be declared within the declarative part of any block, subprogram, or package.
To reference the rows within a PL/SQL table, a primary key value must be specified using the array-like syntax, as shown in this diagram:
The PL/SQL code in the image shows a declaration syntax for PL/SQL tables. Here is the PL/SQL language extracted from the image:
-- Syntax
TYPE type_name IS TABLE OF
{ column_type | variable%TYPE |
table.column%TYPE } [NOT NULL]
INDEX BY BINARY_INTEGER;
-- Example
TYPE type_name IS TABLE OF
{ column_type | variable%TYPE |
table.column%TYPE } [NOT NULL]
INDEX BY BINARY_INTEGER;
Explanation
type_name: A type specifier used within subsequent declarations of PL/SQL tables.
column_type: Any scalar datatype such as CHAR, DATE, or NUMBER.
variable%TYPE and table.column%TYPE allow for data types that correspond to specific variables or table columns.
INDEX BY BINARY_INTEGER: Creates an associative array where elements are indexed by integer values.
PL/SQL Syntax: TYPE type_name IS TABLE OF
The following graphic shows an example of declaring a PL/SQL table:
PL SQL Table Example: Using the type specifier
A PL/SQL table method is a built-in procedure or function that you can use with any PL/SQL table.
The method is called using the dot notation:
table_name.method_name [(parameters)]
The following series of images describes PL/SQL table methods.
1) The EXISTS(n) method returns TRUE if the nth element within the PL/SQL table exists.
2) The COUNT method returns the number of elements that the PL/SQL table currently contains.
DECLARE
TYPE PetNameTabTyp IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER;
PName_tab PetNameTabTyp;
pet_count NUMBER;
BEGIN
PName_tab(1) := 'DOG';
-- The number of elements in the table
-- is stored to pet_count
pet_count := PName_tab.COUNT;
END;
Explanation:
The code declares a PL/SQL table (`PetNameTabTyp`) indexed by binary integers and assigns a value ('DOG') to the first element in `PName_tab`. The `COUNT` method is used to determine the number of elements in the table, which is then stored in `pet_count`. The note at the bottom explains that the `COUNT` method returns the number of elements that the PL/SQL table currently contains.
3) The FIRST LAST method returns the first and last (smallest and largest) index numbers within the PL/SQL table. This method returns NULL if the PL/SQL table is empty.
DECLARE
TYPE PetNameTabTyp IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER;
PName_tab PetNameTabTyp;
table_index NUMBER;
first_pet_name VARCHAR2(32);
last_pet_name VARCHAR2(32);
BEGIN
PName_tab(1) := 'DOG';
Explanation:
This code declares a PL/SQL table type (`PetNameTabTyp`) indexed by binary integers and then defines a table (`PName_tab`) of this type. It also declares variables `table_index`, `first_pet_name`, and `last_pet_name`. The `PName_tab(1) := 'DOG';` line assigns the string 'DOG' to the first element in `PName_tab`.
Note at the Bottom:
The note explains that the `FIRST` and `LAST` methods return the first and last (smallest and largest) index numbers within the PL/SQL table. If the table is empty, these methods return `NULL`.
FIRST and LAST Methods
Use the FIRST and LAST methods with nested tables, associative arrays, and VARRAYs to return, respectively, the lowest and highest index values defined in the collection. For string-indexed associative arrays, these methods return strings; “lowest” and “highest” are determined by the ordering of the character set in use in that session. For all other collection types, these methods return integers. The specifications for these functions follow.
FUNCTION FIRST RETURN PLS_INTEGER | VARCHAR2;
FUNCTION LAST RETURN PLS_INTEGER | VARCHAR2;
For example, the following code scans from the start to the end of my collection:
FOR indx IN holidays.FIRST .. holidays.LAST
LOOP
send_everyone_home (indx);
END LOOP;
This kind of loop will only work if the collection is densely populated. In the next example, I use COUNT to concisely specify that I want to append a row to the end of an associative array. I use a cursor FOR loop to transfer data from the database to an associative array of records. When the first record is fetched, the companies collection is empty, so the COUNT operator will return 0.
FOR company_rec IN company_cur
LOOP
companies ((companies.COUNT) + 1).company_id
company_rec.company_id;
END LOOP;
Boundary considerations:
FIRST and LAST return NULL when they are applied to initialized collections that have
no elements. For VARRAYs, which have at least one element, FIRST is always 1, and
LAST is always equal to COUNT.
Exceptions possible:
If FIRST and LAST are applied to an uninitialized nested table or a VARRAY, they raise
the COLLECTION_ IS_NULL predefined exception.
4) Example of the FIRST LAST method continued.
Here is the PL/SQL code shown in the image:
-- The first element in the table is stored
table_index := PName_tab.FIRST;
IF table_index > 0 THEN
first_pet_name := PName_tab(table_index);
END IF;
-- The last element in the table is stored
table_index := PName_tab.LAST;
IF table_index > 0 THEN
last_pet_name := PName_tab(table_index);
END IF;
-- Note that both the variables in this
-- case will have the same value
END;
Explanation:
This code snippet continues from the previous example, demonstrating the use of the `FIRST` and `LAST` methods. The code retrieves the smallest (first) and largest (last) index values from `PName_tab` and assigns the corresponding table values to `first_pet_name` and `last_pet_name` variables. If the `table_index` is greater than 0, it proceeds with the assignment. The comment notes that, in this case, both `first_pet_name` and `last_pet_name` will have the same value since there is only one element in `PName_tab`.
5) The PRIOR(n) method returns the index number that precedes index n within the PL/SQL table.
6) The NEXT method returns the index number that succeeds index n within a PL/SQL table
DECLARE
TYPE PetNameTabTyp IS TABLE OF CHAR(10) INDEX
BY BINARY_INTEGER;
PName_tab PetNameTabTyp;
BEGIN
PName_tab(1) := 'DOG';
PName_tab(3) := 'CAT';
PName_tab(5) := 'FISH';
-- delete element 3
PName_tab.DELETE(3);
-- PL/SQL keeps a placeholder for element 3.
-- So, the next statement appends element 4.
PName_tab.EXTEND;
PName_tab(4) := 'RABBIT';
END;
The EXTEND (n,i) method increases the size of the PL/SQL table. The TRIM method removes one element from the end of the PL/SQL table.
The DELETE method removes all the elements from the PL/SQL table.
The EXISTS(n) method returns TRUE if the nth element within the PL/SQL table exists.
The COUNT method returns the number of elements that the PL/SQL table currently contains.
The FIRST LAST method returns the first and last (smallest and largest) index numbers within the PL/SQL table.
This method returns NULL if the PL/SQL table is empty.
Example of the FIRST LAST method continued.
The PRIOR(n) method returns the index number that precedes index n within the PL/SQL table.
The NEXT method returns the index number that succeeds index n within a PL/SQL table
The EXTEND (n,i) method increases the size of the PL/SQL table.
The TRIM method removes one element from the end of the PL/SQL table.
The DELETE method removes all the elements from the PL/SQL table.
In the next lesson, you will learn how to create a PL/SQL record.
