This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
kurs:stored_procedures [2008/11/05 12:47] mh |
kurs:stored_procedures [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | PROCEDURE SHOW_EMPLOYEES | ||
+ | (p_min_sal IN employees.salary%type) is | ||
+ | begin | ||
+ | for r_emp in | ||
+ | (select first_name, last_name from employees | ||
+ | where salary > p_min_sal) loop | ||
+ | dbms_output.put_line(r_emp.first_name||' '|| | ||
+ | r_emp.last_name); | ||
+ | end loop; | ||
+ | end; | ||
+ | </code> | ||
+ | <code> | ||
+ | PROCEDURE RAISE_SALARY | ||
+ | ( p_emp_id IN number, | ||
+ | p_raise IN number) | ||
+ | IS | ||
+ | |||
+ | BEGIN | ||
+ | update bonus set salary = salary + p_raise | ||
+ | where employee_id = p_emp_id; | ||
+ | if SQL%notfound then | ||
+ | raise_application_error | ||
+ | (-20010, 'no such emp_id '||p_emp_id ); | ||
+ | end if; | ||
+ | | ||
+ | /* exception | ||
+ | when e_no_emp then | ||
+ | dbms_output.put_line('Fehler >' | ||
+ | || SQLCODE ||'< ' | ||
+ | || SQLERRM ); */ | ||
+ | |||
+ | END; -- Procedure | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | PROCEDURE CHANGE_SALARY_EMPLOYEES | ||
+ | (p_min_sal IN employees.salary%type, | ||
+ | p_raise in number | ||
+ | ) is | ||
+ | begin | ||
+ | for r_emp in | ||
+ | (select employee_id, first_name, last_name from bonus | ||
+ | where salary > p_min_sal) loop | ||
+ | dbms_output.put_line(r_emp.first_name||' '|| | ||
+ | r_emp.last_name); | ||
+ | raise_salary(r_emp.employee_id, p_raise); | ||
+ | end loop; | ||
+ | end; | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | PROCEDURE RAISE_EMP_SAL | ||
+ | IS | ||
+ | |||
+ | e_no_emp exception; | ||
+ | | ||
+ | pragma exception_init | ||
+ | (e_no_emp, -20010); | ||
+ | |||
+ | BEGIN | ||
+ | begin | ||
+ | raise_salary(p_emp_id => 333, | ||
+ | p_raise => 1000); | ||
+ | exception | ||
+ | when e_no_emp then | ||
+ | dbms_output.put_line( | ||
+ | '>'|| SQLCODE ||'< ' | ||
+ | || SQLERRM ); | ||
+ | end; | ||
+ | | ||
+ | dbms_output.put_line('Alles Vorbei'); | ||
+ | |||
+ | END; -- Procedure | ||
+ | </code> |