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

9.1存儲(chǔ)過(guò)程的基本概念

在使用Transact-SQL語(yǔ)言編程的過(guò)程中，可以將某些多次調(diào)用以實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫(xiě)成一個(gè)過(guò)程，將其保存在數(shù)據(jù)庫(kù)中，并由SQLServer服務(wù)器通過(guò)過(guò)程名調(diào)用它們，這些過(guò)程就叫做存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程分為系統(tǒng)存儲(chǔ)過(guò)程和用戶自定義的存儲(chǔ)過(guò)程。系統(tǒng)存儲(chǔ)過(guò)程由

SQLServer2000提供，用戶可以直接使用。用戶自定義存儲(chǔ)過(guò)程需要用戶自己創(chuàng)建和維護(hù)。系統(tǒng)存儲(chǔ)過(guò)程使用“sp_”作為前綴。

存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：可以在單個(gè)存儲(chǔ)過(guò)程中執(zhí)行一系列

SQL語(yǔ)句。

可以從自己的存儲(chǔ)過(guò)程內(nèi)引用其它存儲(chǔ)過(guò)程，這可以簡(jiǎn)化一系列復(fù)雜語(yǔ)句。

存儲(chǔ)過(guò)程在創(chuàng)建時(shí)即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來(lái)比單個(gè)

SQL語(yǔ)句快。

9.2創(chuàng)建存儲(chǔ)過(guò)程

在SQL-Server中，可以使用三種方法創(chuàng)建存儲(chǔ)過(guò)程：使用向?qū)?chuàng)建存儲(chǔ)過(guò)程。

使用SQL-Server企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程。

使用Transact-SQL語(yǔ)句中的CreateProcedure命令創(chuàng)建存儲(chǔ)過(guò)程。

默認(rèn)情況下，創(chuàng)建存儲(chǔ)過(guò)程的許可權(quán)歸屬數(shù)據(jù)庫(kù)的所有者，數(shù)據(jù)庫(kù)的所有者可以授權(quán)給其他用戶。9.2.1使用向?qū)?chuàng)建存儲(chǔ)過(guò)程

（1）在企業(yè)管理器中選中某個(gè)SQL-Server服務(wù)器中的數(shù)據(jù)庫(kù)，這里選中sales數(shù)據(jù)庫(kù)。選擇工具菜單中的“向?qū)А辈藛雾?xiàng)，系統(tǒng)會(huì)彈出“選擇向?qū)А睂?duì)話框。選中“創(chuàng)建存儲(chǔ)過(guò)程向?qū)А边x項(xiàng)。（2）單擊“確定”按鈕，出現(xiàn)“歡迎使用創(chuàng)建存儲(chǔ)過(guò)程向?qū)А睂?duì)話框。（3）單擊“下一步”按鈕，出現(xiàn)“選擇數(shù)據(jù)庫(kù)”對(duì)話框，如圖9-3所示。該對(duì)話框用于選擇創(chuàng)建存儲(chǔ)過(guò)程中使用的數(shù)據(jù)庫(kù)。

圖9-3“選擇數(shù)據(jù)庫(kù)”對(duì)話框

（4）我們選擇sales數(shù)據(jù)庫(kù)，單擊“下一步”按鈕，出現(xiàn)“選擇操作對(duì)象”對(duì)話框，如圖9-4所示，在該對(duì)話框中，列出了所有可選擇的表，以及可以對(duì)表進(jìn)行的數(shù)據(jù)庫(kù)操作，即插入刪除和更新。這里我們對(duì)表Salers進(jìn)行操作，因此選中表Salers對(duì)應(yīng)的插入列、刪除列和更新列下面的復(fù)選框。

圖9-4“選擇操作對(duì)象”對(duì)話框

（5）單擊“下一步”按鈕，出現(xiàn)確認(rèn)存儲(chǔ)過(guò)程信息對(duì)話框，如圖9-5所示。圖9-5“完成創(chuàng)建存儲(chǔ)過(guò)程向?qū)А睂?duì)話框

9.2.2使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程

（1）在企業(yè)管理器中選中某個(gè)SQL-Server服務(wù)器中的數(shù)據(jù)庫(kù)，這里選中sales數(shù)據(jù)庫(kù)。右鍵單擊數(shù)據(jù)庫(kù)下的“存儲(chǔ)過(guò)程”選項(xiàng)，彈出快捷菜單，如圖9-8所示。

圖9-8快捷菜單內(nèi)容

（2）在快捷菜單中選擇“新建存儲(chǔ)過(guò)程”命令出現(xiàn)“新建存儲(chǔ)過(guò)程”對(duì)話框，如圖9-9所示。圖9-9“新建存儲(chǔ)過(guò)程”對(duì)話框

（3）在“新建存儲(chǔ)過(guò)程”對(duì)話框的“文本”列表框中輸入存儲(chǔ)過(guò)程名稱和程序語(yǔ)句，如圖9-9所示。

CREATEPROCEDUREUP_SalersASSelect*fromSalers存儲(chǔ)過(guò)程UP_Salers的完成的功能是從Salers表中查詢?nèi)繑?shù)據(jù)。（4）單擊“檢查語(yǔ)法”按鈕，執(zhí)行語(yǔ)法正確性檢驗(yàn)。（5）單擊“確定”按鈕，返回企業(yè)管理器窗口，可以看到所創(chuàng)建的存儲(chǔ)過(guò)程。9.2.3使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程

CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement

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

@parameter：過(guò)程中的參數(shù)。

data_type：參數(shù)的數(shù)據(jù)類型。

Default：參數(shù)的默認(rèn)值。

OUTPUT：表明參數(shù)是返回參數(shù)。

AS：指定過(guò)程要執(zhí)行的操作。

sql_statement：過(guò)程中要包含的任意數(shù)目和類型的

Transact-SQL語(yǔ)句。

例9-1不帶有參數(shù)的存儲(chǔ)過(guò)程下面的存儲(chǔ)過(guò)程從sales數(shù)據(jù)庫(kù)的三個(gè)表的聯(lián)接中返回訂單的編號(hào)、客戶名稱、銷售員和訂單日期。該存儲(chǔ)過(guò)程不使用任何參數(shù)。USEsalesGOCREATEPROCEDUREUP_OrderInfoASSELECTOrders.OrderID,Customers.CompanyName,Salers.Salename,Orders.OrderDateFROMOrdersINNERJOINSalersONOrders.SaleID=Salers.SaleIDINNERJOINCustomersONOrders.CustomerID=Customers.CustomerIDGO例9-2帶有參數(shù)的存儲(chǔ)過(guò)程下面的存儲(chǔ)過(guò)程從sales數(shù)據(jù)庫(kù)的三個(gè)表的聯(lián)接中返回訂單的編號(hào)、客戶名稱、銷售員和訂單日期。該存儲(chǔ)過(guò)程接受日期區(qū)間參數(shù)：開(kāi)始日期和結(jié)束日期。

USEsalesGOCREATEPROCEDUREUP_OrderInfoWithParam@StartDatedatetime,@EndDatedatetimeASSELECTOrders.OrderID,Customers.CompanyName,Salers.Salename,Orders.OrderDateFROMOrdersINNERJOINSalersONOrders.SaleID=Salers.SaleIDINNERJOINCustomersONOrders.CustomerID=Customers.CustomerIDWHERE(Orders.OrderDateBETWEEN@StartDateAND@EndDate)GO9.3執(zhí)行存儲(chǔ)過(guò)程

[[EXEC[UTE]]{[@return_status=]{procedure_name]}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]EXEC[UTE]：為執(zhí)行存儲(chǔ)過(guò)程的關(guān)鍵字。如果所執(zhí)行存儲(chǔ)過(guò)程語(yǔ)句為批中的第一個(gè)語(yǔ)句時(shí)，則可以省略EXECUTE關(guān)鍵字。

@return_status：是一個(gè)可選的整型變量，保存存儲(chǔ)過(guò)程的返回狀態(tài)。

@parameter：是過(guò)程參數(shù)，在

CREATEPROCEDURE語(yǔ)句中定義。

Value：是過(guò)程中參數(shù)的值。

@variable：是用來(lái)保存參數(shù)或者返回參數(shù)的變量。OUTPUT：指定存儲(chǔ)過(guò)程必須返回一個(gè)參數(shù)。

DEFAULT：根據(jù)過(guò)程的定義，提供參數(shù)的默認(rèn)值。

1．不帶參數(shù)的存儲(chǔ)過(guò)程的調(diào)用：USEsalesEXECUTEUP_OrderInfo2．帶參數(shù)的存儲(chǔ)過(guò)程的調(diào)用：

USEsalesEXECUTEUP_OrderInfoWithParam'2005-1-1','2005-5-1'--OrEXECUTEUP_OrderInfoWithParam@StartDate='2005-1-1',@EndDate='2005-5-1'--OrEXECUTEUP_OrderInfoWithParam@EndDate='2005-5-1',@StartDate='2005-1-1'3．自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程可以通過(guò)調(diào)用一個(gè)系統(tǒng)存儲(chǔ)過(guò)程來(lái)設(shè)置一個(gè)存儲(chǔ)過(guò)程為自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。這樣的存儲(chǔ)過(guò)程可以在SQLserver啟動(dòng)時(shí)自動(dòng)執(zhí)行。這個(gè)系統(tǒng)存儲(chǔ)過(guò)程是sp_procoption,其調(diào)用語(yǔ)法格式為：sp_procoption[@ProcName=]'procedure',[@OptionName=]'option',[@OptionValue=]'value'

[@ProcName=]'procedure'：是要為其設(shè)置或查看選項(xiàng)的過(guò)程名。

[@OptionName=]'option'：要設(shè)置的選項(xiàng)的名稱。option的唯一值是startup，該值設(shè)置存儲(chǔ)過(guò)程的自動(dòng)執(zhí)行狀態(tài)。設(shè)置為自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程會(huì)在每次MicrosoftSQLServer啟動(dòng)時(shí)運(yùn)行。

[[@OptionValue=]'value']：表示選項(xiàng)是設(shè)置為開(kāi)（true或on）還是關(guān)（false或off）。9.4管理存儲(chǔ)過(guò)程

9.4.1查看存儲(chǔ)過(guò)程

創(chuàng)建存儲(chǔ)過(guò)程之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中。可以使用企業(yè)管理器或系統(tǒng)存儲(chǔ)過(guò)程查看用戶創(chuàng)建的存儲(chǔ)過(guò)程。

1．使用企業(yè)管理器查看用戶創(chuàng)建的存儲(chǔ)過(guò)程

在企業(yè)管理器中，打開(kāi)指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，這里打開(kāi)HBSI服務(wù)器下的sales數(shù)據(jù)庫(kù)，并單擊sales中的“存儲(chǔ)過(guò)程”項(xiàng)，此時(shí)在右邊的窗口中顯示出sales數(shù)據(jù)庫(kù)中的所有存儲(chǔ)過(guò)程。如圖9-12所示。

圖9-12存儲(chǔ)過(guò)程顯示窗口

右鍵單擊要查看的存儲(chǔ)過(guò)程，從彈出的快捷菜單中選擇“屬性”選項(xiàng)，彈出“存儲(chǔ)過(guò)程屬性”選項(xiàng)?；蛘咦箧I雙擊要查看的存儲(chǔ)過(guò)程，也可以彈出“存儲(chǔ)過(guò)程屬性”選項(xiàng)。在此對(duì)話框中能夠看到存儲(chǔ)過(guò)程的源代碼。

2．使用系統(tǒng)存儲(chǔ)過(guò)程查看用戶創(chuàng)建的存儲(chǔ)過(guò)程

sp_help

sp_helptext

sp_depends

sp_stored_procedures9.4.2修改存儲(chǔ)過(guò)程

1．使用企業(yè)管理器修改存儲(chǔ)過(guò)程

2．使用Transact-SQL語(yǔ)句修改存儲(chǔ)過(guò)程

ALTERPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement9.4.3重命名存儲(chǔ)過(guò)程

1．使用企業(yè)管理器重命名存儲(chǔ)過(guò)程名稱在企業(yè)管理器中，右鍵單擊要更名的存儲(chǔ)過(guò)程，從彈出的快捷菜單中選擇“重命名”選項(xiàng)，當(dāng)存儲(chǔ)過(guò)程名稱變成可輸入狀態(tài)時(shí)，就可以直接修改該存儲(chǔ)過(guò)程的名稱了。

2．使用系統(tǒng)存儲(chǔ)過(guò)程重命名存儲(chǔ)過(guò)程名稱

sp_renameold_name，new_name例如：USEsalesGOsp_renameP_OrderInfoWithParam,UP_OrderInfoDateGO9.4.4刪除存儲(chǔ)過(guò)程

1．使用企業(yè)管理器刪除存儲(chǔ)過(guò)程在企業(yè)管理器中，右鍵單擊要更名的存儲(chǔ)過(guò)程，從彈出的快捷菜單中選擇“刪除”選項(xiàng)，彈出“除去對(duì)象”對(duì)話框，如圖9-14所示。在該對(duì)話框中，單擊“全部