1例外簡介
?1） 例外分類
? 預定義分類，非預定義分類，自定義例外。
2 處理預定義例外
? 1) 常用預定義例外
?? a access_into_null;
???? create type emp_type as object
???? (name varchar2(2),sal number(6,2));
????
???? declare
?????? emp emp_type;
???? begin
?????? emp.name:='scott';
???? exception
?????? when access_into_null then
????????? dbms_output.put_line('首先初始化對象emp');
??? b case_not_found
???? undef no
???? declare
?????? v_val emp.sal%type;
???? begin
?????? select sal into v_sal from emp where empno=&no;
?????? case
???????? when v_sal<1000 then
?????????? update emp set sal=sal+100 where empno=&no;
???????? when v_sal<2000 then
?????????? update emp set sal=sal+150 where empno=&no;
???????? when v_sal<3000 then
????????? update emp set sal=sal+200 where empno=&no;
?????? end case;
?????? exception
???????? when case_not_found then
?????????? dbms_output.put_line();
????? end;
??? c collection is null
??? 在給集合元素（嵌套表或array類型）賦值前，必須首先初始化集合元素
??? declare type ename_table_type is table of emp.eanme%type;
??? ename_table ename_table_type;
??? begin
????? select e_name into ename_talbe(2) from emp where empno=$no;
??? exception
????? when collection_is_null then
??????? dbms_output.put_lilne('必須使用構造方法初始化集合元素');
??? end;
??? d currsor_already_open
??? reopen curosr 如果用戶已經使用了open 命令打開了顯示游標，或執行for循環(隱式的打開游標)
?????? delcare
???????? cursor emp_surosr is select ename,sal,from emp;
?????? begin
???????? open emp_curosr;
???????? for emp_record in emp_cursor loop
????????????? dbms_output.put_line(emp_record.eanme);
???????? end loop;
?????? exception
????????? when cursor_already_open then
??????????? dbms_output.put_line("游標已經打開");
?????? end;
???? e dup_val_on_index
???? begin
???? exception
??????? when dup_val_on_index then
??????? dbms_output.put_line("列上不能出現重復值");
???? end;
???? d invalid_curosr
???? delcare
?????? curosr emp_cursor is select ename,sla from emp;
?????? emp_record emp_crusor%rowtype;
???? begin
?????? fetch emp_cursor into emp_record;
?????? close emp_crusro;
???? exception
?????? dbms_output.put_line("游標沒有打開");
???? end;
???? f invalid_number? can not convert char to nmuber successfully
?????? begin
???????? update mep set sal=sal+1oo;
?????? exception
???????? when invalid_number then
???? g no_data_found? when select into is executed ,no row is returned?????
?????? declare
???????? v_sal emp.sal%type;
?????? begin
????????? select sal into v_cal form emp where lower(ename)=lower('&name');
?????? exception
???????? when no_data_found then
???????????? dbms_output.put_line('沒有返回結果');
?????? end;
????? h too_many_row? ora -01422 there are too many are return when "select into" is executed
????? i zero_divide ora-01476
????? g subscript_beyond_count ora-065533
????? declare
??????? type emp_array_type is varray(20) of varchar2(10);
??????? emp_array emp_array_type;
????? begin
???????? emp_array:=emp_array_type('scott','mary');
???????? dbms_output.put_line('emp_array(3)');
????? exception
???????? when subscript_beyone_count then
????????? dbms_out.put_line('超出下標范圍');
????? end;
????? k subscript_outside_limit
?????? emp_array(-1);
????? l value_error the length of variable cannot contain the actual value;
????? declare
????? begin
????? end;
3 處理非預定義例外
? delcare
??? e_integrity exception;
??? pragma exception_init(e_integrity,-2291);
? begin
??? update emp set deptno=dno where empno=&no;
? exception
??? when a_integrity then
? end;
4 處理自定義例外
? 與oracle 錯誤沒有任何聯系，為業務邏輯所定義的例外
? delcare
??? e_no_employee exception;
? begin
??? update emp set deptno=&dno where empno=&eno;
? if sql%notfound then
??? raise e_no_employee;
? end if;
? exception
??? when e_no_emplyee then
???? dbms_output.put_line('該雇員不存在');
5 使用錯誤例外函數
? 使用例外函數可以取得錯誤號以及相關的錯誤消息，sqlcode 用于取得oracle 的錯誤號，而sqlerrm
則用于取得與之相關的錯誤信息。
? 1 sqlcode 和 sqlerrm
? 為了在pl/sql 應用程序中處理其他為預料的到的oracle 錯誤，用戶可以在例外處理部分的
when others 自句后，引用兩個函數
? declare
??? v_ename emp.ename%type;
? begin
??? select ename into v_ename form emp where sal='&v_sal';
? exception
??? when no_data_found then
??? when others then
?????? dbms_output.put_line(sqlcode);
?????? dbms_output.put_line(sqlerrm);
? end;
? 2 raise_aaplicaition_error
?? 只能在子程序中使用（過程，函數，包，觸發器）
?? raise_application_error(error_number,message,[true|false]);
?? error_number 錯誤號，在－20000到－20999 之間
?? message 指定錯誤消息，不能超過2048?
?? if v_comm is null then
?????? raise_application_error(-20001,'該雇員無補助');
?? end if;