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> | <code> | ||
CREATE OR REPLACE PROCEDURE EMP_PER_DEP AS | CREATE OR REPLACE PROCEDURE EMP_PER_DEP AS |