概念:
一种PL/SQL标识符,它用于处理PL/SQL程序的运行错误。
类型
一 系统例外:
1 预定义例外:用于处理常见的Oracle错误
2 非预定义例外:用于处理预定义例外所不能处理的Oracle错误
no_data_found: 没有找到数据,报错;
too_many_rows:行数太多,报错;
zero_divide:被0整除,报错;
values_errors: 算术或者转换错误;
timeout_on_resource: 等待资源超时;
set serveroutput on;
declare
pnum number;
begin
pnum :=1/0;
exception
when zero_divide then dbms_output.put_line('0不能作为除数');
when too_many_rows then dbms_output.put_line('行数太多');
when others then dbms_output.put_line('其他异常');
end;
系统例外语法:
EXCEPTION
WHEN exception1 [OR exception2…] THEN
statement1;
statement2;
…
WHEN exception3 [ OR exception4…] THEN
statement1;
statement2;
…
WHEN OTHERS THEN
statement1;
statement2;
…
二 自定义例外:处理与Oracle错误无关的其他情况。
set serveroutput on;
declare
myexc EXCEPTION;
pnum number :=1;
begin
if pnum = 1 then raise myexc;--抛例外
end if;
exception
when myexc then dbms_output.put_line('自定义例外');
end;
例题1 是否存在编号是50的部门,如果不存在,抛出一个例外;若存在则打印;
set serveroutput on;
declare
cursor cemp(dno number) is select ename from emp where deptno = dno;
pename emp.ename%type;
no_emp_found exception;
begin
open cemp(50);
FETCH cemp INTO pename;
if cemp%notfound then raise no_emp_found;
else
loop
exit when cemp%notfound;
FETCH cemp INTO pename;
dbms_output.put_line(pename);
end loop;
end if;
exception when no_emp_found then dbms_output.put_line('没有这个部门!');
close cemp;
end;
例题2 统计每年的入职人数;
--select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');
set serveroutput on;
declare
cursor cemp is select to_char(hiredate,'yyyy') from emp;
pdate varchar2(4);
psum_80 number:=0;
psum_81 number:=0;
psum_87 number:=0;
psum_82 number:=0;
begin
open cemp;
loop
fetch cemp into pdate;
exit when cemp%notfound;
if pdate='1980' then psum_80 :=psum_80+1;
elsif pdate='1981' then psum_81 :=psum_81+1;
elsif pdate='1982' then psum_82 :=psum_82+1;
else psum_87 :=psum_87+1;
end if;
end loop;
close cemp;
DBMS_OUTPUT.PUT_LINE(psum_80);
DBMS_OUTPUT.PUT_LINE(psum_81);
DBMS_OUTPUT.PUT_LINE(psum_82);
DBMS_OUTPUT.PUT_LINE(psum_87);
end;
例题3 涨工资,每人涨10%;按入职时间顺序涨工资,且涨后的总工资不能超过5万,查询出涨工资的人个数以及涨后的工资总额;
set SERVEROUTPUT ON;
declare
cursor cemp is select empno,sal from emp order by hiredate asc;
pempno emp.empno%type;
psal emp.sal%type;
countEmp number :=0;
salTotal number :=0;
begin
open cemp;
loop
fetch cemp into pempno,psal;
exit when cemp%notfound;
if salTotal +psal*1.1<50000
then update emp set sal=sal*1.1 where empno=pempno;
countEmp:=countEmp+1;
salTotal :=salTotal +psal*1.1;
else
dbms_output.put_line('人数'||countEmp||'总额'||salTotal);
exit;
end if;
end loop;
close cemp;
end;
例题 4 :
统计各部门的工资情况,格式如下:
部门编号 <2000的人数 2000-4000人数 >4000人数 工资总额
set SERVEROUTPUT ON;
declare
cursor cdept is select deptno from dept;
pdept dept.deptno%type;
cursor cemp(dno number) is select sal from emp where deptno=dno;
psal emp.sal%type;
count1 number :=0;
count2 number :=0;
count3 number :=0;
--各部门的工资总额;
totalCount number :=0;
begin
open cdept;
loop--外层循环,遍历所有的部门编号;内层循环,遍历工资;
fetch cdept into pdept;
exit when cdept%notfound;
open cemp(pdept);
loop
fetch cemp into psal;
exit when cemp%notfound;
select sum(sal) into totalCount from emp where deptno=pdept;
if psal<2000 then count1:=count1+1;
elsif psal>=2000 and psal<4000 then count2:=count2+1;
else count3:=count3+1;
end if;
end loop;
close cemp;
dbms_output.put_line(pdept||' '||count1||' '||count2||' '||count3||' '||totalCount);
end loop;
close cdept;
end;