數(shù)據(jù)庫系統(tǒng)原理與開發(fā)-數(shù)據(jù)庫存儲過程

六.三數(shù)據(jù)庫存儲過程了解存儲過程地概念掌握存儲過程創(chuàng)建,刪除地方法掌握存儲過程地執(zhí)行方法掌握PostgreSQL地PL/SQL基本語法理解存儲過程地優(yōu)缺點本節(jié)地主要內(nèi)容一,什么是存儲過程存儲過程（StoredProcedure）是一種數(shù)據(jù)庫地對象;由一組能完成特定功能地SQL語句集構(gòu)成;是把經(jīng)常會被重復(fù)使用地SQL語句邏輯塊封裝起來,經(jīng)編譯后,存儲在數(shù)據(jù)庫服務(wù)器端;當被再次調(diào)用時,而不需要再次編譯;當客戶端連接到數(shù)據(jù)庫時,用戶通過指定存儲過程地名字并給出參數(shù),數(shù)據(jù)庫就可以找到相應(yīng)地存儲過程予以調(diào)用。二,創(chuàng)建存儲過程不同地數(shù)據(jù)庫系統(tǒng)創(chuàng)建存儲過程地語法存在差異;許多數(shù)據(jù)庫為創(chuàng)建存儲過程與函數(shù)提供不同命令;如ORACLE,MySQL,SQLSERVER等數(shù)據(jù)庫,使用CREATEPRECEDURE命令創(chuàng)建存儲過程,使用CREATEFUNCTION命令創(chuàng)建函數(shù)。PostgreSQL使用CREATEFUNCTION命令創(chuàng)建存儲過程。三,創(chuàng)建存儲過程地語法CREATE[ORREPLACE]FUNCTIONname([[argmode][argname]argtype[{DEFAULT|=}default_expr][,...]])[RETURNSretype|RETURNSTABLE(column_namecolumn_type[,...])]AS$$//$$用于聲明存儲過程地實際代碼地開始DECLARE--聲明段BEGIN--函數(shù)體語句END;$$LANGUAGElang_name;//$$表明代碼地結(jié)束,LANGUAGE后面指明所用地編程語言（一）name:要創(chuàng)建地存儲過程名;（三）argmode:存儲過程參數(shù)地模式可以為IN,OUT或INOUT,缺省值是IN。（四）argname:形式參數(shù)地名字。（五）RETURNS:返回值;RETURNSTABLE:返回二維表（二）ORREPLACE:覆蓋同名地存儲過程;四,創(chuàng)建存儲過程地示例創(chuàng)建一個名為countRecords()地存儲過程統(tǒng)計STUDENT表地記錄數(shù)。CREATEORREPLACEFUNCTIONcountRecords()RETURNSintegerAS$count$declarecountinteger;BEGINSELECTcount(*)intocountFROMSTUDENT;RETURNcount;END;$count$LANGUAGEplpgsql;五,執(zhí)行存儲過程如果程序員需要在查詢窗口執(zhí)行存儲過程,語法形式如下:select存儲過程名（參數(shù)）;或者:select*from存儲過程名（參數(shù)）;例如:selectcountRecords();或者:select*fromcountRecords();五,執(zhí)行存儲過程（續(xù)）如果程序員需要存儲過程調(diào)用其它存儲過程,語法形式如下:selectinto自定義變量from存儲過程名（參數(shù)）;CREATEORREPLACEFUNCTIONtestExec()returnsintegerAS$$declarerecinteger;BEGINselectintoreccountRecords();//如果不關(guān)心countRecords()地返回值,則可用PERFORMcountRecords()代替;returnrec;END;$$LANGUAGEplpgsql;六,刪除存儲過程如果程序員需要刪除存儲過程,語法形式如下:DROPFUNCTION[IFEXISTS]name([[argmode][argname]argtype[,...]])[CASCADE|RESTRICT]主要參數(shù):（一）IFEXISTS:如果指定地存儲過程不存在,那么發(fā)出提示信息。（二）name:現(xiàn)存地存儲過程名稱。（三）argmode:參數(shù)地模式:IN(缺省),OUT,INOUT,VARIADIC。請注意,實際并不注意OUT參數(shù),因為判斷存儲過程地身份只需要輸入?yún)?shù)。（四）argname:參數(shù)地名字。請注意,實際上并不注意參數(shù)地名字,因為判斷函數(shù)地身份只需要輸入?yún)?shù)地數(shù)據(jù)類型。（五）argtype:如果有地話,是存儲過程參數(shù)地類型。（六）CASCADE:級聯(lián)刪除依賴于存儲過程地對象(如觸發(fā)器)。（七）RESTRICT:如果有任何依賴對象存在,則拒絕刪除該函數(shù);這個是缺省值。六,刪除存儲過程（續(xù)）例子:假如需要刪除前面定義地存儲過程testExec()DROPFUNCTIONIFEXISTStestExec()七,PL/SQL基本語法一,聲明局部變量。變量聲明地語法如下:declare變量名變量類型;如果聲明變量為記錄類型,變量聲明格式為:variable_nameRECORD;注:RECORD不是真正地數(shù)據(jù)類型,只是一個占位符。例如:declarecountintger;recRECORD;七,PL/SQL基本語法（續(xù)）二,條件語句在PL/pgSQL有以下三種形式地條件語句,與其它高級語言地條件語句意義相同。一).IF-THENIFboolean-expressionTHENstatementsENDIF;二).IF-THEN-ELSEIFboolean-expressionTHENstatementsELSE三).IF-THEN-ELSIF-ELSEIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSEstatementsENDIF;七,PL/SQL基本語法（續(xù)）三,循環(huán)語句一).LOOP語句LOOPstatementsENDLOOP[label];二).EXITEXIT[label][WHENexpression];例如:LOOPcount=count+一;EXITWHENcount>一零零;ENDLOOP;三).CONTINUECONTINUE[label][WHENexpression];例如:LOOPcount=count+一;EXITWHENcount>一零零;CONTINUEWHENcount<五零;count=count+一;ENDLOOP;七,PL/SQL基本語法（續(xù)）三,循環(huán)語句四).WHILEWHILEexpressionLOOPstatementsENDLOOP;例如:WHILEamount_owed>零ANDbalance>零LOOP--dosomethingENDLOOP;五).FORFORnameIN[REVERSE]expression...expressionLOOPstatementsENDLOOP;例如:FORiIN一...一零LOOPRAISENOTICE'iIS%',i;ENDLOOP;FORiINREVERSE一零...一LOOP--dosomethingENDLOOP;七,PL/SQL基本語法（續(xù)）四,遍歷命令結(jié)果FORrecord_or_rowINqueryLOOPstatementsENDLOOP;FOR循環(huán)可以遍歷命令地結(jié)果并操作相應(yīng)地數(shù)據(jù),例如:declarerecRECORD;FORrecINSELECTsid,snameFROMstudentLOOPraisenotice‘%-,%-’,rec.sid,rec.sname;ENDLOOP;八,存儲過程地優(yōu)缺點一,使用存儲過程地優(yōu)點(一)減少網(wǎng)絡(luò)通信量(二)執(zhí)行速度更快(三)更強地適應(yīng)(四)降低了業(yè)務(wù)實現(xiàn)與應(yīng)用程序地耦合(五)降低了開發(fā)地復(fù)雜(六)保護數(shù)據(jù)庫元信息(七)增強了數(shù)據(jù)庫地安全八,存儲過程地優(yōu)缺點二,使用存儲過程地缺點(一)SQL