存儲(chǔ)過(guò)程課件

12.存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程1存儲(chǔ)過(guò)程綜述2創(chuàng)建、執(zhí)行、修改、刪除簡(jiǎn)單存儲(chǔ)過(guò)程3創(chuàng)建和執(zhí)行含參數(shù)的存儲(chǔ)過(guò)程4存儲(chǔ)過(guò)程的重新編譯5利用模板創(chuàng)建存儲(chǔ)過(guò)程12.存儲(chǔ)過(guò)程導(dǎo)言：存儲(chǔ)過(guò)程例：查看某系（計(jì)算機(jī)系、商務(wù)技術(shù)系、機(jī)電系、人文系）的班級(jí)名稱。

createviewxb_bjmcasselect班級(jí)名稱from班級(jí)where系部代碼=（select系部代碼from系部where系部名稱=‘計(jì)算機(jī)系’）12.存儲(chǔ)過(guò)程1、存儲(chǔ)過(guò)程概念存儲(chǔ)過(guò)程是一種數(shù)據(jù)庫(kù)對(duì)象，是為了實(shí)現(xiàn)某個(gè)特定任務(wù)，將一組預(yù)編譯的SQL語(yǔ)句以一個(gè)存儲(chǔ)單元的形式存儲(chǔ)在服務(wù)器上，供用戶調(diào)用。存儲(chǔ)過(guò)程在第一次執(zhí)行時(shí)進(jìn)行編譯，然后將編譯好的代碼保存在高速緩存中以便以后調(diào)用，這樣可以提高代碼的執(zhí)行效率。1存儲(chǔ)過(guò)程綜述12.存儲(chǔ)過(guò)程2、存儲(chǔ)過(guò)程特點(diǎn)存儲(chǔ)過(guò)程同其它編程語(yǔ)言中的過(guò)程相似，有如下特點(diǎn)：接收輸入?yún)?shù)并以輸出參數(shù)的形式將多個(gè)值返回至調(diào)用過(guò)程或批處理。包含執(zhí)行數(shù)據(jù)庫(kù)操作（包括調(diào)用其它過(guò)程）的編程語(yǔ)句。向調(diào)用過(guò)程或批處理返回狀態(tài)值，以表明成功或失敗以及失敗原因。12.存儲(chǔ)過(guò)程3、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：

1、安全機(jī)制：只給用戶訪問(wèn)存儲(chǔ)過(guò)程的權(quán)限，而不授予用戶訪問(wèn)表和視圖的權(quán)限。

2、改良了執(zhí)行性能：在第一次執(zhí)行后，會(huì)在SQLserver的緩沖區(qū)中創(chuàng)建查詢樹(shù)，以后執(zhí)行無(wú)需編譯。

3、減少網(wǎng)絡(luò)流量：存儲(chǔ)過(guò)程存在于服務(wù)器上，調(diào)用時(shí)，只需傳遞執(zhí)行存儲(chǔ)過(guò)程的執(zhí)行命令和返回結(jié)果。

4、模塊化的程序設(shè)計(jì)：增強(qiáng)了代碼的可重用性，提高了開(kāi)發(fā)效率。12.存儲(chǔ)過(guò)程4、存儲(chǔ)過(guò)程類型用戶定義的存儲(chǔ)過(guò)程：用戶定義的存儲(chǔ)過(guò)程是用戶根據(jù)需要，為完成某一特定功能，在自己的普通數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程。系統(tǒng)存儲(chǔ)過(guò)程：系統(tǒng)存儲(chǔ)過(guò)程以sp_為前綴，主要用來(lái)從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理SQLServer提供幫助，為用戶查看數(shù)據(jù)庫(kù)對(duì)象提供方便。比如用來(lái)查看數(shù)據(jù)庫(kù)對(duì)象信息的系統(tǒng)存儲(chǔ)過(guò)程sp_help。系統(tǒng)存儲(chǔ)過(guò)程被系統(tǒng)安裝在master數(shù)據(jù)庫(kù)中擴(kuò)展存儲(chǔ)過(guò)程：擴(kuò)展存儲(chǔ)過(guò)程以xp_為前綴，它是關(guān)系數(shù)據(jù)庫(kù)引擎的開(kāi)放式數(shù)據(jù)服務(wù)層的一部分，其可以使用戶在動(dòng)態(tài)鏈接庫(kù)(DLL)文件所包含的函數(shù)中實(shí)現(xiàn)邏輯功能，從而擴(kuò)展了T-SQL的功能，并且可以象調(diào)用T-SQL過(guò)程那樣從T-SQL語(yǔ)句調(diào)用這些函數(shù)。12.存儲(chǔ)過(guò)程常見(jiàn)的系統(tǒng)存儲(chǔ)過(guò)程Sp_help顯示當(dāng)前數(shù)據(jù)庫(kù)中的相關(guān)信息Sp_helpdb顯示當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器中現(xiàn)有的數(shù)據(jù)庫(kù)Sp_helpfile顯示與指定數(shù)據(jù)庫(kù)相關(guān)的物理存儲(chǔ)文件Sp_helpserver顯示當(dāng)前可用的數(shù)據(jù)庫(kù)服務(wù)器Sp_helpuser顯示當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器中的用戶信息Sp_helptext顯示文件信息Sp_attach_db加載一個(gè)數(shù)據(jù)庫(kù)Sp_detach_db卸載一個(gè)數(shù)據(jù)庫(kù)12.存儲(chǔ)過(guò)程1.創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程時(shí)，需要注意下列事項(xiàng)：

只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。數(shù)據(jù)庫(kù)的所有者可以創(chuàng)建存儲(chǔ)過(guò)程，也可以授權(quán)其他用戶創(chuàng)建存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)對(duì)象，其名稱必須遵守標(biāo)識(shí)符命名規(guī)則。

不能將CREATEPROCEDURE語(yǔ)句與其它SQL語(yǔ)句組合到單個(gè)批處理中。創(chuàng)建存儲(chǔ)過(guò)程時(shí)，應(yīng)指定所有輸入?yún)?shù)和向調(diào)用過(guò)程或批處理返回的輸出參數(shù)、執(zhí)行數(shù)據(jù)庫(kù)操作的編程語(yǔ)句和返回至調(diào)用過(guò)程或批處理以表明成功或失敗的狀態(tài)值。2創(chuàng)建、執(zhí)行、修改、刪除簡(jiǎn)單存儲(chǔ)過(guò)程12.存儲(chǔ)過(guò)程使用SQL語(yǔ)句創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程語(yǔ)法格式如下：

CREATEPROC[EDURE]procedure_name

[WITH

{RECOMPILE|ENCRYPTION}]

ASsql_statement[...n]procedure_name：新存儲(chǔ)過(guò)程的名稱。過(guò)程名必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。RECOMPILE：

SQL不會(huì)緩存該過(guò)程的計(jì)劃，該過(guò)程將在運(yùn)行時(shí)重新編譯。ENCRYPTION：SQLServer加密使用ENCRYPTION可防止將過(guò)程作為SQLServer復(fù)制的一部分發(fā)布。12.存儲(chǔ)過(guò)程例：在醫(yī)生數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)查詢存儲(chǔ)過(guò)程dbo.ysghxm，要求該存儲(chǔ)過(guò)程列出兒科系的醫(yī)生工號(hào)和姓名。

（1）在創(chuàng)建本例存儲(chǔ)過(guò)程時(shí)，可以先在查詢編輯器中編寫實(shí)現(xiàn)存儲(chǔ)過(guò)程功能的T-SQL語(yǔ)句。代碼如下：USE

醫(yī)生數(shù)據(jù)庫(kù)GOSELECT

醫(yī)生工號(hào),姓名FROM

醫(yī)生表WHERE

科室代碼=

(SELECT

科室代碼FROM

科室表WHERE

科室名稱='兒科')12.存儲(chǔ)過(guò)程（2）調(diào)試該語(yǔ)句正確后，再創(chuàng)建存儲(chǔ)過(guò)程。在查詢編輯器中輸入其完整程的代碼如下：USE

醫(yī)生數(shù)據(jù)庫(kù)GOCREATE

PROCdbo.ysghxmASSELECT

醫(yī)生工號(hào),姓名

FROM

醫(yī)生表WHERE

科室代碼=

(SELECT

科室代碼

FROM

科室表WHERE

科室名稱='兒科')GO（3）單擊“分析”按鈕，進(jìn)行語(yǔ)法檢查；語(yǔ)法無(wú)誤后，單擊“執(zhí)行”按鈕，創(chuàng)建該存儲(chǔ)過(guò)程。12.存儲(chǔ)過(guò)程2.執(zhí)行存儲(chǔ)過(guò)程對(duì)存儲(chǔ)在服務(wù)器上的存儲(chǔ)過(guò)程，可以使用EXECUTE命令或其名稱執(zhí)行它，其語(yǔ)法格式如下：

[EXEC[UTE]]procedure_name[number]}

如果存儲(chǔ)過(guò)程是批處理中的第一條語(yǔ)句，EXECUTE命令可以省略，可以使用存儲(chǔ)過(guò)程的名字執(zhí)行該存儲(chǔ)過(guò)程。例：在查詢分析器中執(zhí)行存儲(chǔ)過(guò)程ysghxm，其代碼清單如下：USESTUDENTGOEXECUTEysghxmGO12.存儲(chǔ)過(guò)程3.查看存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程創(chuàng)建好后，其名稱保存在系統(tǒng)sysobjects中，其源代碼保存在syscomments中，兩表通過(guò)ID字段進(jìn)行關(guān)聯(lián)?？梢允褂萌缦氯N方式查看存儲(chǔ)過(guò)程相關(guān)信息，使用SQLServerManagementStudio直接使用系統(tǒng)表使用系統(tǒng)存儲(chǔ)過(guò)程12.存儲(chǔ)過(guò)程（1）使用SQLServerManagementStudio查看存儲(chǔ)過(guò)程信息啟動(dòng)SQLServerManagementStudio，在“對(duì)象資源管理器”窗口中，依次展開(kāi)數(shù)據(jù)庫(kù)、醫(yī)生數(shù)據(jù)庫(kù)、可編程性、存儲(chǔ)過(guò)程節(jié)點(diǎn)。在展開(kāi)的存儲(chǔ)過(guò)程節(jié)點(diǎn)中右鍵單擊需要查看的存儲(chǔ)過(guò)程，在彈出的快捷菜單中右鍵單擊“屬性”命令，打開(kāi)“存儲(chǔ)過(guò)程屬性”窗口。12.存儲(chǔ)過(guò)程（2）使用系統(tǒng)表查看醫(yī)生數(shù)據(jù)庫(kù)中名為ysghxm的存儲(chǔ)過(guò)程的定義信息。代碼如下：USE

醫(yī)生數(shù)據(jù)庫(kù)GOSELECT

TEXT

FROMSYSCOMMENTS

WHEREIDIN

(SELECTIDFROMSYSOBJECTS

WHERE

NAME

='ysghxm'

ANDXTYPE='P')GO12.存儲(chǔ)過(guò)程使用sp_help查看存儲(chǔ)過(guò)程的一般信息，包含存儲(chǔ)過(guò)程的名稱、擁有者、類型和創(chuàng)建時(shí)間，語(yǔ)法格式：sp_help存儲(chǔ)過(guò)程名使用sp_helptext查看存儲(chǔ)過(guò)程的定義信息，語(yǔ)法格式：sp_helptext存儲(chǔ)過(guò)程名使用sp_depends查看存儲(chǔ)過(guò)程的相關(guān)性，其語(yǔ)法格式：sp_depends存儲(chǔ)過(guò)程名

（3）使用系統(tǒng)存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程的相關(guān)信息12.存儲(chǔ)過(guò)程4.修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程的T-SQL語(yǔ)句為ALTERPROCEDURE，其語(yǔ)法格式為：ALTER

PROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]12.存儲(chǔ)過(guò)程例如：修改存儲(chǔ)過(guò)程ysghxm，使該存儲(chǔ)過(guò)程列出兒科的科室的醫(yī)生工號(hào)和姓名。代碼如下：USE

醫(yī)生數(shù)據(jù)庫(kù)GOALTER

PROCdbo.ysghxmAS

SELECT

醫(yī)生工號(hào),姓名FROM

醫(yī)生表WHERE

科室代碼=

(SELECT

科室代碼

FROM

科室表WHERE

科室名稱='口腔科')GO12.存儲(chǔ)過(guò)程5.刪除存儲(chǔ)過(guò)程使用DROPPROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程DROPPROCEDURE語(yǔ)句可以一次從當(dāng)前數(shù)據(jù)庫(kù)中將一個(gè)或多個(gè)存儲(chǔ)過(guò)程或過(guò)程組刪除，其語(yǔ)法格式如下：DROPPROCEDURE存儲(chǔ)過(guò)程名稱[,…n]例：刪除存儲(chǔ)過(guò)程ysghxm。代碼如下：USE

醫(yī)生數(shù)據(jù)庫(kù)GODROP

PROCEDUREysghxmGO12.存儲(chǔ)過(guò)程使用SQL語(yǔ)句創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程語(yǔ)法格式如下：3創(chuàng)建和執(zhí)行含參數(shù)的存儲(chǔ)過(guò)程CREATE

PROC[EDURE]procedure_name[number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

ASsql_statement[...n]12.存儲(chǔ)過(guò)程procedure_name：新建存儲(chǔ)過(guò)程的名稱，其名稱必須符合標(biāo)識(shí)符命名規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。number：該參數(shù)是可選的整數(shù)，用來(lái)對(duì)同名的過(guò)程分組，以便用一條DROPPROCEDURE語(yǔ)句即可將同組的過(guò)程一起刪除。例如，名為orders的應(yīng)用程序使用的過(guò)程可以命名為orderproc1、orderproc2等。DROPPROCEDUREorderproc語(yǔ)句將刪除整個(gè)組。parameter：存儲(chǔ)過(guò)程中的輸入和輸出參數(shù)。data_type：參數(shù)的數(shù)據(jù)類型。Default：指參數(shù)的默認(rèn)值，必須是常量或NULL。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過(guò)程。12.存儲(chǔ)過(guò)程OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過(guò)程。RECOMPILE：表明SQLServer不保存存儲(chǔ)過(guò)程的計(jì)劃，該過(guò)程將在運(yùn)行時(shí)重新編譯。ENCRYPTION：表示SQLServer加密syscomments表中包含CREATEPROCEDURE語(yǔ)句文本的條目。sql_statement：指存儲(chǔ)過(guò)程中的任意數(shù)目和類型的Transact-SQL語(yǔ)句。

在存儲(chǔ)過(guò)程中使用參數(shù)，可以擴(kuò)展存儲(chǔ)過(guò)程的功能。使用輸入?yún)?shù)，可以將外部信息傳入到存儲(chǔ)過(guò)程；使用輸出參數(shù)，可以將存儲(chǔ)過(guò)程內(nèi)的信息傳出到外部。12.存儲(chǔ)過(guò)程1．使用輸入?yún)?shù)例：在醫(yī)生數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)查詢存儲(chǔ)過(guò)程ysghxm_cs，要求該存儲(chǔ)過(guò)程帶一個(gè)輸入?yún)?shù)，用于接收科室名稱。執(zhí)行該存儲(chǔ)過(guò)程時(shí)，將根據(jù)輸入的科室名稱列出該科室的醫(yī)生工號(hào)和姓名，代碼如下：CREATE

PROCysghxm_cs@ksmcvarchar(30)ASSELECT

醫(yī)生工號(hào),姓名FROM

醫(yī)生表WHERE

科室代碼=

(SELECT

科室代碼FROM

科室表WHERE

科室名稱=@ksmc)12.存儲(chǔ)過(guò)程執(zhí)行帶參數(shù)的存儲(chǔ)過(guò)程，可以采用以下兩種方式：按位置傳遞：在調(diào)用存儲(chǔ)過(guò)程時(shí)，直接給出參數(shù)值。如果多于一個(gè)參數(shù)，給出的參數(shù)值要與定義的參數(shù)的順序一致。如：執(zhí)行存儲(chǔ)過(guò)程st_bjmc，查看“商務(wù)技術(shù)系”的班級(jí)名稱，代碼如下：EXECst_bjmc‘商務(wù)技術(shù)系’使用參數(shù)名稱傳遞：在調(diào)用存儲(chǔ)過(guò)程時(shí)，按“參數(shù)名=參數(shù)值”的形式給出參數(shù)值。采用此方式，參數(shù)如果多于一個(gè)時(shí)，給出的參數(shù)順序可以與定義的參數(shù)順序不一致。如：執(zhí)行存儲(chǔ)過(guò)程st_bjmc，查看“經(jīng)濟(jì)管理系”的班級(jí)名稱，代碼如下：EXECst_bjmc@xbmc=‘經(jīng)濟(jì)管理系’

12.存儲(chǔ)過(guò)程1.使用輸入?yún)?shù)--設(shè)置參數(shù)默認(rèn)值

對(duì)存儲(chǔ)過(guò)程ysghxm_cs進(jìn)行修改，實(shí)現(xiàn)默認(rèn)顯示骨科的醫(yī)生工號(hào)和班級(jí)的功能。代碼如下：ALTER

PROCysghxm_cs@ksmcvarchar(30)=

'骨科'ASSELECT

醫(yī)生工號(hào),姓名FROM

醫(yī)生表WHERE

科室代碼=

(SELECT

科室代碼FROM

科室表WHERE

科室名稱=@ksmc)12.存儲(chǔ)過(guò)程對(duì)存儲(chǔ)在服務(wù)器上的存儲(chǔ)過(guò)程，可以使用EXECUTE命令或其名稱執(zhí)行它，其語(yǔ)法格式如下：

[EXEC[UTE]]{[@return_status=]procedure_name[number]}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n]@return_status：是一個(gè)可選的整型變量，用來(lái)保存存儲(chǔ)過(guò)程的返回狀態(tài)。@parameter：存儲(chǔ)過(guò)程的參數(shù)。

1.使用輸入?yún)?shù)--執(zhí)行存儲(chǔ)過(guò)程例如：執(zhí)行存儲(chǔ)過(guò)程：EXECysghxm_cs骨科EXECysghxm_cs@ksmc=骨科12.存儲(chǔ)過(guò)程2.使用輸出參數(shù)在醫(yī)生數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)查詢存儲(chǔ)過(guò)程ysrs，要求該存儲(chǔ)過(guò)程帶一個(gè)輸出參數(shù)，用于返回總?cè)藬?shù)，一般情況下，輸出參數(shù)的數(shù)據(jù)類型要與它接收的確定值的類型一致。執(zhí)行該存儲(chǔ)過(guò)程時(shí)，將把姓張的醫(yī)生人數(shù)傳遞出來(lái)。代碼如下：USE

醫(yī)生數(shù)據(jù)庫(kù)GOCREATE

PROCdbo.ysrs@zrstinyint

OUTPUTAS

SELECT@zrs=count(醫(yī)生工號(hào))

FROM

醫(yī)生表

WHERE

姓名like

'張%'GO12.存儲(chǔ)過(guò)程2.使用輸出參數(shù)執(zhí)行帶有輸出參數(shù)的存儲(chǔ)過(guò)程時(shí)，需要聲明變量接收存儲(chǔ)過(guò)程的返回值。在使用該變量時(shí)，還必須為這個(gè)變量加上OUTPUT聲明，一般情況下，聲明的變量的數(shù)據(jù)類型要與存儲(chǔ)過(guò)程的輸出參數(shù)的數(shù)據(jù)類型一致。執(zhí)行st_kcpjf的代碼如下：USE

醫(yī)生數(shù)據(jù)庫(kù)GODECLARE@zrstinyintEXECysrs@zrsOUTPUTPRINT

'fsfdsfds'+str(@zrs)12.存儲(chǔ)過(guò)程3.使用多個(gè)參數(shù)創(chuàng)建一個(gè)判斷存儲(chǔ)過(guò)程ecfg，要求該存儲(chǔ)過(guò)程帶兩個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)CREATE

PROCdbo.ecfg@aint,@bint

,@cint,@x2decimal

output,@x1decimal

outputasdeclare@ddecimalset@d=@b*@b-4*@a*@cif@d>=0begin

set@x2=

-(@b*@b-sqrt(@b*@b-4*@a*@c))/(2*@a)

set@x1=

-(@b*@b+sqrt(@b*@b-4*@a*@c))/(2*@a)endelsebegin

set@x2=-@b*@b/(2*@a)

set@x1=-@b*@b/(2*@a)end12.存儲(chǔ)過(guò)程/*執(zhí)行存儲(chǔ)過(guò)程*/DECLARE@x1decimalDECLARE@x2decimalEXECecfga1,4,4,@x1output,@x2outputselect@x1,@x212.存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程第一次執(zhí)行后，其被編譯的代碼將駐留在高速緩存中，當(dāng)用戶再次執(zhí)行該存儲(chǔ)過(guò)程時(shí)，SQLServer將其從緩存中調(diào)出執(zhí)行。有時(shí)，在我們使用了一次存儲(chǔ)過(guò)程后，可能會(huì)因?yàn)槟承┰?，必須向表中新增加?shù)據(jù)列或者為表新添加索引，從而改變了數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)。這時(shí)，如果調(diào)用緩存中的存儲(chǔ)過(guò)程，需要對(duì)它進(jìn)行重新編譯，使存儲(chǔ)過(guò)程能夠得到優(yōu)化。SQLServer提供三種重新編譯存儲(chǔ)過(guò)程的方法：1、在建立存儲(chǔ)過(guò)程時(shí)設(shè)定重新編譯創(chuàng)建存儲(chǔ)過(guò)程時(shí)，在其定義中指定WITHRECOMPILE選項(xiàng)，使SQLServer在每次執(zhí)行存儲(chǔ)過(guò)程時(shí)都要重新編譯。4存儲(chǔ)過(guò)程的重新編譯12.存儲(chǔ)過(guò)程2、在執(zhí)行存儲(chǔ)過(guò)程時(shí)設(shè)定重編譯在執(zhí)行存儲(chǔ)過(guò)程時(shí)指定WITHRECOMPILE選項(xiàng)，可強(qiáng)制對(duì)存儲(chǔ)過(guò)程進(jìn)行重新編譯