存儲過程課件

存儲過程

9.1存儲過程的基本概念

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

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

存儲過程的優(yōu)點：可以在單個存儲過程中執(zhí)行一系列

SQL語句。

可以從自己的存儲過程內(nèi)引用其它存儲過程，這可以簡化一系列復雜語句。

存儲過程在創(chuàng)建時即在服務器上進行編譯，所以執(zhí)行起來比單個

SQL語句快。

9.2創(chuàng)建存儲過程

在SQL-Server中，可以使用三種方法創(chuàng)建存儲過程：使用向導創(chuàng)建存儲過程。

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

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

默認情況下，創(chuàng)建存儲過程的許可權歸屬數(shù)據(jù)庫的所有者，數(shù)據(jù)庫的所有者可以授權給其他用戶。9.2.1使用向導創(chuàng)建存儲過程

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

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

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

圖9-4“選擇操作對象”對話框

（5）單擊“下一步”按鈕，出現(xiàn)確認存儲過程信息對話框，如圖9-5所示。圖9-5“完成創(chuàng)建存儲過程向導”對話框

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Transact-SQL語句。

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

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

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

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

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

CREATEPROCEDURE語句中定義。

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

@variable：是用來保存參數(shù)或者返回參數(shù)的變量。OUTPUT：指定存儲過程必須返回一個參數(shù)。

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

1．不帶參數(shù)的存儲過程的調(diào)用：USEsalesEXECUTEUP_OrderInfo2．帶參數(shù)的存儲過程的調(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．自動執(zhí)行的存儲過程可以通過調(diào)用一個系統(tǒng)存儲過程來設置一個存儲過程為自動執(zhí)行的存儲過程。這樣的存儲過程可以在SQLserver啟動時自動執(zhí)行。這個系統(tǒng)存儲過程是sp_procoption,其調(diào)用語法格式為：sp_procoption[@ProcName=]'procedure',[@OptionName=]'option',[@OptionValue=]'value'

[@ProcName=]'procedure'：是要為其設置或查看選項的過程名。

[@OptionName=]'option'：要設置的選項的名稱。option的唯一值是startup，該值設置存儲過程的自動執(zhí)行狀態(tài)。設置為自動執(zhí)行的存儲過程會在每次MicrosoftSQLServer啟動時運行。

[[@OptionValue=]'value']：表示選項是設置為開（true或on）還是關（false或off）。9.4管理存儲過程

9.4.1查看存儲過程

創(chuàng)建存儲過程之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂闷髽I(yè)管理器或系統(tǒng)存儲過程查看用戶創(chuàng)建的存儲過程。

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

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

圖9-12存儲過程顯示窗口

右鍵單擊要查看的存儲過程，從彈出的快捷菜單中選擇“屬性”選項，彈出“存儲過程屬性”選項。或者左鍵雙擊要查看的存儲過程，也可以彈出“存儲過程屬性”選項。在此對話框中能夠看到存儲過程的源代碼。

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

sp_help

sp_helptext

sp_depends

sp_stored_procedures9.4.2修改存儲過程

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

2．使用Transact-SQL語句修改存儲過程

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

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

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

sp_renameold_name，new_name例如：USEsalesGOsp_renameP_OrderInfoWithParam,UP_OrderInfoDateGO9.4.4刪除存儲過程

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