Microsoft PowerPoint - sql2005_ch10.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - sql2005_ch10.ppt"

便褒密陈
5 years ago
Views:

1 第十章預存程序 (Stored Procedure) 10-1 為何使用預存程序使用預存程序的優點預存程序的種類建立預存程序執行預存程序修改預存程序 10-2 預存程序的相關設計使用參數取得傳回值預存程序的編譯在應用系統執行預存程序 1/24

2 10-1 為何使用預存程序使用預存程序的優點接受輸入參數輸出參數呼叫其他程序將狀態值予以傳回可進行模組化的程式撰寫 (sp 在資料庫上執行 ) 可加快執行速度 ( 重複執行時, 可不需要重新編譯 ) 可減少網路負荷 2/24

3 [ 補充 ] 預存程序限制 CREATE PROCEDURE 陳述式不能與其他 SQL 陳述式合併在單一批次 (GO) 處理中若要建立程序, 您必須擁有資料庫的 CREATE PROCEDURE 權限以及建立程序之結構描述的 ALTER 權限對於 CLR (Common Language Runtime) 預存程序, 您必須擁有在 <method_specifier> 中參考的組件, 或是擁有該組件的 REFERENCES 權限預存程序是結構描述範圍的物件, 而且其名稱必須依照識別碼的規則您只可以在目前資料庫中建立預存程序 3/24

4 預存程序的種類可區分為三個類別 : 1. 系統預存程序 2. 觸發程序 3. 一般預存程序 4/24

5 一. [ 補充 ]: 預存程序 (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

6 [ 補充 ]: 預存程序 (2/3) 二. 擴充預存程序 : 擴充預存程序可讓您在程式設計語言中 ( 如 : C ), 建立自己的外部常式擴充預存程序是 Microsoft SQL Server 執行個體可以動態載入和執行的 DLL 擴充預存程序會直接在 SQL Server 執行個體的位址空間中執行, 並且使用 SQL Server 擴充預存程序 API 進行程式設計 [ 注意 ]: 未來版本的 Microsoft SQL Server 將不再提供此功能請避免在新的開發工作中使用此功能, 並計劃修改目前使用此功能的應用程式使用 <CLR 整合 > 代替 6/24

7 [ 補充 ]: 預存程序 (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 範例 : 擴充預存程序 8/24

$AS 陳述式資料庫 \ 可程式性 \ 預存程序 \ 新增預存程序$

9 建立預存程序使用 CREATE PROCEDURE AS 陳述式資料庫 \ 可程式性 \ 預存程序 \ 新增預存程序 9/24

10 圖 10-3 SET ANSI_NULLS 是 ON 時 : 等於 (=) 和不等於 (<>) 比較運算子採用遵照 SQL-92 標準, 即 SQL-92 標準要求對於 Null 值的等於 (=) 或不等於 (<>) 的比較得出 FALSE 當 SET QUOTED_IDENTIFIER 是 ON ( 預設值 ), 您可以用雙引號來分隔識別碼, 文字則必須用單引號來分隔當 SET QUOTED_IDENTIFIER 是 OFF 時, 識別碼不能附加引號 10/24

11 撰寫程式的原則以下的陳述式不得用於預存程序中的任何位置 : CREATE DEFAULT CREATE TRIGGER CREATE PROCEDURE CREATE VIEW CREATE RULE 預存程序內可以建立其他資料庫物件預存程序中可以引用暫存資料表 ( 區域 #, 全域 ##),p.10-7 建立本機暫存資料表, 該暫存資料表將只為該預存程序而存在 11/24

12 撰寫程式的原則被呼叫的預存程序可以存取第一個預存程序所建的所有物件, 包括暫存資料表若執行了一個遠端預存程序而變更遠端的 SQL Server 執行個體, 則這些變更無法復原參數數量上限是 2100 個區域變數個數沒有限制預存程序的大小上限是 128 MB 12/24

13 執行預存程序 EXEC PROCEDURE 輸入參數 13/24

14 修改預存程序開啟可程式性預存程序後, 選取欲更改的預存程序, 按下右鍵及選取修改語法是 ALTER PROCEDURE 14/24

15 10-2 預存程序的相關設計使用參數參數是預存程序非常重要的運用參數共分三種, 包括輸入及輸出, 及一個傳回碼定義參數時, 符號 15/24

16 Q: 左邊預存程序功能為何? 16/24

17 取得傳回值 17/24

18 取得傳回值 : 結果 18/24

19 [ 補充篇 ] 預存程序 - 範例 Q: 左邊預存程序功能為何? USE AdventureWorks; GO IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.usp_GetList; GO CREATE PROCEDURE money 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 AND p.listprice -- Populate the output = (SELECT MAX(p.ListPrice) FROM Production.Product p JOIN Production.ProductSubcategory s ON p.productsubcategoryid = s.productsubcategoryid WHERE s.name AND p.listprice -- Populate the output GO 19/24

20 [ 補充篇 ] 預存程序 - 執行 money EXECUTE Production.usp_GetList '%Bikes%', OUTPUT 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 [ 補充篇 ] 預存程序 - 結果 21/24

22 預存程序的編譯以 WITH 為首的兩個關鍵字 WITH RECOMPILE: 每次執行時均重新編譯 WITH ENCRYPTION: 為預存程序的內容予以加密編譯目的是產生查詢計畫查詢計畫會在執行後被保留在快取中, 故不一定每次都要編譯 22/24

23 在應用系統執行預存程序在 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

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

6-1 Table Column Data Type Row Record 1. DBMS 2. DBMS MySQL Microsoft Access SQL Server Oracle 3. ODBC SQL 1. Structured Query Language 2. IBM

CHAPTER 6 SQL SQL SQL 6-1 Table Column Data Type Row Record 1. DBMS 2. DBMS MySQL Microsoft Access SQL Server Oracle 3. ODBC SQL 1. Structured Query Language 2. IBM 3. 1986 10 ANSI SQL ANSI X3. 135-1986