This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kurs:cursor_uebersicht [2008/10/29 14:58] mh |
kurs:cursor_uebersicht [2010/05/12 15:20] mh |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | === cursor mit rowtype === | ||
+ | <code> | ||
+ | declare | ||
+ | cursor c_bonus is | ||
+ | select * from bonus order by salary desc; | ||
+ | | ||
+ | r_bonus bonus%rowtype; | ||
+ | | ||
+ | v_old_salary bonus.salary%type; | ||
+ | v_diff number; | ||
+ | begin | ||
+ | open c_bonus; | ||
+ | loop | ||
+ | fetch c_bonus into r_bonus; | ||
+ | exit when not c_bonus%found; | ||
+ | dbms_output.put_line(c_bonus%rowcount ||'. '||r_bonus.first_name ||' ' | ||
+ | || r_bonus.last_name ||' '|| r_bonus.salary | ||
+ | || ' diff ' || to_char((r_bonus.salary - v_old_salary))*-1); | ||
+ | v_old_salary := r_bonus.salary; | ||
+ | end loop; | ||
+ | close c_bonus; | ||
+ | end; | ||
+ | </code> | ||
+ | |||
+ | === for cursor === | ||
+ | <code> | ||
+ | declare | ||
+ | cursor c_bonus is | ||
+ | select * from bonus order by salary desc; | ||
+ | | ||
+ | v_old_salary bonus.salary%type; | ||
+ | v_diff number; | ||
+ | begin | ||
+ | for r_bonus in c_bonus loop | ||
+ | dbms_output.put_line(c_bonus%rowcount ||'. '||r_bonus.first_name ||' ' | ||
+ | || r_bonus.last_name ||' '|| r_bonus.salary | ||
+ | || ' diff ' || to_char((r_bonus.salary - v_old_salary))*-1); | ||
+ | v_old_salary := r_bonus.salary; | ||
+ | end loop; | ||
+ | end; | ||
+ | </code> | ||
+ | |||
+ | === cursor mit index by varchar2 table === | ||
+ | <code> | ||
+ | declare | ||
+ | cursor c_bonus is | ||
+ | select first_name||' '||last_name as fn, salary | ||
+ | from bonus order by salary desc; | ||
+ | | ||
+ | type person_type_table is table of number | ||
+ | index by varchar2(100); | ||
+ | |||
+ | t_person person_type_table; | ||
+ | i varchar2(100); | ||
+ | r_bonus c_bonus%rowtype; | ||
+ | | ||
+ | begin | ||
+ | |||
+ | for r_bonus in c_bonus loop | ||
+ | t_person(r_bonus.fn) := r_bonus.salary; | ||
+ | end loop; | ||
+ | | ||
+ | i := t_person.first; | ||
+ | while (i is not null) loop | ||
+ | dbms_output.put_line(i||' verdient '||t_person(i)); | ||
+ | i := t_person.next(i); | ||
+ | end loop; | ||
+ | | ||
+ | end; | ||
+ | </code> | ||
+ | |||
+ | === Implizite Cursor === | ||
+ | <code> | ||
+ | PROCEDURE hr.implicit_cursor | ||
+ | |||
+ | IS | ||
+ | BEGIN | ||
+ | for r_emp in (select * from employees) loop | ||
+ | dbms_output.put_line(r_emp.last_name); | ||
+ | end loop; | ||
+ | END; -- Procedure | ||
+ | </code> | ||
+ | |||
+ | < | ||