Differences

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

--- kurs:table_als_tabellendatentyp [2010/05/19 13:36]
mh created
+++ kurs:table_als_tabellendatentyp [2014/09/10 21:22]
@@ Line 1: / Line 1: @@
-<code>
- CREATE OR REPLACE TYPE "HR"."EMP_LIST"
- AS TABLE OF VARCHAR2(100)
-</code>
-<code>
-CREATE TABLE DEP_EMP as select department_name from departments;
-alter table dep_emp add (emp_names emp_list)
-nested table emp_names store as nested_tab return as value;
-</code>
-<code>
-PROCEDURE fill_dep_emp
-   IS
-   TYPE emp_table_type IS TABLE OF emp_list
-      INDEX BY varchar2(100);
-   t_emp    emp_table_type;
-   v_index  varchar2(100);
-BEGIN
- for r_ed in (select department_name from dep_emp) loop
-   t_emp(r_ed.department_name) := emp_list();
- end loop;
- for r_ed in (SELECT FIRST_NAME||' '||LAST_NAME e_name, DEPARTMENT_NAME
-                     FROM DEPARTMENTS , EMPLOYEES
-                    WHERE ( (DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) ))
- loop
-   t_emp(r_ed.department_name).extend;
-   t_emp(r_ed.department_name)
-   (t_emp(r_ed.department_name).last):= r_ed.e_name;
- end loop;
-v_index := t_emp.first;
-loop
-  exit when v_index is null;
-  update dep_emp set emp_names = t_emp(v_index)
-  where department_name = v_index;
-  v_index := t_emp.next(v_index);
-end loop;
-END; -- Procedure
-</code>

dokuwiki