dokuwiki

PROCEDURE PC_DEPARTMENT_EMP_SELECT
    ( dep_name IN departments.department_name%TYPE )
   IS
    cursor c_get_emp is
        SELECT   employees.last_name, employees.first_name,
            departments.department_name, jobs.job_title
        FROM employees, departments, jobs
        WHERE (    (departments.department_id = employees.department_id)
            AND (jobs.job_id = employees.job_id)
            AND (departments.department_name = dep_name)
            )
        ORDER BY employees.last_name ASC, employees.first_name ASC;

    type erg is record (
        last_name employees.last_name%TYPE,
        first_name employees.first_name%TYPE,
        department_name departments.department_name%TYPE,
        job_title jobs.job_title%TYPE);
        
    erg_record erg;

BEGIN
    open c_get_emp;

    dbms_output.put_line('Alle '||dep_name||' Angestellten:');

    loop
        fetch c_get_emp into erg_record;
        exit when c_get_emp%NOTFOUND;
        dbms_output.put_line(erg_record.last_name||' '||erg_record.first_name||' '||erg_record.department_name||' '||erg_record.job_title);
    end loop;
    if (c_get_emp%rowcount = 0) then
        dbms_output.put_line('Kein Datensatz gefunden!!');
    else
        dbms_output.put_line(c_get_emp%rowcount||' Datensätze gefunden!!');
    end if;

    close c_get_emp;

END; -- Procedure