User Tools

Site Tools


kurs:stored_procedures
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;
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
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;
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
kurs/stored_procedures.txt · Last modified: 2014/09/10 21:22 (external edit)