oracle異常及觸發(fā)器

oracle異常及觸發(fā)器0異常處理什么是異常情態(tài)異常處理處理的是運行時錯誤，異常分為預(yù)定義異常和用戶自定義異常。聲明異常情態(tài)異常情態(tài)在塊的聲明部分進行聲明，在塊的執(zhí)行部分進行觸發(fā)，在塊的異常部分進行處理。用戶定義類型異常情態(tài),需要程序員自己定義代碼，對異常情況進行處理。例：Declaree_toomanystudentsexception;1e_toomanystudents的作用域和本塊的其他變量相同。預(yù)定義型異常情態(tài)預(yù)定義型異常情態(tài)可以直接使用，沒有必要聲明。Invalid_cursor:當執(zhí)行非法的游標操作時，會引發(fā)這個錯誤，如試圖關(guān)閉已關(guān)閉的游標。Cursor_already_open：試圖打開已經(jīng)打開的游標，會引發(fā)這個錯誤。No_data_found：當selectinto語句沒有返回行時，和引用沒有賦值過的pl/sql表的元素時會引發(fā)這個錯誤。Too_may_rows：當selectinto語句返回多個行時，會引發(fā)這個錯誤。Invalid_number：當試圖從字符串轉(zhuǎn)換為數(shù)值失敗時，會引發(fā)這個錯誤，在過程性語句中會引發(fā)value_error錯誤。如：下面的語句引發(fā)invalid_number錯誤，因為‘x’不是數(shù)值。Insertintostudents(id,first_name,last_name)values(‘x’,’scott’,’smith’);2Storage_error和program_error：是內(nèi)部的異常情態(tài)，通常不會引發(fā)他們。Value_error：當在過程性語句中發(fā)生了算術(shù)、轉(zhuǎn)換、截尾或限制性錯誤時會引發(fā)這個情態(tài)。如果在sql語句中發(fā)生錯誤，會引發(fā)invalid_number錯誤。這個錯誤可能是一條賦值語句或selectinto語句的執(zhí)行結(jié)果。下面的兩個例子都引發(fā)value_error錯誤。Declarev_tempvarvarchar2(3);Beginv_tempvar:='ABCD';End;Declarev_tempvarnumber(2);Beginselectidintov_tempvarfromstudentswherelast_name='smith';End;3觸發(fā)異常情態(tài)當預(yù)定義的異常情態(tài)錯誤發(fā)生時，就會觸發(fā)該異常情態(tài)。用戶定義的異常情態(tài)由raise語句觸發(fā)。例：Declaree_toomanystudentsexceptionv_currentstudentsnumber(3);v_maxstudentsnumber(3);Beginselectcurrent_students,max_studentsintov_currentstudents,v_maxstudentsfromclasseswheredepartment=‘HIS’andcourse=‘101’;4ifv_currentstudents>v_maxstudentsthenraisee_toomanystudentsEndif;End;當引發(fā)異常情態(tài)時，控制轉(zhuǎn)給該塊的異常處理部分。如果沒有異常處理部分，該異常處理部分將傳給包含該塊的塊。一旦將控制轉(zhuǎn)給異常處理部分，沒有辦法再將控制返回給該塊的執(zhí)行部分。處理異常情態(tài)異常部分的語法如下：Exceptionwhenexception_namethen處理錯誤語句序列whenexception_namethen處理錯誤語句序列whenothersthen處理錯誤語句序列end;5一個處理器可以對多個異常情態(tài)進行處理，用or連接例：Exceptionwhenno_data_foundortoo_many_rowstheninsertintolog_table(info)values(‘a(chǎn)selecterroroccurred’);End;Others異常處理器將對所有語法的異常情態(tài)進行處理，一般放在異常處理的最后，可以保證所有的錯誤都被檢測到。Declaree_toomanystudentsexceptionv_currentstudentsnumber(3);v_maxstudentsnumber(3);v_errorcodenumber;v_errortextvarchar2(200);Beginselectcurrent_students,max_studentsintov_currentstudents,v_maxstudentsfromclasseswheredepartment=‘HIS’andcourse=‘101’;6ifv_currentstudents>v_maxstudentsthenraisee_toomanystudentsEndif;Exceptionwhene_toomanystudentstheninsertintolog_table(info)values(‘history101has’||v_currentstudents||‘students:maxallowedis‘||v_maxstudents);whenothersthenv_errorcode:=sqlcode;v_errortext:=substr(sqlerrm,1,200);insertintolog_table(info)values(‘a(chǎn)nothererroroccurred’);end;*sqlcode和sqlerrm先被賦值給本地變量，然后這些變量在sql語句中被使用，sqlcode和sqlerrm不能直接在sql語句中使用，因為他們是過程性的函數(shù)。sqlcode返回當前的錯誤號，sqlerrm返回當前的錯誤信息正文。

7代碼消息使用的sqlerrm函數(shù)0ora-0000:normal,successfulcompletionsqlerrm(0)+100Ora-1403:nodatafoundsqlerrm(100)+10User-definedexceptionSqlerrm(10)nullora-0000:normal,successfulcompletionsqlerrm-1Ora-0001:uniqueconstraint(.)violatedSqlerrm(-1)-54Ora-00054:resourcebusyandacquirebynowaitspecifiedSqlerrm(-54)8Exception_init可以將一個經(jīng)過命名的異常情態(tài)與一個特別的oracle錯誤相聯(lián)系。這樣可以用when撲獲此錯誤，不用others撲獲。通過exception_initpragma實現(xiàn)的。其語法如下：Pragmaexception_init(exception_name,oracle_error_number)Pragma必須在聲明部分例：下面的例子在運行時刻如遇到“ora-1400：mandatorynotnullcolumnmissingornullduringinsert”錯誤時，將引發(fā)e_missingnull異常情態(tài)。Declaree_missingnullexception;

pragmaexception_init(e_missingnull,-1400);Begininsertintostudents(id)values(null);Exceptionwhene_missingnulltheninsertintolog_table(info)values(‘ora-1400occurred’);End;9每次發(fā)發(fā)生pragmaexception_init時，，一個個oracle錯誤誤只能能和一一個用用戶定定義的的異常常情態(tài)態(tài)相關(guān)關(guān)聯(lián)。。在異異常處處理器器內(nèi)部部，sqlcode和sqlerrm將返返回發(fā)發(fā)生oracle錯錯誤的的代碼碼和錯錯誤信信息，，而不不會返返回用用戶定定義的的消息息。例：Declareex_hfyexception;Pragmaexception_init(ex_hfy,-1400);Begininsertintostudentsvalues(null);Exceptionwhenex_hfythendbms_output.put_line('不不能把把空值值插入入到非非空列列');End;注意，，這里里-1400不不能為為別的的，因因為把把空值值插入入到非非空列列的錯錯誤號號就是是這個個?？煽梢詥螁为殘?zhí)執(zhí)行insert語語句查查看錯錯誤號號。10使用raise_application_error可以用用raise_application_error創(chuàng)創(chuàng)建自自己的的錯誤誤消息息，這這比命命名的的異常常情態(tài)態(tài)更具具有說說明性性。其其語法法如下下：Raise_application_error(error_number,error_message,[keep-errors]);這里error_number是是從－－20,000到－－20,999之間間的數(shù)數(shù)，error_message是與與此錯錯誤相相關(guān)的的錯誤誤正文文。Keep_errors為布布爾值值，如如果他他為true，，則新新的錯錯誤被被添加加到已已經(jīng)引引發(fā)的的錯誤誤列表表中（（如果果有的的話））。如如果為為false（（為缺缺省值值），，則新新的錯錯誤將將替換換錯誤誤的當當前列列表。。例：：下面面的過過程為為一個個新的的學(xué)生生注冊冊以前前，檢檢查是是否在在班級級中有有足夠夠的地地方容容納他他。11Createorreplaceprocedureregister(p_studentidinstudents.id%type,p_departmentinclasses.department%type,p_courseinclasses.course%type)asv_currentstudentsnumber;v_maxstudentsnumber;Beginselectcurrent_students,max_studentsintov_currentstudent,v_maxstudentsfromclasseswheredepartment=p_departmentandcourse=p_course;12ifv_currentstudents+1>v_maxstudentsthenraise_application_error(-20000,‘can’’’taddmorestudentsto’’||p_department||‘‘‘‘||p_course);endif;classpackage.addstudent(p_studentid,p_department,p_course);Exceptionwhenno_data_foundthenraise_application_error(-20001,p_department||‘‘‘||p_course||‘‘doesn’’’texist!’);Endregister;以上程程序運運行時時，當當沒有有足夠夠的空空間容容納新新的學(xué)學(xué)生時時，返返回ora-20000:can’’taddmorestudentstohis101錯誤誤消息息。13觸發(fā)器器綜述觸發(fā)器器可以以理解解為特特殊的的存儲儲過程程。當當應(yīng)用用程序序用一一條滿滿足觸觸發(fā)器器條件件的SQLDML語句句指向向與觸觸發(fā)器器相連連接的的表時時，Oracle將將自動動執(zhí)行行該觸觸發(fā)器器以執(zhí)執(zhí)行任任務(wù)。。DML觸發(fā)發(fā)器，，定義義在對對數(shù)據(jù)據(jù)庫表表的操操縱行行為（（insert,delete,update)上上的觸觸發(fā)器器。前觸發(fā)發(fā)器、、后觸觸發(fā)器器。語句觸觸發(fā)、、行觸觸發(fā)14觸發(fā)器器15觸發(fā)器器16觸發(fā)器器關(guān)于觸觸發(fā)器器中的的old和和new關(guān)關(guān)鍵字字：old表示示數(shù)據(jù)據(jù)操縱縱前的的值，，old只只對UPDATE和和DELETE操作作。new表示示數(shù)據(jù)據(jù)操縱縱后的的值，，new只只對UPDATE和和INSERT操作作。17觸發(fā)器器定義的的一般般語法法：CREATE[ORREPLACE]TRIGGERtrigger{BEFORE|AFTER}{DELETE|INSERT|UPDATE[OFcolumn[,column]……]}[OR{DELETE|INSERT|UPDATE[OFcolumn[,column]…]}]…ONtableFOREACHROW[WHENcondition]BEGIN…pl/sqlblock..END[trigger]18觸發(fā)發(fā)器器示示例例CREATEORREPLACETRIGGERlogempBEFOREINSERTORUPDATEORDELETEONempFOREACHROWDECLAREstatementtypeCHAR(20);BEGINIFINSERTINGTHENstatementtype:='INSERTTRIGGER!';ELSIFUPDATINGTHENst