第 14 章 預存程序.

Presentation on theme: "第 14 章 預存程序."— Presentation transcript:

1 第 14 章 預存程序

2 本章重點 14-1 預存程序簡介 14-2 預存程序的建立、使用與修改 14-3 設計預存程序的技巧 14-4 使用 table 型別的參數

3 14-1 預存程序簡介 預存程序的優點 預存程序的種類

4 預存程序的優點 執行效率高 統一的操作流程 重複使用 安全性

5 透過檢視表或預存程序, 來存取沒有使用權限的資料表

6 預存程序的種類 系統預存程序 (System stored procedures) 進行系統的各項設定、取得資訊或相關管理工作。
延伸預存程序 (Extended stroed procedures)：

7 預存程序的種類 使用者自訂的預存程序 (User-defined stored procedures)

8 "sp_" 的特殊意義

9 "sp_" 的特殊意義

10 "sp_" 的特殊意義

11 14-2 預存程序的建立、使用與修改 用 SQL 語言建立預存程序
使用 SQL Server Management Studio 建立預存程序 更改預存程序的名稱 修改與刪除自訂預存程序 使用 SQL Server Management Studio 執行、管理預存程序

12 用 SQL 語言建立預存程序

13 用 SQL 語言建立預存程序 CREATE PROC[EDURE] procedure_name [;number]
@parameter data_type [VARYING] [= default] [OUTPUT] [READONLY] WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }

14 用 SQL 語言建立預存程序 FOR REPLICATION

15 用 SQL 語言建立預存程序

16 用 SQL 語言建立預存程序

17 用 SQL 語言建立預存程序

18 用 SQL 語言建立預存程序

19 用 SQL 語言建立預存程序

20 用 SQL 語言建立預存程序

21 使用 SQL Server Management Studio 建立預存程序

22 使用 SQL Server Management Studio 建立預存程序

23 使用 SQL Server Management Studio 建立預存程序

24 使用 SQL Server Management Studio 建立預存程序

25 更改預存程序的名稱

26 修改與刪除自訂預存程序 修改預存程序

27 修改與刪除自訂預存程序 刪除預存程序

28 使用 SQL Server Management Studio 執行、管理預存程序

29 使用 SQL Server Management Studio 執行、管理預存程序

30 使用 SQL Server Management Studio 執行、管理預存程序

31 14-3 設計預存程序的技巧 在預存程序中使用敘述的限制 參數傳遞的技巧 預存程序的 3 種傳回值 自訂預存程序傳回資料集的格式
SET NOCOUNT 選項

32 設計預存程序的技巧 SET QUOTED_IDENTIFIER 及 SET ANSI_NULLS 選項 暫存性的預存程序 巢狀呼叫
檢視預存程序的使用與被使用關係 執行遠端 SQL Server 中的預存程序

33 在預存程序中使用敘述的限制 在預存程序中, 有些敘述不可使用, 包括

34 在預存程序中使用敘述的限制 在同一個資料庫中, 只要使用不同的結構描述, 便可以建立相同名稱的物件, 例如 dbo.test、abc.test、sales.test 三個資料表可以同時存在。

35 在預存程序中使用敘述的限制

36 在預存程序中使用敘述的限制 有些指令在執行時若未指定結構描述, 會固定以目前使用者的預設結構描述來尋找或建立物件, 這些指令包括

37 參數傳遞的技巧

38 參數傳遞的技巧

39 參數傳遞的技巧

40 預存程序的 3 種傳回值 在程序中以 "RETURN n" 傳回整數值。 在參數中指定 OUTPUT 選項的參數。
預存程序中執行敘述 (例如 SELECT) 所傳回的資料集 (RecordSet) 及通知訊息。

41 預存程序的 3 種傳回值

42 預存程序的 3 種傳回值

43 自訂預存程序傳回資料集的格式

44 自訂預存程序傳回資料集的格式

45 自訂預存程序傳回資料集的格式

46 自訂預存程序傳回資料集的格式 column_name data_type [ NULL | NOT NULL ]
EXEC ... WITH RESULT SETS UNDEFINED EXEC ... WITH RESULT SETS NONE EXEC ... WITH RESULT SETS ((資料集定義),(資料集定義),...)

47 自訂預存程序傳回資料集的格式

48 自訂預存程序傳回資料集的格式

49 SET NOCOUNT 選項

50 SET QUOTED_IDENTIFIER 及 SET ANSI_NULLS 選項

51 暫存性的預存程序 暫存預存程序會存放在 tempdb 資料庫中。 當暫存預存程序的使用者都離線之後, 暫存預存程序會自動被刪除。
區域暫存預存程序的名稱以# 開頭, 只有建立它的人可以使用。 全域暫存預存程序的名稱以## 開頭, 所有的使用者都可以使用它。

52 巢狀呼叫

53 巢狀呼叫

54 檢視預存程序的使用與被使用關係

55 執行遠端 SQL Server 中的預存程序

56 執行遠端 SQL Server 中的預存程序

57 執行遠端 SQL Server 中的預存程序

58 執行遠端 SQL Server 中的預存程序

59 14-4 使用 table 型別的參數

60 使用 table 型別的參數