oracle 预定义例外

常见的预定义例外

case_not_found
create or replase procedure sp_pro12(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
declare 
cursor emp_cursor  is select ename,sal from emp;
begin
open emp_cursor;
for emp_record1 in emp_cursor 
loop
dbms_ouutput.put_line(emp_record1.ename);
end loop;
exception
when cursor_already_open then
dbms_output.put_line('游标已经打开');
end;

--dup_val_on_index 在唯一索引所对应的列上插入重复值时，会隐含的触发例外
begin 
insert into dept values(10,'公共','北京');
exception
when dup_val_on_index then
dbms_output.put_line('在deptno列上不能出现重复值');
end;

--invalild_cursor当试图在不合法的游标上执行操作时，触发该例外
declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp cursor%rowtype;
begin
--open emp_cursor--打开游标
fetch emp_cursor into emp_record
dbms_output.put_line(emp_record,emage);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.put_line('请检测游标是否打开');
end;

--invalid_number当输入的数据有误时
begin
update emp set sal=sal+''100;
exception
when invalid_number then
dbms_output.put_line('输入的数字不正确');
end;

--no_data_found当执行select into没有返回行

declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
where ename=&name;
exception
when no_data_found then
dbms_output.put_line('不存在该员工');
end;

--too_many_rows当执行select into语句时，如果返回超过一行，触发该例外
declare
v_ename emp.ename%type;
begin
select ename into v_eanme from emp;
exception
when too_many_rows then
dbms_output.put_line('返回了多行');
end;

--zero_divide除以0异常 如2/0

--value_error当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发例外。
declare
v_eanem varchar2(5);
begin
select ename into v_ename from emp where wmpno=&no;
dbms_output.put_line(v_ename);
exception
when value_error then
dbms_output.put_line('变量尺寸不足');
end;