Oracle PL/SQL Tutorial/Collections/CARDINALITY

CARDINALITY Operator

CARDINALITY operator gets the number of elements in a nested table.

SQL>
SQL> CREATE OR REPLACE PROCEDURE cardinality_example AS
  2    TYPE nestedTableType IS TABLE OF VARCHAR2(10);
  3    myTable1 nestedTableType;
  4    cardinality_var INTEGER;
  5  BEGIN
  6    myTable1 := nestedTableType("F", "G", "S");
  7    cardinality_var := CARDINALITY(myTable1);
  8    DBMS_OUTPUT.PUT_LINE("cardinality_var = " || cardinality_var);
  9  END cardinality_example;
 10  /
Procedure created.
SQL> CALL cardinality_example();
cardinality_var = 3
Call completed.
SQL>
SQL>

CARDINALITY operator lets count the elements in a collection.

SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
  2  /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
  2    returnValue VARCHAR2(2000);
  3  BEGIN
  4      FOR i IN set_in.FIRST..set_in.LAST LOOP
  5        returnValue := set_in(i) ||" ";
  6      END LOOP;
  7    RETURN returnValue;
  8  END format_list;
  9  /
Function created.
SQL>
SQL> DECLARE
  2    a LIST := list(1,2,3,3,4,4);
  3  BEGIN
  4    dbms_output.put_line(CARDINALITY(a));
  5  END;
  6  /
6
PL/SQL procedure successfully completed.

CARDINALITY table collection

SQL> CREATE OR REPLACE PROCEDURE cardinality_example AS
  2    TYPE charTable IS TABLE OF VARCHAR2(10);
  3    emp1 charTable;
  4    cardinality_var INTEGER;
  5  BEGIN
  6    emp1 := charTable("A", "B", "C");
  7    cardinality_var := CARDINALITY(emp1);
  8    DBMS_OUTPUT.PUT_LINE("cardinality_var = " || cardinality_var);
  9  END cardinality_example;
 10  /
Procedure created.
SQL>