一、统计每年入职的员工人数
--分析
sql语句：select to_char(hiredate,'yyyy') from emp;
变量：1.初始值 2.最终得到
每年入职的员工人数：
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
--解
sql>set serveroutput on;
declare
cursor cemp is select to_char(hiredate,'yyyy') from emp;
--定义变量
phiredate varchar2(4);
--每年入职的员工人数:初始值都为0
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%，但工资总额不能超过5w元，请计算涨工资的人数和涨工资后的工资总额，并输出涨工资人数及工资总额
--分析
sql语句：select empno,sal from emp order by sal;
变量：1.初始值 2.最终得到
涨工资的人数：countEmp number := 0;
涨后的工资总额：salTotal number;
1.select sum(sal) into salTotal from emp;
2.涨后=涨前 + sal * 0.1;
--解
set serveroutput on;
declare
--首先查出从低到高排序，并定义变量
cursor cemp is select empno,sal from 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;
--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(
depno 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 on
declare
--部门
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分以上）'大学物理' 课程
各分数段的学生人数，及各系学生的平均成绩
提示：可建立一张新表，存放统计信息
--建立一张存放数据结构表:
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_number1 := 0;
stu_number2 := 0;
stu_number3 := 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 order by dno;
cursor cstudent(c_name char,d_no char) is
select grade from sc where sno in(select distinct sno student where dno=d_no)
and cno in(select distinct cno from course where cnamec_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 stsu_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;
