存储过程课件

存儲過程

9.1存儲過程的基本概念

在使用Transact-SQL語言編程的過程中，可以將某些多次調用以實現某個特定任務的代碼段編寫成一個過程，將其保存在資料庫中，並由SQLServer伺服器通過過程名調用它們，這些過程就叫做存儲過程。存儲過程分為系統存儲過程和用戶自定義的存儲過程。系統存儲過程由

SQLServer2000提供，用戶可以直接使用。用戶自定義存儲過程需要用戶自己創建和維護。系統存儲過程使用“sp_”作為首碼。

存儲過程的優點：可以在單個存儲過程中執行一系列

SQL語句。

可以從自己的存儲過程內引用其他存儲過程，這可以簡化一系列複雜語句。

存儲過程在創建時即在伺服器上進行編譯，所以執行起來比單個

SQL語句快。

9.2創建存儲過程

在SQL-Server中，可以使用三種方法創建存儲過程：使用嚮導創建存儲過程。

使用SQL-Server企業管理器創建存儲過程。

使用Transact-SQL語句中的CreateProcedure命令創建存儲過程。

默認情況下，創建存儲過程的許可權歸屬資料庫的所有者，資料庫的所有者可以授權給其他用戶。9.2.1使用嚮導創建存儲過程

（1）在企業管理器中選中某個SQL-Server伺服器中的資料庫，這裏選中sales資料庫。選擇工具菜單中的“嚮導”菜單項，系統會彈出“選擇嚮導”對話框。選中“創建存儲過程嚮導”選項。（2）單擊“確定”按鈕，出現“歡迎使用創建存儲過程嚮導”對話框。（3）單擊“下一步”按鈕，出現“選擇資料庫”對話框，如圖9-3所示。該對話框用於選擇創建存儲過程中使用的資料庫。

圖9-3“選擇資料庫”對話框

（4）我們選擇sales資料庫，單擊“下一步”按鈕，出現“選擇操作對象”對話框，如圖9-4所示，在該對話框中，列出了所有可選擇的表，以及可以對表進行的資料庫操作，即插入刪除和更新。這裏我們對表Salers進行操作，因此選中表Salers對應的插入列、刪除列和更新列下麵的複選框。

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

（5）單擊“下一步”按鈕，出現確認存儲過程資訊對話框，如圖9-5所示。圖9-5“完成創建存儲過程嚮導”對話框

9.2.2使用企業管理器創建存儲過程

（1）在企業管理器中選中某個SQL-Server伺服器中的資料庫，這裏選中sales資料庫。右鍵單擊資料庫下的“存儲過程”選項，彈出快捷菜單，如圖9-8所示。

圖9-8快捷菜單內容

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

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

CREATEPROCEDUREUP_SalersASSelect*fromSalers存儲過程UP_Salers的完成的功能是從Salers表中查詢全部數據。（4）單擊“檢查語法”按鈕，執行語法正確性檢驗。（5）單擊“確定”按鈕，返回企業管理器窗口，可以看到所創建的存儲過程。9.2.3使用Transact-SQL語句創建存儲過程

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

procedure_name：新存儲過程的名稱。過程名必須符合識別字規則，且對於資料庫及其所有者必須唯一。

@parameter：過程中的參數。

data_type：參數的數據類型。

Default：參數的默認值。

OUTPUT：表明參數是返回參數。

AS：指定過程要執行的操作。

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

Transact-SQL語句。

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

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

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

@return_status：是一個可選的整型變數，保存存儲過程的返回狀態。

@parameter：是過程參數，在

CREATEPROCEDURE語句中定義。

Value：是過程中參數的值。

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

DEFAULT：根據過程的定義，提供參數的默認值。

1．不帶參數的存儲過程的調用：USEsalesEXECUTEUP_OrderInfo2．帶參數的存儲過程的調用：

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．自動執行的存儲過程可以通過調用一個系統存儲過程來設置一個存儲過程為自動執行的存儲過程。這樣的存儲過程可以在SQLserver啟動時自動執行。這個系統存儲過程是sp_procoption,其調用語法格式為：sp_procoption[@ProcName=]'procedure',[@OptionName=]'option',[@OptionValue=]'value'

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

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

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

9.4.1查看存儲過程

創建存儲過程之後，它的名字就存儲在系統表sysobjects中，它的源代碼存放在系統表syscomments中。可以使用企業管理器或系統存儲過程查看用戶創建的存儲過程。

1．使用企業管理器查看用戶創建的存儲過程

在企業管理器中，打開指定的伺服器和數據庫項，這裏打開HBSI伺服器下的sales資料庫，並單擊sales中的“存儲過程”項，此時在右邊的窗口中顯示出sales資料庫中的所有存儲過程。如圖9-12所示。

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

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

2．使用系統存儲過程查看用戶創建的存儲過程

sp_help

sp_helptext

sp_depends

sp_stored_procedures9.4.2修改存儲過程

1．使用企業管理器修改存儲過程

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

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

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

2．使用系統存儲過程重命名存儲過程名稱

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

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