oracle-存储过程实例

<span style="font-size:18px;color:#FF0000;">--定义一个存储过程，获取给定部门工资总和，（通过out参数）要求：部门号和工资总额定义为参数</span>
 create or replace produre get_sal2(dept_id number,sumsal out number)
is
 cursor salarya is select salary from employees where department_id=dept_id;
 begin
 sumsal:=0;
 for c in salarya loop
 sumsal :=sumsal+c.salary;
 end loop;
 dbms_output.put_line(sumsal);
 end; 

//调用

declare

v_sal number(10):=0;

begin

get_sal2(80,v_sal);

end;

create or replace procedure add_sal(dept_id number,temp_sal out number)

is
cursor sal_cursor is select employee_id,salary,hire_date from employee where
 department_id=dept_id;
 
  v_i number(4,2):=0;
 
  begin
  temp_sal:=0;
   for c in sal_cursor loop
    if to_char(c.hire_date,'yyyy')<'1995' then v_i:=0.05;
    elsif to_char(c.hire_date,'yyyy')<'1998' then v_i:=0.03;
    else v_i:=0.01;
    end if;
    --1.更新工资
    update employees set salary=salay*(1+v_i) where employee_id=c.employee_id;
    
    --2.付出的成本
    
    temp_sal:=temp_sal+c.salay*v_i;
    end loop;
    end;

//调用

declare v_temp number(10):=0;

begin

  add_sal(80,v_temp);

end;