New Feature for collections in Oracle 11
Starting with Oracle 11 it is now possible to use set operators on “some” collection types (ie varrays and nested tables, NOT index by Tables).
A simple example:
PROCEDURE COLL_SET_OP
IS
cursor c_emp(c_p_salary number) is
select first_name, last_name, salary from employees
where salary > c_p_salary;
type t_emp is table of varchar2(50);
a1_emp t_emp := t_emp();
a2_emp t_emp := t_emp();
a_emp_op t_emp := t_emp();
i number := 0;
BEGIN
for r in c_emp(5000) loop
i := i+1;
a1_emp.extend;
a1_emp(i) := r.last_name;
end loop;
i:=0;
for r in c_emp(10000) loop
i := i+1;
a2_emp.extend;
a2_emp(i) := r.last_name;
end loop;
dbms_output.put_line(cardinality(a1_emp));
dbms_output.put_line(cardinality(a2_emp));
a_emp_op := a1_emp multiset except a2_emp;
i:=a_emp_op.first;
while (i is not null) loop
dbms_output.put_line(i ||' '||
a_emp_op(i));
i:=a_emp_op.next(i);
end loop;
END;
there are operators to mimic all SQL-set operators (union, minus, intersect). Its not exaclty a feature in PLSQL I’ve been anxiously waiting for, but there may be some uses for it. Please keep in mind that it won’t work for “index by varchar2” tables which would make it a lot more usefull.
Posted: November 8th, 2009 under Oracle, PLSQL, SQL Beispiele.