The COUNT method returns the number of elements in the collection, ignoring deleted elements (even if their placeholders are retained by the DELETE operation).
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Usage with a varray
For a varray, COUNT is always equal to LAST. If you use the EXTEND or TRIM method to change the size of the varray, the value of COUNT changes.
obclient> DECLARE
TYPE oblist IS VARRAY(10) OF INTEGER;
t oblist := OBLIST(2,4,6,8,10);
PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('t.COUNT = ' || t.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('t.LAST = ' || t.LAST);
END print_count_and_last;
BEGIN
print_count_and_last;
t.EXTEND(2);
print_count_and_last;
t.TRIM(4);
print_count_and_last;
END;
/
Query OK, 0 rows affected
t.COUNT = 5, t.LAST = 5
t.COUNT = 7, t.LAST = 7
t.COUNT = 3, t.LAST = 3
The preceding example declares a varray variable, initializes it with four elements, and then executes EXTEND(3) and TRIM(5). It then outputs the COUNT and LAST values of the varray.
Usage with a nested table
For a nested table, COUNT is equal to LAST unless elements are deleted from the middle of the table, in which case COUNT is less than LAST.
obclient> DECLARE
TYPE oblist IS TABLE OF INTEGER;
t oblist := OBLIST(2,4,6,8,10);
PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('t.COUNT = ' || t.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('t.LAST = ' || t.LAST);
END print_count_and_last;
BEGIN
print_count_and_last;
t.EXTEND(2); --Adds two NULL elements to the end
print_count_and_last;
t.DELETE(3); --Deletes the third element
print_count_and_last;
FOR i IN 1..8 LOOP
IF t.EXISTS(i) THEN
IF t(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('t(' || i || ') = ' || t(i));
ELSE
DBMS_OUTPUT.PUT_LINE('t(' || i || ') = NULL');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('t(' || i || ') does not exist');
END IF;
END LOOP;
END;
/
Query OK, 0 rows affected
t.COUNT = 5, t.LAST = 5
t.COUNT = 7, t.LAST = 7
t.COUNT = 6, t.LAST = 7
t(1) = 2
t(2) = 4
t(3) does not exist
t(4) = 8
t(5) = 10
t(6) = NULL
t(7) = NULL
t(8) does not exist
The preceding example declares a nested table variable, initializes it with five elements, adds two NULL elements to the end, and deletes the third element. It then outputs the COUNT and LAST values of the nested table. Finally, it outputs the status of elements 1 through 8 in the example.
The example declares a nested table variable, initializes it with five elements, then appends two null elements at the end and deletes the third element, and shows the `COUNT` and `LAST` values of the nested table. Finally it outputs the state of elements 1 through 8.