dokuwiki

This is an old revision of the document!

---

PROCEDURE COLL_SET_OP
   IS
   cursor c_emp(c_p_salary number) is
   select first_name, last_name, salary from emp
   where salary > c_p_salary;
   
 --  type t_emp is varray(1000) of varchar2(50); --c_emp%rowtype;
  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;

   --fetch c_emp bulk collect into a1_emp;
   --fetch c_emp(c_p_salary => 10000) bulk collect into a2_emp;
   
   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; -- Procedure