【oracle学习】8.PLSQL练习

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

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

员工信息表

create table EMP(    EMPNO NUMBER,    ENAME VARCHAR2(10),    JOB VARCHAR2(9),    MGR NUMBER,    HIREDATE DATE,    SAL BINARY_DOUBLE,    COMM BINARY_DOUBLE,    DEPTNO NUMBER);

其中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

部门表

create table dept(    DEPTNO NUMBER,    DNAME VARCHAR2(50));

SQL> select * from dept t;

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



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

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

语句:

declare  cursor cemp is select to_char(hiredate,'yyyy') from emp;  phiredate varchar2(4);  --计数器(2010-2016的入职人数统计)  count10 number := 0;  count11 number := 0;  count12 number := 0;  count13 number := 0;  count14 number := 0;  count15 number := 0;  count16 number := 0;begin  open cemp;  loop    --取一个数据    fetch cemp into phiredate;    exit when cemp%notfound;    --判断    if phiredate = '2010' then count10:=count10+1;      elsif phiredate = '2011' then count11:=count11+1;      elsif phiredate = '2012' then count12:=count12+1;      elsif phiredate = '2013' then count13:=count13+1;      elsif phiredate = '2014' then count14:=count14+1;      elsif phiredate = '2015' then count15:=count15+1;      else count16:=count16+1;    end if;  end loop;  close cemp;  --输出  dbms_output.put_line('total:'||(count10+count11+count12+count13+count14+count15+count16));  dbms_output.put_line('2010:'||count10);  dbms_output.put_line('2011:'||count11);  dbms_output.put_line('2012:'||count12);  dbms_output.put_line('2013:'||count13);  dbms_output.put_line('2014:'||count14);  dbms_output.put_line('2015:'||count15);  dbms_output.put_line('2016:'||count16);end;/

结果


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

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

语句:

declare  psal emp.sal%type;  pempno emp.empno%type;  s_sal emp.sal%type; --总工资数  counts number := 0;  --涨工资的人数  cursor cemp is select empno,sal from emp order by sal;begin  select sum(sal) into s_sal from emp;  open cemp;  loop   exit when s_sal+psal*0.1>50000;   fetch cemp into pempno,psal;   exit when cemp%notfound;   update emp set sal=sal+sal*0.1 where empno=pempno;   counts := counts+1;   s_sal:=s_sal+psal*0.1;  end loop;  close cemp;  dbms_output.put_line('涨工资人数:'||counts);  dbms_output.put_line('工资总额:'||s_sal);end;/

运行前:


运行后:

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


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

我们一共有5个部门。

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

declare  psal emp.sal%type;  pdeptno emp.deptno%type;  cursor cemp is select sal,deptno from emp order by deptno;  counts201 number := 0;counts202 number := 0;counts203 number := 0;  s20_sal number := 0;  counts301 number := 0;counts302 number := 0;counts303 number := 0;  s30_sal number := 0;  counts401 number := 0;counts402 number := 0;counts403 number := 0;  s40_sal number := 0;  counts501 number := 0;counts502 number := 0;counts503 number := 0;  s50_sal number := 0;begin  open cemp;     loop       fetch cemp into psal,pdeptno;       exit when cemp%notfound;       if pdeptno='20'         then           s20_sal:=s20_sal+psal;           if psal<3000 then             counts201:=counts201+1;           elsif psal>=3000 and psal<=6000 then             counts202:=counts202+1;           else             counts203:=counts203+1;           end if;       elsif pdeptno='30'         then           s30_sal:=s30_sal+psal;           if psal<3000 then             counts301:=counts301+1;           elsif psal>=3000 and psal<=6000 then             counts302:=counts302+1;           else             counts303:=counts303+1;           end if;       elsif pdeptno='40'         then           s40_sal:=s40_sal+psal;           if psal<3000 then             counts401:=counts401+1;           elsif psal>=3000 and psal<=6000 then             counts402:=counts402+1;           else             counts403:=counts403+1;           end if;       elsif pdeptno='50'         then           s50_sal:=s50_sal+psal;           if psal<3000 then             counts501:=counts501+1;           elsif psal>=3000 and psal<=6000 then             counts502:=counts502+1;           else             counts503:=counts503+1;           end if;       end if;     end loop;  close cemp;  dbms_output.put_line('部门 小于3000数 3000-6000 大于6000 工资总额');  dbms_output.put_line('20    '||counts201||'    '||counts202||'    '||counts203||'    '||s20_sal);  dbms_output.put_line('30    '||counts301||'    '||counts302||'    '||counts303||'    '||s30_sal);  dbms_output.put_line('40    '||counts401||'    '||counts402||'    '||counts403||'    '||s40_sal);  dbms_output.put_line('50    '||counts501||'    '||counts502||'    '||counts503||'    '||s50_sal);end;/

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

declare  --部门  dbms_output.put_line('部门 小于3000数 3000-6000 大于6000 工资总额');    cursor cdept is select deptno from dept;  pdno dept.deptno%type;   --部门中的员工  cursor cemp(dno number) is select sal from emp where deptno=dno;  psal emp.sal%type;   --各个段的人数  count1 number;count2 number;count3 number;  --部门的工资总额  salTotal number;begin  open cdept;  loop    --取部门    fetch cdept into pdno;    exit when cdept%notfound;        --初始化    count1 :=0;count2:=0;count3:=0;    select sum(sal) into salTotal  from emp where deptno=pdno;        --取部门中的员工    open cemp(pdno);    loop      fetch cemp into psal;      exit when cemp%notfound;            --判断      if psal<3000 then count1:=count1+1;        elsif psal>=3000 and psal<6000 then count2:=count2+1;        else count3:=count3+1;      end if;            end loop;    close cemp;        --输出    dbms_output.put_line(pdno||'    '||count1||'    '||count2||'    '||count3||'    '||nvl(salTotal,0));      end loop;  close cdept;end;/

结果:


看一下表中的数据

上述结果完全正确。
转载请注明出处：http://blog.csdn.net/acmman/article/details/52422492