Oracle的基本操作（二：存储过程）

 

1、编写一个存储过程，根据输入的工作类型，输入该工作的平均工资。

-- Created on 2018/9/30 by YANXUKUN

create or replace procedure avgsal(v_job in scott.emp.job%type)

is

avgsal2 number;

begin

  select avg(sal) into avgsal2 from scott.emp where job = v_job;

  dbms_output.put_line(v_job || '---'|| avgsal2);

end;

 

2、创建一个存储过程，以员工号为参数，输出该员工的工资；

create or replace procedure p_sxtl(v_empno in scott.emp.empno%type,v_sal out scott.emp.sal%type)

  is  begin

    select sal into v_sal from scott.emp where empno = v_empno;

end;

-- Created on 2018/9/30 by YANXUKUN

declare

   v_empno scott.emp.empno%type := 7369;

   v_sal scott.emp.sal%type;

   begin

     p_sxtl(v_empno,v_sal);

     dbms_output.put_line(v_empno || '员工工资为：'||v_sal);

     end;

 

3、创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；--若属于其他部门，则增加300。

create or replace procedure p_sxt2(v_empno in emp.empno%type)

is

v_deptno emp.deptno%type;

v_sal emp.sal%type;

Begin

  select deptno into v_deptno from emp where empno=v_empno;

  select sal into v_sal from emp where empno=v_empno;

  dbms_output.put_line(v_empno || '的部门是' || v_deptno || '修改前的工资是' || v_sal);

  case v_deptno

    when 10 then update emp set sal=sal+150 where empno=v_empno;

    when 20 then update emp set sal=sal+200 where empno=v_empno;

    when 30 then update emp set sal=sal+250 where empno=v_empno;

     else  update emp set sal=sal+300 where empno=v_empno;

end case;

select sal into v_sal from emp where empno=v_empno;

dbms_output.put_line(v_empno || '部门是' || v_deptno || '修改后的工资是：' || v_sal);

commit;

end;

调用存储过程：

begin

  p_sxt2(7369);

end;

4、创建一个函数，以员工号为参数，返回该员工的工资。

create or replace function f_gongzi(v_empno scott.emp.empno%type)

    return scott.emp.sal%type is

    vr_sal scott.emp.sal%type;

    begin

      select sal into vr_sal from scott.emp where empno = v_empno;

      return vr_sal;

end;

5、创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资

create or replace function avegsal(v_empno in emp.empno%type)

  return emp.sal%type

  as

  avgsal2 emp.sal%type;

begin

  -- Test statements here

  select avg(sal) into avgsal2 from emp where deptno = (select deptno from emp where empno=v_empno);

  return avgsal2;

end;

调用函数：

begin

   :result := avegsal(7369);

end;

6、创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。

--如果修改成功，则显示“员工由……号部门调入调入……号部门”；如果不存在该员工，则显示

--“员工号不存在，请输入正确的员工号。”；如果不存在该部门，则显示

--“该部门不存在，请输入正确的部门号。”。

create or replace procedure p_deptno(v_empno in emp.empno%type,v_deptno in emp.deptno%type)

as

vu_empno number := 0 ;

vu_deptno number :=0 ;

vm_deptno emp.deptno%type;

begin

  select deptno into vm_deptno from emp where empno=v_empno;

  select count(*) into vu_empno from emp where empno=v_empno;

  select count(distinct deptno) into vu_deptno from emp where deptno=v_deptno;

  if vu_empno=0 then dbms_output.put_line('员工号不存在，请输入正确的员工号.');

  end if;

  if vu_deptno=0 then dbms_output.put_line('该部门不存在，请输入正确的部门号.');

  end if;

  if vu_empno=1 and vu_deptno=1 then dbms_output.put_line('员工由' || vm_deptno || '号部门调入' || v_deptno || '号部门');

  update emp set deptno=v_deptno where empno=v_empno;

    commit;

  end if;

  end;

调用存储过程：

(1)begin

  p_deptno(7369,40);

end;

(2)begin

  p_deptno(7369,10);

end;