Differences

This shows you the differences between two versions of the page.

--- kurs:nested_tables_und_collection_operator [2010/05/18 11:15]
127.0.0.1 external edit
+++ kurs:nested_tables_und_collection_operator [2011/11/16 14:59]
mh
@@ Line 1: / Line 1: @@
 <code>
-PROCEDURE COLL_SET_OP
+PROCEDURE hr.multiset
-   IS
+is
-   cursor c_emp(c_p_salary number) is
+cursor c_emp_salary_lt(cp_min_salary employees.salary%type) is
-   select first_name, last_name, salary from emp
+select first_name||' '||last_name||' '||salary
-   where salary > c_p_salary;
+  from employees
+  where salary > cp_min_salary;
- --  type t_emp is varray(1000) of varchar2(50); --c_emp%rowtype;
-  type t_emp is table of varchar2(50);
+-- nested table
+type t_emp is table of varchar2(100);
-   a1_emp t_emp := t_emp();
-   a2_emp t_emp := t_emp();
+emp1 t_emp;
-   a_emp_op t_emp := t_emp();
+emp2 t_emp;
-   i number := 0;
+emp_multi t_emp;
 BEGIN
-   for r in c_emp(5000) loop
-     i := i+1;
-     a1_emp.extend;
-     a1_emp(i) := r.last_name;
-   end loop;
-   i:=0;
+open c_emp_salary_lt(5000);
-   for r in c_emp(10000) loop
+fetch c_emp_salary_lt bulk collect into emp1;
-     i := i+1;
+close c_emp_salary_lt;
-     a2_emp.extend;
-     a2_emp(i) := r.last_name;
+open c_emp_salary_lt(15000);
-   end loop;
+fetch c_emp_salary_lt bulk collect into emp2;
+close c_emp_salary_lt;
+-- multiset funktioniert NUR mit nested table
+emp_multi := emp1 multiset intersect emp2;
-   --fetch c_emp bulk collect into a1_emp;
+for c in 1..emp_multi.last loop
-   --fetch c_emp(c_p_salary => 10000) bulk collect into a2_emp;
+  dbms_output.put_line(emp_multi(c));
+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; -- Procedure
 </code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools