Differences

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

--- kurs:plsql_tables [2014/04/08 13:17]
mh created
+++ kurs:plsql_tables [2015/04/09 15:38] (current)
mh
@@ Line 1: / Line 1: @@
+== PLSQL Table übergeben ==
+<code>
+--------------------------------------------------------
+--  DDL for Package PCK_EMPLOYEES
+--------------------------------------------------------
+  CREATE OR REPLACE PACKAGE "HR"."PCK_EMPLOYEES" AS
+cursor c_emp_dep(cp_department_name varchar2) is
+  select employee_id,
+         first_name,
+         last_name,
+         salary,
+         department_name
+    from employees e join departments d
+      on e.department_id = d.department_id
+      where department_name = cp_department_name;
+type type_emp_dep is table of c_emp_dep%rowtype
+  index by binary_integer;
+procedure get_emp_per_dep_name(
+  p_department_name in departments.department_name%type,
+  t_emp_dep out type_emp_dep);
+procedure print_emp_dep(t_emp_dep in type_emp_dep);
+/*
+procedure get_emp_per_dep_name (p_department_name in
+type_emp_dep out von einem zu definierenden table type
+procedure print_emp_dep(type_emp_dep in)
+)
+*/
+procedure next_emp;
+END PCK_EMPLOYEES;
+/
+</code>
 zufällige, nicht doppelte Employees ziehen
@@ Line 65: / Line 112: @@
 END PLSQL_TABLE;
 </code>
+=== add & delete education ===
+<code>
+CREATE OR REPLACE PROCEDURE ADD_EDUCATION
+(
+  P_PER_NAME IN VARCHAR2
+, P_NEW_EDUCATION IN VARCHAR2
+) AS
+  v_edu education := education();
+  v_index number;
+  v_add_edu education := education(P_new_EDUCATION);
+BEGIN
+  select coalesce(per_education, education()) into v_edu
+    from persons where per_name = p_per_name;
+  /*
+  v_edu.extend;
+  v_edu(v_edu.last) := p_new_education;
+  */
+  v_edu := v_edu multiset union all v_add_edu;
+  update persons set per_education = v_edu
+    where per_name = p_per_name;
+END ADD_EDUCATION;
+ /*v_edu.extend;
+  v_edu(v_edu.last) := 'test1';
+  v_edu.extend;
+  v_edu(v_edu.last) := 'test2';
+  v_edu.extend;
+  v_edu(v_edu.last) := 'test3';
+  dbms_output.put_line(v_edu.first||' '|| v_edu.last);
+  for i in v_edu.first .. v_edu.last loop
+    dbms_output.put_line(i||' '|| v_edu(i));
+  end loop;
+  v_index := v_edu.first;
+  loop
+    dbms_output.put_line(v_index||' '|| v_edu(v_index));
+    v_index := v_edu.next(v_index);
+    exit when v_index is null;
+  end loop;
+  */
+</code>
+<code>
+CREATE OR REPLACE PROCEDURE DELETE_EDUCATION
+(
+  P_PER_NAME IN VARCHAR2
+, P_DEL_EDUCATION IN VARCHAR2
+) AS
+  v_edu education := education();
+  v_index number;
+  v_del_edu education := education(P_DEL_EDUCATION);
+BEGIN
+  select coalesce(per_education, education()) into v_edu
+    from persons where per_name = p_per_name;
+  /* v_index := v_edu.first;
+  loop
+    dbms_output.put_line(v_index||' '|| v_edu(v_index));
+    if  v_edu(v_index) = P_DEL_EDUCATION then
+      v_edu.delete(v_index);
+    end if;
+    v_index := v_edu.next(v_index);
+    exit when v_index is null;
+  end loop;
+  */
+  v_edu := v_edu multiset except v_del_edu;
+   update persons set per_education = v_edu
+    where per_name = p_per_name;
+END DELETE_EDUCATION;
+</code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools