1、關(guān)于存儲過程編寫的一點總結(jié).數(shù)據(jù)類型和相關(guān)函數(shù)1 .數(shù)字PLS_INTEGER:-21474836472147483647NUMBER:十進制或者整數(shù)相關(guān)函數(shù)：ABS(n)絕對值CEIL(n)向上取整FLOOR(n)向下取整ROUND(m,n)四舍五入TRUNC(m,n)截取GREATEST(n,n)最大值LEAST(n,n)最小值MOD(m,n)余數(shù)SIGN(n)符號2 .字符CHAR:定長字符VARCHAR2:變長字符相關(guān)函數(shù)：INITCAP/LOWER/UPPER(c)大小寫格式化INSTR/INSTRB(c1,c2川,m)搜索指定字符/字節(jié)的位置索引LENGTH/LENGTHB(c)字

2、符/字節(jié)數(shù)TRIM(LEADING|TRAILING|BOTHc2FROMc1)從左/右/全部截掉指定字符SUBSTR(c,m,n)截取REPLACE(c1,c2,c3)替代3 .日期DATE相關(guān)函數(shù)ADD_MONTHS(d,n)增力口月份LAST_DAY(d)當(dāng)月最后一天MONTHS_BETWEEN(d1,d2)兩個日期之間的月數(shù)其它函數(shù)：轉(zhuǎn)換函數(shù)TO_CHAR(d|n,fmt)/TO_NUMBER(c,fmt)/TO_DATE(c,fmt)DECODE(expr,case,result,case,result兒default)嵌入式IF條件判斷函數(shù)二.匿名塊和存儲過程結(jié)構(gòu)存貯過程應(yīng)該是一組

3、經(jīng)過壓縮處理的經(jīng)常使用的一組命令，如交叉表的查詢、更新和插入操作。存貯過程允許程序員簡單地將該過程作為一個函數(shù)來調(diào)用，而不是重復(fù)地執(zhí)行過程內(nèi)部的語句。而且存貯過程還有些附加的優(yōu)點。當(dāng)存貯過程執(zhí)行時，SQL語句將在服務(wù)器中繼續(xù)運行，一些數(shù)據(jù)信息將會返回給用戶的電腦直至過程執(zhí)行完畢。這會極大地提高性能并帶來了附加的好處。存貯過程在第一次執(zhí)行時，在數(shù)據(jù)庫經(jīng)過了編譯操作，編譯的映象將存貯在服務(wù)器的過程中。因此你不必在每一次執(zhí)行它的時候都對它進行優(yōu)化，這也使性能得到了提高。12345e789ia11121314151E17IB1920CREATEORREPLACEPBDCEDVKEPRCACCOWTIT

4、ONEYISV_COSTTYPKYD_XTC£.SXZ%TYPE;V_DWFLVAACHHR2tLOQO);V_GWFLNUKBER(12,4);頭部分聲明部分2324252G272B293031323334353S373939404142434445464748BECIN:=SYSDATEV_COSTTYPH:='1'j主V_I>NFL：-O.OOUV_GWFL:=0.001;SELECTSXZINTOV_COSTTTTEFROMTD_XTCSNHERESX=，違約金計罐方式IFV_COSTTYPE*'1'THENVflARECINC_LACK

5、INFDLOOP體BEGINSELECTA.DNFL/A.GNFLINTOV_DNFL,V_GJJFLEROMYD_WYFL皂，YD_YHJEDABHEREB.HH=V_HH部INBA.IDBrWTFL;EXCEPTIONWHENOTKEESTHEEINSERTIHTOJJSYCH?O<(Z>CS,HHYCSM)LUEffEY燈ATeH_HHJ用戶檔箕中違約費率ID和費率表中ID不能關(guān)聯(lián)；ENP;V_aFHJ:*V_BCYJI>F-V_BCTSJE分ENDLOOP;ELSENULL;ENDIB;V_EWDTIHE:=SYSIME;INSERTINTOTD_0VBRJI0NE&

6、#165;L0GtMTRTIHE/BIGINTIHB,ENDTIME,FLAKVhLlTESSYSDATE,V_EEGINTIME,V_ENDTIHE,1);CDNKIT；EXCEPTIONOTHERSTHENR0LLBATK;INSERTINTOYD_fliVERHDNEYLOC<DATETIME,BEGINTIHH,ENTTLHE,FLAG)VALVES號DATE,V_BEGIMTIHE,V_Sm)TIHS,0);COMMIT;ENDPKC_ACCdHWTHONBY;異常部分匿名塊就是將頭部分改成DECLARE的存儲過程語法格式：createprocedureprocedure_na

7、me()parameter_namedatatype(length)|(precision,scale)=defaultoutput,parameter_namedatatype(length)|(precision,scale)=defaultoutput)withrecompileasSQL_statements運行存貯過程的EXECUTE命令的語法如下：executereturn_status=procedure_nameparameter_name=value|parameter_name=variableoutput.withrecompile創(chuàng)建存儲過程的例子createproce

8、dureMatch_Names_To_Mediadescriptionchar(30)asselectARTISTS.namefromARTISTS,MEDIA,RECORDINGSwhereMEDIA.description=descriptionandMEDIA.media_type=RECORDINGS.media_typeandRECORDINGS.artist_id=ARTISTS.artist_id執(zhí)行存儲過程的例子：executeMatch_Names_To_Media"CD"存儲過程概述存儲過程(StoredProcedure)是一組為了完成特定功能的SQL

9、語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中.用戶通過指定存儲過程的名字，并給出參數(shù)(如果該存儲過程帶有參數(shù))來執(zhí)行它.存貯過程是一個專業(yè)數(shù)據(jù)庫編程人員必須掌握的概念.存貯過程可以在最大程度上發(fā)揮出SQL的潛能.該功能可以被如java等編程語言，像執(zhí)行自己的函數(shù)一樣地調(diào)用或執(zhí)行.存儲過程的優(yōu)點：A存儲過程允許標準組件式編程存儲過程在被創(chuàng)建以后可以在程序中被多次調(diào)用，而不必重新編寫該存儲過程的SQL句.而且數(shù)據(jù)庫專業(yè)人員可隨時對存儲過程進行修改，但對應(yīng)用程序源代碼毫無影響.因為應(yīng)用程序源代碼只包含存儲過程的調(diào)用語句，從而極大地提高了程序的可移植性.B存儲過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度如果某一操作包含大量的Tran

10、saction-SQL代碼或分別被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快很多.因為存儲過程是預(yù)編譯的，在首次運行一個存儲過程時查詢優(yōu)化器對其進行分析優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行計劃.C存儲過程能夠減少網(wǎng)絡(luò)流量對于同一個針對數(shù)據(jù)數(shù)據(jù)庫對象的操作，如查詢，修改，如果這一操作所涉及到的Transaction-SQL語句被組織成一存儲過程,那么當(dāng)在客戶計算機上調(diào)用該存儲過程時,網(wǎng)絡(luò)中傳送的只是該調(diào)用語句，降低網(wǎng)絡(luò)負載.D存儲過程可被作為一種安全機制來充分利用系統(tǒng)管理員通過對執(zhí)行某一存儲過程的權(quán)限進行限制，從而能夠?qū)崿F(xiàn)對相應(yīng)的數(shù)據(jù)訪問權(quán)限的限制.避免非授權(quán)用戶對數(shù)據(jù)的訪問保證數(shù)據(jù)的安全.三

11、.變量的聲明和使用一般格式：variable_nametypeCONSTANTNOTNULL:=initial_value;強類型聲明variable_nameschema.table_name.column_name%TYPE;弱類型聲明關(guān)鍵字variable_name指定變量名。最長30個字符，必須用字母開始，可以包含字母、數(shù)字、下劃線、$和#等等。變量名除了放在雙引號中的之外是不區(qū)分大小寫的，其它地方（如表名，列名等等）也基本如此，除了放在雙引號之中，不可以使用保留字（如SELECT等等）作為變量type指定變量數(shù)據(jù)類型。CONSTANT保留字，指定一個初始化值用于該變量，而且不可以改變

12、該值。NOTNULL指定一個初始化值用于該變量，而且不可以設(shè)置為NULL。initial_value初始化值。省略則默認為NULL。指定了CONSTANT或者NOTNULL則必須指定初始化值。schema數(shù)據(jù)庫中的架構(gòu)（用戶）名。省略則使用當(dāng)前架構(gòu)（用戶）名。table_name特定架構(gòu)中的表名。column_name特定表中的列名。關(guān)于聲明記錄類型和索引表類型PL/SQL中，想要將一定數(shù)量的相關(guān)變量作為單元操作時，可以聲明記錄類型。該類型與C語言中的結(jié)構(gòu)相似。要使用記錄，必須首先定義記錄類型。然后基于該類型聲明記錄變量。TYPEtype_nameISRECORD(variable_namet

13、ypeNOTNULL:=initial_value,variable_nametypeNOTNULL:=initial_value);record_nametype_name;記錄的獨立字段可以使用點符號(.)record_name.variable_namePL/SQL中需要一個變量多次出現(xiàn)時候，可以定義索引表。類似與C語言中的一維數(shù)組。同樣，必須先定義類型，然后基于該類型聲明索引表變量。TYPEtabletype_nameISTABLEOFtype|schema.table_name%ROWTYPEINDEXBYBINARY_INTEGER;table_nametype_name;聲明的結(jié)

14、果是具有兩列的內(nèi)存表：KEY(BINARY_INTEGER)VALUE(在聲明語句中定義的類型type)可以使用table_name(key)來引用單個元素。四.EXECUTEIMMEDIATE在存儲過程內(nèi)使用的SQL語句只能是下列之一：SELECTINSERTUPDATEDELETECOMMITROLLBACK(還有幾個很不常用的，沒有列出)PL/SQL中不允許數(shù)據(jù)定義語言(DDL),包括會話和系統(tǒng)控制語句。Oracle自擴展的統(tǒng)計分析函數(shù)(例如MAXover.)也不被允許。當(dāng)因程序需要而必須在PL/SQL中執(zhí)行這些不被允許的語句時候，就可以使用EXECUTEIMMEDIATE。EXECUT

15、EIMMEDIATEstring;EXECUTEIMMEDIATE后面跟一個由可執(zhí)行語句組成的字符串，在編譯的時候由于是字符串所以不會被編譯，只有在執(zhí)行存儲過程的時候才會把這個字符串提交編譯執(zhí)行。利用這點可以執(zhí)行例如TRUNCATETABLE,CREATETABLE,DROPTABLE等等語句。還有一個功能是可以動態(tài)生成SQL執(zhí)行語句。可以在存儲過程內(nèi)部將sql語句分割成數(shù)個字符串，實現(xiàn)sql語句的拼接，然后利用EXECUTEIMMEDIATE執(zhí)行。五.IF條件控制IFexpressionTHENstatement;statement;ELSIFexpressionTHENststement;

16、statement;ELSEststement;statement;ENDIF;6 .LOOP循環(huán)控制1. LOOP循環(huán)LOOPEXIT;EXITWHENcondition;statement;statement;-ENDLOOP;2. WHILE循環(huán)WHILEconditionLOOPstatement;statement;-ENDLOOP;3. FOR循環(huán)FORcounterINREVERSEstart.endLOOPstatement;statement;-ENDLOOP;7 .異常處理1.異常的類型異常Oracle錯誤號描述CURSOR_ALREADY_OPENORA-06511當(dāng)游標

17、已經(jīng)處于打開狀態(tài)時，試圖再次打開游標DUP_VAL_ON_INDEXORA-00001違反唯一約束條件INVALID_CURSORORA-01001非法操作，如試圖關(guān)閉已經(jīng)關(guān)閉的游標INVALID_NUMBERORA-01722在SQL中將字符進行顯式或隱式轉(zhuǎn)換為數(shù)字時失敗LOGIN_DENIEDORA-01017無效的用戶名或者口令NO_DATA_FOUNDORA-01404查詢沒有返回行NOT_LOGGED_ONORA-01012會話沒有連接到Oracle上OTHERS非預(yù)期錯誤PROGRAM_ERRORORA-06501內(nèi)部PL/SQL錯誤ROWTYPE_MISMATCHORA-0650

18、4主機游標變量與PL/SQL游標變量的類型/、匹配STORAGE_ERRORORA-06500進程超出了內(nèi)存范圍TIMEOUT_ON_RESOURCEORA-00051需要資源，但是操作超時TOO_MANY_ROWSORA-01422SELECT查詢返回不止一行TRANSACTION_BACKED_OUTORA-00061死鎖條件強制ROLLBACKVALUE_ERRORORA-06502在過程語句中發(fā)生了轉(zhuǎn)換錯誤ZERO_DIVIDEORA-01476試圖除以02 .異常的處理EXCEPTIONWHENexception_nameTHENpl/sql_statements;WHENexcep

19、tion_nameTHENpl/sql_statements;WHENOTHERSTHENpl/sql_statements;END;異常部分可以使用兩個異常函數(shù)：SQLCODE返回Oracle錯誤號SQLERRM返回Oracle錯誤文本3 .拋出異常和自定義異常RAISERAISEexception_name;RAISE_APPLICATION_ERRORRAISE_APPLICATION_ERROR(error_numer,error_text,keep_errors);error_number指定提供的錯誤號。該錯誤號應(yīng)該在-20000和-20999之間提供。該范圍是Oracle為用戶定

20、義的錯誤號設(shè)置的。error_text長度為12048字節(jié)的字符串，提供與錯誤相關(guān)的文本。keep_errors布爾值，為TRUE或FALSE。如果指定為TRUE,則將錯誤添加到已經(jīng)提供的錯誤清單中。如果為FALSE,則使用以前的錯誤代替堆棧中的錯誤。默認為FALSE。八.游標的聲明和使用PL/SQL游標提供了從Oracle數(shù)據(jù)庫中訪問多行數(shù)據(jù)，并在每一行上執(zhí)行程序的完全控制方法。1 .使用游標步驟聲明游標CURSORcursor_nameISselect_statement;聲明動態(tài)游標CURSORcursor_typeISREFCURSOR;cursor_namecursor_type;打開游標OPENcursor