第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指令敘述的集合，這是在資料庫管理系統執行的指令敘述集合，可以簡化相關的資料庫操作來增進系統效能。 預存程序（Stored Procedures）是一組T-SQL指令敘述的集合，我們可以使用T-SQL流程控制指令來撰寫複雜的功能。不只如此，因為預存程序只需編譯一次，就可以執行多次，換句話說，執行預存程序可以增進系統效能，因為執行時並不需重新再編譯T-SQL指令敘述。

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

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

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

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

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

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

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

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

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

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

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

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

14-2-2 執行預存程序- EXECUTE指令執行T-SQL指令字串(語法) 上述語法使用EXECUTE或EXEC指令，之後是欲執行的T-SQL指令敘述字串。

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

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

14-4-1 使用RETURN指令-語法 在預存程序可以使用RETURN指令傳回預存程序的執行狀態，其基本語法如下所示： 因為預存程序會傳回值，所以在執行時需要宣告一個T-SQL變數來取得預存程序的傳回值，其語法如下所示： EXEC[UTE] ＠傳回值變數 = 預存程序名稱 參數值 [, …]

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)

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

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

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

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

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

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

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

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

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

14-5-2 刪除預存程序- 使用Management Studio

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

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

14-6 系統預存程序-常用程序

14-6 系統預存程序-範例1 SQL指令碼檔：Ch14-6-01.sql 顯示【課程查詢】預存程序的內容，如下所示： EXEC sp_helptext 課程查詢

14-6 系統預存程序-範例2 SQL指令碼檔：Ch14-6-02.sql 顯示【學生】資料表的欄位資訊，如下所示： EXEC sp_columns 學生

End