課程名稱：資料庫系統 授課老師：李春雄 博士 第 十四章 預存程序 課程名稱：資料庫系統 授課老師：李春雄 博士

本章學習目標 1.讓讀者瞭解預存程序的意義、使用時機、優缺點 及種類。 2.讓讀者瞭解建立與維護預存程序。

本章內容 14-1 何謂預存程序(Stored Procedure) 14-2 預存程序的優點與缺點 14-3 預存程序的種類 14-4 建立與維護預存程序 14-5 建立具有傳入參數的預存程序 14-6 建立傳入參數具有「預設值」的預存程序 14-7 傳回值的預存程序 14-8 執行預存程序命令 14-9 建立具有Recompile選項功能的預存程序 14-10 建立具有Encryption選項功能的預存程序 14-11 如何利用VB程式來呼叫預存程序

14-1 何謂預存程序(Stored Procedure) 【定義】 預存程序像是程式語言中的副程式，我們可以將常用的查詢或對資料庫進行複雜的操作指令預先寫好存放在資料庫裡，也這些預先儲存的整批指令就稱為「預存程序」。 【作法】 將整批 SQL 指令預先寫好存放在資料庫裡面，然後在適當的時機以單一SQL指令執行它。

【未使用與使用預儲程序之差異】 【第一種方法：未使用預儲程序】是指將T-SQL程式儲存在用戶(Client)端 【說明】 【未使用與使用預儲程序之差異】　 【第一種方法：未使用預儲程序】是指將T-SQL程式儲存在用戶(Client)端 【說明】 當使用者對資料庫有許多查詢需求時，則客戶端的應用程式就必須每次都要發佈一連串的SQL指令。如此一來，將會導致「客戶端」與「資料庫伺服器」之間的負荷提高，並且降低執行效率。

【第二種方法：使用預儲程序】指將T-SQL程式儲存為SQL Server的預存程序 【說明】 當使用者對資料庫有許多查詢需求時，則客戶端的應用程式只需要發佈呼叫「預儲程序」指令即可。因此，就不須要每次都要發佈一連串的SQL指令。如此一來，將可以降低「客戶端」與「資料庫伺服器」之間的負荷，並且提高執行效率。

14-2 預存程序的優點與缺點　 由於預存程序是一種直接在資料庫伺服端上執行的SQL程序。因此，客戶端的「使用者」只要透過呼叫預存程序名稱，即可執行「資料庫伺服端」上的預存程序之SQL指令。基本上，我們會將「常用」且「固定」異動操作(如：新增、修改、刪除)或查詢動作撰寫成預存程序，以達到以下四點優點： 一、提高執行效率 二、減少網路流量 三、增加資料的安全性 四、模組化以便重複使用

一、提高執行效率　 由於預存程序(Store Procedure)的每一行SQL指令只要事先編譯過一次 ,就可以進行剖析和最佳化，而傳統的T-SQL指令則是每次執行時都要反覆地從用戶端傳到伺服器。因此，它比傳統的T-SQL指令的執行速度來的快。

二、減少網路流量　 利用EXECUTE指令來執行預存程序時，就不須要每次在網路上傳送數十行至數百行的T-SQL程式碼。只要在前端送出一條執行預存程序的指令即可。

三、增加資料的安全性　 預存程序與檢視表相同，都是將使用者常用且固定的查詢操作，利用T-SQL指令撰寫成一段類似副程式，讓使用者不會直接接觸到基底資料表，以達到資料的安全性。

四、模組化以便重複使用　 設計者只要建立一次預存程序，並且將它儲存在資料庫中，爾後就可以提供不同使用者重複使用。

預存程序(Store Procedure)的缺點 攜性較差 可攜性較差是預存程序的主要缺點，因為每一家RDBMS廠商所提供的預存程序之程式語法不盡相同，MS SQL Server是以T-SQL來撰寫預存程序，Oracle則用PL-SQL。

14-3 預存程序的種類 【定義】 基本上，在SQL Server中，它提供三種不同的預存程序來讓使用者呼叫。 一、系統預存程序 14-3 預存程序的種類　 【定義】 基本上，在SQL Server中，它提供三種不同的預存程序來讓使用者呼叫。 一、系統預存程序 二、擴充預存程序 三、使用者自定預存程序

一、系統預存程序 【定義】 它是以sp_ 開頭名稱，所建立的預存程序。 【目的】用來管理或查詢系統相關的資訊。 【執行步驟】 一、系統預存程序　 【定義】 它是以sp_ 開頭名稱，所建立的預存程序。 【目的】用來管理或查詢系統相關的資訊。 【執行步驟】 進入SQL Server Enterprise Manager、執行「資料庫 / ch14_DB / 可程式性／預存程序／系統預存程序」找到系統提供的預存程序。 如下所示：

【範例】 請先建立一個檢視表「高雄市客戶檢視表」，再透過sp_helptext「系統預存程序」來查詢此檢視表的T-SQL指令 【程式碼】 【範例】　 請先建立一個檢視表「高雄市客戶檢視表」，再透過sp_helptext「系統預存程序」來查詢此檢視表的T-SQL指令 【程式碼】 【執行結果】 use ch14_DB go Create View 高雄市客戶檢視表 AS Select * From dbo.客戶資料表 Where 城市='高雄市' Select * From 高雄市客戶檢視表 Exec sp_helptext '高雄市客戶檢視表' --查詢此檢視表的T-SQL指令

【實例1】 利用「系統預存程序」來查詢目前資料庫系統的使用者有那些？ 【程式碼】 【執行結果】 您可以直接指定查詢「sa」的處理程序。 【實例1】　 利用「系統預存程序」來查詢目前資料庫系統的使用者有那些？ 【程式碼】 【執行結果】 您可以直接指定查詢「sa」的處理程序。 您也可以直接查詢目前正在使用中的處理程序。 Exec sp_who Go Exec sp_who 'sa' Exec sp_who 'active'

【實例2】 利用sp_detach_db「系統預存程序」來卸離資料庫？ 【程式碼】 【注意】 【實例2】　 利用sp_detach_db「系統預存程序」來卸離資料庫？ 【程式碼】 【注意】 在進行卸離資料庫動作時，必須將游標移到其他資料庫，否則無法進行。 EXEC sp_detach_db 'ch14_DB'

【實例3】 利用sp_attach_db「系統預存程序」來附加資料庫？ 【實例3】　 利用sp_attach_db「系統預存程序」來附加資料庫？ 首先將附書光碟中的ch14_DB.mdf與ch14_DB_log.ldf這兩個檔案同時複製到「D:\dbms」目錄下。 【程式碼】 EXEC sp_attach_db 'ch14_DB', 'D:\dbms\ch14_DB.mdf', 'D:\dbms\ch14_DB_log.ldf'

【實例4】 利用sp_helpdb「系統預存程序」來查詢目前全部的資料庫？ 【程式碼】 EXEC sp_helpdb

【實例5】 利用sp_renamedb「系統預存程序」來更改指定資料庫的名稱？ 【程式碼】 【實例5】　 利用sp_renamedb「系統預存程序」來更改指定資料庫的名稱？ 【程式碼】 EXEC sp_renamedb 'ch14_DB_OLD','ch14_DB_NEW'

二、擴充預存程序 【定義】 是指利用傳統程式語言來撰寫，以擴充T-SQL的功能。並且它是以xp_開頭名稱，所建立的預存程序。 二、擴充預存程序　 【定義】 是指利用傳統程式語言來撰寫，以擴充T-SQL的功能。並且它是以xp_開頭名稱，所建立的預存程序。 【目的】用來處理傳統T-SQL程式無法達成功能。

三、使用者自定預存程序　 【定義】 是指由使用者來自行設計預存程序，其方法與撰寫一般的副程式相同，都必須要命名一個名稱，但是在命名時最好不要以sp_或xp_開頭，否則容易與系統預存程序與擴充預存程序混淆。 【目的】可以依使用者的需求來設計預存程序。

【執行步驟】 進入SQL Server Enterprise Manager、執行「資料庫 / ch14_DB / 可程式性／預存程序」。 如下所示：

14-4 建立與維護預存程序　 在本單元中，我們會介紹如何建立預存程序，並且在建立之後，如何進行維護此預存程序。

14-4.1 建立預存程序 【定義】資料庫管理師依使用者的需求來建立預存程序。 【語法】 【符號說明】 14-4.1 建立預存程序　 【定義】資料庫管理師依使用者的需求來建立預存程序。 【語法】 【符號說明】 { | }代表在大括號內的項目是必要項，但可以擇一。 [ ] 代表在中括號內的項目是非必要項，依實際情況來選擇。 CREATE PROC[EDURE] procedure_name[;number]    [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [WITH   {    RECOMPILE   | ENCRYPTION   | RECOMPILE, ENCRYPTION   }] [FOR REPLICATION] AS      T-SQL_Sql_statement

 PROC[EDURE]：建立預存程序的關鍵字有兩種寫法： 簡寫：PROC 全名：PROCEDURE CREATE PROC[EDURE] procedure_name[;number]    [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [WITH   {    RECOMPILE   | ENCRYPTION   | RECOMPILE, ENCRYPTION   }] [FOR REPLICATION] AS      T-SQL_Sql_statement 【說明】  PROC[EDURE]：建立預存程序的關鍵字有兩種寫法： 簡寫：PROC 全名：PROCEDURE procedure_name：代表欲建立的預存程序的名稱。 number：用來管理相同預存程序之群組。 @parameter data_type：用來宣告參數的資料型態。以作為預存程序傳入或傳出之用。  default：用來設定所宣告之參數的預設值。  OUTPUT：用來輸出參數傳回的結果。

 ENCRYPTION：用來將設計者撰寫的預存程序進行編碼，亦即所謂的「加密」。 CREATE PROC[EDURE] procedure_name[;number]    [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [WITH   {    RECOMPILE   | ENCRYPTION   | RECOMPILE, ENCRYPTION   }] [FOR REPLICATION] AS      T-SQL_Sql_statement  RECOMPILE：代表每次執行此預存程序時，都會再重新編譯。其目的是當預存程序有異動時，能夠提供最佳的執行效能。但是，如果有指定FOR REPLICATION時，就不能指定此選項功能。  ENCRYPTION：用來將設計者撰寫的預存程序進行編碼，亦即所謂的「加密」。 FOR REPLICATION：是指用來設定此預存程序只能提供「複寫」功能。注意：此選項功能不能與WITH RECOMPILE同時使用。

【實作】 【建立預存程序】 【執行預存程序】 【執行結果】 將目前的「客戶資料表」中，住在「高雄市」的客戶，建立成一個預存程序。 【實作】　 將目前的「客戶資料表」中，住在「高雄市」的客戶，建立成一個預存程序。 【建立預存程序】 【執行預存程序】 【執行結果】 use ch14_DB go Create PROC 高雄市客戶之預存程序 AS Select * From dbo.客戶資料表 Where 城市='高雄市' 注意：預存程序內的欄位名稱是來 自於SQL敘述中的Select後 的欄位串列 EXEC 高雄市客戶之預存程序

【隨堂實作1】 【建立預存程序】 【執行預存程序】 【執行結果】 【隨堂實作1】　 請利用T-SQL指令，在「產品資料表」中，訂價超過2000元以上的產品，將「產品代號」、「產品名稱」及「訂價」建立成高價位產品之預存程序(命名為：MyProc1) 【建立預存程序】 【執行預存程序】 【執行結果】 USE ch14_DB go Create Proc MyProc1 /*建立高價位產品之預存程序*/ As Select 產品代號,產品名稱,訂價 From dbo.產品資料表 Where 訂價>2000 EXEC MyProc1

【隨堂實作2】建立預存程序群組 【建立預存程序】 【執行預存程序】 【執行結果】 【隨堂實作2】建立預存程序群組　 請利用T-SQL指令，在「產品資料表」中，訂價低於2000元(含)的產品，建立預存程序 (命名為：MyProc;1) 【建立預存程序】 【執行預存程序】 【執行結果】 USE ch14_DB go Create Proc MyProc;1 /*建立預存程序群組的第一個程序*/ As Select * From dbo.產品資料表 Where 訂價<=2000 EXEC MyProc;1

【隨堂實作3】建立預存程序群組 【建立預存程序】 【執行預存程序】 【執行結果】 【隨堂實作3】建立預存程序群組　 請利用T-SQL指令，在「產品資料表」中，訂價高於2000元(不含)的產品，建立預存程序 (命名為：MyProc;2) 【建立預存程序】 【執行預存程序】 【執行結果】 USE ch14_DB go Create Proc MyProc;2 /*建立預存程序群組的第二個程序*/ As Select * From dbo.產品資料表 Where 訂價>2000 EXEC MyProc;2

14-4.2 修改預存程序 【定義】用來修改已經存在的預存程序。 【語法】與建立預存程序相同，只要將Create改為Alter即可。 14-4.2 修改預存程序　 【定義】用來修改已經存在的預存程序。 【語法】與建立預存程序相同，只要將Create改為Alter即可。 【實作】將已經建立完成「高雄市客戶之預存程序」，改為只列出 「客戶姓名、電話及城市」等三個欄位的預存程序。 【修改預存程序】 【執行預存程序】 【執行結果】 use ch14_DB go Alter PROC 高雄市客戶之預存程序 AS Select 客戶姓名,電話,城市 From dbo.客戶資料表 Where 城市='高雄市' EXEC 高雄市客戶之預存程序

【隨堂實作】 【建立預存程序】 【執行預存程序】 【執行結果】 USE ch14_DB go 【隨堂實作】　 請利用T-SQL指令，修改已經「建立的高價位產品之預存程序」，將列出訂價超過2000元以上的產品的全部欄位名稱列出(命名為MyProc1) 【建立預存程序】 【執行預存程序】 【執行結果】 USE ch14_DB go Alter Proc MyProc1 /*建立高價位產品之預存程序*/ As Select * From dbo.產品資料表 Where 訂價>2000 EXEC MyProc1

14-4.3 刪除預存程序　 【定義】用來刪除已經存在的預存程序。 【語法】 DROP PROC[EDURE] 預存程序名稱

【實作】 將已經建立完成「高雄市客戶之預存程序」刪除。 【刪除預存程序】 use ch14_DBMS go DROP PROC 高雄市客戶之預存程序

14-5 建立具有傳入參數的預存程序　 在前一個本單元中，我們已經學會如何建立基本的預存程序之後，在本單元中，我們將進一步介紹，如何在預存程序中傳入參數，以讓預存程序能夠發揮更大的運用與彈性。

【語法】 【說明】  PROC[EDURE]：建立預存程序的關鍵字有兩種寫法： (1)簡寫：PROC (2)全名：PROCEDURE 【語法】　 CREATE PROC[EDURE] procedure_name     [ {@parameter data_type} [= default]] [,…n ] AS      T-SQL_Statement 【說明】  PROC[EDURE]：建立預存程序的關鍵字有兩種寫法： (1)簡寫：PROC (2)全名：PROCEDURE procedure_name：代表欲建立的預存程序的名稱。 @parameter data_type：用來宣告參數的資料型態。 以作為預存程序傳入或傳出之用。  default：用來設定所宣告之參數的預設值。 n：代表參數的個數

【實作】 在上一個例子中，我們執行預存程序時，只能建立住在「高雄市」的客戶的預存程序。但是，如果我也想再建立住在各城市的客戶時，那就必須要利用傳入參數的方式來建立。  【建立預存程序】 use ch14_DB go Create PROC CITY_CUS_PROC @City CHAR(10) AS Select * From dbo.客戶資料表 Where 城市=@City 傳遞參數宣告 傳入參數使用

【執行預存程序】 【執行結果】 傳遞參數宣告 傳入參數使用 use ch14_DBMS go Create PROC CITY_CUS_PROC @City CHAR(10) AS Select * From dbo.客戶資料表 Where 城市=@City 傳遞參數宣告 傳入參數使用 【執行預存程序】 【執行結果】 EXEC CITY_CUS_PROC '台北市' EXEC CITY_CUS_PROC '台南市' EXEC CITY_CUS_PROC '高雄市'

14-6 建立傳入參數具有「預設值」 的預存程序　 除了使用者指定傳入參數之外，我們也可以使用預設值。因此，其執行優先順序以使用者輸入為主要，但是如果使用者沒有給了傳入參數值，則以預設值來執行。 【實作】請利用傳入參數具有「預設值」來比較「沒有指定傳入參數」 與「有指定傳入參數」之不同。 【建立預存程序】 use ch14_DB go Create PROC CITY_CUS_PROC @City CHAR(10)='高雄市' AS Select * From dbo.客戶資料表 Where 城市=@City 傳遞參數設定預設值 傳入參數使用

沒有指定傳入參數 【執行預存程序】 【執行結果】 use ch14_DBMS go Create PROC CITY_CUS_PROC 沒有指定傳入參數　 use ch14_DBMS go Create PROC CITY_CUS_PROC @City CHAR(10)='高雄市' AS Select * From dbo.客戶資料表 Where 城市=@City 傳遞參數設定預設值 傳入參數使用 【執行預存程序】 【執行結果】 EXEC CITY_CUS_PROC

有指定傳入參數 【執行預存程序】 【執行結果】 use ch14_DBMS go Create PROC CITY_CUS_PROC 有指定傳入參數　 use ch14_DBMS go Create PROC CITY_CUS_PROC @City CHAR(10)='高雄市' AS Select * From dbo.客戶資料表 Where 城市=@City 傳遞參數設定預設值 傳入參數使用 【執行預存程序】 【執行結果】 EXEC CITY_CUS_PROC '台北市'

14-7 傳回值的預存程序 基本上，在撰寫預存程序時，會有三種不同傳回值的方法： 一、利用「OUTPUT」選項 14-7 傳回值的預存程序　 基本上，在撰寫預存程序時，會有三種不同傳回值的方法： 一、利用「OUTPUT」選項 二、利用「RETURN n」，其中n必須是整數 三、利用「EXEC」執行T-SQL

14-7 .1 利用OUTPUT傳出參數來傳回值 在預存程序中，我們可以利用OUTPUT「傳出參數」來回傳資料。 【實作】請利用傳出參數來查詢「產品資料表」中的訂價之差價。 【建立預存程序】 use ch14_DB go Create PROC Product_Diff_Price_PROC @P_Diff_Price int OUTPUT --產品差價 AS Declare @High_Price int --最高訂價 Declare @Low_Price int --最低訂價 Select @High_Price=MAX(訂價),@Low_Price=Min(訂價) From dbo.產品資料表 --計算產品差價 set @P_Diff_Price=@High_Price-@Low_Price

【執行預存程序】 【執行結果】 use ch14_DBMS go Create PROC Product_Diff_Price_PROC @P_Diff_Price int OUTPUT --產品差價 AS Declare @High_Price int --最高訂價 Declare @Low_Price int --最低訂價 Select @High_Price=MAX(訂價),@Low_Price=Min(訂價) From dbo.產品資料表 --計算產品差價 set @P_Diff_Price=@High_Price-@Low_Price 【執行預存程序】 【執行結果】 Declare @P_Diff int Exec Product_Diff_Price_PROC @P_Diff OUTPUT print '產品最高與最低的差價='+ CONVERT(VARCHAR,@P_Diff) go

14-7 .2 利用RETURN指令來傳回值　 在預存程序中，我們除了可以利用OUTPUT「傳出參數」來回傳資料之外，也可以利用「RETURN」指令來進行。 基本上，如果利用「RETURN」指令來傳回值，大部份是用來判斷某一預存程序是否執行成功。如果執行成功，則傳回0，否則傳回非0。 【實作】請利用「RETURN」指令來傳回產品資料表中指定的產品名稱。 【建立預存程序】 use ch14_DB go Create PROC Product_Name_PROC @P_No char(3) AS Select 產品名稱 From dbo.產品資料表 Where 產品代號=@P_No IF @@ROWCOUNT=0 return 1 --如果執行不成功，則傳回1 else return 0 --如果執行成功，則傳回0

第一種情況：成功找到 【執行預存程序】 【執行結果】 結果視窗 訊息視窗 use ch14_DBMS go 第一種情況：成功找到　 use ch14_DBMS go Create PROC Product_Name_PROC @P_No char(3) AS Select 產品名稱 From dbo.產品資料表 Where 產品代號=@P_No IF @@ROWCOUNT=0 return 1 else return 0 【執行預存程序】 【執行結果】 declare @ReValue int EXEC @ReValue=Product_Name_PROC P2 IF @ReValue=1 PRINT '找不到此產品的代號' ELSE PRINT '可以找到此產品代號' 結果視窗 訊息視窗

第二種情況：未成功找到 【執行預存程序】 【執行結果】 結果視窗 訊息視窗 use ch14_DBMS go 第二種情況：未成功找到　 use ch14_DBMS go Create PROC Product_Name_PROC @P_No char(3) AS Select 產品名稱 From dbo.產品資料表 Where 產品代號=@P_No IF @@ROWCOUNT=0 return 1 else return 0 【執行預存程序】 【執行結果】 declare @ReValue int EXEC @ReValue=Product_Name_PROC P20 IF @ReValue=1 PRINT '找不到此產品的代號' ELSE PRINT '可以找到此產品代號' 結果視窗 訊息視窗

14-7 .3 利用EXEC執行SQL字串來 傳回值　 在預存程序中，我們除了可以利用OUTPUT「傳出參數」與「RETURN」指令來回傳資料之外，也可以利用「EXEC」執行SQL字串來傳回值。 【實作】請利用EXEC執行SQL字串來傳回產品資料表中指定的產品名稱。 【建立預存程序】 use ch14_DB go Create PROC Product_PROC AS Declare @sqlStr char(200) Set @sqlStr='Select 產品名稱From 產品資料表' EXEC(@sqlStr)

【執行預存程序】 【執行結果】 use ch14_DBMS go Create PROC Product_PROC AS Declare @sqlStr char(200) Set @sqlStr='Select 產品名稱From 產品資料表' EXEC(@sqlStr) 【執行預存程序】 【執行結果】 EXEC Product_PROC

14-8 執行預存程序命令　 在我們撰寫完成預存程序之後，我們要再透過「EXECUTE」命令來執行。但是，有那預存程序是帶有參數，因此，要特別注意輸入參數的數目及順序，否則會產生錯誤。 在執行預存程序命令時，基本上，有兩種參數傳入方法： 一、未指定傳入參數名稱：它必須要按照預存程序中的參數位置順序。 二、有指定傳入參數名稱：不須要按照預存程序中的參數位置順序。

【實作】請利用帶有傳入參數預存程序來比較「未指定」與「有指定」傳入參數名稱。將在「產品資料表」中，產品名稱為「隨身碟」降價20%。 【建立預存程序】 【執行預存程序】--隨身碟調降之前 【執行結果】 use ch14_DB go Create PROC Down_HD_Price_PROC @Name CHAR(10), @Down_Price float AS Update 產品資料表 Set 訂價=訂價*(1-@Down_Price) Where 產品名稱=@Name Select * from 產品資料表 Where 產品名稱='隨身碟'

未指定傳入參數名稱 【執行預存程序】必須要按照預存程序中的參數位置順序 【執行結果】 --隨身碟第一次調降之後 未指定傳入參數名稱　 【執行預存程序】必須要按照預存程序中的參數位置順序 【執行結果】 --隨身碟第一次調降之後 Exec Down_HD_Price_PROC '隨身碟',0.2 Select * from 產品資料表 Where 產品名稱='隨身碟'

有指定傳入參數名稱 【執行預存程序】不須要按照預存程序中的參數位置順序 【執行結果】 --隨身碟第二次調降之後 有指定傳入參數名稱　 【執行預存程序】不須要按照預存程序中的參數位置順序 【執行結果】 --隨身碟第二次調降之後 Exec Down_HD_Price_PROC @Down_Price=0.2,@Name='隨身碟' Select * from 產品資料表 Where 產品名稱='隨身碟'

14-9 建立具有Recompile選項功能 的預存程序 【定義】 每次執行此預存程序時，都會再重新編譯。其目的是當預存程序有異動時，能夠提供最佳的執行效能。但是，如果有指定FOR REPLICATION時，就不能指定此選項功能。 【實作】請利用傳出參數來查詢產品資料表中的訂價之差價。 【建立預存程序】 use ch14_DB go Create PROC Product_Diff_Price_PROC @P_Diff_Price int OUTPUT --產品差價 WITH RECOMPILE --重新編譯 AS Declare @High_Price int --最高訂價 Declare @Low_Price int --最低訂價 Select @High_Price=MAX(訂價),@Low_Price=Min(訂價) From dbo.產品資料表 --計算產品差價 set @P_Diff_Price=@High_Price-@Low_Price

【執行預存程序】 【執行結果】 use ch14_DBMS go Create PROC Product_Diff_Price_PROC @P_Diff_Price int OUTPUT --產品差價 WITH RECOMPILE --重新編譯 AS Declare @High_Price int --最高訂價 Declare @Low_Price int --最低訂價 Select @High_Price=MAX(訂價),@Low_Price=Min(訂價) From dbo.產品資料表 --計算產品差價 set @P_Diff_Price=@High_Price-@Low_Price 【執行預存程序】 【執行結果】 Declare @P_Diff int Exec Product_Diff_Price_PROC @P_Diff OUTPUT print '產品最高與最低的差價='+ CONVERT(VARCHAR,@P_Diff) go

14-10 建立具有Encryption選項功能 的預存程序 【定義】用來將設計者撰寫的預存程序進行編碼，亦即所謂的「加密」。 【實作】請撰寫對「產品資料表」中的「訂價」的五成就是「低價」 的預存程序 【建立預存程序】 use ch14_DB go Create PROC LOW_Price_PROC @P_NO CHAR(2) WITH ENCRYPTION AS Select 產品代號,產品名稱,訂價,訂價*0.5 as 低價 From dbo.產品資料表 WHERE 產品代號=@P_NO

【註】如果想要同時設定「Recompile」與「Encryption」兩項功能時，則只須在兩項之間加入「，」逗點即可。如下所示： WITH RECOMPILE , ENCRYPTION

14-11 如何利用VB程式來呼叫預存程序 在我們學會如何利用SQL語言來撰寫「預存程序」之後，接下來，我們來介紹，如何利用應用程式來呼叫資料庫管理系統中的「預存程序」。在本書中，筆者以VB程式語言來呼叫。 接下來，我們來介紹，如何利用VB程式語言來呼叫「預存程序」，其常見的呼叫方式有以下兩種： VB連結預存程序(沒有指定傳入參數名稱) VB連結預存程序(有指定傳入參數名稱)

一、VB連結預存程序(沒有指定傳入參數名稱) 是指利用VB2010程式語言來呼叫SQL Server2008中的「預存程序」。 【實作】請利用VB呼叫ch14-7 .3單元所建立取出「產品資料表中的 產品名稱」之預存程序 【程式碼】 Imports System.Data Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim Source As String ' 宣告連線的字串 Source = "server=localhost;" ' 伺服器 Source += "database=ch14_DB;" ' 資料庫 Source += "user id=sa;" ' 登入的帳號 Source += "password=AAABBBCCC" ' 密碼(依您的情況而定) Dim conn As SqlConnection ' 宣告連線的物件 conn = New SqlConnection(Source) ' 連線 conn.Open() '開啟資料庫 '宣告物件 Dim DtApter As SqlDataAdapter Dim DtSet As DataSet DtApter = New SqlDataAdapter("Product_PROC", conn) ' VB連結預存程序 DtSet = New DataSet '讀取資料表 DtApter.Fill(DtSet, "產品資料表") DataGridView1.DataSource = DtSet.Tables("產品資料表") conn.Close() ' 關閉資料庫 End Sub

【執行結果】

二、VB連結預存程序(有指定傳入參數名稱) 【實作】請利用VB呼叫ch14-8單元所建立「Down_HD_Price_PROC」預存程序，並指定傳入參數名稱的方式來呼叫，其參數有兩項： @Down_Price=0.2 @Name='隨身碟‘” 【程式碼】 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim Source As String ' 宣告連線的字串 Source = "server=localhost;" ' 伺服器 Source += "database=ch14_DB;" ' 資料庫 Source += "user id=sa;" ' 登入的帳號 Source += "password=AAABBBCCC" ' 密碼(依您的情況而定) Dim conn As SqlConnection ' 宣告連線的物件 conn = New SqlConnection(Source) ' 連線 conn.Open() '開啟資料庫 '宣告物件 Dim DtApter1 As SqlDataAdapter Dim DtApter2 As SqlDataAdapter Dim DtSet As DataSet ' VB連結預存程序(有指定傳入參數名稱) DtApter1 = New SqlDataAdapter("Down_HD_Price_PROC @Down_Price=0.2,@Name='隨身碟'", conn) '顯示結果 DtApter2 = New SqlDataAdapter("Select * from 產品資料表 Where 產品名稱='隨身碟'", conn) DtSet = New DataSet '讀取資料表 DtApter1.Fill(DtSet, "產品資料表") DtApter2.Fill(DtSet, "產品資料表") DataGridView1.DataSource = DtSet.Tables("產品資料表") conn.Close() ' 關閉資料庫 End Sub

【執行結果】