Chap 13 視界與資料庫程式設計.

Presentation on theme: "Chap 13 視界與資料庫程式設計."— Presentation transcript:

1 Chap 13 視界與資料庫程式設計

2 視界的內容-說明 「視界」（Views）相當於ANSI/SPARC三層資料系統架構中外部層顯示的資料，這是從基底關聯表導出的虛擬關聯表，如下圖所示：

3 視界的內容-資料來源 視界之所以稱為虛擬關聯表，這是因為它並沒有真正將資料儲存在磁碟，而只是一些定義資料，定義從那些基底關聯表或視界挑出那些屬性或值組，SQL語言是使用CREATE VIEW指令來定義視界。 視界顯示的資料是從定義的基底關聯表導出，依照定義過濾掉不屬於視界的資料，如果視界是從其他視界導出，只是重複過濾一次，所以視界如同是一個從不同基底關聯表或視界抽出的資料積木，然後使用這些積木拼出所需的關聯表。

4 視界的內容-資料來源圖例

5 視界的種類 列欄子集視界（Row-and-Column Subset Views）：從單一基底關聯表或視界導出的視界，只挑選關聯表或視界中所需的屬性和值組。 合併視界（Join Views）：使用合併查詢從多重基底關聯表或視界所導出的視界，新視界的屬性和值組是來自多個基底關聯表或視界。 統計摘要視界（Statistical Summary Views）：一種列欄子集視界或合併視界，只是使用聚合函數產生指定欄位所需的統計資料。

6 視界的優缺點-優點1 達成邏輯資料獨立：視界相當於外部與概念對映，更改基底關聯表的綱要，只需同時更改視界的外部與概念對映的定義資料，就可以讓使用者檢視相同觀點的資料，而不會影響外部綱要。 增加資料安全性：視界可以隱藏和過濾資料，只讓使用者看到它允許看到的資料，增加資料的安全性。 例如：在Students資料表擁有GPA欄位的學生成績，只需使用視界就可以隱藏GPA欄位的學生成績，只讓使用者看到其他部分的學生資料。

7 視界的優缺點-優點2 簡化資料查詢：將常用和複雜的查詢定義成視界，就可以簡化資料查詢，因為不再需要每次重複執行複雜的SQL查詢指令，直接使用現成的視界即可。 簡化使用者觀點：視界可以增加資料的可讀性，讓資料庫使用者專注於所需的資料。 例如：替欄位更名成使用者觀點的欄位名稱。

8 視界的優缺點-缺點 執行效率差：視界並沒有真正儲存資料，資料是在使用時才從基底關聯表導出，因為經過轉換手續，執行效率一定比不過直接存取基底關聯表。 更多的操作限制：在新增、更新和刪除視界資料時，為了避免違反資料庫的完整性限制條件，在操作上有更多的限制。 增加管理的複雜度：視界可以一層一層的從其他視界導出。 例如：View2和View4是從View1導出，View3是從View2導出，複雜的視界關聯將增加管理眾多關聯表和視界的複雜度，一不小心刪錯視界，就有可能造成嚴重後果。

9 13-2 建立與刪除視界-建立 在SQL語言建立視界是使用CREATE VIEW指令，其基本語法如下所示：
CREATE VIEW view_name AS select_statement 上述語法建立名為view_name的視界，資料來源是AS之後的SELECT指令敘述。視界的欄位和資料型態都是對應SELECT指令敘述的欄位，如果使用聚合函數（Aggregate Function），請使用AS指令定義別名。

10 13-2 建立與刪除視界-刪除 對於資料表中不再需要的視界，SQL語言可以使用DROP VIEW指令刪除視界，其基本語法如下所示：
DROP VIEW view_name 刪除名為view_name的視界。

11 建立列欄子集視界-說明 列欄子集視界是指視界的內容是基底關聯表屬性集的子集合，也就是以資料表的欄位和記錄為單位，從這些欄位和記錄集合中，取出所需子集合的檢視表。 例如：在本節準備建立視界的基底關聯表是Students資料表，如下圖所示：

12 13-2-1 建立列欄子集視界 欄子集視界是指這個視界的屬性是基底關聯表屬性集合的子集合。 SQL查詢範例：
在Students基底關聯表建立學生電話連絡資料的Tel_View視界，如下所示： CREATE VIEW Tel_View AS SELECT sid, name, tel FROM Students

13 建立列欄子集視界 SQL查詢範例： 查詢Tel_View視界的內容，如下所示： SELECT * FROM Tel_View

14 13-2-1 建立列欄子集視界 列子集視界是指這個視界的值組是基底關聯表值組集合的子集合。 SQL查詢範例：
在Students基底關聯表建立學生成績GPA欄位大於等於3.0的GPA_View視界，如下所示： CREATE VIEW GPA_View AS SELECT * FROM Students WHERE GPA >= 3.0

15 建立列欄子集視界 SQL查詢範例： 查詢GPA_View視界內容，如下所示： SELECT * FROM GPA_View

16 13-2-1 建立列欄子集視界 列欄子集視界（是指視界的屬性和值組都是基底關聯表屬性和值組集合的子集合。 SQL查詢範例：
在Students基底關聯表建立學生成績GPA欄位大於等於3.0，而且只有sid、name和GPA三個欄位的Score_View視界，如下所示： CREATE VIEW Score_View AS SELECT sid, name, GPA FROM Students WHERE GPA >= 3.0

17 建立列欄子集視界 SQL查詢範例： 查詢Score_View視界內容，如下所示：SELECT * FROM Score_View

18 建立合併視界-說明 合併視界（Join Views）是多個關聯表執行合併查詢所建立的視界。

19 建立合併視界-範例 SQL查詢範例： 在Students、Courses、Instructors和Classes四個資料表建立合併視界Std_Class_View，可以顯示學生的選課資料，如下所示： CREATE VIEW Std_Class_View AS SELECT Classes.sid, Students.name, Classes.eid, Instructors.name AS professor, Classes.c_no, Courses.title, Classes.room FROM Students, Courses, Instructors, Classes WHERE Students.sid = Classes.sid and Courses.c_no = Classes.c_no and Instructors.eid = Classes.eid

20 13-2-2 建立合併視界-結果 SQL查詢範例： 查詢Std_Class_View視界內容，如下所示：
SELECT * FROM Std_Class_View

21 建立統計摘要視界-說明 統計摘要視界（Statistical Summary Views）屬於一種列欄子集視界或合併視界，只是使用聚合函數（Aggregate Function）產生指定欄位所需的統計資料。

22 13-2-3 建立統計摘要視界-範例1 SQL查詢範例：
建立Students、Courses和Classes三個資料表的統計摘要視界Credits_View的合併視界，因為使用COUNT()和SUM()的聚合函數來顯示每位學生的選課數和所修的總學分： CREATE VIEW Credits_View AS SELECT Students.sid, COUNT(*) AS numofcourses, SUM(Courses.credits) AS credits FROM Students, Courses, Classes WHERE Students.sid = Classes.sid and Courses.c_no = Classes.c_no GROUP BY Students.sid

23 13-2-3 建立統計摘要視界-結果1 SQL查詢範例： 查詢Credits_View視界內容，如下所示：
SELECT * FROM Credits_View

24 13-2-3 建立統計摘要視界-範例2 SQL查詢範例：
請修改統計摘要視界Credits_View，建立只顯示學生所修總學分超過6個學分的學生選課總數和學分數的合併視界： CREATE VIEW Top_Credits_View AS SELECT Students.sid, COUNT(*) AS numofcourses, SUM(Courses.credits) AS credits FROM Students, Courses, Classes WHERE Students.sid = Classes.sid and Courses.c_no = Classes.c_no GROUP BY Students.sid HAVING SUM(Courses.credits) >= 6

25 13-2-3 建立統計摘要視界-結果2 SQL查詢範例： 查詢Top_Credits_View視界內容，如下所示：
SELECT * FROM Top_Credits_View

26 從其他視界建立視界 視界不只可以從基底關聯表導出，如果有已經存在的視界，我們也可以從現有視界來建立新視界。

27 從其他視界建立視界 SQL查詢範例： 在上一節的Credits_View視界只顯示學號，我們可以再次使用此視界和Students基底關聯表，建立合併視界Std_Credits_View來顯示學生姓名name和電話tel欄位的詳細資料，如下所示： CREATE VIEW Std_Credits_View AS SELECT Credits_View.*, Students.name, Students.tel FROM Students, Credits_View WHERE Students.sid = Credits_View.sid

28 從其他視界建立視界

29 13-2-4 從其他視界建立視界 SQL查詢範例： 查詢Std_Credits_View視界內容，如下所示：
SELECT * FROM Std_Credits_View

30 13-3 編輯視界的內容 從視界新增資料表的記錄 從視界更新資料表的記錄 從視界刪除資料表的記錄

31 13-3 編輯視界的內容-限制條件 視界需要包含資料表的主鍵。
在CREATE VIEW指令的select_statement指令不可包含DISTINCT、聚合函數、GROUP BY和HAVING子句，如果有，視界就只能查詢，換句話說，統計摘要視界擁有聚合函數，所以只能查詢，而不能新增、更新和刪除記錄。 因為視界是從基底關聯表導出，所以新增、更新和刪除操作仍然需要遵守其來源基底關聯表的完整性限制條件。

32 13-3 編輯視界的內容 WITH CHECK OPTION指令是CREATE VIEW指令的選項，在建立視界時加上此選項，表示新增、更新和刪除記錄時，需要檢查完整性限制條件，如果不符合條件，就顯示錯誤訊息。 WITH CHECK OPTION指令的基本語法如下所示： CREATE VIEW view_name AS select_statement WITH CHECK OPTION

33 13-3 編輯視界的內容 例如：從Students資料表導出，建立學生生日資料的Birthday_View視界，且使用WITH CHECK OPTION指令，其SQL指令如下所示： CREATE VIEW Birthday_View AS SELECT sid, name, birthday, tel FROM Students WITH CHECK OPTION Birthday_View的視界滿足前述限制條件。

34 13-3 編輯視界的內容 再建立一個學生生日資料的No_Birthday_View視界，這個視界不含資料表主鍵sid，其SQL指令如下所示：
CREATE VIEW No_Birthday_View AS SELECT name, birthday, tel FROM Students WITH CHECK OPTION No_Birthday_View視界不滿足前述限制條件。

35 13-3-1 從視界新增資料表的記錄 SQL查詢範例： 在Birthday_View視界新增一筆學生記錄，如下所示：
INSERT INTO Birthday_View VALUES (‘S006’, ‘江峰’, ‘ ’, 3.0 )

36 13-3-1 從視界新增資料表的記錄 SQL查詢範例： 在No_Birthday_View視界新增一筆學生記錄，如下所示：
INSERT INTO No_Birthday_View VALUES (‘江峰火’, ‘ ’, 3.0)

37 13-3-2 從視界更新資料表的記錄 SQL查詢範例： 在Birthday_View視界將學號S006學生的GPA改為3.3，如下所示：
UPDATE Birthday_View SET GPA = 3.3 WHERE sid = 'S006'

38 13-3-2 從視界更新資料表的記錄 SQL查詢範例： 在No_Birthday_View視界將學生江峰的GPA改為3.3，如下所示：
UPDATE No_Birthday_View SET GPA = 3.3 WHERE name = ‘江峰’ 當執行上述SQL指令，SQL Server仍然會更新Students資料表的記錄，不過，理論上應該避免在沒有主鍵的視界執行更新操作。

39 13-3-3 從視界刪除資料表的記錄 SQL查詢範例： 在Birthday_View視界刪除學號S006學生資料，如下所示：
DELETE FROM Birthday_View WHERE sid = ‘S006’

40 13-3-3 從視界刪除資料表的記錄 SQL查詢範例： 在No_Birthday_View視界刪除學生江峰，如下所示：
DELETE No_Birthday_View WHERE name = ‘江峰’ 當執行上述SQL指令，SQL Server仍然會刪除Students資料表的記錄，不過，在理論上應該避免在沒有主鍵的視界執行刪除操作。

41 13-4 資料庫程式設計的基礎 資料庫程式設計（Database Programming）的目的是建立資料庫系統的應用程式，以主從架構資料庫系統來說，就是客戶端應用程式。 例如：本書使用的Query Express應用程式，因為應用程式需要存取資料庫的資料，所以，SQL結構化查詢語言將扮演十分重要的角色。

42 資料庫程式設計的語言-說明 P SQL語言屬於一種宣告式的高階語言，可以很方便和容易存取關聯式資料庫的語言。不過，SQL語言並不適合處理一般程式邏輯，雖然Transact-SQL擴充SQL語言的功能，增加很多批次指令可以建立程式邏輯的條件和迴圈，不過功能仍然有限。 換句話說，單純使用SQL語言並不足以建立所需的應用程式，因為SQL語言並沒有通用用途的程式語言的功能，例如：VB、C/C++和Java語言。

43 資料庫程式設計的語言-分工 SQL語言：負責資料庫查詢和操作的資料存取，分為兩種：「嵌入式SQL」（Embedded SQL）和「動態SQL」（Dynamic SQL）。 通用用途的程式語言：負責處理其他操作的商業邏輯和使用介面，稱為「宿主語言」（Host Languages）。

44 資料庫程式設計的實作-解決方案 P 特殊語法和預先編譯的嵌入式SQL：嵌入式SQL是將SQL指令置於宿主語言的程式檔案，資料庫管理系統提供「前置處理程式」（Pre-processor）將程式檔案中的嵌入式SQL指令編譯成純宿主語言的程式碼檔案，然後使用宿主語言的編譯程式編譯成應用程式。 資料庫函式庫與動態SQL：在宿主語言的程式碼檔案中，SQL指令是一個字串資料型態的變數，應用程式是在執行階段才使用資料庫函式庫的函數送出SQL指令字串執行資料庫存取，稱為動態SQL。

45 資料庫程式設計的實作-資料庫函式庫 資料庫程式設計可以在宿主語言直接使用「資料庫函式庫」（Database API），API的全名是（Application Programming Interface）。函式庫提供函數使用參數方式傳入SQL指令字串，然後執行資料庫存取。 資料庫函式庫可以分為兩種，如下所示： 原生資料庫函式庫（Native Database API）：資料庫管理系統提供的資料庫函式庫。 中介層的資料庫函式庫（Middle Layer Database API）：中介軟體提供的資料庫函式庫。

46 13-5 嵌入式SQL P 「嵌入式SQL」（Embedded SQL）是ANSI-SQL 92的標準，可以使用特殊語法將SQL指令包含在通用用途程式語言的程式碼檔案，例如：VB、C/C++和Java語言等，這些語言稱為宿主語言。

47 嵌入式SQL的基礎-語法 P 嵌入式SQL是將SQL指令置於宿主語言的程式碼中，為了分辨那些部分是嵌入式SQL，嵌入式SQL的指令是以EXEC SQL開頭的指令敘述，其基本語法如下所示： EXEC SQL embedded_sql_statement;

48 13-5-1 嵌入式SQL的基礎-編譯過程 嵌入式SQL程式碼需要兩個步驟的編譯過程，如下所示：
使用前置處理程式轉換成一系列的函式呼叫（Function Call），以SQL Server為例，前置處理程式名為nsqlprep.exe，可以將嵌入式SQL轉換成C語言的Embedded SQL for C（簡稱ESQL/C）函數呼叫。 嵌入式SQL程式轉換成宿主語言程式碼後，就可以使用宿主語言的編譯程式連結ESQL/C函式庫檔案建立應用程式。

49 嵌入式SQL的基礎-編譯圖例

50 13-5-1 嵌入式SQL的基礎-優點 嵌入式SQL的優點，如下所示：
嵌入式SQL可以處理SQL指令和宿主語言變數間的資料交換，這些變數稱為「宿主變數」（Host Variables），也就是將SQL查詢結果的資料傳入宿主變數，反過來說，SQL查詢指令也可以直接使用宿主變數來建立查詢條件。 嵌入式SQL的SQL指令敘述在編譯階段就會進行語法檢查。 嵌入式SQL是ANSI-SQL 92的標準，所以很多資料庫管理系統都支援標準的嵌入式SQL。

51 嵌入式SQL的程式架構-架構1 P #include <stdio.h> #include <string.h> /* 宣告宿主變數 */ EXEC SQL BEGIN DECLARE SECTION; char stdID[4]; char stdname[10]; char stdtel[12]; char stdbirthday[12]; float stdGPA; EXEC SQL END DECLARE SECTION; …………….

52 13-5-2 嵌入式SQL的程式架構-架構2 int main() { …………… strcpy(stdID, “S010”);
strcpy(stdname, “陳蘭皋”); strcpy(stdtel, “ ”); strcpy(stdIbirthday, “1956/10/23”); stdGPA = 2.8; /* 執行新增操作, 新增學號S010 */ EXEC SQL INSERT INTO Students VALUES (:stdID, :stdname, :stdtel, :stdbirthday, :stdGPA); /* 執行更新操作, 更新學號S010 */

53 13-5-2 嵌入式SQL的程式架構-架構3 SET GPA = GPA + 0.5 WHERE sid = :stdID;
EXEC SQL UPDATE Students SET GPA = GPA WHERE sid = :stdID; /* 執行刪除操作, 刪除學號S010 */ EXEC SQL DELETE FROM Students WHERE sid = :stdID; strcpy(stdID, “S001”); /* 執行SQL查詢，查詢學號S001的學生姓名 */ EXEC SQL SELECT name INTO :stdname FROM Students /* 顯示學生姓名 */ printf("學生姓名: %s\n%", stdname); …………… return (0); }

54 13-5-2 嵌入式SQL的程式架構-說明1 宣告宿主變數
在嵌入式SQL指令和C程式語言的變數交換機制是宿主變數（Host Variables），這是使用DECLAR SECTION宣告的C語言變數，如下所示： EXEC SQL BEGIN DECLARE SECTION; char stdID[4]; char stdname[10]; char stdtel[12]; char stdbirthday[12]; float stdGPA; EXEC SQL END DECLARE SECTION;

55 13-5-2 嵌入式SQL的程式架構-說明2 在嵌入式SQL指令使用宿主變數
在嵌入式SQL指令使用宿主變數，就是使用C語言的變數值作為SQL指令條件，或用來建立SQL指令敘述，這是使用’:’開頭的宿主變數。 更新記錄的嵌入式SQL指令UPDATE，如下所示： EXEC SQL UPDATE Students SET GPA = GPA WHERE sid = :stdID;

56 13-5-2 嵌入式SQL的程式架構-說明3 使用宿主變數取得查詢結果
我們也可以使用使用宿主變數取得查詢結果，查詢記錄的嵌入式SQL指令SELECT，如下所示： EXEC SQL SELECT name INTO :stdname FROM Students WHERE sid = :stdID; SELECT INTO指令可以取得資料表的一筆記錄，條件是宿主變數:stdID，取得查詢結果的學生姓名name欄位值是存入INTO指令後的:stdname宿主變數。

57 13-5-3 空值的處理 在嵌入式SQL新增「指示變數」（Indicators）的旗標變數，以判斷宿主變數是否是空值，如下所示：
P 在嵌入式SQL新增「指示變數」（Indicators）的旗標變數，以判斷宿主變數是否是空值，如下所示： EXEC SQL BEGIN DECLARE SECTION; char stdID[4]; char stdname[10]; float stdGPA; int name_ind; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT name, GPA INTO :stdname:name_ind, :stdGPA FROM Students WHERE sid = :stdID; If ( name_ind == 0 ) /* 不是空值 */ { }

58 指標的使用-說明 P 嵌入式SQL提供「指標」（Cursor），可以取得記錄集合中的每一筆記錄，我們可以將它視為是一個資料列標籤（Row Marker），記錄在記錄集合中，目前存取的是那一筆記錄。

59 13-5-4 指標的使用-宣告語法 宣告指標（Cursor） 嵌入式SQL使用指標（Cursor）需要事先宣告，其宣告語法，如下所示：
EXEC SQL DECLARE cursor_name CURSOR FOR select_statement; 語法宣告建立名為cursor_name的指標，在FOR指令之後是取得記錄集合的SELECT查詢指令。

60 13-5-4 指標的使用-宣告範例 例如：在嵌入式SQL程式碼檔案宣告名為std_cursor的指標，如下所示：
EXEC SQL DECLARE std_cursor CURSOR FOR SELECT sid, name, birthday, GPA FROM Students WHERE GPA >= 3.0;

61 13-5-4 指標的使用-開啟指標 開啟指標 在宣告指標之後，我們需要使用OPEN指令開啟指標，如下所示：
EXEC SQL OPEN std_cursor;

62 13-5-4 指標的使用-FETCH語法 取得查詢結果 在開啟指標後，就可以使用FETCH指令從指標位置取得指定列的記錄，其語法如下所示：
EXEC SQL FETCH [ [ NEXT | PRIOR | FIRST | LAST ] FROM ] cursor_name INTO :host_var1 [, host_var2...] 語法是將目前cursor_name位置的記錄資料存入INTO指令後的宿主變數。

63 13-5-4 指標的使用-FETCH參數 在FETCH指令後可以指定指標的移動方式，如下所示：
NEXT：這是預設移動方式，如果是第一次執行FETCH指令，就是取得記錄集合中的第一筆記錄，否則就是移到記錄集合目位置的下一筆記錄。 PRIOR：取得上一筆記錄。 FIRST：將指標移到第一筆，取得第一筆記錄。 LAST：將指標移到最後一筆，取得最後一筆記錄。

64 指標的使用-FETCH範例 FETCH指令一次可以取得一筆記錄，如果需要取得記錄集合的每一筆記錄，就需要配合C語言的while迴圈指令，如下所示： while (SQLCODE == 0) { EXEC SQL FETCH std_cursor INTO :stdID, :stdname, :stdbirthday, :stdGPA if (SQLCODE == 0) printf("%4s %12s %s %f\n", stdID, stdname, stdbirthday, stdGPA); }

65 指標的使用- SQLCODE變數說明 SQLCODE變數是C語言SQLCA（SQL Communications Area）結構的欄位，SQL Server前置處理程式會自動在嵌入式SQL應用程式加入SQLCA結構，這是嵌入式SQL的錯誤處理機制。 我們也可以使用INCLUDE指令在程式檔案開頭加入此結構，如下所示： EXEC SQL INCLUDE SQLCA;

66 指標的使用- SQLCODE變數種類 嵌入式SQL程式碼可以檢查此結構的欄位，以了解嵌入式SQL指令的執行狀態，主要的欄位說明，如下所示： SQLCODE：負值的SQL Server錯誤碼，0表示執行成功。 SQLWARN：旗標變數，如果設定，表示有異常的例外情況發生。 SQLERRM：錯誤訊息的說明字串。 SQLERRD1：錯誤碼。 SQLERRD3：這是一個陣列，指出受影響的記錄編號。 SQLSTATE：ANSI-SQL 92標準執行階段錯誤碼。

67 指標的使用-關閉指標 關閉指標 當指標不再需要時，請使用CLOSE指令關閉指標，釋放查詢結果記錄集合所佔用的記憶體空間，如下所示： EXEC SQL CLOSE sid_cursor;

68 13-6 動態SQL-說明 動態SQL（Dynamic SQL）是對比「靜態SQL」（Static SQL），其說明如下所示：
P 動態SQL（Dynamic SQL）是對比「靜態SQL」（Static SQL），其說明如下所示： 靜態SQL：SQL指令是在編譯階段就決定，預先編譯程式可以執行資料型態的檢查，嵌入式SQL就是一種靜態SQL。 動態SQL：SQL指令是動態在執行階段才產生，這些指令是儲存成宿主語言的字串變數，在執行階段才送到資料庫管理系統執行。

69 13-6 動態SQL-嵌入式SQL的動態SQL指令
動態SQL的重點是在執行階後才建立SQL指令，在嵌入式SQL也提供2個動態SQL指令（Transact-SQL支援），如下所示： PREPARE指令：建立SQL查詢指令字串，通常是宿主語言的字串變數，字串可以內含’?’號的參數。 EXECUTE指令：執行PREPARE指令的SQL查詢指令。

70 13-6 動態SQL-嵌入式SQL的動態SQL指令範例1
首先使用建立儲存SQL指令字串的宿主變數sqlstring，其程式碼如下所示： EXEC SQL BEGIN DECLARE SECTION; char sqlstring[255]; char desiredID[20]; EXEC SQL END DECLARE SECTION; 接著指定sqlstring字串內容的SQL指令，條件sid欄位是一個參數，如下所示： sqlstring = “DELETE FROM Students WHERE sid= ?”;

71 13-6 動態SQL-嵌入式SQL的動態SQL指令範例2
然後使用PREPARE指令以宿主變數建立SQL查詢，如下所示： EXEC SQL PREPARE sqlDelete FROM :sqlstring; 最後就是以使用者輸入的學號變數desiredID，使用EXECUTE執行查詢，如下所示： EXEC SQL EXECUTE sqlDelete USING :desiredID;

72 13-6 動態SQL-使用資料庫函式庫執行動態SQL指令
我們可以在程式碼呼叫資料庫函式庫的函數將完整SQL指令字串，在執行時以參數方式送給資料庫管理系統執行。例如：ASP.NET技術使用ADO.NET元件的Connection物件，透過OLE DB中介軟體來執行動態SQL指令，如下所示： strSQL = "INSERT INTO Students (sid, name" & _ ",tel, GPA, birthday) " ……… strSQL &= "'" & birthday.Text & "')" objCon = New SqlConnection(strDbCon) objCon.Open() objCmd = New SqlCommand(strSQL, objCon) count = objCmd.ExecuteNonQuery()

73 13-7 Transact-SQL的預存程序 P Transact-SQL支援程式化功能，可以撰寫SQL程式檔案的批次指令、預存程序和觸發程序，其說明如下所示： 批次指令（Batch）：提供IF/ELSE、GOTO、WHILE、BREAK、CONTINUE等條件或迴圈指令。 預存程序（Stored Procedure）：將例行、常用和複雜的資料庫操作預先建立成SQL指令的程式檔，可以簡化相關的資料庫操作。 觸發程序（Trigger）：一種特殊用途的預存程序，不過是主動執行的程序，當資料表操作符合指定的條件時，就會自動執行觸發程序。

74 預存程序的基礎-說明 P 「預存程序」（Stored Procedure）是一個個程序，每一個程序可以執行所需的資料庫操作，它一樣可以使用Transact-SQL的流程控制指令，撰寫出複雜的資料庫操作功能。 換句話說，我們可以將目前的資料庫相關的查詢指令轉換成預存程序。

75 13-7-1 預存程序的基礎-範例1 例如：在Management Studio輸入SELECT查詢指令，如下所示：
SELECT column1, column2 FROM table SQL指令取出資料表table的2個欄位，將這個SQL指令轉換成預存程序，程序內容如下所示： CREATE PROCEDURE MyStoredProcedure AS GO

76 13-7-1 預存程序的基礎-範例2 預存程序也可以傳遞參數，value值是傳入的參數，如下所示：
CREATE PROCEDURE MyStoredProcedure @MyPara AS SELECT column1, column2 FROM table WHERE column1 GO 執行預存程序就需要加上傳入的參數MyPara，如下所示： MyStoredProcedure 10

77 13-7-2 建立預存程序- 在Management Studio建立預存程序
P 在SQL Sever可以使用Management Studio建立和測試預存程序。

78 13-7-2 建立預存程序- 在Query Express建立預存程序

79 13-7-3 測試預存程序-Management Studio(步驟)
P 現在我們已經成功建立2個預存程序，可以分別顯示Students資料表的記錄和使用WHERE子句的條件。 首先測試名為ls_ShowStudents的預存程序，請在Management Studio展開【School】資料庫的預存程序。 在ls_ShowStudents預存程序上，執行【右】鍵快顯功能表的【執行預存程序】指令後，可以看到「執行程序」對話方塊。按【確定】鈕，稍等一下，可以看到執行結果。

80 13-7-3 測試預存程序-Management Studio(結果)

81 13-7-3 測試預存程序-Query Express(指令)
接著使用Query Express執行名為ls_ShowStudents_p的預存程序，請開啟Ch sql的程式檔案，如下所示： USE School GO EXEC ls_ShowStudents_p 'S003'

82 13-7-3 測試預存程序-Query Express(結果)