User Tools

Site Tools


kurs:uebung_1

Differences

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

Link to this comparison view

kurs:uebung_1 [2014/09/10 21:22] (current)
Line 1: Line 1:
 +== Aufgabenstellung ==
 +Erstelle einen Cursor, der die ersten 5 Mitarbeiter (alphabethisch aufsteigend sortiert) \\
 +eines Departments,​ das durch den User eingegeben wird, ausgibt.
 +== Code ==
 +<code php>
 +PROCEDURE P_CURSOR_FEATURES
 +  (sel_dep in varchar2) ​ /* erm√∂glicht die Eingabe der Abteilung */
 +  IS
 +  TYPE dep_rec_type IS RECORD (
 +    f_name ​     employees.first_name%TYPE,​
 +    l_name ​     employees.last_name%TYPE,​
 +    department ​ departments.department_name%TYPE,​
 +    title       ​jobs.job_title%TYPE
 +  );
 +  dep_rec dep_rec_type;​
 +  /* Anlegen des Cursors der mir alle Mitarbeiter
 +    des vorher eingegebenen Abteilung selektiert */
 +  cursor curs_Department is
 +    SELECT
 +      employees.first_name,​
 +      employees.last_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 (lower(departments.department_name) = lower(sel_dep))
 +    )
 +    ORDER BY employees.last_name ASC;
 +BEGIN
 +  open curs_Department;​
 +    /* Schleife die mir die ersten 5 Mitarbeiter ausgibt */
 +    for i in 1..5 loop
 +      fetch curs_Department into dep_rec ;
 +      dbms_output.put_line(
 +        dep_rec.f_name||'​ '||
 +        dep_rec.l_name||',​ '||
 +        dep_rec.title
 +      );
 +    end loop;
 +  close curs_Department;​
 +END;
 +</​code>​
 +== Ergebnis (sales eingegeben) ==
 +Ellen Abel, Sales Representative \\
 +Sundar Ande, Sales Representative \\
 +Amit Banda, Sales Representative \\
 +Elizabeth Bates, Sales Representative \\
 +David Bernstein, Sales Representative
  
kurs/uebung_1.txt · Last modified: 2014/09/10 21:22 (external edit)