oracle plsql例子,oracle--PLSQL的应用实例

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 on

declare

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 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;

--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 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分以上)“大学物理”课程各分数段的学生人数，及各系学生的平均成绩。

提示：可建立一张新表，存放统计信息。

参考运行结果：

结果.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;