第十章預存程序 (Stored Procedure) 10-1 為何使用預存程序 10-1-1 使用預存程序的優點 10-1-2 預存程序的種類 10-1-3 建立預存程序 10-1-4 執行預存程序 10-1-5 修改預存程序 10-2 預存程序的相關設計 10-2-1 使用參數 10-2-2 取得傳回值 10-2-3 預存程序的編譯 10-2-4 在應用系統執行預存程序 1/24
10-1 為何使用預存程序 10-1-1 使用預存程序的優點 接受輸入參數 輸出參數 呼叫其他程序 將狀態值予以傳回 可進行模組化的程式撰寫 (sp 在資料庫上執行 ) 可加快執行速度 ( 重複執行時, 可不需要重新編譯 ) 可減少網路負荷 2/24
[ 補充 ] 預存程序限制 CREATE PROCEDURE 陳述式不能與其他 SQL 陳述式合併在單一批次 (GO) 處理中 若要建立程序, 您必須擁有資料庫的 CREATE PROCEDURE 權限以及建立程序之結構描述的 ALTER 權限 對於 CLR (Common Language Runtime) 預存程序, 您必須擁有在 <method_specifier> 中參考的組件, 或是擁有該組件的 REFERENCES 權限 預存程序是結構描述範圍的物件, 而且其名稱必須依照識別碼的規則 您只可以在目前資料庫中建立預存程序 3/24
10-1-2 預存程序的種類 可區分為三個類別 : 1. 系統預存程序 2. 觸發程序 3. 一般預存程序 4/24
一. [ 補充 ]: 預存程序 (1/3) 使用者自訂預存程序 : 預存程序是封裝程式碼以供重複使用的模組或常式 預存程序可以採用輸入參數 將表格式或純量結果和訊息傳回用戶端 採用資料定義語言 (DDL) 和資料操作語言 (DML) 陳述式, 以及傳回輸出參數 在 SQL Server 2005 中, 預存程序有兩種類型 :Transact-SQL 或 CLR Transact-SQL SQL: Transact-SQL 預存程序是儲存的 Transact-SQL 陳述式集合, 可以採用及傳回使用者提供的參數 例如, 根據用戶端應用程式提供的資訊, 預存程序可能包含將新資料列插入一或多個資料表所需的陳述式 預存程序可能從資料庫將資料傳回用戶端應用程式 例如, 電子商務 Web 應用程式可能根據線上使用者指定的搜尋條件, 使用預存程序傳回特定產品的資訊 CLR: CLR 預存程序是 Microsoft.NET Framework Common Language Runtime (CLR) 方法的參考, 可以採用並傳回使用者提供的參數 它們會在.NET Framework 組件中的類別上, 當作公開的靜態方法實作 5/24
[ 補充 ]: 預存程序 (2/3) 二. 擴充預存程序 : 擴充預存程序可讓您在程式設計語言中 ( 如 : C ), 建立自己的外部常式 擴充預存程序是 Microsoft SQL Server 執行個體可以動態載入和執行的 DLL 擴充預存程序會直接在 SQL Server 執行個體的位址空間中執行, 並且使用 SQL Server 擴充預存程序 API 進行程式設計 [ 注意 ]: 未來版本的 Microsoft SQL Server 將不再提供此功能 請避免在新的開發工作中使用此功能, 並計劃修改目前使用此功能的應用程式 使用 <CLR 整合 > 代替 6/24
[ 補充 ]: 預存程序 (3/3) 三. 系統預存程序 : SQL Server 2005 中的許多管理活動都可透過特殊種類的程序 ( 稱為系統預存程序 ) 來執行 例如,sys.sp_changedbowner 就是一種系統預存程序 系統預存程序實際上儲存在 Resource 資料庫中, 而且會有 sp_ 前置詞 系統預存程序邏輯上會出現在每個系統自訂和使用者自訂資料庫的 sys 結構描述中 SQL Server 2005 中,GRANT DENY 和 REVOKE 權限都可套用至系統預存程序 SQL Server 支援的系統預存程序, 可對各種維護活動提供 SQL Server 到外部程式的介面 這些擴充預存程序會使用 xp_ 前置詞 7/24
範例 : 擴充預存程序 8/24
10-1-3 建立預存程序 使用 CREATE PROCEDURE AS 陳述式 資料庫 \ 可程式性 \ 預存程序 \ 新增預存程序 9/24
圖 10-3 SET ANSI_NULLS 是 ON 時 : 等於 (=) 和不等於 (<>) 比較運算子採用遵照 SQL-92 標準, 即 SQL-92 標準要求對於 Null 值的等於 (=) 或不等於 (<>) 的比較得出 FALSE 當 SET QUOTED_IDENTIFIER 是 ON ( 預設值 ), 您可以用雙引號來分隔識別碼, 文字則必須用單引號來分隔 當 SET QUOTED_IDENTIFIER 是 OFF 時, 識別碼不能附加引號 10/24
撰寫程式的原則 以下的陳述式不得用於預存程序中的任何位置 : CREATE DEFAULT CREATE TRIGGER CREATE PROCEDURE CREATE VIEW CREATE RULE 預存程序內可以建立其他資料庫物件 預存程序中可以引用暫存資料表 ( 區域 #, 全域 ##),p.10-7 建立本機暫存資料表, 該暫存資料表將只為該預存程序而存在 11/24
撰寫程式的原則 被呼叫的預存程序可以存取第一個預存程序所建的所有物件, 包括暫存資料表 若執行了一個遠端預存程序而變更遠端的 SQL Server 執行個體, 則這些變更無法復原 參數數量上限是 2100 個 區域變數個數沒有限制 預存程序的大小上限是 128 MB 12/24
10-1-4 執行預存程序 EXEC PROCEDURE 輸入參數 13/24
10-1-5 修改預存程序 開啟 可程式性 預存程序 後, 選取欲更改的預存程序, 按下右鍵及選取 修改 語法是 ALTER PROCEDURE 14/24
10-2 預存程序的相關設計 10-2-1 使用參數 參數是預存程序非常重要的運用 參數共分三種, 包括輸入及輸出, 及一個傳回碼 定義參數時, 參數名稱之前須加上 @ 符號 15/24
Q: 左邊預存程序功能為何? 16/24
10-2-2 取得傳回值 17/24
取得傳回值 : 結果 18/24
[ 補充篇 ] 預存程序 - 範例 Q: 左邊預存程序功能為何? USE AdventureWorks; GO IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.usp_GetList; GO CREATE PROCEDURE Production.usp_GetList @product varchar(40), @maxprice money, @compareprice money OUTPUT, @listprice money OUT AS SELECT p.name AS Product, p.listprice AS 'List Price' FROM Production.Product p JOIN Production.ProductSubcategory s ON p.productsubcategoryid = s.productsubcategoryid WHERE s.name LIKE @product AND p.listprice < @maxprice; -- Populate the output variable @listprice. SET @listprice = (SELECT MAX(p.ListPrice) FROM Production.Product p JOIN Production.ProductSubcategory s ON p.productsubcategoryid = s.productsubcategoryid WHERE s.name LIKE @product AND p.listprice < @maxprice); -- Populate the output variable @compareprice. SET @compareprice = @maxprice; GO 19/24
[ 補充篇 ] 預存程序 - 執行 DECLARE @compareprice money, @cost money EXECUTE Production.usp_GetList '%Bikes%', 700, @compareprice OUT, @cost OUTPUT IF @cost <= @compareprice BEGIN PRINT 'These products can be purchased for less than $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.' END ELSE PRINT 'The prices for all products in this category exceed $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.' GO 20/24
[ 補充篇 ] 預存程序 - 結果 21/24
10-2-3 預存程序的編譯 以 WITH 為首的兩個關鍵字 WITH RECOMPILE: 每次執行時均重新編譯 WITH ENCRYPTION: 為預存程序的內容予以加密 編譯目的是產生查詢計畫 查詢計畫會在執行後被保留在快取中, 故不一定每次都要編譯 22/24
10-2-4 在應用系統執行預存程序 在 ODBC 使用 ADO 物件 13 Dim cm As ADODB.Command 宣告及設定新變數 14 Set cm = New ADODB.Command 15 cm.activeconnection = Conn 設定資料連接 16 cm.commandtype = adcmdstoredproc 設定型態 17 cm.commandtext = "eb_sp_insertcategory" 設定預存程序名稱 18 cm.parameters(1) = Me![CateType] 分別輸入參數值 19 cm.parameters(2) = Me![CateName] 20 cm.execute 執行預存程序 23/24
在應用系統執行預存程序 在 Visual Basic.NET 使用 ADO.NET 01 Dim cm As SqlClient.SqlCommand = New SqlClient.SqlCommand 02 cm.connection = Conn 設定及建立新 Command 物件 03 cm.commandtype = CommandType.StoredProcedure 設定型態 04 cm.commandtext = "eb_sp_gettype" 設定預存程序名稱 05 Dim cp1 As SqlClient.SqlParameter = cm.parameters.add("@inputtypeid", SqlDbType.NVarChar, 50) 06 cp1.direction = ParameterDirection.Input 輸入型態 07 cp1.value = e.node.tag 建立第一個參數 08 Dim cp2 As SqlClient.SqlParameter = cm.parameters.add("@outtype", SqlDbType.NVarChar, 50) 09 cp2.direction = ParameterDirection.Output 建立第二個輸出參數 10 Dim dr As SqlClient.SqlDataReader = cm.executereader() 執行 11 labselectcategory.text = cp2.value 取得傳回值 24/24