存储过程简单示例，oracle

问题描述：

自定义一个存储过程完成以下操作: 

对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (~ , 95) 期间,    为其加薪 %5；

                                                               (~ , 95)               %5
                                                               [95 , 98)            %3       
                                                               [98, ~)                %1                                                  
问题: 为此次加薪公司每月需要额外付出多少成本？

建表语句和数据：

create table EMPLOYEES
(
  employee_id    NUMBER(6) not null,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25),
  email          VARCHAR2(25),
  phone_number   VARCHAR2(20),
  hire_date      DATE,
  job_id         VARCHAR2(10),
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4)
);
insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (195, '2.2.99.0.408312', 'Jones_Tom', 'VJONES', '650.501.4876', to_date('17-03-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2940.00, null, 123, 50);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (170, '2.2.99.0.408383', 'Fox', 'TFOX', '011.44.1343.729268', to_date('24-01-1998', 'dd-mm-yyyy'), 'SA_REP', 12147.66, 0.20, 148, 80);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (250, 'aa', 'bo', 'bb@qq.com', null, to_date('04-11-2020 19:45:46', 'dd-mm-yyyy hh24:mi:ss'), '1', null, null, null, null);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (100, '2.2.99.0.408313', 'King', 'SKING', '515.123.4567', to_date('17-06-1987', 'dd-mm-yyyy'), 'AD_PRES', 24250.00, null, null, 90);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (101, '2.2.99.0.408314', 'Kochhar', 'NKOCHHAR', '515.123.4568', to_date('21-09-1989', 'dd-mm-yyyy'), 'AD_VP', 17170.00, null, 100, 90);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (102, '2.2.99.0.408315', 'De Haan', 'LDEHAAN', '515.123.4569', to_date('13-01-1993', 'dd-mm-yyyy'), 'AD_VP', 17170.00, null, 100, 90);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (103, '2.2.99.0.408316', 'Hunold', 'AHUNOLD', '590.423.4567', to_date('03-01-1990', 'dd-mm-yyyy'), 'IT_PROG', 9270.00, null, 102, 60);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (104, '2.2.99.0.408317', 'Ernst', 'BERNST', '590.423.4568', to_date('21-05-1991', 'dd-mm-yyyy'), 'IT_PROG', 6180.00, null, 103, 60);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (105, '2.2.99.0.408318', 'Austin', 'DAUSTIN', '590.423.4569', to_date('25-06-1997', 'dd-mm-yyyy'), 'IT_PROG', 5040.00, null, 103, 60);

insert into employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (106, '2.2.99.0.408319', 'Pataballa', 'VPATABAL', '590.423.4560', to_date('05-02-1998', 'dd-mm-yyyy'), 'IT_PROG', 5040.00, null, 103, 60);

存储过程：

create or replace procedure pay_rise_procedure(deptid in number,total out number) is
cursor rise_cursor is select t.employee_id,t.hire_date,t.salary from employees t where t.department_id=deptid;
v_rate number(4,2):=0;
begin
  total:=0;
   for emp in rise_cursor loop
     if to_char(emp.hire_date)<'1995' then  v_rate:=0.05;
     elsif to_char(emp.hire_date)<'1998' then v_rate:=0.03;
     else v_rate:=0.01;
       end if;
       update employees t set t.salary=t.salary*(1+v_rate) where t.employee_id=emp.employee_id;
       total:=total+emp.salary*v_rate;
     end loop;
     commit;
end pay_rise_procedure;

测试程序：

-- Created on 2020/11/18 by FUBOWEN 
declare 
  -- Local variables here
 v_total number(10,2);
begin
  -- Test statements here
  pay_rise_procedure(80,v_total);
  dbms_output.put_line(v_total);
end;