1 / 54

第 14 章 預存程序

第 14 章 預存程序. 14-1 預存程序的基礎 14-2 建立與執行預存程序 14-3 預存程序的參數傳遞 14-4 預存程序的傳回值 14-5 修改與刪除預存程序 14-6 系統預存程序. 14-1 預存程序的基礎. 14-1-1 預存程序簡介 14-1-2 預存程序的優點. 14-1-1 預存程序簡介 - 說明. 預存程序（ Stored Procedure ）是將例行、常用和複雜的資料庫操作預先建立成 T-SQL 指令敘述的集合，這是在資料庫管理系統執行的指令敘述集合，可以簡化相關的資料庫操作來增進系統效能。

cormac
Download Presentation

第 14 章 預存程序

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第14章 預存程序 • 14-1 預存程序的基礎 • 14-2 建立與執行預存程序 • 14-3 預存程序的參數傳遞 • 14-4 預存程序的傳回值 • 14-5 修改與刪除預存程序 • 14-6 系統預存程序

  2. 14-1 預存程序的基礎 • 14-1-1 預存程序簡介 • 14-1-2 預存程序的優點

  3. 14-1-1 預存程序簡介-說明 • 預存程序（Stored Procedure）是將例行、常用和複雜的資料庫操作預先建立成T-SQL指令敘述的集合，這是在資料庫管理系統執行的指令敘述集合，可以簡化相關的資料庫操作來增進系統效能。 • 預存程序（Stored Procedures）是一組T-SQL指令敘述的集合，我們可以使用T-SQL流程控制指令來撰寫複雜的功能。不只如此，因為預存程序只需編譯一次，就可以執行多次，換句話說，執行預存程序可以增進系統效能，因為執行時並不需重新再編譯T-SQL指令敘述。

  4. 14-1-1 預存程序簡介-種類 • 使用者自訂預存程序（User-defined Stored Procedures）：使用者自行使用T-SQL指令敘述所建立的預存程序。 • 系統預存程序（System Stored Procedures）：系統提供使用「sp」字首開頭的預存程序，這些是SQL Server已經預設寫好的預存程序，可以用來擴充T-SQL的功能，換句話說，我們可以馬上使用這些系統預存程序來執行所需的操作。

  5. 14-1-2 預存程序的優點-執行T-SQL指令 • 一般來說，我們建立的用戶端程式共有兩種方式來敘述，如下所示： • 在用戶端建立資料庫應用程式後，使用ADO或ADO.NET等元件送出T-SQL指令敘述至SQL Server，就可以在SQL Server資料庫引擎執行T-SQL指令敘述。 • 在SQL Server先將欲執行的T-SQL指令敘述建立成預存程序，此時用戶端程式可以是直接執行位在SQL Server的預存程序。

  6. 14-1-2 預存程序的優點-優點1 • 增加執行效率：預存程序可以減少編譯花費的時間，當我們重複執行預存程序時，因為並不需要重新編譯，所以能夠增進執行T-SQL指令敘述的效率。 • 節省網路頻寬：在用戶端只需送出一列指令敘述就可以執行位在SQL Server伺服器的預存程序，而不用傳送完整數列、數十至數百列的T-SQL指令敘述，可以減少網路傳送的資料量。

  7. 14-1-2 預存程序的優點-優點2 • 模組化程式設計：透過預存程序，T-SQL語言也可以使用模組化程式設計，將常常執行的T-SQL指令敘述建立成多個預存程序的模組，讓使用者重複使用這些預存程序建立的函式庫。 • 提供安全性：預存程序是SQL Server資料庫物件，我們可以透過授與預存程序的權限，來存取使用者並沒有擁有權限的物件。而且，擁有參數的預存程序還可以增加用戶端程式的安全性，降低駭客攻擊SQL Server伺服器的機會。

  8. 14-2 建立與執行預存程序 • 14-2-1 建立預存程序 • 14-2-2 執行預存程序 • 14-2-3 建立暫存預存程序

  9. 14-2 建立與執行預存程序 • T-SQL語言是使用CREATE PROCEDURE（或CREATE PROC）指令來建立預存程序，其基本語法如下所示： CREATE PROC[EDURE] 預存程序名稱 [ WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] AS T-SQL指令敘述

  10. 14-2-1 建立預存程序-使用Management Studio • 在Management Studio工具提供指令建立預存程序，不過只是提供範本，事實上，仍然是執行CREATE PROCEDURE指令來建立預存程序。

  11. 14-2-1 建立預存程序-新增查詢來建立預存程序(說明) • 因為Management Studio指令建立預存程序就是編輯和執行SQL指令碼檔案，換句話說，我們可以直接按【新增查詢】鈕新增查詢編輯視窗後，自行輸入建立預存程序的T-SQL指令敘述。

  12. 14-2-1 建立預存程序-新增查詢來建立預存程序(範例) SQL指令碼檔：Ch14-2-1-02.sql • 建立學生上課資料的預存程序【學生上課報表】，這是使用內部合併查詢合併【學生】、【課程】、【教授】和【班級】資料表，如下所示： CREATE PROCEDURE 學生上課報表 AS BEGIN SELECT 學生.學號, 學生.姓名, 課程.*, 教授.* FROM 教授 INNER JOIN (課程 INNER JOIN (學生 INNER JOIN 班級 ON 學生.學號 = 班級.學號) ON 班級.課程編號 = 課程.課程編號) ON 班級.教授編號 = 教授.教授編號 END

  13. 14-2-2 執行預存程序-使用Management Studio • 在「物件總管」視窗展開【教務系統】資料庫的預存程序清單。在【課程資料報表】上，執行【右】鍵快顯功能表的【執行預存程序】指令後，可以看到「執行程序」對話方塊。 • 按【確定】鈕，稍等一下，就可以看到執行結果，如右圖所示：

  14. 14-2-2 執行預存程序-EXECUTE指令執行預存程序(語法) • T-SQL語言是使用EXECUTE指令來執行預存程序，其基本語法如下所示： EXEC[UTE] {預存程序名稱 | @預存程序名稱變數} • 上述語法可以使用EXECUTE或EXEC指令，之後是預存程序名稱或擁有預存程序名稱的變數。

  15. 14-2-2 執行預存程序-EXECUTE指令執行預存程序(範例) SQL指令碼檔：Ch14-2-2-01.sql • 使用EXEC指令執行【課程資料報表】預存程序，如下所示： EXEC 課程資料報表 SQL指令碼檔：Ch14-2-2-02.sql • 使用EXECUTE指令以變數來執行【學生上課報表】預存程序，如下所示： DECLARE @proc_name char(20) SET @proc_name = '學生上課報表' EXECUTE @proc_name

  16. 14-2-2 執行預存程序-EXECUTE指令執行T-SQL指令字串(語法) • EXECUTE指令除了可以執行預存程序外，也可以用來執行T-SQL指令敘述的字串，其基本語法如下所示： EXEC[UTE] ('T-SQL指令敘述字串') • 上述語法使用EXECUTE或EXEC指令，之後是欲執行的T-SQL指令敘述字串。

  17. 14-2-2 執行預存程序-EXECUTE指令執行T-SQL指令字串(範例) SQL指令碼檔：Ch14-2-2-03.sql • 使用EXEC指令執行SELECT指令的T-SQL指令敘述字串，如下所示： DECLARE @table_name char(20) SET @table_name = '學生' EXEC ('SELECT * FROM ' + @table_name)

  18. 14-2-3 建立暫存預存程序-說明 • 「暫存預存程序」（Temporary Procedures）如同暫存資料表也是一種因需求而暫時建立的資料庫物件，只有在使用者的工作階段（Session）存在，即使用者在線上時存在，當使用者離線後，SQL Server就自動刪除暫存預存程序。 • SQL Server暫存預存程序是儲存在tempdb系統資料庫，和暫存資料表相同也分為兩種：名稱使用「#」開頭的區域暫存預存程序，和「##」開頭全域暫存預存程序。

  19. 14-2-3 建立暫存預存程序-範例 SQL指令碼檔：Ch14-2-3.sql • 建立名為【#學生查詢】的預存程序，這是一個區域暫存預存程序，如下所示： CREATE PROC #學生查詢 AS BEGIN SELECT 學號, 姓名, 電話 FROM 學生 END GO EXEC #學生查詢

  20. 14-3 預存程序的參數傳遞 • 14-3-1 建立擁有參數的預存程序 • 14-3-2 預設值參數 • 14-3-3 巢狀呼叫

  21. 14-3-1 建立擁有參數的預存程序-說明 • 「參數」（Parameters）是預存程序的引數，如果需要，我們可以在預存程序宣告一至多個參數，參數值是在呼叫預存程序時，才由使用者提供。 • 預存程序的參數預設是一種輸入參數（Input Parameters），其值是使用者呼叫預存程序時傳入的值。我們可以在預存程序中，使用參數名稱來取得或更改參數值。

  22. 14-3-1 建立擁有參數的預存程序-建立擁有參數的預存程序(語法) • CREATE PROCEDURE指令建立擁有參數的預存程序的語法，如下所示： CREATE PROC[EDURE] 預存程序名稱 @參數1 資料型別, @參數2 資料型別 [, …] AS T-SQL指令敘述

  23. 14-3-1 建立擁有參數的預存程序-建立擁有參數的預存程序(範例1) SQL指令碼檔：Ch14-3-1-01.sql • 建立擁有一個參數名為【課程查詢】的預存程序，其參數是課程編號c_no，可以查詢指定課程的資訊，如下所示： CREATE PROCEDURE 課程查詢 @c_no char(5) AS BEGIN SELECT 課程編號, 名稱, 學分 FROM 課程 WHERE 課程編號 = @c_no END

  24. 14-3-1 建立擁有參數的預存程序-建立擁有參數的預存程序(範例2-1) SQL指令碼檔：Ch14-3-1-02.sql • 建立擁有2個參數名為【員工查詢】的預存程序，參數是薪水salary和tax稅，可以顯示員工資料，如下所示： CREATE PROCEDURE 員工查詢 @salary money, @tax money AS BEGIN IF @salary <= 0 SET @salary = 30000

  25. 14-3-1 建立擁有參數的預存程序-建立擁有參數的預存程序(範例2-2) IF @tax <= 0 SET @tax = 300 SELECT 身份證字號, 姓名, (薪水-扣稅) AS 所得額 FROM 員工 WHERE 薪水 >= @salary AND 扣稅 >= @tax END

  26. 14-3-1 建立擁有參數的預存程序-執行擁有參數的預存程序(語法) • 預存程序如果擁有參數，在執行預存程序時，使用者就需要加上傳入的參數值，其基本語法如下所示： EXEC[UTE] 預存程序名稱 參數值1, 參數值2 [, …] 或 EXEC[UTE] 預存程序名稱 ＠參數名稱1=參數值1, @參數名稱2=參數值2 [, …]

  27. 14-3-1 建立擁有參數的預存程序-執行擁有參數的預存程序(範例1) SQL指令碼檔：Ch14-3-1-03.sql • 使用名稱來呼叫【課程查詢】的預存程序，參數是課程編號CS101，可以查詢指定課程的資訊，如下所示： EXEC 課程查詢 @c_no = 'CS101'

  28. 14-3-1 建立擁有參數的預存程序-執行擁有參數的預存程序(範例2) SQL指令碼檔：Ch14-3-1-02.sql • 使用位置順序來呼叫【員工查詢】預存程序，參數依序是薪水salary和稅tax，如下所示： EXEC 員工查詢 50000, 500

  29. 14-3-2 預設值參數-語法 • 在預存程序的參數除了可以是輸入參數外，我們也可以指定參數的預設值，表示參數是一個選項參數（Optional Parameters），可有可無，其基本語法如下所示： @參數1 資料型別 [=預設值], @參數2 資料型別 [=預設值][, …] • 上述語法使用「=」指定運算子來指定參數的預設值，預設值也可以是NULL空值。

  30. 14-3-2 預設值參數-範例1-1 SQL指令碼檔：Ch14-3-2-01.sql • 建立名為【地址查詢】的預存程序，其參數是預設值參數，有指定預設值，如下所示： CREATE PROCEDURE 地址查詢 @city char(5) = '台北', @street varchar(30) = '中正路' AS BEGIN

  31. 14-3-2 預設值參數-範例1-2 SELECT 身份證字號, 姓名, (薪水-扣稅) AS 所得額, (城市+街道) AS 地址 FROM 員工 WHERE 城市 LIKE @city AND 街道 LIKE @street END

  32. 14-3-2 預設值參數-範例2 SQL指令碼檔：Ch14-3-2-02.sql • 呼叫名為【地址查詢】預存程序，因為有預設值，所以使用名稱方式指定單一參數值，如下所示： EXEC 地址查詢 @city = '桃園'

  33. 14-3-3 巢狀呼叫-說明 • 「巢狀呼叫」（Nesting）是指在預存程序中呼叫另一個預存程序，在T-SQL最多支援32層巢狀呼叫，超過就會產生錯誤。 • 預存程序可以使用@@NESTLEVEL系統函數來取得目前呼叫的層數。

  34. 14-3-3 巢狀呼叫-範例1 SQL指令碼檔：Ch14-3-3-01.sql • 建立名為【呼叫程序】和【測試程序】的2個預存程序，如下所示： CREATE PROCEDURE 呼叫程序 @proc_name varchar(30) AS PRINT 開始層數: ' + CAST(@@NESTLEVEL AS char) EXEC @proc_name PRINT ‘結束層數: ' + CAST(@@NESTLEVEL AS char) GO CREATE PROCEDURE 測試程序 AS PRINT ‘層數: ' + CAST(@@NESTLEVEL AS char)

  35. 14-3-3 巢狀呼叫-範例2 SQL指令碼檔：Ch14-3-3-02.sql • 呼叫名為【呼叫程序】的預存程序，因為是巢狀呼叫，它會再呼叫名為【測試程序】的預存程序，如下所示： EXEC 呼叫程序 '測試程序'

  36. 14-4 預存程序的傳回值 • 14-4-1 使用RETURN指令 • 14-4-2 使用OUTPUT關鍵字

  37. 14-4-1 使用RETURN指令-語法 • 在預存程序可以使用RETURN指令傳回預存程序的執行狀態，其基本語法如下所示： RETURN [整數運算式] • 上述語法是位在預存程序的程式區塊中，當預存程序執行到RETURN指令，就馬上中止程序的執行，並且傳回選項的整數運算式，如果沒有指定傳回值，預設傳回值是0。 • 因為預存程序會傳回值，所以在執行時需要宣告一個T-SQL變數來取得預存程序的傳回值，其語法如下所示： EXEC[UTE] ＠傳回值變數 = 預存程序名稱 參數值 [, …]

  38. 14-4-1 使用RETURN指令-範例1-1 SQL指令碼檔：Ch14-4-1-01.sql • 建立名為【新增課程】的預存程序來新增課程記錄，參數是欄位值，程序可以判斷是否新增記錄成功，如果失敗就傳回錯誤碼，如下所示： CREATE PROCEDURE 新增課程 @c_no char(5), @title varchar(30), @credits int AS BEGIN DECLARE @errorNo int INSERT INTO 課程 VALUES (@c_no, @title, @credits)

  39. 14-4-1 使用RETURN指令-範例1-2 SET @errorNo = @@ERROR IF @errorNo <> 0 BEGIN IF @errorNo = 2627 PRINT ‘錯誤! 重複索引鍵!' ELSE PRINT ‘錯誤! 未知錯誤發生!' RETURN @errorNO END END

  40. 14-4-1 使用RETURN指令-範例2 SQL指令碼檔：Ch14-4-1-02.sql • 呼叫名為【新增課程】預存程序來新增一筆課程記錄，如下所示： DECLARE @retVar int EXEC @retVar = 新增課程 'CS222','資料庫程式設計',3 PRINT ‘傳回代碼:' + CONVERT(varchar, @retVar)

  41. 14-4-2 使用OUTPUT關鍵字-語法 • 預存程序可以使用輸出參數（Output Parameters）來取得預存程序的傳回值，其基本語法如下所示： @參數1 資料型別 [=預設值] [OUTPUT], @參數2 資料型別 [=預設值] [OUTPUT] [, …] • 執行擁有輸出參數的預存程序需要宣告變數來取得傳回值，並且在執行時指定OUTPUT關鍵字，其語法如下所示： EXEC[UTE] 預存程序名稱 ＠傳回值變數 = 參數值 OUTPUT [, …]

  42. 14-4-2 使用OUTPUT關鍵字-範例1 SQL指令碼檔：Ch14-4-2-01.sql • 建立名為【薪水查詢】的預存程序來查詢員工薪水，參數是員工姓名，可以使用輸出參數傳回員工的薪水，如下所示： CREATE PROCEDURE 薪水查詢 @name varchar(12), @salary money OUTPUT AS BEGIN SELECT @salary = 薪水 FROM 員工 WHERE 姓名 = @name END

  43. 14-4-2 使用OUTPUT關鍵字-範例2 SQL指令碼檔：Ch14-4-2-02.sql • 呼叫名為【薪水查詢】預存程序來取得指定員工的薪水，如下所示： DECLARE @mySalary money EXEC 薪水查詢 '張無忌', @salary = @mySalary OUTPUT PRINT ‘Joe’s 薪水:' + CONVERT(varchar, @mySalary)

  44. 14-5 修改與刪除預存程序 • 14-5-1 修改預存程序 • 14-5-2 刪除預存程序

  45. 14-5-1 修改預存程序-使用Management Studio 修改預存程序的內容 • 在「物件總管」視窗的預存程序上，執行【右】鍵快顯功能表的【修改】指令，即可重新編輯預存程序的T-SQL指令敘述。 更改預存程序名稱 • 在「物件總管」視窗欲更改的預存程序名稱上，執行【右】鍵快顯功能表的【重新命名】指令，可以看到反白顯示的名稱和游標，請直接輸入預存程序的新名稱即可。

  46. 14-5-1 修改預存程序-使用T-SQL指令(說明) • T-SQL語言是使用ALTER PROCEDURE指令來修改預存程序，其基本語法和CREATE PROCEDURE相同。簡單的說，修改預存程序就是重新定義預存程序。 • ALTER PROCEDURE指令並無法更改預存程序名稱，我們可以使用sp_rename系統預存程序來更改預存程序的名稱，詳細說明請參閱＜第7-4-1節：修改資料表名稱＞。

  47. 14-5-1 修改預存程序-使用T-SQL指令(範例) SQL指令碼檔：Ch14-5-1.sql • 修改名為【課程資料報表】的預存程序，新增WHERE子句的條件，如下所示： ALTER PROCEDURE 課程資料報表 AS BEGIN SELECT 課程編號, 名稱, 學分 FROM 課程 WHERE 學分 > 3 END GO EXEC 課程資料報表

  48. 14-5-2 刪除預存程序-使用Management Studio • 在Management Studio工具的「物件總管」視窗展開預存程序，在其上執行【右】鍵快顯功能表的【刪除】指令，再按【確定】鈕，即可刪除預存程序。

  49. 14-5-2 刪除預存程序-使用T-SQL指令 • T-SQL語言是使用DROP PROCEDURE指令來刪除預存程序，其基本語法如下所示： DROP PROC[EDURE] 預存程序名稱 • 上述語法可以刪除名為【預存程序名稱】的預存程序，如果不只一個請使用「,」逗號分隔。 SQL指令碼檔：Ch14-5-2.sql • 刪除名為【課程資料報表】的預存程序，如下所示： DROP PROCEDURE 課程資料報表

  50. 14-6 系統預存程序-說明 • 系統預存程序（System Stored Procedures）與擴充預存程序（Extended Stored Procedures）是SQL Server系統已經預設寫好的預存程序，其主要目的是擴充T-SQL語言的功能，換句話說，我們可以馬上使用這些預存程序來執行所需的操作。

More Related