Differences

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

--- kurs:cursor_employees_per_department [2014/11/03 15:34]
mh created
+++ kurs:cursor_employees_per_department [2016/05/12 15:56] (current)
mh
@@ Line 1: / Line 1: @@
+<code>
+create or replace
+PROCEDURE
+ P_GET_EMP_BY_DEPARTMENT_NAME(
+ p_department_name departments.department_name%type)
+-- AUTHID CURRENT_USER
+AS
+/* uebergabe eines department_name
+   ausgabe first_/last_name aller employees des
+   departments
+*/
+cursor c_emp_by_departement_name(
+  pc_department_name departments.department_name%type)
+  is
+  select first_name,
+         last_name
+    from employees e join
+         departments d on
+         e.department_id = d.department_id
+    where department_name = pc_department_name;
+i number := 0;
+e_department_empty exception;
+pragma exception_init(e_department_empty, -20000);
+e_no_department_found exception;
+pragma exception_init(e_no_department_found, -20001);
+BEGIN
+  delete from employees_temp;
+  select count(department_id) into i
+    from departments
+   where department_name = p_department_name;
+  if i = 0 then
+    raise_application_error
+       (-20001, 'no department found');
+  end if;
+  i := 0;
+  for r_emp in c_emp_by_departement_name
+    (p_department_name)
+  loop
+    i := i + 1;
+    insert into employees_temp values r_emp;
+  end loop;
+  if i < 1 then
+    raise_application_error
+       (-20000, 'no employee found');
+    -- raise e_department_empty;
+  end if;
+  dbms_output.put_line(
+    to_char(sysdate, 'yyyy-mm-ddd hh24:mi:ss')
+    );
+  /* bulk collect geht nicht eine Tabelle
+  open c_emp_by_departement_name(p_department_name);
+  fetch c_emp_by_departement_name
+    bulk collect into employees_temp;
+  */
+END;
+</code>
 <code>
 CREATE OR REPLACE PROCEDURE EMP_PER_DEP AS

dokuwiki

User Tools

Site Tools

Differences

Page Tools