BI

ORACLE

PLSQL

---

》》金融银行大数据报表开发 --存储过程：

/ *
对给定部门t作为翰入参数)的员工进行加薪操作，若其到公司的时间在期间，为其加薪;

（95  +0.05     98 +0.03    其他的 0.01）
得到以下返回结果:为此次加薪公司每月需要额外付出多少成本(定义一个ouT型的输出参数)．*/
 

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 employees

                                       where department_id = dept_1d;

        v_i number (4,2):=0;
begin
temp_sal :=o;
for c in sal_cureor

        loop
                if to_char (c.hire_ date, ' vywvy') < '1995' then vi :=0.05;

                elsif to_char (c.hire_date, ' yvvy') < '1998'then v i := 0.03;

                else v_i:= o.o1;

                end if;
        --1.更新工资
        update emplayees set salary - salary * (1 + v_i) where employee_id = c.employee_id;

        --2.付出的成本
        temp_sal :-=tenP_sal +c.salary * v_i;

        end loop;

dbms_output.pue_line(temp_sal) ;

end;

---