异常

--预定义异常
select * from emp
--未进行异常处理
declare
v_ename emp.ename%TYPE;
begin
select ename into v_ename
from emp
where empno=1234;
dbms_output.put_line('雇员名：'||v_ename);
end;

--进行异常处理
declare
v_ename emp.ename%TYPE;
begin
select ename into v_ename
from emp
where empno=1234;
dbms_output.put_line('雇员名：'||v_ename);
exception
when no_data_found then
dbms_output.put_line('雇员号不正确');
when TOO_MANY_ROWS then
dbms_output.put_line('查询只能返回单行');
when OTHERS then
dbms_output.put_line('错误号'||sqlcode||'错误描述'||sqlerrm);
end;

----------------------------------------------------------------------------------------------------------------------------------------
--非预定义异常

select * from emp
select * from dept
--需求修改编号为7788的雇员的部门编号为99

--在deot和emp表中建立主外键关系
alter table emp
add constraint pk_empno primary key(empno);
alter table dept
add constraint pk_deptno primary key(deptno);
alter table emp
add constraint fk_deptno foreign key(deptno) references dept(deptno);

declare
e_interity exception;
pragma exception_init(e_interity,-2291);-- -2291为oracle定义的错误号 违背了主外健约束
begin
update emp set deptno=99 where empno=7788;
exception
when e_interity then
dbms_output.put_line('该部门不存在');
end;

--------------------------------------------------------------------------------------------------------------------------------
--自定义异常
--查询编号为7788雇员的补助为例
declare
v_comm emp.comm%TYPE;
e_comm_is_null exception; --定义异常类型变量
begin
select comm into v_comm from emp where empno=7788;
if v_comm is null then
raise e_comm_is_null;
end if;
exception
when no_data_found then
dbms_output.put_line('雇员不存在！错误为：'||sqlcode||sqlerrm);
when e_comm_is_null then
dbms_output.put_line('雇员无补助');
when OTHERS then
dbms_output.put_line('其他异常');
end;