If a collection has two or more elements, the FIRST and LAST methods respectively return the indexes of the first and last elements, with deleted elements ignored even if the DELETE method retains placeholders for the deleted elements.
If the collection has only one element, the FIRST and LAST methods return the same index. If the collection is empty, the FIRST and LAST methods return NULL.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Usage in associative arrays
For an associative array of the PLS_INTEGER index type, the first and last elements respectively have the minimum and maximum index values. For an associative array of the string type, the first and last elements respectively have the lowest and highest key values.
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 returned by the FIRST and LAST methods for an associative array of the string index type, and the values returned by the FIRST and LAST methods after the first and last elements are deleted.
Usage in varrays
For a variable-size array (or varray) that is not empty, the FIRST method always returns 1. For any varrays, the value returned by the LAST method is always equal to the value of 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(); -- No team 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
---
Usage in nested tables
For a nested table, the value returned by the LAST function is equal to the value of COUNT. If intermediate elements are deleted, the value returned by the LAST method is greater than the value of 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(); -- A football team exists but has no players.
print_name('Team Members:');
name := players('Charles', 'Carl', 'James', 'Gary','Brian'); -- Add players to the team.
print_name('Team Members:');
name.DELETE(3,4); -- Delete the third and fourth players.
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
---
In the preceding example, the FOR LOOP statement with team.FIRST and team.LAST outputs a nested table named name. Nested tables can be sparse. Therefore, the FOR LOOP statement outputs team(i) only when team.EXISTS(i) is TRUE.