Oracle PL/SQL/PL SQL/CARDINALITY — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 09:59, 26 мая 2010
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>
Count only the unique values by combining the CARDINALITY and SET operators
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>