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 leaves 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 provides only MySQL-compatible mode.
Use in associative arrays
For an associative array with an index type of PLS_INTEGER, the first and last elements are the ones with the smallest and largest indices, respectively. For an associative array with a string index type, the first and last elements are the ones 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 values of FIRST and LAST for an associative array with a string index type. It then deletes the first and last elements and shows the values of FIRST and LAST again.
Use in varrays
For a non-empty varray, FIRST always returns 1. For any varray, LAST is always equal to 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 exists but has no 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 a nested table, LAST is equal to 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 members.
print_name('Team Members:');
name := players('Charles', 'Carl', 'James', 'Gary','Brian'); -- Add members.
print_name('Team Members:');
name.DELETE(3,4); -- Delete the third and fourth members.
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 a nested table can be sparse, the FOR LOOP statement outputs team(i) only when team.EXISTS(i) is TRUE.