例外的种类

---------------------------------------------------------------------------------------------

--例外（20多个）的案例

---------------------------------------------------------------------------------------------

-no_data_found(没有返回的行会触发该例外)

declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('名字:'||v_ename);
exception
  when no_data_found then
  dbms_output.put_line('编号不存在');
end;
-----------------------------------------------------------------------------------------
--case_not_found例外

--在pl/sql中使用case语句时候，我很子句没有包含必须的条件分支，就会触发case_not_found例外
create or replace procedure sp_pro13(spno number) is
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=spno;
  case
  when v_sal<1000 then
  update emp set sal=sal+100 where empno=spno;
  when v_sal<2000 then
  update emp set sal=sal+200 where empno=spno;
  end case;
exception
  when case_not_found then
  dbms_output.put_line('case语句没有找到与'||v_sal||'相关的条件');

end;

--------------------------------------------------------------------------------------------------

cursor_already_open(打开已经打开的游标会触发此例外)

--------------------------------------------------------------------------------------------------

dup_val_on_index(插入重复的值)

---------------------------------------------------------------------------------------------------------------

invalid_cursor（在不合法的游标上执行操作，触发该例外）

---------------------------------------------------------------------------------------------------------------------

invalid_number(当输入的数据有误时候，触发该例外)

declare
begin
update emp set sal=sal+'1oo';
exception
  when invalid_number then
  dbms_output.put_line('输入的数字不正确!');
end;

-----------------------------------------------------------------------------------------------------------------

too_many_rows(select into 语句中返回超过了一行，触发该例外)

-------------------------------------------------------------------------------------------------------------------------------

--zero_divide(当执行运算操作例如:2/0，触发该例外)

-------------------------------------------------------------------------------------------------------------------------------

value_error(赋值操作时，变量长度不足以容纳实际数据，触发该例外)

------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------

==========================================================================================

处理自定义例外

==========================================================================================

案例：

--处理自定义例外
create or replace procedure ex_test(spNo number) is
--定义一个例外
myex exception;
begin
  update emp set sal=sal+1000 where empno=spNo;--默认update不会报异常，但是select会报异常
  --sql%notfound表示没有update或者失败
  --raise myex表示触发myex例外
  if sql%notfound then
  raise myex;
  end if;
exception 
  when myex then
  dbms_output.put_line('没有更新任何用户!');
end;

----------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------

--视图的操作
--创建视图，将emp表的sal<1000的雇员映射到该视图(view)
create or replace view myview1
as
select * from emp where sal<1000;


select * from myview1;
--利用视图显示雇员编号，姓名，和部门名称
create or replace view myview2
as
select a1.empno,a1.ename,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;


select * from myview2;


--删除视图
drop view myview;