dokuwiki

PROCEDURE PC_CURSOR_FOR
    ( 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
    dbms_output.put_line('Alle '||dep_name||' Angestellten:');

    for erg_record in c_get_emp loop
        dbms_output.put_line(c_get_emp%rowcount||' '||erg_record.last_name||' '||erg_record.first_name||' '||erg_record.department_name||' '||erg_record.job_title);
    end loop;
    
END; -- Procedure