oracle異常及觸發(fā)器

1、oracle 異常及0異常處理異常處理l什么是異常情態(tài)異常處理處理的是運(yùn)行時(shí)錯(cuò)誤，異常分為預(yù)定義異常和用戶自定義異常。聲明異常情態(tài)異常情態(tài)在塊的聲明部分進(jìn)行聲明，在塊的執(zhí)行部分進(jìn)行觸發(fā)，在塊的異常部分進(jìn)行處理。l用戶定義類(lèi)型異常情態(tài),需要程序員自己定義代碼，對(duì)異常情況進(jìn)行處理。例：Declare e_toomanystudents exception;1e_toomanystudents的作用域和本塊的其他變量相同。l預(yù)定義型異常情態(tài)預(yù)定義型異常情態(tài)可以直接使用，沒(méi)有必要聲明。Invalid_cursor:當(dāng)執(zhí)行非法的游標(biāo)操作時(shí)，會(huì)引發(fā)這個(gè)錯(cuò)誤，如試圖關(guān)閉已關(guān)閉的游標(biāo)。Cursor_alrea

2、dy_open：試圖打開(kāi)已經(jīng)打開(kāi)的游標(biāo)，會(huì)引發(fā)這個(gè)錯(cuò)誤。No_data_found：當(dāng)select into語(yǔ)句沒(méi)有返回行時(shí)，和引用沒(méi)有賦值過(guò)的pl/sql表的元素時(shí)會(huì)引發(fā)這個(gè)錯(cuò)誤。Too_may_rows：當(dāng)select into 語(yǔ)句返回多個(gè)行時(shí)，會(huì)引發(fā)這個(gè)錯(cuò)誤。Invalid_number：當(dāng)試圖從字符串轉(zhuǎn)換為數(shù)值失敗時(shí)，會(huì)引發(fā)這個(gè)錯(cuò)誤，在過(guò)程性語(yǔ)句中會(huì)引發(fā)value_error錯(cuò)誤。如：下面的語(yǔ)句引發(fā)invalid_number錯(cuò)誤，因?yàn)閤不是數(shù)值。Insert into students(id,first_name,last_name) values (x,scott,smith);

3、 2Storage_error和program_error：是內(nèi)部的異常情態(tài)，通常不會(huì)引發(fā)他們。Value_error：當(dāng)在過(guò)程性語(yǔ)句中發(fā)生了算術(shù)、轉(zhuǎn)換、截尾或限制性錯(cuò)誤時(shí)會(huì)引發(fā)這個(gè)情態(tài)。如果在sql語(yǔ)句中發(fā)生錯(cuò)誤，會(huì)引發(fā)invalid_number錯(cuò)誤。這個(gè)錯(cuò)誤可能是一條賦值語(yǔ)句或select into語(yǔ)句的執(zhí)行結(jié)果。下面的兩個(gè)例子都引發(fā)value_error錯(cuò)誤。Declare v_tempvar varchar2(3);Begin v_tempvar:=ABCD;End;Declare v_tempvar number(2);Begin select id into v_tempvar

4、from students where last_name=smith;End;3觸發(fā)異常情態(tài)l當(dāng)預(yù)定義的異常情態(tài)錯(cuò)誤發(fā)生時(shí)，就會(huì)觸發(fā)該異常情態(tài)。用戶定義的異常情態(tài)由raise語(yǔ)句觸發(fā)。l例：Declare e_toomanystudents exception v_currentstudents number(3); v_maxstudents number(3);Begin select current_students,max_students into v_currentstudents,v_maxstudents from classes where department=HIS a

5、nd course=101;4 if v_currentstudentsv_maxstudents then raise e_toomanystudents End if;End;當(dāng)引發(fā)異常情態(tài)時(shí)，控制轉(zhuǎn)給該塊的異常處理部分。如果沒(méi)有異常處理部分，該異常處理部分將傳給包含該塊的塊。一旦將控制轉(zhuǎn)給異常處理部分，沒(méi)有辦法再將控制返回給該塊的執(zhí)行部分。處理異常情態(tài)l異常部分的語(yǔ)法如下：Exception when exception_name then 處理錯(cuò)誤語(yǔ)句序列 when exception_name then 處理錯(cuò)誤語(yǔ)句序列 when others then 處理錯(cuò)誤語(yǔ)句序列 end;5

6、一個(gè)處理器可以對(duì)多個(gè)異常情態(tài)進(jìn)行處理，用or連接例：Exception when no_data_found or too_many_rows then insert into log_table(info) values (a select error occurred);End;lOthers異常處理器將對(duì)所有語(yǔ)法的異常情態(tài)進(jìn)行處理，一般放在異常處理的最后，可以保證所有的錯(cuò)誤都被檢測(cè)到。Declare e_toomanystudents exception v_currentstudents number(3); v_maxstudents number(3); v_errorcode n

7、umber; v_errortext varchar2(200);Begin select current_students,max_students into v_currentstudents,v_maxstudents from classes where department=HIS and course=101;6 if v_currentstudentsv_maxstudents then raise e_toomanystudents End if;Exception when e_toomanystudents then insert into log_table(info)

8、values (history 101 has | v_currentstudents | students: max allowed is | v_maxstudents); when others then v_errorcode:=sqlcode; v_errortext:=substr(sqlerrm,1,200); insert into log_table(info) values (another error occurred); end;*sqlcode和sqlerrm先被賦值給本地變量，然后這些變量在sql語(yǔ)句中被使用，sqlcode和sqlerrm不能直接在sql語(yǔ)句中使用

9、，因?yàn)樗麄兪沁^(guò)程性的函數(shù)。sqlcode返回當(dāng)前的錯(cuò)誤號(hào)，sqlerrm返回當(dāng)前的錯(cuò)誤信息正文。 7代碼消息使用的sqlerrm函數(shù)0ora-0000:normal,successful completionsqlerrm(0)+100 Ora-1403:no data foundsqlerrm(100)+10User-defined exceptionSqlerrm(10)nullora-0000:normal,successful completionsqlerrm-1Ora-0001:unique constraint(.) violated Sqlerrm(-1)-54Ora-0005

10、4:resource busy and acquire by nowait specifiedSqlerrm(-54)8Exception_init 可以將一個(gè)經(jīng)過(guò)命名的異常情態(tài)與一個(gè)特別的oracle錯(cuò)誤相聯(lián)系。這 樣 可 以 用 w h e n 撲 獲 此 錯(cuò) 誤 ， 不 用 o t h e r s 撲 獲 。 通 過(guò)exception_init pragma實(shí)現(xiàn)的。其語(yǔ)法如下：Pragma exception_init (exception_name,oracle_error_number)Pragma必須在聲明部分例：下面的例子在運(yùn)行時(shí)刻如遇到“ora-1400：mandatory n

11、ot null column missing or null during insert”錯(cuò)誤時(shí)，將引發(fā)e_missingnull異常情態(tài)。Declare e_missingnull exception; pragma exception_init (e_missingnull,-1400);Begin insert into students(id) values(null);Exception when e_missingnull then insert into log_table(info) values(ora-1400 occurred);End;9每次發(fā)生pragma excep

12、tion_init時(shí)，一個(gè)oracle錯(cuò)誤只能和一個(gè)用戶定義的異常情態(tài)相關(guān)聯(lián)。在異常處理器內(nèi)部，sqlcode和sqlerrm將返回發(fā)生oracle錯(cuò)誤的代碼和錯(cuò)誤信息，而不會(huì)返回用戶定義的消息。例：Declare ex_hfy exception;Pragma exception_init(ex_hfy,-1400);Begin insert into students values(null);Exception when ex_hfy then dbms_output.put_line(不能把空值插入到非空列);End;注意，這里-1400不能為別的，因?yàn)榘芽罩挡迦氲椒强樟械腻e(cuò)誤號(hào)就是這

13、個(gè)。可以單獨(dú)執(zhí)行insert語(yǔ)句查看錯(cuò)誤號(hào)。10q使用使用raise_application_errorraise_application_error可以用raise_application_error創(chuàng)建自己的錯(cuò)誤消息，這比命名的異常情態(tài)更具有說(shuō)明性。其語(yǔ)法如下：Raise_application_error(error_number,error_message,keep-errors);這里error_number是從20,000到20,999之間的數(shù)，error_message是與此錯(cuò)誤相關(guān)的錯(cuò)誤正文。Keep_errors為布爾值，如果他為true，則新的錯(cuò)誤被添加到已經(jīng)引發(fā)的錯(cuò)誤列表

14、中（如果有的話）。如果為false（為缺省值），則新的錯(cuò)誤將替換錯(cuò)誤的當(dāng)前列表。例：下面的過(guò)程為一個(gè)新的學(xué)生注冊(cè)以前，檢查是否在班級(jí)中有足夠的地方容納他。11 Create or replace procedure register( p_studentid in students.id%type, p_department in classes.department%type, p_course in classes.course%type) as v_currentstudents number; v_maxstudents number;Begin select current_stud

15、ents,max_students into v_currentstudent,v_maxstudents from classes where department=p_department and course=p_course; 12 if v_currentstudents+1v_maxstudents then raise_application_error(-20000, cant add more students to | p_department | |p_course); end if; classpackage.addstudent(p_studentid, p_depa

16、rtment,p_course);Exception when no_data_found then raise_application_error(-20001,p_department | | p_course | doesnt exist!);End register;以上程序運(yùn)行時(shí)，當(dāng)沒(méi)有足夠的空間容納新的學(xué)生時(shí)，返回ora-20000: cant add more students to his 101錯(cuò)誤消息。13觸發(fā)器觸發(fā)器l綜述觸發(fā)器可以理解為特殊的存儲(chǔ)過(guò)程。當(dāng)應(yīng)用程序用一條滿足觸發(fā)器條件的SQL DML語(yǔ)句指向與觸發(fā)器相連接的表時(shí)，Oracle將自動(dòng)執(zhí)行該觸發(fā)器以執(zhí)行任務(wù)。D

17、ML觸發(fā)器，定義在對(duì)數(shù)據(jù)庫(kù)表的操縱行為（insert,delete,update)上的觸發(fā)器。前觸發(fā)器、后觸發(fā)器。語(yǔ)句觸發(fā)、行觸發(fā)14觸發(fā)器觸發(fā)器使使 用用 數(shù)數(shù) 據(jù)據(jù) 庫(kù)庫(kù) 觸觸 發(fā)發(fā) 器器 的的 主主 要要 好好 處處 ：1。 觸觸 發(fā)發(fā) 器器 能能 夠夠 進(jìn)進(jìn) 行行 復(fù)復(fù) 雜雜 的的 有有 效效 性性 檢檢 驗(yàn)驗(yàn) 。2。 審審 計(jì)計(jì)3。 一一 個(gè)個(gè) 表表 中中 的的 觸觸 發(fā)發(fā) 器器 可可 以以 修修 改改 另另 一一 個(gè)個(gè) 表表 的的 內(nèi)內(nèi) 容容 。觸觸 發(fā)發(fā) 事事 件件 是是 通通 過(guò)過(guò) 對(duì)對(duì) 數(shù)數(shù) 據(jù)據(jù) 庫(kù)庫(kù) 表表 進(jìn)進(jìn) 行行 插插 入入 、 刪刪 除除 和和 修修 改改 操操 作

18、作 而而 引引 發(fā)發(fā) 的的 。15觸發(fā)器觸發(fā)器在在 觸觸 發(fā)發(fā) 器器 內(nèi)內(nèi) ， 行行 級(jí)級(jí) 觸觸 發(fā)發(fā) 器器 可可 以以 引引 用用 觸觸 發(fā)發(fā) 器器 觸觸 發(fā)發(fā) 時(shí)時(shí) 已已 存存 在在 行行 中中 各各列列 的的 值值 。1） 對(duì) 于 INSERT 語(yǔ) 句 ， 要 被 插 入 的 新 行 中 各 列 的 新 值 包 含 在: new.column_name其其 中中 column_name是是 表表 中中 的的 列列 名名 。2） 對(duì)對(duì) 于于 UPDATE語(yǔ)語(yǔ) 句句 ， 將將 要要 被被 修修 改改 的的 行行 中中 各各 列列 的的 原原 來(lái)來(lái) 值值 包包 含含在在:old .column_name列列 的的 新新 值 包 含 在: new.column_name3） 對(duì) 于 DELETE語(yǔ) 句 ， 將 要 刪 除 的 行 的 各 列 的 值 放 在:old .column_nameli16觸發(fā)器觸發(fā)器17觸發(fā)器觸發(fā)器18觸發(fā)器示例觸發(fā)器示例CREATE OR REPLACE TRIGGER logempBEFORE INSERT OR UPDATE OR DELET