Oracle数据库——PL/SQL程序设计综合案例

一、程序设计方法

1、瀑布模型

2、SQL 语句

3、变量：

• 初始值是多少
• 最终值如何得到

二、综合案例

1、综合案例1

统计每年入职的员工人数

/*
	SQL语句
		select to_char(hiredate,'yyyy') from emp; 
		-->光标-->循环-->退出条件: not found
	变量: 1.初始值: 2、如何得到:
	每年入职的员工人数:
	count80 number := 0;
	count81 number := 0;
	count82 number := 0;
	count87 number := 0;
*/
set serveroutput on

declare
	--定义光标
	cursor cemp is select to_char(hiredate,'yyy') 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; 
		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;
/

2、综合案例2

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

/*
SQL语句
select empno,sal from emp order by sal;
-->光标-->循环-->退出条件: 1.工资总额> 5W; 2、%notfound

变量: 1、初始值; 2、如何得到
涨工资的人数:
1、select sum(sal) into salTotal from emp;
2、涨后的工资总额=涨前的工资总额+sal*0.1
*/
set serveroutput on
declare
	--定义光标
	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;
	--涨后的工资总额=涨前的工资总额+sal*0.1
	salTotal := salTotal + psal*0.1;
	end loop;
	--关闭光标
	close cemp;
	commit;

dbms_output.put_line('人数: '||countEmp||' 涨后的工资总额: '||salTotal);
end;
/

3、综合案例3

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

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

/*
SQL语句
1.有哪些部门
	select deptno from dept-->光标-->循环-->退出条件: not found
2、部门中员工的薪水
	select sal from emp where deptno=? --> 带一个参数的光标--> 循环--> 退出条件: notfoun
	
count2 number;
count3 number;

每个部门的工资总额:
saltotal number;
1、 select sumsal) into saltotal from emp where deptno=???
*/
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;
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;

		--保存当前部门的结果
		-- nv1();为虑空函数
		insert into msg values (pdeptno, count1, count2, count3, nvl(SALTOTAL,0));
	end loop;
	
	--关闭部门的光标
	close cdept;
	commit;
	dbms_output.put_line('统计完成');
end;
/

4、综合案例4

用PL/SQL语言编写一段程序。按系（系名）分段统计（成绩小于60分，60~85分，85分以上）“大学物理”课程各分数段的学生人数，及各系学生的平均成绩。

create table msg1 (
	coursename varchar2(20) ,
	dname varchar2(20) ,
	count1 number,
	count2 number ,
	count3 number ,
	avggrade number
);

/*
QL语句
1、得到有哪些系
	select dno, dname from dep;
	-->光标-->循环-->退出条件: not found
2、得到系中，选修了“大学物理”课程学生的成绩
	select grade from sc where cno=(select cno from course where cname-'大学物理')
							and sno in (select sno from student shere dno=??);
	-->带一个参数的光标--> 循环--> 退出条件: notfound

变量: 1.初始值2.如何得到
每个分数段的人数

每个系选修了“大学物理”学生的平均成绩:
avggrade number;
1、算术运算
2、select avg(grade) into avggrade from sc where cno=select cno from course whefe cname-'大学物理')
						and sno in (select sno trom student where dno=??);
*/
set serveroutput on
declare
	cursor cdept is select dno,dname from dep;
	pdno dep.dno%type;
	pdname dep.dname%type;
	--成绩光标
	cursor cgrade(coursename varchar2, depno number)
		is select grade from sc where cno=(select cno from course where cname=coursename)
							and sno in (select sno from student where dno=depno);
	pgrade sc.grade%type;
	--每个分段的人数
	count1 number; count2 number; count3 number;
	--每个系选修了“大学物理”学生的平均成绩
	avggrade number;
	--课程名称
	pcourseName varchar2(20) := '大学物理';
begin
	--打开系的光标
	open cdept; 
	loop
	--取出一个系的信息
	fetch cdept into pdno, pdname;
	exit when cdept%notfound;
	
	--初始化的工作
	count1:=0; count2:=0; count3:=0;
	--系的平均成绩
	select avg(grade) into avggrade from sc where cno=(select cno from course where cname=pcourseName)
		and sno in (select sno from student where dno=pdno);

	--取系中,选修了大学物理的学生成绩
	open cgrade (pcourseName, pdno);
	loop
		--取一个学生的成绩
		fetch cgrade into pgrade;
		exit when cgrade%notfound;

	--判断成绩的范围
	if pgrade<60 then count1 :=count1+1;
	elsif pgrade>=60 and pgrade<85 then count2 :=count2+1;
	else count3:=count3+1;
	end if;
	end loop;
	close cgrade;

	--保存当前的结果
	-- nv1();为虑空函数
	insert into msg1 values (pcourseName, pdno, count1, count2,count3, avggrade);
end loop;
	
	--关闭系的光标
	close cdept;
	dbms_output.put_line('统计完成');
end;
/