dokuwiki

PROCEDURE EMP_PER_DEP
   IS

-- durch den aufbau der where clause koenne entweder ALLE
-- oder nur ein bestimmtes Department gelesen werden

   cursor c_emp_per_dep(p_dep_name varchar2) is 
   SELECT   departments.department_name, employees.first_name,
         employees.last_name
    FROM departments, employees
   WHERE (departments.department_id = employees.department_id)
   and ((department_name = p_dep_name and p_dep_name is not null)
   or  (p_dep_name is null))   
ORDER BY departments.department_name DESC; 

BEGIN
   for r_emp_per_dep in c_emp_per_dep(null) loop
     dbms_output.put_line(r_emp_per_dep.department_name || ' ' ||
                          r_emp_per_dep.first_name || ' ' ||
                          r_emp_per_dep.last_name);
   end loop;
END; -- Procedure