Oracle学习第四天

PL/SQL

1.例外
例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。
①系统例外
No_data_found(没有找到数据)
Too_many_rows(select...into语句匹配多个行)
Zero_Divide(被零除)
Value_error(算术或转换错误)
Timeout_on_resource(在等待资源时发生超时)


exception
when too_many_rows then ...;
when others then...;
②自定义例外
抛出例外 ：raise




例：
--查询50号部门的员工姓名
set serveroutput on
declare 
--定义光标，代表50号部门的员工姓名
cursor cemp is select ename form emp where deptno=50;
peanme emp.ename%type;
--自定义例外

no_emp_found exception;
begin
--打开光标
open cemp;
--直接取一个员工的姓名
fethc cemp into pename;
if cemp%nofound then
--抛出例外
raise no_emp_found;
end if;
--关闭光标
--oracle自动启动pmon(process monitor)
close cemp;
exception
when no_emp_found then dbms_output.put_line("没有找到员工");
when others then dbms_output.put_line("其他");
end;
/

综合案例

1.统计每年入职的员工人数
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;
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;
end;
/
2.员工涨工资问题
set serveroutput on
declare
--定义光标
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%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;
--%outfound
exit when cemp%notfound;
if(salTotal+psal*1.1)<50000 then
--涨工资
update empp set sal=sal*1.1 where empno=pempno;
--人数+1
countEmp:=countEmp+1;
-涨工资后的总额
salTotal:=salTotal+psal*0.1;
else exit;
end if;
end loop;
close cemp;
commit;
end;
/
3.操作两张表的员工涨工资问题
set serveroutput on
declare 
--定义光标部门
cursor cdept is select demtno from dept;
pdeptno dept deptno%type;
--部门中员工的薪水
cursor cemp(dno number) is select sal from emp where deptno= dno;
--每个段的员工人数
count1 number;
count2 number;
count3 number;
--每个部门的工资总额
saltotal number;
begin
--打开部门的光标
open cdept;
--取出一个部门
loop
fetch cdept inot 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;
end;
/
nvl(a,b):如果a为null就取b，如果a不为null就取本身


4.成绩统计
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);
pgradge sc.grade%type;
--每个段的人数
count1 number,
count2 number,
count3 number;
-每个系选修了“大学物理”学生的平均成绩
avggrade number;

--课程名称
pcourseName varchar2:='大学物理';
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 scno=(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;
--保存当前结果
insert into msg1 values(pcourseName,pdno,count1,count2,count3)
end loop;
close cdept;
commit;
end;
/