數(shù)據(jù)庫存儲過程課件

6.3數(shù)據(jù)庫存儲過程6.3數(shù)據(jù)庫存儲過程了解存儲過程的概念掌握存儲過程創(chuàng)建、刪除的方法掌握存儲過程的執(zhí)行方法掌握PostgreSQL的PL/SQL基本語法理解存儲過程的優(yōu)缺點【本節(jié)的主要內(nèi)容】了解存儲過程的概念【本節(jié)的主要內(nèi)容】一、什么是存儲過程存儲過程（StoredProcedure）是一種數(shù)據(jù)庫的對象；由一組能完成特定功能的SQL語句集構成；是把經(jīng)常會被重復使用的SQL語句邏輯塊封裝起來，經(jīng)編譯后，存儲在數(shù)據(jù)庫服務器端；當被再次調(diào)用時，而不需要再次編譯；當客戶端連接到數(shù)據(jù)庫時，用戶通過指定存儲過程的名字并給出參數(shù)，數(shù)據(jù)庫就可以找到相應的存儲過程予以調(diào)用。一、什么是存儲過程存儲過程（StoredProcedure二、創(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)建存儲過程不同的數(shù)據(jù)庫系統(tǒng)創(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;//$$表明代碼的結束,LANGUAGE后面指明所用的編程語言（1）name：要創(chuàng)建的存儲過程名；（3）argmode：存儲過程參數(shù)的模式可以為IN、OUT或INOUT，缺省值是IN。（4）argname：形式參數(shù)的名字。（5）RETURNS：返回值；RETURNSTABLE：返回二維表（2）ORREPLACE：覆蓋同名的存儲過程；三、創(chuàng)建存儲過程的語法CREATE[ORREPLACE四、創(chuàng)建存儲過程的示例創(chuàng)建一個名為countRecords()的存儲過程統(tǒng)計STUDENT表的記錄數(shù)。CREATEORREPLACEFUNCTIONcountRecords()RETURNSintegerAS$count$declarecountinteger;BEGIN

SELECTcount(*)intocountFROMSTUDENT;RETURNcount;END;

$count$LANGUAGEplpgsql;四、創(chuàng)建存儲過程的示例創(chuàng)建一個名為countRecords(五、執(zhí)行存儲過程如果程序員需要在查詢窗口執(zhí)行存儲過程，語法形式如下：

select

存儲過程名（參數(shù)）；或者：

select*from存儲過程名（參數(shù)）；例如：selectcountRecords();或者：select*fromcountRecords();五、執(zhí)行存儲過程如果程序員需要在查詢窗口執(zhí)行存儲過程，語法形五、執(zhí)行存儲過程（續(xù)）如果程序員需要存儲過程調(diào)用其它存儲過程，語法形式如下：selectinto自定義變量from存儲過程名（參數(shù)）；CREATEORREPLACEFUNCTIONtestExec()returnsintegerAS$$declarerecinteger;BEGIN

selectintoreccountRecords();

//如果不關心countRecords()的返回值，則可用PERFORMcountRecords()代替;returnrec;END;

$$LANGUAGEplpgsql;五、執(zhí)行存儲過程（續(xù)）如果程序員需要存儲過程調(diào)用其它存儲過程六、刪除存儲過程如果程序員需要刪除存儲過程，語法形式如下：DROPFUNCTION[IFEXISTS]name([[argmode][argname]argtype[,...]])[CASCADE|RESTRICT]主要參數(shù)：（1）IFEXISTS：如果指定的存儲過程不存在，那么發(fā)出提示信息。（2）name：現(xiàn)存的存儲過程名稱。（3）argmode：參數(shù)的模式：IN(缺省),OUT,INOUT,VARIADIC。請注意，實際并不注意OUT參數(shù)，因為判斷存儲過程的身份只需要輸入?yún)?shù)。（4）argname：參數(shù)的名字。請注意，實際上并不注意參數(shù)的名字，因為判斷函數(shù)的身份只需要輸入?yún)?shù)的數(shù)據(jù)類型。（5）argtype：如果有的話，是存儲過程參數(shù)的類型。（6）CASCADE：級聯(lián)刪除依賴于存儲過程的對象(如觸發(fā)器)。（7）RESTRICT：如果有任何依賴對象存在，則拒絕刪除該函數(shù)；這個是缺省值。六、刪除存儲過程如果程序員需要刪除存儲過程，語法形式如下：D六、刪除存儲過程（續(xù)）例子：假如需要刪除前面定義的存儲過程testExec()DROPFUNCTIONIFEXISTStestExec()六、刪除存儲過程（續(xù)）例子：假如需要刪除前面定義的存儲過程七、PL/SQL基本語法1、聲明局部變量。變量聲明的語法如下：

declare

變量名變量類型；如果聲明變量為記錄類型，變量聲明格式為：variable_nameRECORD;注：RECORD不是真正的數(shù)據(jù)類型，只是一個占位符。例如：declare

countintger；recRECORD；七、PL/SQL基本語法1、聲明局部變量。變量聲明的語法如下七、PL/SQL基本語法（續(xù)）2、條件語句在PL/pgSQL中有以下三種形式的條件語句，與其他高級語言的條件語句意義相同。1).IF-THENIFboolean-expressionTHENstatementsENDIF;2).IF-THEN-ELSEIFboolean-expressionTHENstatementsELSE

3).IF-THEN-ELSIF-ELSEIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSEstatementsENDIF;七、PL/SQL基本語法（續(xù)）2、條件語句1).IF-TH七、PL/SQL基本語法（續(xù)）3、循環(huán)語句1).LOOP語句LOOPstatementsENDLOOP[label];2).EXITEXIT[label][WHENexpression];例如：

LOOPcount=count+1;

EXITWHENcount>100;

ENDLOOP;3).CONTINUE

CONTINUE[label][WHENexpression];例如：

LOOPcount=count+1;EXITWHENcount>100;

CONTINUEWHENcount<50;count=count+1;

ENDLOOP;七、PL/SQL基本語法（續(xù)）3、循環(huán)語句1).LOOP七、PL/SQL基本語法（續(xù)）3、循環(huán)語句4).WHILEWHILEexpressionLOOPstatementsENDLOOP;例如：

WHILEamount_owed>0ANDbalance>0LOOP--dosomethingENDLOOP;5).FORFORnameIN[REVERSE]expression...expressionLOOP

statementsENDLOOP;例如：

FORiIN1...10LOOP

RAISENOTICE'iIS%',i;ENDLOOP;FORiINREVERSE10...1LOOP--dosomethingENDLOOP;七、PL/SQL基本語法（續(xù)）3、循環(huán)語句4).WHIL七、PL/SQL基本語法（續(xù)）4、遍歷命令結果FORrecord_or_rowINqueryLOOPstatementsENDLOOP;FOR循環(huán)可以遍歷命令的結果并操作相應的數(shù)據(jù)，例如：declare

recRECORD；

FORrecINSELECTsid,snameFROMstudentLOOP

raisenotice‘%-,%-’,rec.sid,rec.sname;

ENDLOOP;七、PL/SQL基本語法（續(xù)）4、遍歷命令結果FORrec八、存儲過程的優(yōu)缺點1、使用存儲過程的優(yōu)點

(1)減少網(wǎng)絡通信量(2)執(zhí)行速度更快(3)更強的適應性(4)降低了業(yè)務實現(xiàn)與應用程序的耦合(5)降低了開發(fā)的復雜性(6)保護數(shù)據(jù)庫元信息(7)增強了數(shù)據(jù)庫的安全性八、存儲過程的優(yōu)缺點1、使用存儲過程的優(yōu)點八、存儲過程的優(yōu)缺點2、使用存儲過程的缺點

(1)SQL本身是一種結構化查詢語言，而存儲過程本質(zhì)上是過程化的程序；面對復雜的業(yè)務邏輯，過程化處理邏輯相對比較復雜；而SQL語言的優(yōu)勢是面向數(shù)據(jù)查詢而非業(yè)務邏輯的處理。(2)如果存儲過程的參數(shù)或返回數(shù)據(jù)發(fā)生變化，一般需要修改存儲過程的代碼，同時還需要更新主程序調(diào)用存儲過程的代碼。(3)開發(fā)調(diào)試復雜，由于缺乏支持存儲過程的集成開發(fā)環(huán)境，存儲過程的開發(fā)調(diào)試要比一般程序困難。

(4)可移植性差

八、存儲過程的優(yōu)缺點2、使用存儲過程的缺點6.3數(shù)據(jù)庫存儲過程6.3數(shù)據(jù)庫存儲過程了解存儲過程的概念掌握存儲過程創(chuàng)建、刪除的方法掌握存儲過程的執(zhí)行方法掌握PostgreSQL的PL/SQL基本語法理解存儲過程的優(yōu)缺點【本節(jié)的主要內(nèi)容】了解存儲過程的概念【本節(jié)的主要內(nèi)容】一、什么是存儲過程存儲過程（StoredProcedure）是一種數(shù)據(jù)庫的對象；由一組能完成特定功能的SQL語句集構成；是把經(jīng)常會被重復使用的SQL語句邏輯塊封裝起來，經(jīng)編譯后，存儲在數(shù)據(jù)庫服務器端；當被再次調(diào)用時，而不需要再次編譯；當客戶端連接到數(shù)據(jù)庫時，用戶通過指定存儲過程的名字并給出參數(shù)，數(shù)據(jù)庫就可以找到相應的存儲過程予以調(diào)用。一、什么是存儲過程存儲過程（StoredProcedure二、創(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)建存儲過程不同的數(shù)據(jù)庫系統(tǒng)創(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;//$$表明代碼的結束,LANGUAGE后面指明所用的編程語言（1）name：要創(chuàng)建的存儲過程名；（3）argmode：存儲過程參數(shù)的模式可以為IN、OUT或INOUT，缺省值是IN。（4）argname：形式參數(shù)的名字。（5）RETURNS：返回值；RETURNSTABLE：返回二維表（2）ORREPLACE：覆蓋同名的存儲過程；三、創(chuàng)建存儲過程的語法CREATE[ORREPLACE四、創(chuàng)建存儲過程的示例創(chuàng)建一個名為countRecords()的存儲過程統(tǒng)計STUDENT表的記錄數(shù)。CREATEORREPLACEFUNCTIONcountRecords()RETURNSintegerAS$count$declarecountinteger;BEGIN

SELECTcount(*)intocountFROMSTUDENT;RETURNcount;END;

$count$LANGUAGEplpgsql;四、創(chuàng)建存儲過程的示例創(chuàng)建一個名為countRecords(五、執(zhí)行存儲過程如果程序員需要在查詢窗口執(zhí)行存儲過程，語法形式如下：

select

存儲過程名（參數(shù)）；或者：

select*from存儲過程名（參數(shù)）；例如：selectcountRecords();或者：select*fromcountRecords();五、執(zhí)行存儲過程如果程序員需要在查詢窗口執(zhí)行存儲過程，語法形五、執(zhí)行存儲過程（續(xù)）如果程序員需要存儲過程調(diào)用其它存儲過程，語法形式如下：selectinto自定義變量from存儲過程名（參數(shù)）；CREATEORREPLACEFUNCTIONtestExec()returnsintegerAS$$declarerecinteger;BEGIN

selectintoreccountRecords();

//如果不關心countRecords()的返回值，則可用PERFORMcountRecords()代替;returnrec;END;

$$LANGUAGEplpgsql;五、執(zhí)行存儲過程（續(xù)）如果程序員需要存儲過程調(diào)用其它存儲過程六、刪除存儲過程如果程序員需要刪除存儲過程，語法形式如下：DROPFUNCTION[IFEXISTS]name([[argmode][argname]argtype[,...]])[CASCADE|RESTRICT]主要參數(shù)：（1）IFEXISTS：如果指定的存儲過程不存在，那么發(fā)出提示信息。（2）name：現(xiàn)存的存儲過程名稱。（3）argmode：參數(shù)的模式：IN(缺省),OUT,INOUT,VARIADIC。請注意，實際并不注意OUT參數(shù)，因為判斷存儲過程的身份只需要輸入?yún)?shù)。（4）argname：參數(shù)的名字。請注意，實際上并不注意參數(shù)的名字，因為判斷函數(shù)的身份只需要輸入?yún)?shù)的數(shù)據(jù)類型。（5）argtype：如果有的話，是存儲過程參數(shù)的類型。（6）CASCADE：級聯(lián)刪除依賴于存儲過程的對象(如觸發(fā)器)。（7）RESTRICT：如果有任何依賴對象存在，則拒絕刪除該函數(shù)；這個是缺省值。六、刪除存儲過程如果程序員需要刪除存儲過程，語法形式如下：D六、刪除存儲過程（續(xù)）例子：假如需要刪除前面定義的存儲過程testExec()DROPFUNCTIONIFEXISTStestExec()六、刪除存儲過程（續(xù)）例子：假如需要刪除前面定義的存儲過程七、PL/SQL基本語法1、聲明局部變量。變量聲明的語法如下：

declare

變量名變量類型；如果聲明變量為記錄類型，變量聲明格式為：variable_nameRECORD;注：RECORD不是真正的數(shù)據(jù)類型，只是一個占位符。例如：declare

countintger；recRECORD；七、PL/SQL基本語法1、聲明局部變量。變量聲明的語法如下七、PL/SQL基本語法（續(xù)）2、條件語句在PL/pgSQL中有以下三種形式的條件語句，與其他高級語言的條件語句意義相同。1).IF-THENIFboolean-expressionTHENstatementsENDIF;2).IF-THEN-ELSEIFboolean-expressionTHENstatementsELSE

3).IF-THEN-ELSIF-ELSEIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSIFboolean-expressionTHENstatementsELSEstatementsENDIF;七、PL/SQL基本語法（續(xù)）2、條件語句1).IF-TH七、PL/SQL基本語法（續(xù)）3、循環(huán)語句1).LOOP語句LOOPstatementsENDLOOP[label];2).EXITEXIT[label][WHENexpression];例如：

LOOPcount=count+1;

EXITWHENcount>100;

ENDLOOP;3).CONTINUE

CONTINUE[label][WHENexpression];例如：

LOOPcount=count+1;EXITWHENcount>100;

CONTINUEWHENcount<50;count=count+1;

ENDLOOP;七、PL/SQL基本語法（續(xù)）3、循環(huán)語句1).LOOP七、PL/SQL基本語法（續(xù)）3、循環(huán)語句4).WHILEWHILEexpressionLOOPstatementsENDLOOP;例如：

WHILEamount_owed>0ANDbalance>0LOOP--dosomethingENDLOOP;5).FORFORnameIN[REVERSE]expressio