首页 > 数据库 > Oracle > 正文
【oracle学习】8.PLSQL练习
2016-09-05       个评论      
收藏    我要投稿
前言
我们以下的所有操作均在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;
/

结果:
\

看一下表中的数据
\
上述结果完全正确。
点击复制链接 与好友分享!回本站首页
上一篇:Oracle管理用户
下一篇:ORACLE逻辑备份与恢复
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站