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

桃扇骨 2022-01-28 10:17 376阅读 0赞

### 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;

![20190523222524128.png][]

### 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;

![20190523222559847.png][]

### 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;

[20190523222524128.png]: /images/20220128/32ad7c4d3712417d8a2162dc0a5793b5.png
[20190523222559847.png]: /images/20220128/34dd2533663242efb2402ad43bf8725e.png