In Oracle 10g it made easy to filter out duplicate data from pl/sql table or nested table. This is possible in 10g by using "MULTISET" key word.
Following is the example which eliminates duplicate values from the PLSQL table...
OUTPUT
========
Cities BEFORE DISTINCT..
------------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW YORK
NEW YORK
DELHI
PERTH
MADURAI
.
Cities AFTER DISTINCT..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
DELHI
MADURAI
PL/SQL PROCEDURE successfully completed.
Following is the example which eliminates duplicate values from the PLSQL table...
DECLARE
TYPE nested_typ IS TABLE OF VARCHAR2(200);
v_test_type nested_typ := nested_typ();
v_temp nested_typ ;
BEGIN
v_test_type.extend(9);
-- read values into array.
v_test_type(1) := 'NEW YORK ';
v_test_type(2) := 'SYDNEY ';
v_test_type(3) := 'SINGAPORE ';
v_test_type(4) := 'PERTH ';
v_test_type(5) := 'NEW YORK ';
v_test_type(6) := 'NEW YORK ';
v_test_type(7) := 'DELHI ';
v_test_type(8) := 'PERTH ';
v_test_type(9) := 'MADURAI ';
dbms_output.put_line('Cities before distinct..');
-- display values before distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
dbms_output.put_line(v_test_type(i));
ENDLOOP ;
-- assign all values to v_temp, which are in v_test_type
v_temp := v_test_type ;
-- take ditinct values out of v_temp, v_test_type into v_test_type
v_test_type := v_test_type MULTISET UNION DISTINCT v_temp ;
dbms_output.put_line('Cities after distinct..');
-- display values after distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
dbms_output.put_line(v_test_type(i));
ENDLOOP ;
END ;
/
TYPE nested_typ IS TABLE OF VARCHAR2(200);
v_test_type nested_typ := nested_typ();
v_temp nested_typ ;
BEGIN
v_test_type.extend(9);
-- read values into array.
v_test_type(1) := '
v_test_type(2) := '
v_test_type(3) := '
v_test_type(4) := '
v_test_type(5) := '
v_test_type(6) := '
v_test_type(7) := '
v_test_type(8) := '
v_test_type(9) := '
dbms_output.put_line('Cities before distinct..');
-- display values before distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
dbms_output.put_line(v_test_type(i));
END
-- assign all values to v_temp, which are in v_test_type
v_temp := v_test_type ;
-- take ditinct values out of v_temp, v_test_type into v_test_type
v_test_type := v_test_type MULTISET UNION DISTINCT v_temp ;
dbms_output.put_line('Cities after distinct..');
-- display values after distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
dbms_output.put_line(v_test_type(i));
END
END ;
/
OUTPUT
========
------------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW YORK
NEW YORK
DELHI
PERTH
MADURAI
.
Cities AFTER DISTINCT..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
DELHI
MADURAI
PL/SQL PROCEDURE successfully completed.
No comments:
Post a Comment