Oracle PL/SQL/PL SQL/CARDINALITY

Материал из SQL эксперт
Перейти к: навигация, поиск

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>
   
  


Count only the unique values by combining the CARDINALITY and SET operators

   <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(SET(a)));
 5  END;
 6  /

4 PL/SQL procedure successfully completed. SQL> SQL>



 </source>