当前位置：JX BLOG > 数据库 > 正文

【oracle学习】8.PLSQL练习

  • 光仔December 发布于 2018-03-20
  • 分类：数据库
  • 来源：CSDN
  • 阅读(87)
  • 评论(0)

前言
我们以下的所有操作均在PL/Sql Developer工具上完成:

我们以下的表操作可能会基于以下两张表:
我们创建一个员工表和部门表:

员工信息表

[sql] view plain copy

  1. create table EMP(
  2.     EMPNO NUMBER,
  3.     ENAME VARCHAR2(10),
  4.     JOB VARCHAR2(9),
  5.     MGR NUMBER,
  6.     HIREDATE DATE,
  7.     SAL BINARY_DOUBLE,
  8.     COMM BINARY_DOUBLE,
  9.     DEPTNO NUMBER
  10. );

其中job是职位，mgr是该员工的上司的id，sal是工资，comm是提成，deptno是所属部门。

SQL> select * from emp;
EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
————— ——————– —————— ————— ————– —– —– —————
1110 张三                 主管                          1110 12-3月 -14      5200     0          20
1111 李四                 销售                          1116 03-11月-15      3400   500          30
1112 王五                 销售                          1116 25-4月 -12      4400   800          30
1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
1114 李磊磊               会计                          1110 22-12月-15      2500     0          50
1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30
1116 林建国               主管                          1116 22-1月 -16      5700     0          20
1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
1118 沈倩                 会计                          1116 06-5月 -10      2100     0          50

部门表

[sql] view plain copy

  1. create table dept(
  2.     DEPTNO NUMBER,
  3.     DNAME VARCHAR2(50)
  4. );

SQL> select * from dept t;

DEPTNO    DNAME
——–  ——–
20        管理部门
30        销售部门
40        后勤部门
50        金融部门

(1)实例1:统计每年入职的员工个数

可能用到的sql:
select to_char(hiredate,’yyyy’) from emp;

语句:

[sql] view plain copy

  1. declare
  2.   cursor cemp is select to_char(hiredate,‘yyyy’) from emp;
  3.   phiredate varchar2(4);
  4.   –计数器(2010-2016的入职人数统计)
  5.   count10 number := 0;
  6.   count11 number := 0;
  7.   count12 number := 0;
  8.   count13 number := 0;
  9.   count14 number := 0;
  10.   count15 number := 0;
  11.   count16 number := 0;
  12. begin
  13.   open cemp;
  14.   loop
  15.     –取一个数据
  16.     fetch cemp into phiredate;
  17.     exit when cemp%notfound;
  18.     –判断
  19.     if phiredate = ‘2010’ then count10:=count10+1;
  20.       elsif phiredate = ‘2011’ then count11:=count11+1;
  21.       elsif phiredate = ‘2012’ then count12:=count12+1;
  22.       elsif phiredate = ‘2013’ then count13:=count13+1;
  23.       elsif phiredate = ‘2014’ then count14:=count14+1;
  24.       elsif phiredate = ‘2015’ then count15:=count15+1;
  25.       else count16:=count16+1;
  26.     end if;
  27.   end loop;
  28.   close cemp;
  29.   –输出
  30.   dbms_output.put_line(‘total:’||(count10+count11+count12+count13+count14+count15+count16));
  31.   dbms_output.put_line(‘2010:’||count10);
  32.   dbms_output.put_line(‘2011:’||count11);
  33.   dbms_output.put_line(‘2012:’||count12);
  34.   dbms_output.put_line(‘2013:’||count13);
  35.   dbms_output.put_line(‘2014:’||count14);
  36.   dbms_output.put_line(‘2015:’||count15);
  37.   dbms_output.put_line(‘2016:’||count16);
  38. end;
  39. /

结果

(2)实例2:涨工资
为员工涨工资，从最低工资调起，每人涨10%，但工资总额不能超过5万元，
请计算涨工资的人数和涨工资后的工资总额，并输出涨工资人数和工资总额。

可能用到的sql:
select empno,sal form emp order by sal;
select sum(sal) from emp;

语句:

[sql] view plain copy

  1. declare
  2.   psal emp.sal%type;
  3.   pempno emp.empno%type;
  4.   s_sal emp.sal%type; –总工资数
  5.   counts number := 0;  –涨工资的人数
  6.   cursor cemp is select empno,sal from emp order by sal;
  7. begin
  8.   select sum(sal) into s_sal from emp;
  9.   open cemp;
  10.   loop
  11.    exit when s_sal+psal*0.1>50000;
  12.    fetch cemp into pempno,psal;
  13.    exit when cemp%notfound;
  14.    update emp set sal=sal+sal*0.1 where empno=pempno;
  15.    counts := counts+1;
  16.    s_sal:=s_sal+psal*0.1;
  17.   end loop;
  18.   close cemp;
  19.   dbms_output.put_line(‘涨工资人数:’||counts);
  20.   dbms_output.put_line(‘工资总额:’||s_sal);
  21. end;
  22. /

运行前:

运行后:


(3)实例3:统计工资段
用PL/SQL语言编写一程序，实现按部门分段(6000以上、(6000,3000)、3000元以下)
统计各工资段的职工人数、以及各部门的工资总额(工资总额不包括奖金)，参考如下格式:

可能用到的sql:
select sal form emp where deptno=??;
select sum(sal) from emp where deptno=??;

我们一共有5个部门。

语句:
方法1(没有用到带参数的光标)：

[sql] view plain copy

  1. declare
  2.   psal emp.sal%type;
  3.   pdeptno emp.deptno%type;
  4.   cursor cemp is select sal,deptno from emp order by deptno;
  5.   counts201 number := 0;counts202 number := 0;counts203 number := 0;
  6.   s20_sal number := 0;
  7.   counts301 number := 0;counts302 number := 0;counts303 number := 0;
  8.   s30_sal number := 0;
  9.   counts401 number := 0;counts402 number := 0;counts403 number := 0;
  10.   s40_sal number := 0;
  11.   counts501 number := 0;counts502 number := 0;counts503 number := 0;
  12.   s50_sal number := 0;
  13. begin
  14.   open cemp;
  15.      loop
  16.        fetch cemp into psal,pdeptno;
  17.        exit when cemp%notfound;
  18.        if pdeptno=’20’
  19.          then
  20.            s20_sal:=s20_sal+psal;
  21.            if psal<3000 then
  22.              counts201:=counts201+1;
  23.            elsif psal>=3000 and psal<=6000 then
  24.              counts202:=counts202+1;
  25.            else
  26.              counts203:=counts203+1;
  27.            end if;
  28.        elsif pdeptno=’30’
  29.          then
  30.            s30_sal:=s30_sal+psal;
  31.            if psal<3000 then
  32.              counts301:=counts301+1;
  33.            elsif psal>=3000 and psal<=6000 then
  34.              counts302:=counts302+1;
  35.            else
  36.              counts303:=counts303+1;
  37.            end if;
  38.        elsif pdeptno=’40’
  39.          then
  40.            s40_sal:=s40_sal+psal;
  41.            if psal<3000 then
  42.              counts401:=counts401+1;
  43.            elsif psal>=3000 and psal<=6000 then
  44.              counts402:=counts402+1;
  45.            else
  46.              counts403:=counts403+1;
  47.            end if;
  48.        elsif pdeptno=’50’
  49.          then
  50.            s50_sal:=s50_sal+psal;
  51.            if psal<3000 then
  52.              counts501:=counts501+1;
  53.            elsif psal>=3000 and psal<=6000 then
  54.              counts502:=counts502+1;
  55.            else
  56.              counts503:=counts503+1;
  57.            end if;
  58.        end if;
  59.      end loop;
  60.   close cemp;
  61.   dbms_output.put_line(‘部门 小于3000数 3000-6000 大于6000 工资总额’);
  62.   dbms_output.put_line(’20    ‘||counts201||‘    ‘||counts202||‘    ‘||counts203||‘    ‘||s20_sal);
  63.   dbms_output.put_line(’30    ‘||counts301||‘    ‘||counts302||‘    ‘||counts303||‘    ‘||s30_sal);
  64.   dbms_output.put_line(’40    ‘||counts401||‘    ‘||counts402||‘    ‘||counts403||‘    ‘||s40_sal);
  65.   dbms_output.put_line(’50    ‘||counts501||‘    ‘||counts502||‘    ‘||counts503||‘    ‘||s50_sal);
  66. end;
  67. /

方法2(用到了带参数的光标)：

[sql] view plain copy

  1. declare
  2.   –部门
  3.   dbms_output.put_line(‘部门 小于3000数 3000-6000 大于6000 工资总额’);
  4.   cursor cdept is select deptno from dept;
  5.   pdno dept.deptno%type;
  7.   –部门中的员工
  8.   cursor cemp(dno number) is select sal from emp where deptno=dno;
  9.   psal emp.sal%type;
  11.   –各个段的人数
  12.   count1 number;count2 number;count3 number;
  13.   –部门的工资总额
  14.   salTotal number;
  15. begin
  16.   open cdept;
  17.   loop
  18.     –取部门
  19.     fetch cdept into pdno;
  20.     exit when cdept%notfound;
  22.     –初始化
  23.     count1 :=0;count2:=0;count3:=0;
  24.     select sum(sal) into salTotal  from emp where deptno=pdno;
  26.     –取部门中的员工
  27.     open cemp(pdno);
  28.     loop
  29.       fetch cemp into psal;
  30.       exit when cemp%notfound;
  32.       –判断
  33.       if psal<3000 then count1:=count1+1;
  34.         elsif psal>=3000 and psal<6000 then count2:=count2+1;
  35.         else count3:=count3+1;
  36.       end if;
  37.     end loop;
  38.     close cemp;
  40.     –输出
  41.     dbms_output.put_line(pdno||‘    ‘||count1||‘    ‘||count2||‘    ‘||count3||‘    ‘||nvl(salTotal,0));
  43.   end loop;
  44.   close cdept;
  45. end;
  46. /

结果:

看一下表中的数据

上述结果完全正确。

未经允许不得转载：JX BLOG » 【oracle学习】8.PLSQL练习

赞 (0)
标签：

相关推荐

热门推荐

评论 0

评论前必须登录！

登陆 注册