Oracle PL/SQL/PL SQL/CARDINALITY — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 12:59, 26 мая 2010
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>