This shows you the differences between two versions of the page.
— |
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> |