User Tools

Site Tools


kurs:cursor_employees_per_department

Differences

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

Link to this comparison view

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>​ <​code>​
 CREATE OR REPLACE PROCEDURE EMP_PER_DEP AS  CREATE OR REPLACE PROCEDURE EMP_PER_DEP AS 
kurs/cursor_employees_per_department.txt ยท Last modified: 2016/05/12 15:56 by mh