User Tools

Site Tools


kurs:cursor_features

Differences

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

Link to this comparison view

kurs:cursor_features [2014/09/10 21:22] (current)
Line 1: Line 1:
 +<​code>​
 +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
 +</​code>​
kurs/cursor_features.txt · Last modified: 2014/09/10 21:22 (external edit)