Oracle PL/SQL Tutorial/Collections/CARDINALITY

CARDINALITY Operator

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

   <source lang="sql">

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></source>

CARDINALITY operator lets count the elements in a collection.

   <source lang="sql">

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.</source>

CARDINALITY table collection

   <source lang="sql">

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></source>