If the collection has at least one element, the FIRST and LAST methods return the indices of the first and last elements, respectively, ignoring deleted elements (even if DELETE retains placeholders for deleted elements).
If the collection has only one element, FIRST and LAST return the same index. If the collection is empty, FIRST and LAST return NULL.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Use in associative arrays
For associative arrays with an index type of PLS_INTEGER, the first and last elements are the elements with the smallest and largest indices, respectively. For associative arrays with a string index type, the first and last elements are the elements with the lowest and highest key values, respectively.
Here is an example:
obclient> DECLARE
TYPE numlist IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
nl NUMLIST;
PROCEDURE print_first_and_last IS
BEGIN
DBMS_OUTPUT.PUT_LINE('FIRST = ' || nl.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || nl.LAST);
END print_first_and_last;
BEGIN
nl('A') := 2;
nl('C') := 4;
nl('D') := 6;
nl('E') := 8;
nl('B') := 10;
DBMS_OUTPUT.PUT_LINE('Before deleting:');
print_first_and_last;
nl.DELETE('A');
nl.DELETE('E');
DBMS_OUTPUT.PUT_LINE('After deleting:');
print_first_and_last;
END;
/
Query OK, 0 rows affected
Before deleting:
FIRST = A
LAST = E
After deleting:
FIRST = B
LAST = D
The preceding example shows the FIRST and LAST values for an associative array with a string index type. It then deletes the first and last elements and displays the FIRST and LAST values again.
Use in varrays
For non-empty varrays, FIRST always returns 1. For any varray, LAST always equals COUNT.
Here is an example:
obclient> DECLARE
TYPE players IS VARRAY(5) OF VARCHAR2(20);
name PLAYERS;
PROCEDURE print_name (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF name.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('No members');
ELSE
FOR i IN name.FIRST..name.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || name(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_name('Team Members:');
name := PLAYERS(); -- The team does not have members.
print_name('Team Members:');
name := players('Andrew', 'Barton');
print_name('Team Members:');
name := players('Charles', 'Carl', 'James', 'Gary','Brian');
print_name('New Team Members:');
END;
/
Query OK, 0 rows affected
Team Members:
Does not exist
---
Team Members:
No members
---
Team Members:
1. Andrew
2. Barton
---
New Team Members:
1. Charles
2. Carl
3. James
4. Gary
5. Brian
---
Use in nested tables
For nested tables, LAST equals COUNT, except when elements are deleted from the middle, which makes LAST greater than COUNT.
Here is an example:
obclient> DECLARE
TYPE players IS TABLE OF VARCHAR2(20);
name PLAYERS;
PROCEDURE print_name (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF name.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('No members');
ELSE
FOR i IN name.FIRST..name.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '.');
IF name.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(name(i));
ELSE
DBMS_OUTPUT.PUT_LINE('TBD');
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_name('Team Members:');
name := players(); -- The team exists, but has no players.
print_name('Team Members:');
name := players('Charles', 'Carl', 'James', 'Gary','Brian'); -- Players are added.
print_name('Team Members:');
name.DELETE(3,4); -- The third and fourth players are deleted.
print_name('Current Team Members:');
END;
/
Query OK, 0 rows affected
Team Members:
Does not exist
---
Team Members:
No members
---
Team Members:
1.
Charles
2.
Carl
3.
James
4.
Gary
5.
Brian
---
Current Team Members:
1.
Charles
2.
Carl
3.
TBD
4.
TBD
5.
Brian
---
The preceding example uses a FOR LOOP statement with team.FIRST and team.LAST to output the nested table name. Because nested tables can be sparse, the FOR LOOP statement outputs team(i) only when team.EXISTS(i) is TRUE.