PLSQL练习-数据共享与整合技术

忘是亡心i 2022-01-26 04:17 339阅读 0赞

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

**命令如下：**

**创建存储过程：**

create or replace procedure avgsal(v_job in emp.job%type)
    is
    avgsal2 number
    begin
    select avg(sal) into avgsal2 from emp where job=v_job;
    dbms_output.put_line(v_job || '---' || avgsal2)
    end;

**调用存储过程：**

Begin
    avgsal('MANAGER');
    end;

**程序截图：**

**![20190528081435665.jpeg][]**

**![20190528081435662.jpeg][]**

**运行结果：**

**![20190528081435670.jpeg][]**

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

**命令如下：**

**创建存储过程：**

create or replace procedure p_sxtl(v_empno in emp.empno%type,v_sal out emp.sal%type)
    is 
    begin
    select sal into v_sal from emp where empno=v_empno;
    end;

**调用存储过程：**

declare 
    v_empno emp.empno%type :=7369;
    v_sal emp.sal%type;
    begin
    p_sxtl(v_empno,v_sal);
    dbms_output.put_line(v_empno || '员工的工资为：' || v_sal);
    end;

**程序截图：**

**![20190528081546150.jpeg][]**

**![20190528081546390.jpeg][]**

**运行结果：**

**![20190528081546362.jpeg][]**

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

**程序截图：**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTMyOTc1OA_size_16_color_FFFFFF_t_70][]**

**![20190528081644534.jpeg][]**

**运行结果：**

**![20190528081644668.jpeg][]**

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

**命令如下：**

**创建函数：**

create or replace function f_sxtl(v_empno in emp.empno%type,v_sal out emp.sal%type)
    return emp.sal%type
    as 
    begin
    select sal into v_sal from emp where empno=v_empno;
    return v_sal;
    end;

**调用函数：**

Begin
    :result := f_sxtl(7369,v_sal => :v_sal);
    end;

**程序截图：**

**![20190528081729226.jpeg][]![20190528081729143.jpeg][]**

**运行结果：**

**![20190528081729161.png][]**

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

**命令如下：**

**创建函数:**

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;

**程序截图：**

**![20190528081830471.jpeg][]**

**![20190528081830202.jpeg][]**

**运行结果：**

**![20190528081830218.png][]**

**5、创建一个存储过程，以员工号和部门号作为参数，修改员工所在的部门为所输入的部门号。如果修改成功，则显示“员工由……号部门调入调入……号部门”；如果不存在该员工，则显示“员工号不存在，请输入正确的员工号。”；如果不存在该部门，则显示“该部门不存在，请输入正确的部门号。”**

**命令如下：**

**创建存储过程：**

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;

**程序截图：**

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTMyOTc1OA_size_16_color_FFFFFF_t_70 1][]**

**![20190528081923104.jpeg][]**

**(2)**

**![20190528081923321.jpeg][]**

**运行结果：**

**(1)**

**![20190528081923449.jpeg][]**

**(2)**

**![20190528081923535.jpeg][]**

[20190528081435665.jpeg]: /images/20220126/e7e157f4f4d44c789cd669134ebeef74.png
[20190528081435662.jpeg]: /images/20220126/95861327020940e5899461f502da5c92.png
[20190528081435670.jpeg]: /images/20220126/333fc082e3364189877850e5082b99aa.png
[20190528081546150.jpeg]: /images/20220126/f47ed902e1d54f3ea8685dc09792d573.png
[20190528081546390.jpeg]: /images/20220126/f4da5c5af34949c68b4b031002f58b79.png
[20190528081546362.jpeg]: /images/20220126/34398c626ed242bea1df97dbf2185c7b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTMyOTc1OA_size_16_color_FFFFFF_t_70]: /images/20220126/ce8efde700ee427ab3d9bf938e71c020.png
[20190528081644534.jpeg]: /images/20220126/61c361a626234022a12668dfe060cd3e.png
[20190528081644668.jpeg]: /images/20220126/e3530575450548858851a2f7bd3c9403.png
[20190528081729226.jpeg]: https://img-blog.csdnimg.cn/20190528081729226.jpeg
[20190528081729143.jpeg]: /images/20220126/690769e048c947ec877580f5314f03aa.png
[20190528081729161.png]: /images/20220126/519f8a0ade58454d8c154da3078cda82.png
[20190528081830471.jpeg]: /images/20220126/51de2dbc4a804282933e713aeb1f3e78.png
[20190528081830202.jpeg]: /images/20220126/3377e247adbf4a3f83bb3fb1fd4f789d.png
[20190528081830218.png]: /images/20220126/73723360c013436a9963d5acb473e8fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTMyOTc1OA_size_16_color_FFFFFF_t_70 1]: /images/20220126/89051e97d2234b61b61940f315cdb6d4.png
[20190528081923104.jpeg]: /images/20220126/e21150b9840a477da8270e9de9b705a7.png
[20190528081923321.jpeg]: /images/20220126/bcca4530edcf43e690c0b142892fcfb5.png
[20190528081923449.jpeg]: /images/20220126/e6ac9d17fe174a3f917bf96097efc365.png
[20190528081923535.jpeg]: /images/20220126/b1d0f27602b04fb4b92001e7fd553042.png