14 預存程序與順序物件 14-1 預存程序的基礎 14-2 建立與執行預存程序 14-3 預存程序的參數傳遞 14-4 預存程序的傳回值

Presentation on theme: "14 預存程序與順序物件 14-1 預存程序的基礎 14-2 建立與執行預存程序 14-3 預存程序的參數傳遞 14-4 預存程序的傳回值"— Presentation transcript:

1

2 14 預存程序與順序物件 14-1 預存程序的基礎 14-2 建立與執行預存程序 14-3 預存程序的參數傳遞 14-4 預存程序的傳回值
14-5 修改與刪除預存程序 14-6 系統預存程序 14-7 建立與使用順序物件

3 14-1 預存程序的基礎 預存程序簡介 預存程序的優點

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

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

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

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

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

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

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

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

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

13 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 14-2-2 執行預存程序- 使用Management Studio

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

34 巢狀呼叫-說明 「巢狀呼叫」（Nesting）是指在預存程序中呼叫另一個預存程序，在T-SQL最多支援32層巢狀呼叫，超過就會產生錯誤。

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

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

37 14-4 預存程序的傳回值 使用RETURN關鍵字 使用OUTPUT關鍵字

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

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

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

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

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

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

44 14-4-2 使用OUTPUT關鍵字-範例2 SQL指令碼檔：Ch14_4_2_02.sql
呼叫名為【薪水查詢】預存程序來取得指定員工的薪水，如下所示： money EXEC 薪水查詢 OUTPUT PRINT ‘Joe’s 薪水:' +

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

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

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

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

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

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

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

52 14-6 系統預存程序-常用程序 系統與擴充預存程序 說明 sp_help [名稱]
傳回參數指定的資料庫物件、使用者自訂資料類型或 SQL Server內建資料類型的資訊，如果沒有參數，就是傳回所有物件的資訊 sp_helptext 名稱 傳回參數預存程序、自訂函數、觸發程序或檢視表的內容 sp_helpdb [資料庫名稱] 傳回參數資料庫的資訊，如果沒有參數，就是傳回所有資料庫的摘要資訊 sp_columns 資料表名稱 傳回指定資料表或檢視表的欄位資訊 sp_who [登入帳戶] 提供SQL Server執行個體中關於目前使用者、工作階段和處理序的資訊 sp_droplogin 登入帳戶 刪除指定的登入帳戶 xp_cmdshell 執行Windows作業系統的命令 xp_msver 傳回SQL Server版本資訊 xp_logininfo 傳回Windows使用者和群組的資訊

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

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

55 14-7 建立與使用順序物件 建立順序物件 使用順序物件 修改與刪除順序物件

56 建立順序物件-說明 基本上，順序物件的數值序列會以定義的間隔，依照遞增或遞減的順序來產生，當編號用完時，可以重新啟動，即循環產生下一個數值。順序物件和資料表識別欄位（自動編號）的主要差異，在於順序物件和資料表之間沒有任何關聯，它是一個獨立物件，我們是使用T-SQL指令來擷取順序物件的下一個值。 順序物件的優點就是不受限於單一資料表，只要資料表的欄位有需要，就可以共用同一個順序物件替不同資料表的不同欄位產生流水號。

57 14-7-1 建立順序物件- 使用Management Studio

58 14-7-1 建立順序物件- 使用T-SQL指令(語法)
T-SQL語言是使用CREATE SEQUENCE指令建立順序物件，其語法如下所示： CREATE SEQUENCE 順序名稱 [ AS 資料類型 ] [ START WITH 常數值 ] [ INCREMENT BY 常數值 ] [ MINVALUE [ 類數值 ] | NO MINVALUE ] [ MAXVALUE [ 常數值 ] | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE [ 常數值 ] } | { NO CACHE } ]

59 14-7-1 建立順序物件- 使用T-SQL指令(範例)
SQL指令碼檔：Ch14_7_1_01.sql 請建立名為【編號順序】的順序物件，起始值是1；增量也是1，最小值是1，沒有最大值，如下所示： CREATE SEQUENCE 編號順序 AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE

60 使用順序物件-語法 在SQL Server資料庫建立順序物件後，我們就可以使用NEXT VALUE FOR指令取得順序物件的下一個值，其語法如下所示： NEXT VALUE FOR 順序物件名稱 上述語法可以取得名為【順序物件名稱】順序物件的下一個數值。

61 14-7-2 使用順序物件-範例1 SQL指令碼檔：Ch14_7_2_01.sql
請使用SELECT指令取得名為【整數順序】順序物件的下一個值，如下所示： SELECT NEXT VALUE FOR 整數順序 AS 整數順序

62 14-7-2 使用順序物件-範例2a SQL指令碼檔：Ch14_7_2_03.sql
請使用【編號順序】順序物件產生的數值作為客戶編號和員工編號的值，我們準備分別在【好客戶】資料表插入一筆記錄；【好員工】資料表插入2筆記錄，最後使用SELECT指令顯示2個資料表的記錄資料，如下所示： SET IDENTITY_INSERT 好客戶 ON GO INSERT INTO 好客戶 (客戶編號, 身份證字號, 姓名) VALUES (NEXT VALUE FOR 編號順序, 'A ' , '王大安') SET IDENTITY_INSERT 好客戶 OFF

63 14-7-2 使用順序物件-範例2b SET IDENTITY_INSERT 好員工 ON GO
INSERT INTO 好員工 (員工編號, 姓名) VALUES (NEXT VALUE FOR 編號順序, '王允傑') VALUES (NEXT VALUE FOR 編號順序, '陳允傑') SET IDENTITY_INSERT 好員工 OFF SELECT * FROM 好客戶 SELECT * FROM 好員工

64 14-7-3 修改與刪除順序物件- 修改(語法) T-SQL語言是使用ALTER SEQUENCE指令修改順序物件，其語法如下所示：
[ RESTART WITH 常數值 ] [ INCREMENT BY 常數值 ] [ MINVALUE [ 類數值 ] | NO MINVALUE ] [ MAXVALUE [ 常數值 ] | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE [ 常數值 ] } | { NO CACHE } ]

65 14-7-3 修改與刪除順序物件- 修改(範例) SQL指令碼檔：Ch14_7_3_01.sql
請修改名為【編號順序】的順序物件，起始值改為50；增量改為2，如下所示： ALTER SEQUENCE 編號順序 RESTART WITH 50 INCREMENT BY 2

66 14-7-3 修改與刪除順序物件- 刪除 T-SQL語言是使用DROP SEQUENCE指令刪除順序物件，其語法如下所示：
上述語法可以刪除名為【順序物件名稱】的順序物件，如果有多個，請使用「,」號分隔。 SQL指令碼檔：Ch14_7_3_02.sql 請刪除名為【編號順序】的順序物件，如下所示： DROP SEQUENCE 編號順序