ASP.NET 2.0網頁設計範例教本

Size: px

Start display at page:

Download "ASP.NET 2.0網頁設計範例教本"

蜡补鱼养
8 years ago
Views:

1 第 11 章視界與資料庫程式設計 11-1 視界的基礎 11-2 建立與刪除視界 11-3 編輯視界的內容 11-4 資料庫程式設計的基礎 11-5 嵌入式 SQL 11-6 動態 SQL 與 ORM 11-7 Transact-SQL 的預存程序

2 11-1 視界的基礎視界的內容視界的種類視界的優缺點

3 視界的內容 - 說明視界 (Views) 相當於 ANSI/SPARC 三層資料系統架構中外部層 (External Level) 顯示的資料, 這是從基底關聯表 (Base Relation) 導出的虛擬關聯表 (Virtual Relation), 如下圖所示 :

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

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

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

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

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

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

10 11-2 建立與刪除視界建立列欄子集視界建立合併視界建立統計摘要視界從其他視界建立視界

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

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

11-2-1 建立列欄子集視界 - 說明列欄子集視界是指視界的內容是基底關聯表屬性集的子集合, 也就是以資料表的欄位和記錄為單位,

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

11-2-1 建立列欄子集視界 - 建立欄子集視界 ( 範例 ) 欄子集視界 (Column Subset Views) 是指這個視界的屬性是基底關聯表屬性集合的子集合 SQL 查詢範例 :Ch11_2_1_01.

14 建立列欄子集視界 - 建立欄子集視界 ( 範例 ) 欄子集視界 (Column Subset Views) 是指這個視界的屬性是基底關聯表屬性集合的子集合 SQL 查詢範例 :Ch11_2_1_01.sql 從 Courses 基底關聯表建立課程名稱資料的 Title_View 視界, 如下所示 : CREATE VIEW Title_View AS SELECT c_no, title FROM Courses

11-2-1 建立列欄子集視界 - 建立欄子集視界 ( 結果 ) SQL 查詢範例 :Ch11_2_1_02.

15 建立列欄子集視界 - 建立欄子集視界 ( 結果 ) SQL 查詢範例 :Ch11_2_1_02.sql 使用 SELECT 指令查詢 Title_View 視界的內容, 如下所示 : SELECT * FROM Title_View

16 建立列欄子集視界 - 建立列子集視界 ( 範例 ) 列子集視界 (Row Subset Views) 是指這個視界的值組是基底關聯表值組集合的子集合 SQL 查詢範例 :Ch11_2_1_04.sql 從 Courses 基底關聯表建立課程學分 credits 欄位大於等於 3 的 Credits_View 視界, 如下所示 : CREATE VIEW Credits_View AS SELECT * FROM Courses WHERE credits >= 3

11-2-1 建立列欄子集視界 - 建立列子集視界 ( 結果 ) SQL 查詢範例 :Ch11_2_1_05.

17 建立列欄子集視界 - 建立列子集視界 ( 結果 ) SQL 查詢範例 :Ch11_2_1_05.sql 查詢 Credits_View 視界的內容, 如下所示 : SELECT * FROM Credits_View

18 建立列欄子集視界 - 建立列欄子集視界 ( 範例 ) 列欄子集視界 (Row-and-Column Subset Views) 是指視界的屬性和值組都是基底關聯表屬性和值組集合的子集合 SQL 查詢範例 :Ch11_2_1_06.sql 從 Courses 基底關聯表建立學分 credits 欄位大於等於 3, 而且只有 c_no 和 title 二個欄位的 Major_View 視界, 如下所示 : CREATE VIEW Major_View AS SELECT c_no, title FROM Courses WHERE credits >= 3

11-2-1 建立列欄子集視界 - 建立列欄子集視界 ( 結果 ) SQL 查詢範例 :Ch11_2_1_07.

19 建立列欄子集視界 - 建立列欄子集視界 ( 結果 ) SQL 查詢範例 :Ch11_2_1_07.sql 查詢 Major_View 視界的內容, 如下所示 : SELECT * FROM Major_View

20 建立合併視界 - 說明合併視界 (Join Views) 是多個關聯表執行合併查詢所建立的視界

21 建立合併視界 - 範例 SQL 查詢範例 :Ch11_2_2_01.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

11-2-2 建立合併視界 - 結果 SQL 查詢範例 :Ch11_2_2_02.

22 建立合併視界 - 結果 SQL 查詢範例 :Ch11_2_2_02.sql 查詢 Std_Class_View 視界的內容, 如下所示 : SELECT * FROM Std_Class_View

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

24 建立統計摘要視界 - 範例 1 SQL 查詢範例 :Ch11_2_3_01.sql 建立 Students Courses 和 Classes 三個資料表的統計摘要視界 Credits_View, 這是一個合併視界, 而且使用 COUNT() 和 SUM() 聚合函數 (Aggregate Function) 顯示每位學生的選課數和所修的總學分, 如下所示 : CREATE VIEW Total_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

11-2-3 建立統計摘要視界 - 結果 1 SQL 查詢範例 :Ch11_2_3_02.

25 建立統計摘要視界 - 結果 1 SQL 查詢範例 :Ch11_2_3_02.sql 查詢 Total_Credits_View 視界的內容, 如下所示 : SELECT * FROM Total_Credits_View

26 建立統計摘要視界 - 範例 2 SQL 查詢範例 :Ch11_2_3_03.sql 請修改統計摘要視界 Total_Credits_View, 建立只顯示學生所修總學分超過 6 個學分的學生選課總數, 和學分數的合併視界 Top_Creadits_View, 如下所示 : 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

27 建立統計摘要視界 - 結果 2 SQL 查詢範例 :Ch11_2_3_04.sql 查詢 Top_Credits_View 視界的內容, 如下所示 : SELECT * FROM Top_Credits_View

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

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

30 從其他視界建立視界 - 圖例

11-2-4 從其他視界建立視界 - 結果 SQL 查詢範例 :Ch11_2_4_02.

31 從其他視界建立視界 - 結果 SQL 查詢範例 :Ch11_2_4_02.sql 查詢 Std_Credits_View 視界的內容, 如下所示 : SELECT * FROM Std_Credits_View

32 11-3 編輯視界的內容視界編輯的基礎從視界新增資料表的記錄從視界更新資料表的記錄從視界刪除資料表的記錄

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

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

35 視界編輯的基礎 - WITH CHECK OPTION( 範例 1) SQL 查詢範例 :Ch11_3_1_01.sql 使用 Students 資料表建立學生生日資料的 Birthday_View 視界, 而且使用 WITH CHECK OPTION 選項指令, 如下所示 : CREATE VIEW Birthday_View AS SELECT sid, name, birthday FROM Students WITH CHECK OPTION 上述 SQL 指令建立名為 Birthday_View 的視界, 此視界滿足前述限制條件

36 視界編輯的基礎 - WITH CHECK OPTION( 範例 2) SQL 查詢範例 :Ch11_3_1_02.sql 建立學生生日資料的 NP_Birthday_View 視界, 此視界不含資料表主鍵 sid, 同時新增一筆測試記錄 S007, 如下所示 : CREATE VIEW Birthday_View AS SELECT sid, name, birthday FROM Students WITH CHECK OPTION GO INSERT INTO Students VALUES ('S007',' 江峰 ',' ','1965/05/23') 上述 SQL 指令建立的 NP_Birthday_View 視界因為沒有主鍵, 所以不滿足前述限制條件

37 從視界新增資料表的記錄 - Birthday_View 視界 SQL 查詢範例 :Ch11_3_2_01.sql 在 Birthday_View 視界新增一筆學生記錄, 如下所示 : INSERT INTO Birthday_View VALUES ( S006, 江峰, )

11-3-2 從視界新增資料表的記錄 - No_Birthday_View 視界 SQL 查詢範例 :Ch11_3_2_03.

38 從視界新增資料表的記錄 - No_Birthday_View 視界 SQL 查詢範例 :Ch11_3_2_03.sql 在 No_Birthday_View 視界新增一筆學生記錄, 如下所示 : INSERT INTO NP_Birthday_View VALUES ( 江峰年, )

11-3-3 從視界更新資料表的記錄 - Birthday_View 視界 SQL 查詢範例 :Ch11_3_3_01.

39 從視界更新資料表的記錄 - Birthday_View 視界 SQL 查詢範例 :Ch11_3_3_01.sql 在 Birthday_View 視界將學號 S006 學生的 birthday 改為 , 如下所示 : UPDATE Birthday_View SET birthday = ' ' WHERE sid = 'S006'

40 從視界更新資料表的記錄 - No_Birthday_View 視界 SQL 查詢範例 :Ch11_3_3_03.sql 在 NP_Birthday_View 視界將學生姓名 ' 江峰 ' 的 birthday 改為 , 如下所示 : UPDATE NP_Birthday_View SET birthday = ' ' WHERE name = ' 江峰 ' 當執行上述 SQL 指令,SQL Server 仍然會更新 Students 資料表的記錄 S006 和 S007 理論上, 資料庫管理系統應該避免在沒有主鍵的視界執行更新操作

11-3-4 從視界刪除資料表的記錄 - Birthday_View 視界 SQL 查詢範例 :Ch11_3_4_01.

41 從視界刪除資料表的記錄 - Birthday_View 視界 SQL 查詢範例 :Ch11_3_4_01.sql 在 Birthday_View 視界刪除學號 S006 的學生資料, 如下所示 : DELETE FROM Birthday_View WHERE sid = S006

42 從視界刪除資料表的記錄 - No_Birthday_View 視界 SQL 查詢範例 :Ch11_3_4_03.sql 在 NP_Birthday_View 視界刪除學生江峰, 如下所示 : DELETE NP_Birthday_View WHERE name = 江峰當執行上述 SQL 指令,SQL Server 仍然會刪除 Students 資料表的記錄 S007 理論上, 資料庫管理系統應該避免在沒有主鍵的視界執行刪除操作

43 11-4 資料庫程式設計的基礎資料庫程式設計的程式語言資料庫程式設計的實作

44 11-4 資料庫程式設計的基礎資料庫程式設計 (Database Programming) 的目的是建立資料庫系統的應用程式, 以主從架構的資料庫系統來說, 就是指客戶端應用程式, 因為應用程式需要存取資料庫的資料, 所以,SQL 結構化查詢語言扮演十分重要的角色

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

46 資料庫程式設計的語言 - 分工 SQL 語言 : 負責資料庫查詢和操作的資料存取, 分為兩種 : 嵌入式 SQL (Embedded SQL) 和動態 SQL (Dynamic SQL) 通用用途的程式語言 : 負責處理其他操作的商業邏輯和使用介面, 稱為宿主語言 (Host Languages)

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

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

49 11-5 嵌入式 SQL 嵌入式 SQL 的基礎嵌入式 SQL 的程式架構空值的處理指標的使用

50 11-5 嵌入式 SQL 嵌入式 SQL (Embedded SQL) 是 ANSI-SQL 92 的標準, 可以使用特殊語法將 SQL 指令包含在通用用途程式語言的程式碼檔案, 例如 :VB C/C++ 和 Java 語言等, 這些語言稱為宿主語言

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

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

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

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

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

56 嵌入式 SQL 的程式架構 - 架構 2 int main() { strcpy(stdid, S010 ); strcpy(stdname, 陳蘭皋 ); strcpy(stdtel, ); strcpy(stdibirthday, 1956/10/23 ); /* 執行新增操作, 新增學號 S010 */ EXEC SQL INSERT INTO Students VALUES (:stdid, :stdname, :stdtel, :stdbirthday); /* 執行更新操作, 更新學號 S010 */ EXEC SQL UPDATE Students SET birthday = ' ' WHERE sid = :stdid;

57 嵌入式 SQL 的程式架構 - 架構 3 /* 執行刪除操作, 刪除學號 S010 */ EXEC SQL DELETE FROM Students WHERE sid = :stdid; strcpy(stdid, S001 ); /* 執行 SQL 查詢, 查詢學號 S001 的學生姓名 */ EXEC SQL SELECT name INTO :stdname FROM Students WHERE sid = :stdid; /* 顯示學生姓名 */ printf(" 學生姓名 : %s\n%", stdname); return (0); }

58 嵌入式 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]; EXEC SQL END DECLARE SECTION;

59 嵌入式 SQL 的程式架構 - 說明 2 在嵌入式 SQL 指令使用宿主變數在嵌入式 SQL 指令使用宿主變數, 就是使用 C 語言的變數值作為 SQL 指令條件, 或用來建立 SQL 指令敘述, 這是使用 : 開頭的宿主變數更新記錄的嵌入式 SQL 指令 UPDATE, 如下所示 : EXEC SQL UPDATE Students SET birthday = ' ' WHERE sid = :stdid; 上述 UPDATE 指令可以更新資料表的記錄,WHERE 子句的條件是使用宿主變數 :stdid

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

61 空值的處理在嵌入式 SQL 新增指示變數 (Indicators) 的旗標變數, 以判斷宿主變數是否是空值, 如下所示 : EXEC SQL BEGIN DECLARE SECTION; char stdid[4]; char stdname[10]; char stdtel[12]; int name_ind; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT name, tel INTO :stdname:name_ind, :stdtel FROM Students WHERE sid = :stdid; /* 不是空值 */ If ( name_ind == 0 ) { }

62 指標的使用 - 說明嵌入式 SQL 提供指標 (Cursor), 可以取得記錄集合中的每一筆記錄, 我們可以將它視為是一個資料列標籤 (Row Marker), 記錄在記錄集合中, 目前存取的是那一筆記錄

63 指標的使用 - 宣告語法宣告指標 (Cursor) 嵌入式 SQL 使用指標 (Cursor) 需要事先宣告, 其宣告語法, 如下所示 : EXEC SQL DECLARE cursor_name CURSOR FOR select_statement; 語法宣告建立名為 cursor_name 的指標, 在 FOR 指令之後是取得記錄集合的 SELECT 查詢指令

64 指標的使用 - 宣告範例例如 : 在嵌入式 SQL 程式碼檔案宣告名為 std_cursor 的指標, 如下所示 : EXEC SQL DECLARE std_cursor CURSOR FOR SELECT sid, name, birthday, tel FROM Students WHERE birthday<=' ';

65 指標的使用 - 開啟指標開啟指標在宣告指標之後, 我們需要使用 OPEN 指令開啟指標, 如下所示 : EXEC SQL OPEN std_cursor;

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

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

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

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

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

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

72 11-6 動態 SQL 與 ORM 嵌入式 SQL 的動態 SQL 指令使用資料庫函數庫執行動態 SQL 指令 Object-relational Mapping(ORM)

73 11-6 動態 SQL 與 ORM- 說明動態 SQL(Dynamic SQL) 是對比靜態 SQL (Static SQL), 其說明如下所示 : 靜態 SQL:SQL 指令是在編譯階段就決定, 預先編譯程式可以執行資料型態的檢查, 嵌入式 SQL 就是一種靜態 SQL 動態 SQL:SQL 指令是動態在執行階段才產生, 這些指令是儲存成宿主語言的字串變數, 在執行階段才送到資料庫管理系統執行

74 嵌入式 SQL 的動態 SQL 指令 - 說明動態 SQL 的重點是在執行階後才建立 SQL 指令, 在嵌入式 SQL 也提供 2 個動態 SQL 指令 (Transact-SQL 支援 ), 如下所示 : PREPARE 指令 : 建立 SQL 查詢指令字串, 通常是宿主語言的字串變數, 字串可以內含? 號的參數 EXECUTE 指令 : 執行 PREPARE 指令的 SQL 查詢指令

75 嵌入式 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=? ;

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

77 使用資料庫函數庫執行動態 SQL 指令我們可以在程式碼呼叫資料庫函數庫的函數將完整 SQL 指令字串, 在執行時以參數方式送給資料庫管理系統執行例如 :ASP.NET 技術使用 ADO.NET 元件的 Connection 物件, 透過 OLE DB 中介軟體來執行動態 SQL 指令, 如下所示 : strsql = "INSERT INTO Students (sid, name" & _ ",tel, birthday) " strsql &= "'" & birthday.text & "')" objcon = New SqlConnection(strDbCon) objcon.open() objcmd = New SqlCommand(strSQL, objcon) count = objcmd.executenonquery()

78 Object-relational Mapping(ORM)- 說明 ORM(Object-relational Mapping) 也稱為 ORM 或 O/R mapping, 這是一種應用程式開發技術的中介軟體, 可以轉換不同型態資料成為物件導向程式語言 (Object-oriented Language) 的物件 (Object ), 讓程式設計者專注於物件的存取和操作, 而不用考量物件背後連接的資料

79 Object-relational Mapping(ORM)- ORM 與關聯式資料庫以關聯式資料庫來說,ORM 是將資料庫眾多資料表一一轉換成物件導向程式語言的物件, 也就是建立資料庫各資料表與程式語言物件之間的對應 (Mapping), 可以自動提供資料表與物件之間的資料轉換, 例如 :Java C++ PHP Visual Basic 和 C# 等程式語言, 如右圖所示 :

80 Object-relational Mapping(ORM)- 為什麼使用 ORM 當我們使用物件導向程式語言建立資料庫應用程式來存取資料庫時, 馬上面對的問題是程式語言的物件模型 (Object Model) 和資料庫的關聯式資料庫模型並不相容 public class Employee { } private int id; private String name; private int salary; public Employee() {} public Employee(String name, int salary) { this.name = name; this.salary = salary; } public int getid() { return id; } public String getname() { return name; } public int getsalary() { return salary; } create table Employee ( id INT NOT NULL auto_increment, name VARCHAR(20) default NULL, salary INT default NULL, PRIMARY KEY (id) );

81 Object-relational Mapping(ORM)- ORM 的優點增加生產力 : 因為資料存取程式碼通常都是應用程式很重要和主要部分, 使用 ORM 可以自動產生對應資料模型的存取程式碼, 大幅減少應用程式的開發時程建立更佳的應用程式設計 :ORM 可以分割資料存取和商業邏輯的程式碼, 幫助我們建立更佳的應用程式設計建立可重複使用的程式碼 :ORM 自動產生的資料存取程式碼可以重複使用在其他應用程式開發幫助我們維護應用程式 : 因為 ORM 自動產生的程式碼都是經過良好測試的程式碼, 並不用特別維護, 而且當更改資料來源時, 我們只需重新使用 ORM 產生資料存取程式碼, 即可以快速建立適用在不同資料的應用程式, 而不用動到商業邏輯的程式碼

82 Object-relational Mapping(ORM)- ORM 的缺點 ORM 的主要缺點是在效能 (Performance), 因為資料需要對應和轉換, 一般來說,ORM 自動建立的物件程式碼通常比直接撰寫程式碼存取資料庫來的複雜, 而複雜的程式碼會降低程式的執行效能

83 11-7 Transact-SQL 的預存程序預存程序的基礎建立預存程序執行預存程序

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

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

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

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

11-7-2 建立預存程序 - 在 Management Studio 建立預存程序

88 建立預存程序 - 在 Management Studio 建立預存程序在 SQL Sever 可以使用 Management Studio 建立和測試預存程序

89 建立預存程序 - 新增查詢建立預存程序因為 Management Studio 指令建立預存程序是編輯和執行 SQL 指令碼檔案, 我們可以直接按新增查詢鈕新增查詢編輯視窗後, 自行輸入建立預存程序的 SQL 指令敘述 SQL 指令碼檔 :Is_ShowStudents_p.sql 建立顯示學生個人資料的 ls_showstudents_p 預存程序, 擁有參數的學號來顯示學生資料, 如下所示 : CREATE PROCEDURE VARCHAR(4) AS BEGIN SELECT sid, name, birthday, tel FROM Students WHERE sid END

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

91 測試預存程序 - Management Studio( 結果 )

11-7-3 測試預存程序 - 輸入參數如果是執行 ls_showstudents_p

92 測試預存程序 - 輸入參數如果是執行 ls_showstudents_p 預存程序, 因為擁有參數, 在執行程序對話方塊的值欄可以輸入參數值, 如下圖所示 :

投影片 1

投影片 1 資料庫管理程式 ( 補充教材 -Part2) 使用 ADO.NET 連結資料庫 ( 自行撰寫程式碼以實現新增刪除修改等功能 ) Private Sub InsertButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InsertButton.Click ' 宣告相關的 Connection