oracle--PLSQL的应用实例

来源:互联网 发布:centos开机进入grub 编辑:程序博客网 时间:2024/03/29 23:19

PLSQL的应用实例

一、统计每年入职的员工人数

分析:

SQL语句:select to_char(hiredate,'yyyy') from emp;---> 集合 ---> 光标 ---> 循环  ---> 退出条件:notfound变量:1. 初始值  2. 最终得到每年入职的员工人数:count80 number := 0;count81 number := 0;count82 number := 0;count87 number := 0;

解:

set serveroutput ondeclare  cursor cemp is select to_char(hiredate,'yyyy') from emp;  phiredate varchar2(4);  --每年入职的员工人数:  count80 number := 0;  count81 number := 0;  count82 number := 0;  count87 number := 0;begin  open cemp;  loop    --取一个员工的入职年份    fetch cemp into phiredate;    --退出条件:notfound    exit when cemp%notfound;    --判断年份    if phiredate = '1980' then count80:=count80+1;      elsif phiredate = '1981' then count81:=count81+1;      elsif phiredate = '1982' then count82:=count82+1;      else count87:=count87+1;    end if;  end loop;  close cemp;  dbms_output.put_line('Total:'||(count80+count81+count82+count87));  dbms_output.put_line('1980:'||count80);  dbms_output.put_line('1981:'||count81);  dbms_output.put_line('1982:'||count82);  dbms_output.put_line('1987:'||count87);end;

二、使用scott用户下的emp表:

为员工涨工资。从最低工资调起每人涨10%,但工资总额不能超过5万元,请计算涨工资的人数和涨工资后的工资总额,并输出涨工资人数及工资总额。

分析:

SQL语句:select empno,sal from emp order by sal;---> 光标 ---> 退出:1. 总额 > 5w   2. notfound变量:1. 初始值  2. 最终得到涨工资的人数: countEmp number := 0;涨后的工资总额: salTotal number;1. select sum(sal) into salTotal from emp;2. 涨后=涨前 + sal * 0.1

解:

set serveroutput ondeclare  cursor cemp is select empno,sal from emp order by sal;  pempno emp.empno%type;  psal   emp.sal%type;  --涨工资的人数:   countEmp number := 0;  --涨后的工资总额:   salTotal number;begin  --得到初始的工资总额  select sum(sal) into salTotal from emp;  open cemp;  loop    --1. 总额 > 5w    exit when salTotal > 50000;    --取一个员工    fetch cemp into pempno,psal;    --2. notfound    exit when cemp%notfound;    --涨工资    update emp set sal=sal*1.1 where empno=pempno;    --人数+1    countEmp := countEmp + 1;    --2. 涨后=涨前 + sal * 0.1    salTotal := salTotal + psal * 0.1;  end loop;  close cemp;  commit;  dbms_output.put_line('人数:'||countEmp||'   总额:'||salTotal);end;

三、

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

创建一张表来保存数据:

create table msg(deptno number, count1 number, count2 number, count3 number, saltotal number);

分析:

SQL语句:部门: select deptno from dept;部门中员工的薪水:select sal from emp where deptno=???变量:1. 初始值  2. 最终得到每个段的人数:count1 number; count2 number; count3 number;部门的工资总额: salTotal number := 0;1.select sum(sal) into salTotal from emp where deptno=???2.累加

解:

set serveroutput ondeclare  --部门  cursor cdept is select deptno from dept;  pdeptno 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 := 0;begin  open cdept;  loop    --取一个部门    fetch cdept into pdeptno;    exit when cdept%notfound;    --初始化    count1:=0;count2:=0;count3:=0;    --部门的工资总额    select sum(sal) into salTotal from emp where deptno=pdeptno;    --取部门中员工的薪水    open cemp(pdeptno);    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;    --保存结果    insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));  end loop;  close cdept;  commit;  dbms_output.put_line('完成');end;

四、

用PLSQL语言编写一个程序。按系(系名)分段统计(成绩小于60分,60-85分, 85分以上)“大学物理”课程各分数段的学生人数,及各系学生的平均成绩。
提示:可建立一张新表,存放统计信息。

参考运行结果:

结果.png

分析:

--建立存放数据的结构表:create table msg(cname char(20), dname char(30), seg_1 number, seg_2 number, seg_3 number, avggrade number(4,2) );--利用光标循环处理数据,并把处理结果写到结果表中:

答案:

方式一:

declare   cursor c1 is select distinct dno,dname from dep order by dno;  cursor c2(c_name char) is select student.dno,grade,course.cname from student,dep,sc,course                             where student.dno=dep.dno and student.sno=sc.sno and course.cno=sc.cno and cname=c_name  order by dno;  tempcname char(20);  stu_num1 number;  stu_num2 number;  stu_num3 number;  stu_dno dep.dno%type;  stu_grade sc.grade%type;  avggrade number;  people number;begin  open c2('大学物理');  fetch c2 into stu_dno,stu_grade,tempcname;  for r1 in c1  loop     people:=0;     stu_num1:=0;     stu_num2:=0;     stu_num3:=0;     avggrade:=0;     while r1.dno=stu_dno     loop        people:=people+1;        avggrade:=(avggrade*(people-1)+stu_grade)/people;        if stu_grade<60 then stu_num1:=stu_num1+1;        elsif stu_grade<85 and stu_grade>=60 then stu_num2:=stu_num2+1;        else stu_num3:=stu_num3+1;        end if;        fetch c2 into stu_dno,stu_grade,tempcname;        exit when c2%notfound;     end loop;     insert into msg values(tempcname,r1.dname,stu_num1,stu_num2,stu_num3,avggrade);   end loop;commit;end; 

方式二:

declare   cursor cdep is select distinct dno,dname from dep order by dno;   cursor cstudent(c_name char,d_no char) is select grade from sc                                              where sno in (select distinct sno from student where dno=d_no)                                              and cno in (select distinct cno from course where cname=c_name);   pcName char(8);   pdno dep.dno%type;   pdname dep.dname%type;   stu_num1 number;   stu_num2 number;   stu_num3 number;   stu_grade sc.grade%type;   sumGrade number;   people   number;begin   pcName := '大学物理';   open cdep;   loop     fetch cdep into pdno,pdname;     exit when cdep%notfound;     people:=0;     stu_num1:=0;     stu_num2:=0;     stu_num3:=0;     sumGrade:=0;     open cstudent(pcName,pdno);     loop          fetch cstudent into stu_grade;         exit when cstudent%notfound;         if stu_grade<60 then stu_num1:=stu_num1+1;           elsif stu_grade<85 and stu_grade>=60 then stu_num2:=stu_num2+1;           else stu_num3:=stu_num3+1;         end if;              sumGrade := sumGrade+stu_grade;         people := people + 1;     end loop;     close cstudent;     if people>0 then        insert into msg values(pcName,pdname,stu_num1,stu_num2,stu_num3,sumGrade/people);         end if; end loop; close cdep;commit;end;
0 0
原创粉丝点击