User Tools

Site Tools


kurs:cursor_uebersicht

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
kurs:cursor_uebersicht [2008/10/29 13:42]
mh
kurs:cursor_uebersicht [2014/09/10 21:22] (current)
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>​
 + 
 +<