Differences

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

--- kurs:stored_procedures [2008/11/05 12:46]
mh created
+++ 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>

dokuwiki

User Tools

Site Tools

Differences

Page Tools