數(shù)據(jù)庫精品課(僅供個人學習)第08章

1、Theory of DatabaseTheory of Database 數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)庫系統(tǒng)概論An Introduction to Database System 第八章第八章 數(shù)據(jù)庫編程數(shù)據(jù)庫編程Theory of Database第八章第八章 數(shù)據(jù)庫編程數(shù)據(jù)庫編程8.1 嵌入式嵌入式SQL8.2 存儲過程存儲過程8.3 ODBC編程編程Theory of Database8.1 嵌入式嵌入式SQLv SQL語言提供了兩種不同的使用方式： 交互式 嵌入式v 為什么要引入嵌入式SQL SQL語言是非過程性語言 事務處理應用需要高級語言v 這兩種方式細節(jié)上有差別，在程序設計的環(huán)境下，SQ

2、L語句要做某些必要的擴充Theory of Database8.1 嵌入式嵌入式SQL8.1.1 嵌入式嵌入式SQL的處理過程的處理過程8.1.2 嵌入式嵌入式SQL語句與主語言之間的通信語句與主語言之間的通信8.1.3 不使用游標的不使用游標的SQL語句語句8.1.4 使用游標的使用游標的SQL語句語句8.1.5 動態(tài)動態(tài)SQL8.1.6 小結小結Theory of Database8.1.1 嵌入式嵌入式SQL的處理過程的處理過程v主語言 嵌入式SQL是將SQL語句嵌入程序設計語言中，被嵌入的程序設計語言，如C、C+、Java，稱為宿主語言，簡稱主語言。v處理過程 預編譯方法Theory

3、of Database嵌入式嵌入式SQL的處理過程（續(xù)）的處理過程（續(xù)） 主語言程序 含ESQL語句RDBMS的預處理程序ESQL語句轉換為函數(shù)調用主語言編譯程序目標語言程序ESQL基本處理過程Theory of Database嵌入式嵌入式SQL的處理過程（續(xù)）的處理過程（續(xù)） v為了區(qū)分SQL語句與主語言語句， 所有SQL語句必須加前綴EXEC SQL，以(;)結束: EXEC SQL ;Theory of Database8.1 嵌入式嵌入式SQL8.1.1 嵌入式嵌入式SQL的處理過程的處理過程8.1.2 嵌入式嵌入式SQL與主語言的通信與主語言的通信8.1.3 不使用游標的不使用游標

4、的SQL語句語句8.1.4 使用游標的使用游標的SQL語句語句8.1.5 動態(tài)動態(tài)SQL8.1.6 小結小結Theory of Database8.1.2 嵌入式嵌入式SQL語句與主語言之間的通信語句與主語言之間的通信v 將SQL嵌入到高級語言中混合編程，程序中會含有兩種不同計算模型的語句nSQL語句 描述性的面向集合的語句 負責操縱數(shù)據(jù)庫n高級語言語句 過程性的面向記錄的語句 負責控制程序流程n它們之間應該如何通信？Theory of Database嵌入式嵌入式SQL語句與主語言之間的通信語句與主語言之間的通信（續(xù)）（續(xù)） v 數(shù)據(jù)庫工作單元與源程序工作單元之間的通信：n1. SQL通信區(qū)

5、向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息使主語言能夠據(jù)此控制程序流程n2. 主變量主語言向SQL語句提供參數(shù)將SQL語句查詢數(shù)據(jù)庫的結果交主語言進一步處理n3. 游標解決集合性操作語言與過程性操作語言的不匹配Theory of Database一、一、SQL通信區(qū)通信區(qū)v SQLCA： SQL Communication AreanSQLCA是一個數(shù)據(jù)結構v SQLCA的用途nSQL語句執(zhí)行后，RDBMS反饋給應用程序信息 描述系統(tǒng)當前工作狀態(tài) 描述運行環(huán)境n這些信息將送到SQL通信區(qū)SQLCA中n應用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來執(zhí)行的語句Theory of Database

6、SQL通信區(qū)通信區(qū)v SQLCA使用方法： 定義SQLCA 用EXEC SQL INCLUDE SQLCA定義 使用SQLCASQLCA中有一個存放每次執(zhí)行SQL語句后返回代碼的變量SQLCODE如果SQLCODE等于預定義的常量SUCCESS，則表示SQL語句成功，否則表示出錯應用程序每執(zhí)行完一條SQL 語句之后都應該測試一下SQLCODE的值，以了解該SQL語句執(zhí)行情況并做相應處理Theory of Database二、主變量二、主變量 v主變量 嵌入式SQL語句中可以使用主語言的程序變量來輸入或輸出數(shù)據(jù) 在SQL語句中使用的主語言程序變量簡稱為主變量（Host Variable）Theo

7、ry of Database主變量（續(xù)）主變量（續(xù)） v主變量的類型 輸入主變量 輸出主變量 一個主變量有可能既是輸入主變量又是輸出主變量Theory of Database主變量（續(xù)）主變量（續(xù)） v指示變量： 一個主變量可以附帶一個指示變量（Indicator Variable） 什么是指示變量 指示變量的用途Theory of Database主變量（續(xù)）主變量（續(xù)） v在SQL語句中使用主變量和指示變量的方法 1) 說明主變量和指示變量BEGIN DECLARE SECTION. . (說明主變量和指示變量).END DECLARE SECTIONTheory of Database主

8、變量（續(xù)）主變量（續(xù)） 2) 使用主變量說明之后的主變量可以在SQL語句中任何一個能夠使用表達式的地方出現(xiàn)為了與數(shù)據(jù)庫對象名（表名、視圖名、列名等）區(qū)別，SQL語句中的主變量名前要加冒號（:）作為標志 3) 使用指示變量 指示變量前也必須加冒號標志 必須緊跟在所指主變量之后Theory of Database主變量（續(xù)）主變量（續(xù)） v在SQL語句之外(主語言語句中)使用主變量和指示變量的方法 可以直接引用，不必加冒號Theory of Database三、游標（三、游標（cursor）v 為什么要使用游標 SQL語言與主語言具有不同數(shù)據(jù)處理方式 SQL語言是面向集合的，一條SQL語句原則上可

9、以產生或處理多條記錄 主語言是面向記錄的，一組主變量一次只能存放一條記錄 僅使用主變量并不能完全滿足SQL語句向應用程序輸出數(shù)據(jù)的要求 嵌入式SQL引入了游標的概念，用來協(xié)調這兩種不同的處理方式Theory of Database 游標（續(xù)游標（續(xù)）v游標 游標是系統(tǒng)為用戶開設的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結果 每個游標區(qū)都有一個名字 用戶可以用SQL語句逐一從游標中獲取記錄，并賦給主變量，交由主語言進一步處理Theory of Database四、建立和關閉數(shù)據(jù)庫連接四、建立和關閉數(shù)據(jù)庫連接v 建立數(shù)據(jù)庫連接 EXEC SQL CONNECT TO target AS connect

10、ion-name USER user-name; target是要連接的數(shù)據(jù)庫服務器：l 常見的服務器標識串，如: l 包含服務器標識的SQL串常量 l DEFAULT connect-name是可選的連接名，連接必須是一個有效的標識符 在整個程序內只有一個連接時可以不指定連接名v 關閉數(shù)據(jù)庫連接 EXEC SQL DISCONNECT connection;v 程序運行過程中可以修改當前連接 ：EXEC SQL SET CONNECTION connection-name | DEFAULT;Theory of Database五、程序實例五、程序實例例1依次檢查某個系的學生記錄，交互式更新

11、某些學生年齡。EXEC SQL BEGIN DEC LARE SECTION; /*主變量說明開始*/ char deptname64;char HSno64;char HSname64; char HSsex64;int HSage;intNEWAGE;EXEC SQL END DECLARE SECTION; /*主變量說明結束*/long SQLCODE;EXEC SQL INCLUDE sqlca; /*定義SQL通信區(qū)*/Theory of Database程序實例（續(xù)）程序實例（續(xù)）int main(void) /*C語言主程序開始*/int count = 0;char yn;

12、/*變量yn代表yes或no*/printf(Please choose the department name(CS/MA/IS): ); scanf(%s, deptname); /*為主變量deptname賦值*/EXEC SQL CONNECT TO TESTlocalhost:54321 USER SYSTEM /MANAGER; /*連接數(shù)據(jù)庫TEST*/EXEC SQL DECLARE SX CURSOR FOR /*定義游標*/SELECT Sno, Sname, Ssex, Sage /*SX對應語句的執(zhí)行結果*/FROM StudentWHERE SDept = :dept

13、name;EXEC SQL OPEN SX; /*打開游標SX便指向查詢結果的第一行*/Theory of Database程序實例（續(xù)）程序實例（續(xù)）for ( ; ; ) /*用循環(huán)結構逐條處理結果集中的記錄*/ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex,:HSage; /*推進游標，將當前數(shù)據(jù)放入主變量*/if (sqlca.sqlcode != 0) /* sqlcode != 0,表示操作不成功*/break; /*利用SQLCA中的狀態(tài)信息決定何時退出循環(huán)*/if(count+ = 0) /*如果是第一行的話，先打出行頭*/ pri

14、ntf(n%-10s %-20s %-10s %-10sn, Sno, Sname, Ssex, Sage); printf(%-10s %-20s %-10s %-10dn, HSno, HSname, HSsex, HSage); /*打印查詢結果*/ printf(UPDATE AGE(y/n)?); /*詢問用戶是否要更新該學生的年齡*/ do scanf(%c,&yn); while(yn != N & yn != n & yn != Y & yn != y);Theory of Database程序實例（續(xù)）程序實例（續(xù)）if (yn = y | y

15、n = Y) /*如果選擇更新操作*/ printf(INPUT NEW AGE:); scanf(%d,&NEWAGE); /*用戶輸入新年齡到主變量中*/ EXEC SQL UPDATE Student /*嵌入式SQL*/SET Sage = :NEWAGEWHERE CURRENT OF SX ; /*對當前游標指向的學生年齡進行更新*/ EXEC SQL CLOSE SX; /*關閉游標SX不再和查詢結果對應*/EXEC SQL COMMIT WORK; /*提交更新*/EXEC SQL DISCONNECT TEST; /*斷開數(shù)據(jù)庫連接*/Theory of Databa

16、se8.1 嵌入式嵌入式SQL8.1.1 嵌入式嵌入式SQL的處理過程的處理過程8.1.2 嵌入式嵌入式SQL語句與主語言之間的通信語句與主語言之間的通信8.1.3 不使用游標的不使用游標的SQL語句語句8.1.4 使用游標的使用游標的SQL語句語句8.1.5 動態(tài)動態(tài)SQL8.1.6 小結小結Theory of Database8.1.3 不用游標的不用游標的SQL語句語句v不用游標的SQL語句的種類 說明性語句 數(shù)據(jù)定義語句 數(shù)據(jù)控制語句 查詢結果為單記錄的SELECT語句 非CURRENT形式的增刪改語句 Theory of Database不用游標的不用游標的SQL語句（續(xù)）語句（續(xù)）

17、v一、查詢結果為單記錄的SELECT語句 v二、非CURRENT形式的增刪改語句Theory of Database一、查詢結果為單記錄的一、查詢結果為單記錄的SELECT語句語句v這類語句不需要使用游標，只需要用INTO子句指定存放查詢結果的主變量 例2 根據(jù)學生號碼查詢學生信息。假設已經把要查詢的學生的學號賦給了主變量givensno。EXEC SQL SELECT Sno，Sname，Ssex，Sage，Sdept INTO :Hsno， : Hname ，:Hsex，:Hage，:Hdept FROM Student WHERE Sno=:givensno；Theory of Data

18、base查詢結果為單記錄的查詢結果為單記錄的SELECT語句（續(xù)）語句（續(xù)）(1) INTO子句、WHERE子句和HAVING短語的條件表達式中均可以使用主變量(2)查詢返回的記錄中，可能某些列為空值NULL。(3) 如果查詢結果實際上并不是單條記錄，而是多條記錄，則程序出錯，RDBMS會在SQLCA中返回錯誤信息 Theory of Database查詢結果為單記錄的查詢結果為單記錄的SELECT語句（續(xù)）語句（續(xù)）例3 查詢某個學生選修某門課程的成績。假設已經把將要查詢的學生的學號賦給了主變量givensno，將課程號賦給了主變量givencno。 EXEC SQL SELECT Sno，

19、Cno，Grade INTO :Hsno，:Hcno，:Hgrade:Gradeid /*指示變量Gradeid*/ FROM SC WHERE Sno=:givensno AND Cno=:givencno；如果如果Gradeid 0，不論，不論Hgrade為何值，均認為該學生成績?yōu)榭罩怠楹沃?，均認為該學生成績?yōu)榭罩?。Theory of Database二、非二、非CURRENT形式的增刪改語句形式的增刪改語句v 在UPDATE的SET子句和WHERE子句中可以使用主變量，SET子句還可以使用指示變量 例4 修改某個學生選修1號課程的成績。EXEC SQL UPDATE SC SET Gr

20、ade=:newgrade /*修改的成績已賦給主變量*/ WHERE Sno=:givensno； /*學號賦給主變量givensno*/ Theory of Database非非CURRENT形式的增刪改語句（續(xù)）形式的增刪改語句（續(xù)）例5 將計算機系全體學生年齡置NULL值。 Sageid=-1； EXEC SQL UPDATE Student SET Sage=:Raise :Sageid WHERE Sdept= CS； 將指示變量Sageid賦一個負值后，無論主變量Raise為何值，RDBMS都會將CS系所有學生的年齡置空值 。等價于： EXEC SQL UPDATE Studen

21、t SET Sage=NULL WHERE Sdept= CS；Theory of Database非非CURRENT形式的增刪改語句（續(xù)）形式的增刪改語句（續(xù)）例6 某個學生退學了，現(xiàn)要將有關他的所有選課記錄刪除掉。假設該學生的姓名已賦給主變量stdname。 EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname)； Theory of Database非非CURRENT形式的增刪改語句（續(xù)）形式的增刪改語句（續(xù)）例7 某個學生新選修了某門課程，將有關記錄插入SC表中。假設插入的學號已

22、賦給主變量stdno，課程號已賦給主變量couno。 gradeid=-1； /*用作指示變量，賦為負值*/EXEC SQL INSERT INTO SC(Sno，Cno，Grade) VALUES(:stdno，:couno，:gr :gradeid)； 由于該學生剛選修課程，成績應為空，所以要把指示變量賦為負值由于該學生剛選修課程，成績應為空，所以要把指示變量賦為負值 Theory of Database8.1 嵌入式嵌入式SQLv8.1.1 嵌入式嵌入式SQL的處理過程的處理過程v8.1.2 嵌入式嵌入式SQL語句與主語言之間的通信語句與主語言之間的通信v8.1.3 不使用游標的不使用游

23、標的SQL語句語句v8.1.4 使用游標的使用游標的SQL語句語句v8.1.5 動態(tài)動態(tài)SQLv8.1.6 小結小結Theory of Database8.1.4 使用游標的使用游標的SQL語句語句v必須使用游標的SQL語句查詢結果為多條記錄的SELECT語句CURRENT形式的UPDATE語句CURRENT形式的DELETE語句Theory of Database使用游標的使用游標的SQL語句（續(xù)）語句（續(xù)）v一、 查詢結果為多條記錄的SELECT語句v二、CURRENT形式的UPDATE和DELETE語句Theory of Database一、一、 查詢結果為多條記錄的查詢結果為多條記錄的

24、SELECT語句語句v使用游標的步驟1. 說明游標2. 打開游標3.推進游標指針并取當前記錄 4. 關閉游標Theory of Database1. 說明游標說明游標v使用DECLARE語句v語句格式EXEC SQL DECLARE CURSOR FOR ;v功能 是一條說明性語句，這時DBMS并不執(zhí)行SELECT指定的查詢操作。Theory of Database2. 打開游標打開游標v 使用OPEN語句v 語句格式 EXEC SQL OPEN ;v 功能 打開游標實際上是執(zhí)行相應的SELECT語句，把所有滿足查詢條件的記錄從指定表取到緩沖區(qū)中 這時游標處于活動狀態(tài)，指針指向查詢結果集中第一

25、條記錄Theory of Database3.推進游標指針并取當前記錄推進游標指針并取當前記錄 v使用FETCH語句v語句格式 EXEC SQL FETCH NEXT|PRIOR| FIRST|LAST FROM INTO ,.;Theory of Database推進游標指針并取當前記錄（續(xù)）推進游標指針并取當前記錄（續(xù)）v 功能 指定方向推動游標指針，然后將緩沖區(qū)中的當前記錄取出來送至主變量供主語言進一步處理 NEXT|PRIOR|FIRST|LAST：指定推動游標指針的方式 NEXT：向前推進一條記錄 PRIOR：向回退一條記錄 FIRST：推向第一條記錄 LAST：推向最后一條記錄 缺

26、省值為NEXTTheory of Database4. 關閉游標關閉游標v 使用CLOSE語句v 語句格式 EXEC SQL CLOSE ;v 功能 關閉游標，釋放結果集占用的緩沖區(qū)及其他資源v 說明 游標被關閉后，就不再和原來的查詢結果集相聯(lián)系 被關閉的游標可以再次被打開，與新的查詢結果相聯(lián)系Theory of Database二、二、CURRENT形式的形式的UPDATE語句和語句和DELETE語句語句vCURRENT形式的UPDATE語句和DELETE語句的用途 面向集合的操作 一次修改或刪除所有滿足條件的記錄Theory of DatabaseCURRENT形式的形式的UPDATE語句

27、和語句和DELETE語句語句(續(xù)續(xù)) 如果只想修改或刪除其中某個記錄用帶游標的SELECT語句查出所有滿足條件的記錄從中進一步找出要修改或刪除的記錄用CURRENT形式的UPDATE語句和DELETE語句修改或刪除之UPDATE語句和DELETE語句中的子句： WHERE CURRENT OF 表示修改或刪除的是最近一次取出的記錄，即游標指針指向的記錄 Theory of DatabaseCURRENT形式的形式的UPDATE語句和語句和DELETE語句語句(續(xù)續(xù))v不能使用CURRENT形式的UPDATE語句和DELETE語句 : 當游標定義中的SELECT語句帶有UNION或ORDER B

28、Y子句 該SELECT語句相當于定義了一個不可更新的視圖 Theory of Database嵌嵌 入入 式式 SQLv8.1.1 嵌入式嵌入式SQL的處理過程的處理過程v8.1.2 嵌入式嵌入式SQL語句與主語言之間的通信語句與主語言之間的通信v8.1.3 不使用游標的不使用游標的SQL語句語句v8.1.4 使用游標的使用游標的SQL語句語句v8.1.5 動態(tài)動態(tài)SQLv8.1.6 小結小結Theory of Database 8.1.5 動態(tài)動態(tài)SQLv 靜態(tài)嵌入式SQL 靜態(tài)嵌入式SQL語句能夠滿足一般要求 無法滿足要到執(zhí)行時才能夠確定要提交的SQL語句 v 動態(tài)嵌入式SQL 允許在程序

29、運行過程中臨時“組裝”SQL語句 支持動態(tài)組裝SQL語句和動態(tài)參數(shù)兩種形式 Theory of Database動態(tài)動態(tài)SQL簡介（續(xù)）簡介（續(xù)）v一、使用SQL語句主變量 v二、動態(tài)參數(shù)Theory of Database一、使用一、使用SQL語句主變量語句主變量vSQL語句主變量： 程序主變量包含的內容是SQL語句的內容，而不是原來保存數(shù)據(jù)的輸入或輸出變量 SQL語句主變量在程序執(zhí)行期間可以設定不同的SQL語句，然后立即執(zhí)行 Theory of Database使用使用SQL語句主變量（續(xù)）語句主變量（續(xù)）例9 創(chuàng)建基本表TESTEXEC SQL BEGIN DECLARE SECTION

30、;const char *stmt = CREATE TABLE test(a int); /* SQL語句主變量 */EXEC SQL END DECLARE SECTION;. .EXEC SQL EXECUTE IMMEDIATE :stmt; /* 執(zhí)行語句 */Theory of Database二、動態(tài)參數(shù)二、動態(tài)參數(shù)v動態(tài)參數(shù) SQL語句中的可變元素 使用參數(shù)符號(?)表示該位置的數(shù)據(jù)在運行時設定v和主變量的區(qū)別 動態(tài)參數(shù)的輸入不是編譯時完成綁定 而是通過 (prepare)語句準備主變量和執(zhí)行(execute)時綁定數(shù)據(jù)或主變量來完成 Theory of Database動態(tài)參

31、數(shù)（續(xù)）動態(tài)參數(shù)（續(xù)）v使用動態(tài)參數(shù)的步驟：1.聲明SQL語句主變量。2.準備SQL語句(PREPARE)。 EXEC SQL PREPARE FROM ;Theory of Database動態(tài)參數(shù)（續(xù)）動態(tài)參數(shù)（續(xù)）v使用動態(tài)參數(shù)的步驟（續(xù)）： 3.執(zhí)行準備好的語句(EXECUTE)EXEC SQL EXECUTE INTO USING ;Theory of Database動態(tài)參數(shù)（續(xù)）動態(tài)參數(shù)（續(xù)）例10向TEST中插入元組。EXEC SQL BEGIN DECLARE SECTION;const char *stmt = INSERT INTO test VALUES(?); /*聲

32、明SQL主變量 */EXEC SQL END DECLARE SECTION;. .EXEC SQL PREPARE mystmt FROM :stmt; /* 準備語句 */. .EXEC SQL EXECUTE mystmt USING 100; /* 執(zhí)行語句 */EXEC SQL EXECUTE mystmt USING 200; /* 執(zhí)行語句 */Theory of Database8.1 嵌入式嵌入式SQL8.1.1 嵌入式嵌入式SQL的處理過程的處理過程8.1.2 嵌入式嵌入式SQL語句與主語言之間的通信語句與主語言之間的通信8.1.3 不使用游標的不使用游標的SQL語句語句8

33、.1.4 使用游標的使用游標的SQL語句語句8.1.5 動態(tài)動態(tài)SQL8.1.6 小結小結Theory of Database8.1.6 小小 結結v在嵌入式SQL中，SQL語句與主語言語句分工非常明確 SQL語句直接與數(shù)據(jù)庫打交道，取出數(shù)據(jù)庫中的數(shù)據(jù)。 主語言語句控制程序流程對取出的數(shù)據(jù)做進一步加工處理Theory of Database小結（續(xù)）小結（續(xù)）v SQL語言是面向集合的，一條SQL語句原則上可以產生或處理多條記錄v 主語言是面向記錄的，一組主變量一次只能存放一條記錄 僅使用主變量并不能完全滿足SQL語句向應用程序輸出數(shù)據(jù)的要求 嵌入式SQL引入了游標的概念，用來協(xié)調這兩種不同的

34、處理方式Theory of Database第八章第八章 數(shù)據(jù)庫編程數(shù)據(jù)庫編程8.1 嵌入式嵌入式SQL8.2 存儲過程存儲過程8.3 ODBC編程編程Theory of Database8.2 存儲過程存儲過程vSQL-invoked routines： 存儲過程(SQL-invoked procedure) 函數(shù)(SQL-invoked function)Theory of Database8.2 存儲過程存儲過程8.2.1 PL/SQL的塊結構的塊結構8.2.2 變量常量的定義變量常量的定義8.2.3 控制結構控制結構8.2.4 存儲過程存儲過程8.2.5 小結小結Theory of D

35、atabase8.2.1 PL/SQL的塊結構的塊結構vPL/SQL ： SQL的擴展 增加了過程化語句功能 基本結構是塊塊之間可以互相嵌套 每個塊完成一個邏輯操作 Theory of DatabasePL/SQL的塊結構（續(xù)）的塊結構（續(xù)）vPL/SOL塊的基本結構：塊的基本結構： 1.定義部分定義部分 DECLARE -變量、常量、游標、異常等 n定義的變量、常量等只能在該基本塊中使用n當基本塊執(zhí)行結束時，定義就不再存在Theory of DatabasePL/SQL的塊結構（續(xù)）的塊結構（續(xù)）vPL/SOL塊的基本結構塊的基本結構(續(xù)續(xù))： 2.執(zhí)行部分執(zhí)行部分 BEGIN -SQL語句

36、、PL/SQL的流程控制語句 EXCEPTION -異常處理部分 END；Theory of Database8.2 存儲過程存儲過程8.2.1 PL/SQL的塊結構的塊結構8.2.2 變量常量的定義變量常量的定義8.2.3 控制結構控制結構8.2.4 存儲過程存儲過程8.2.5 小結小結Theory of Database8.2.2 變量常量的定義變量常量的定義1. PL/SQL中定義變量的語法形式是中定義變量的語法形式是: 變量名 數(shù)據(jù)類型 NOT NULL:=初值表達式或 變量名 數(shù)據(jù)類型 NOT NULL初值表達式2. 常量的定義類似于變量的定義常量的定義類似于變量的定義: 常量名 數(shù)

37、據(jù)類型 CONSTANT :=常量表達式 常量必須要給一個值，并且該值在存在期間或常量的作用域內不能改變。如果試圖修改它，PL/SQL將返回一個異常。3. 賦值語句賦值語句 變量名稱:=表達式Theory of Database8.2 存儲過程存儲過程8.2.1 PL/SQL的塊結構的塊結構8.2.2 變量常量的定義變量常量的定義8.2.3 控制結構控制結構8.2.4 存儲過程存儲過程8.2.5 小結小結Theory of Database8.2.3 控制結構控制結構 vPL/SQL 功能：功能：一、條件控制語句二、循環(huán)控制語句 三、錯誤處理 Theory of Database控制結構（續(xù)）

38、控制結構（續(xù)）v一、一、 條件控制語句條件控制語句 IF-THEN， IF-THEN-ELSE和嵌套的和嵌套的IF語句語句 1. IF condition THEN Sequence_of_statements; END IF 2. IF condition THEN Sequence_of_statements1; ELSE Sequence_of_statements2; END IF;3. 在THEN和ELSE子句中還可以再包括IF語句，即IF語句可以嵌套 Theory of Database控制結構（續(xù)）控制結構（續(xù)）二、循環(huán)控制語句二、循環(huán)控制語句 LOOP， WHILE-LOOP和

39、和FOR-LOOP 1.最簡單的循環(huán)語句最簡單的循環(huán)語句LOOP LOOP Sequence_of_statements; END LOOP; 多數(shù)數(shù)據(jù)庫服務器的PL/SQL都提供EXIT、BREAK或LEAVE等循環(huán)結束語句，保證LOOP語句塊能夠結束。Theory of Database控制結構（續(xù)）控制結構（續(xù)）二、循環(huán)控制語句（續(xù)）二、循環(huán)控制語句（續(xù)）2. WHILE-LOOP WHILE condition LOOP Sequence_of_statements;END LOOP;n每次執(zhí)行循環(huán)體語句之前，首先對條件進行求值n如果條件為真，則執(zhí)行循環(huán)體內的語句序列。n如果條件為假，

40、則跳過循環(huán)并把控制傳遞給下一個語句 3. FOR-LOOP FOR count IN REVERSEbound1 bound2 LOOP Sequence_of_statements;END LOOP;Theory of Database控制結構（續(xù)）控制結構（續(xù)）v三、錯誤處理： 如果PL/SQL在執(zhí)行時出現(xiàn)異常，則應該讓程序在產生異常的語句處停下來，根據(jù)異常的類型去執(zhí)行異常處理語句 SQL標準對數(shù)據(jù)庫服務器提供什么樣的異常處理做出了建議，要求PL/SQL管理器提供完善的異常處理機制 Theory of Database8.2 存儲過程存儲過程v8.2.1 PL/SQL的塊結構的塊結構v8.

41、2.2 變量常量的定義變量常量的定義v8.2.3 控制結構控制結構v8.2.4 存儲過程存儲過程v8.2.5 小結小結Theory of Database8.2.4 存儲過程存儲過程vPL/SQL塊類型： 命名塊：編譯后保存在數(shù)據(jù)庫中，可以被反復調用，運行速度較快。存儲過程和函數(shù)是命名塊 匿名塊：每次執(zhí)行時都要進行編譯，它不能被存儲到數(shù)據(jù)庫中，也不能在其他的PL/SQL塊中調用 Theory of Database存儲過程（續(xù)）存儲過程（續(xù)）v一、 存儲過程的優(yōu)點 v二、 存儲過程的用戶接口 v三、 游標 Theory of Database存儲過程（續(xù)）存儲過程（續(xù)）v存儲過程：由PL/SQ

42、L語句書寫的過程，經編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務器中，使用時只要調用即可。 v一、存儲過程的優(yōu)點：1. 運行效率高2. 降低了客戶機和服務器之間的通信量 3. 方便實施企業(yè)規(guī)則Theory of Database存儲過程（續(xù)）存儲過程（續(xù)）v二、 存儲過程的用戶接口：1. 創(chuàng)建存儲過程 2. 執(zhí)行存儲過程 3. 刪除存儲過程 Theory of Database二、二、 存儲過程的用戶接口存儲過程的用戶接口v1. 創(chuàng)建存儲過程：創(chuàng)建存儲過程：CREATE Procedure 過程名（參數(shù)1，參數(shù)2，.） AS；n過程名：數(shù)據(jù)庫服務器合法的對象標識n參數(shù)列表：用名字來標識調用時給出的參數(shù)值，必須

43、指定值的數(shù)據(jù)類型。參數(shù)也可以定義輸入參數(shù)、輸出參數(shù)或輸入/輸出參數(shù)。默認為輸入參數(shù)。n過程體：是一個。包括聲明部分和可執(zhí)行語句部分 Theory of Database存儲過程的用戶接口（續(xù)）存儲過程的用戶接口（續(xù)）例11 利用存儲過程來實現(xiàn)下面的應用: 從一個賬戶轉指定數(shù)額的款項到另一個賬戶中。 CREATE PROCEDURE TRANSFER(inAccount INT， outAccount INT， amount FLOAT) AS DECLARE totalDeposit FLOAT; BEGIN /* 檢查轉出賬戶的余額 */ SELECT total INTO totalDep

44、osit FROM ACCOUNT WHERE ACCOUNTNUM=outAccount; IF totalDeposit IS NULL THEN /* 賬戶不存在或賬戶中沒有存款 */ ROLLBACK; RETURN; END IF; Theory of Database存儲過程的用戶接口（續(xù)）存儲過程的用戶接口（續(xù)） IF totalDeposit amount THEN /* 賬戶賬戶存款不足 */ ROLLBACK; RETURN;END IF; UPDATE account SET total=total-amount WHERE ACCOUNTNUM=outAccount;

45、/* 修改轉出賬戶，減去轉出額 */ UPDATE account SET total=total + amount WHERE ACCOUNTNUM=inAccount; /* 修改轉入賬戶，增加轉出額 */COMMIT; /* 提交轉賬事務 */END;Theory of Database存儲過程的用戶接口（續(xù)）存儲過程的用戶接口（續(xù)）v重命名存儲過程 ALTER Procedure 過程名1 RENAME TO 過程名2;Theory of Database存儲過程的用戶接口（續(xù)）存儲過程的用戶接口（續(xù)）v2. 執(zhí)行存儲過程：執(zhí)行存儲過程： CALL/PERFORM Procedure

46、過程名(參數(shù)1，參數(shù)2，.)；n使用CALL或者PERFORM等方式激活存儲過程的執(zhí)行。n在PL/SQL中，數(shù)據(jù)庫服務器支持在過程體中調用其他存儲過程例12從賬戶01003815868轉一萬元到01003813828賬戶中。 CALL Procedure TRANSFER(01003813828，01003815868，10000)； Theory of Database存儲過程的用戶接口（續(xù)）存儲過程的用戶接口（續(xù)）3. 刪除存儲過程 DROP PROCEDURE 過程名（）；Theory of Database三、游三、游 標標 v 在PL/SQL中，如果SELECT語句只返回一條記錄，可

47、以將該結果存放到變量中。v 當查詢返回多條記錄時，就要使用游標對結果集進行處理v 一個游標與一個SQL語句相關聯(lián)。v PL/SQL中的游標由PL/SQL引擎管理 Theory of Database8.2 存儲過程存儲過程v8.2.1 PL/SQL的塊結構的塊結構v8.2.2 變量常量的定義變量常量的定義v8.2.3 控制結構控制結構v8.2.4 存儲過程存儲過程v8.2.5 小結小結Theory of Database8.2.5 小小 結結v存儲過程的優(yōu)點 經編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務器中，運行效率高 降低客戶機和服務器之間的通信量 有利于集中控制，方便維護 Theory of Datab

48、ase第八章第八章 數(shù)據(jù)庫編程數(shù)據(jù)庫編程8.1 嵌入式嵌入式SQL8.2 存儲過程存儲過程8.3 ODBC編程編程Theory of Database8.3 ODBC編程編程vODBC優(yōu)點： 移植性好 能同時訪問不同的數(shù)據(jù)庫 共享多個數(shù)據(jù)資源 Theory of Database8.3 ODBC編程編程v8.3.1 數(shù)據(jù)庫互連概述數(shù)據(jù)庫互連概述v8.3.2 ODBC工作原理概述工作原理概述 v8.3.3 ODBC API 基礎基礎 v8.3.4 ODBC的工作流程的工作流程v8.3.5 小結小結Theory of Database8.3.1 數(shù)據(jù)庫互連概述數(shù)據(jù)庫互連概述vODBC產生的原因：產

49、生的原因： 由于不同的數(shù)據(jù)庫管理系統(tǒng)的存在，在某個RDBMS下編寫的應用程序就不能在另一個RDBMS下運行 許多應用程序需要共享多個部門的數(shù)據(jù)資源，訪問不同的RDBMS Theory of Database數(shù)據(jù)庫互連概述（續(xù)）數(shù)據(jù)庫互連概述（續(xù)）vODBC： 是微軟公司開放服務體系(Windows Open Services Architecture，WOSA)中有關數(shù)據(jù)庫的一個組成部分 提供了一組訪問數(shù)據(jù)庫的標準API vODBC約束力：約束力： 規(guī)范應用開發(fā) 規(guī)范RDBMS應用接口Theory of Database8.3 ODBC編程編程v8.3.1 數(shù)據(jù)庫互連概述數(shù)據(jù)庫互連概述v8.3

50、.2 ODBC工作原理概述工作原理概述 v8.3.3 ODBC API 基礎基礎 v8.3.4 ODBC的工作流程的工作流程v8.3.5 小結小結Theory of Database8.3.2 ODBC工作原理概述工作原理概述vODBC應用系統(tǒng)的體系結構 ：一、一、 用戶應用程序用戶應用程序 二、二、 驅動程序管理器驅動程序管理器 三、三、 數(shù)據(jù)庫驅動程序數(shù)據(jù)庫驅動程序四、四、 ODBC數(shù)據(jù)源管理數(shù)據(jù)源管理 Theory of Database一、一、 應用程序應用程序v ODBC應用程序包括的內容應用程序包括的內容: 請求連接數(shù)據(jù)庫； 向數(shù)據(jù)源發(fā)送SQL語句； 為SQL語句執(zhí)行結果分配存儲空

51、間，定義所讀取的數(shù)據(jù)格式； 獲取數(shù)據(jù)庫操作結果，或處理錯誤； 進行數(shù)據(jù)處理并向用戶提交處理結果； 請求事務的提交和回滾操作； 斷開與數(shù)據(jù)源的連接。 Theory of Database二、驅動程序管理器二、驅動程序管理器 v 驅動程序管理器：用來管理各種驅動程序 包含在ODBC32.DLL中 管理應用程序和驅動程序之間的通信 建立、配置或刪除數(shù)據(jù)源并查看系統(tǒng)當前所安裝的數(shù)據(jù)庫ODBC驅動程序 主要功能：裝載ODBC驅動程序選擇和連接正確的驅動程序管理數(shù)據(jù)源檢查ODBC調用參數(shù)的合法性記錄ODBC函數(shù)的調用等 Theory of Database三、數(shù)據(jù)庫驅動程序三、數(shù)據(jù)庫驅動程序v ODBC通

52、過驅動程序來提供應用系統(tǒng)與數(shù)據(jù)庫平臺的獨立性 v ODBC應用程序不能直接存取數(shù)據(jù)庫 其各種操作請求由驅動程序管理器提交給某個RDBMS的ODBC驅動程序 通過調用驅動程序所支持的函數(shù)來存取數(shù)據(jù)庫。 數(shù)據(jù)庫的操作結果也通過驅動程序返回給應用程序。 如果應用程序要操縱不同的數(shù)據(jù)庫，就要動態(tài)地鏈接到不同的驅動程序上。 Theory of Database數(shù)據(jù)庫驅動程序（續(xù)）數(shù)據(jù)庫驅動程序（續(xù)）v ODBC驅動程序類型： 單束數(shù)據(jù)源和應用程序在同一臺機器上驅動程序直接完成對數(shù)據(jù)文件的I/O操作驅動程序相當于數(shù)據(jù)管理器 多束 支持客戶機/服務器、客戶機/應用服務器/數(shù)據(jù)庫服務器等網絡環(huán)境下的數(shù)據(jù)訪問由

53、驅動程序完成數(shù)據(jù)庫訪問請求的提交和結果集接收應用程序使用驅動程序提供的結果集管理接口操縱執(zhí)行后的結果數(shù)據(jù) Theory of Database四、四、ODBC數(shù)據(jù)源管理數(shù)據(jù)源管理 v 數(shù)據(jù)源：是最終用戶需要訪問的數(shù)據(jù)，包含了數(shù)據(jù)庫位置和數(shù)據(jù)庫類型等信息，是一種數(shù)據(jù)連接的抽象 v 數(shù)據(jù)源對最終用戶是透明的 ODBC給每個被訪問的數(shù)據(jù)源指定唯一的數(shù)據(jù)源名（Data Source Name，簡稱DSN）,并映射到所有必要的、用來存取數(shù)據(jù)的低層軟件 在連接中，用數(shù)據(jù)源名來代表用戶名、服務器名、所連接的數(shù)據(jù)庫名等 最終用戶無需知道DBMS或其他數(shù)據(jù)管理軟件、網絡以及有關ODBC驅動程序的細節(jié)Theory

54、 of DatabaseODBC數(shù)據(jù)源管理（續(xù)）數(shù)據(jù)源管理（續(xù)）例如，假設某個學校在MS SQL Server和KingbaseES上創(chuàng)建了兩個數(shù)據(jù)庫：學校人事數(shù)據(jù)庫和教學科研數(shù)據(jù)庫。 學校的信息系統(tǒng)要從這兩個數(shù)據(jù)庫中存取數(shù)據(jù) 為方便與兩個數(shù)據(jù)庫連接，為學校人事數(shù)據(jù)庫創(chuàng)建一個數(shù)據(jù)源名PERSON，為教學科研數(shù)據(jù)庫創(chuàng)建一個名為EDU的數(shù)據(jù)源。 當要訪問每一個數(shù)據(jù)庫時，只要與PERSON和EDU連接即可,不需要記住使用的驅動程序、服務器名稱、數(shù)據(jù)庫名 Theory of Database8.3 ODBC編程編程v8.3.1 數(shù)據(jù)庫互連概述數(shù)據(jù)庫互連概述v8.3.2 ODBC工作原理概述工作原理概述

55、 v8.3.3 ODBC API 基礎基礎 v8.3.4 ODBC的工作流程的工作流程v8.3.5 小結小結Theory of Database8.3.3 ODBC API 基礎基礎vODBC 應用程序接口的一致性 API一致性API一致性級別有核心級、擴展1級、擴展2級 語法一致性語法一致性級別有最低限度SQL語法級、核心SQL語法級、擴展SQL語法級 Theory of DatabaseODBC API 基礎（續(xù)）基礎（續(xù)）v一、 函數(shù)概述v二、 句柄及其屬性v三、 數(shù)據(jù)類型Theory of Database一、一、 函數(shù)概述函數(shù)概述v ODBC 3.0 標準提供了標準提供了76個函數(shù)接

56、口：個函數(shù)接口： 分配和釋放環(huán)境句柄、連接句柄、語句句柄； 連接函數(shù)（SQLDriverconnect等）； 與信息相關的函數(shù)（如獲取描述信息函數(shù)SQLGetinfo、SQLGetFuction）； 事務處理函數(shù)（如SQLEndTran）； 執(zhí)行相關函數(shù)（SQLExecdirect、SQLExecute等）； 編目函數(shù)，ODBC 3.0提供了11個編目函數(shù)如SQLTables、SQLColumn等，應用程序可以通過對編目函數(shù)的調用來獲取數(shù)據(jù)字典的信息如權限、表結構等 Theory of Database函數(shù)概述（續(xù)）函數(shù)概述（續(xù)）vODBC 1.0和ODBC 2.x、ODBC 3.x函數(shù)使用上

57、有很多差異 vMFC ODBC對較復雜的ODBC API進行了封裝，提供了簡化的調用接口 Theory of Database二、二、 句柄及其屬性句柄及其屬性v句柄是32位整數(shù)值，代表一個指針 vODBC 3.0中句柄分類： 環(huán)境句柄 連接句柄 語句句柄 描述符句柄 Theory of Database句柄及其屬性（續(xù)）句柄及其屬性（續(xù)）v 應用程序句柄之間的關系 應用程序句柄之間的關系 Theory of Database句柄及其屬性（續(xù)）句柄及其屬性（續(xù)）1. 每個ODBC應用程序需要建立一個ODBC環(huán)境，分配一個環(huán)境句柄，存取數(shù)據(jù)的全局性背景如環(huán)境狀態(tài)、當前環(huán)境狀態(tài)診斷、當前在環(huán)境上分

58、配的連接句柄等；2. 一個環(huán)境句柄可以建立多個連接句柄，每一個連接句柄實現(xiàn)與一個數(shù)據(jù)源之間的連接；Theory of Database句柄及其屬性（續(xù)）句柄及其屬性（續(xù)）3. 在一個連接中可以建立多個語句句柄，它不只是一個SQL語句，還包括SQL語句產生的結果集以及相關的信息等；4. 在ODBC 3.0中又提出了描述符句柄的概念，它是描述SQL語句的參數(shù)、結果集列的元數(shù)據(jù)集合。Theory of Database三、三、 數(shù)據(jù)類型數(shù)據(jù)類型 vODBC數(shù)據(jù)類型： SQL數(shù)據(jù)類型：用于數(shù)據(jù)源 C數(shù)據(jù)類型 ：用于應用程序的C代碼 v 應用程序可以通過SQLGetTypeInfo來獲取不同的驅動程序對

59、于數(shù)據(jù)類型的支持情況 Theory of Database數(shù)據(jù)類型（續(xù)）數(shù)據(jù)類型（續(xù)）SQL數(shù)據(jù)類型C數(shù)據(jù)類型SQL數(shù)據(jù)類型數(shù)據(jù)源之間轉換應用程序變量傳送到語句參數(shù)（SQLBindparameter）C數(shù)據(jù)類型從結果集列中返回到應用程序變量（SQLBindcol）應用程序變量之間轉換SQL數(shù)據(jù)類型和C數(shù)據(jù)類型之間的轉換規(guī)則Theory of Database8.3 ODBC編程編程v8.3.1 數(shù)據(jù)庫互連概述數(shù)據(jù)庫互連概述v8.3.2 ODBC工作原理概述工作原理概述 v8.3.3 ODBC API 基礎基礎 v8.3.4 ODBC的工作流程的工作流程v8.3.5 小結小結Theory of

60、Database8.3.4 ODBC的工作流程的工作流程v ODBC的工作流程：的工作流程：Theory of DatabaseODBC的工作流程（續(xù)）的工作流程（續(xù)）例13將KingbaseES數(shù)據(jù)庫中Student表的數(shù)據(jù)備份到SQL SERVER數(shù)據(jù)庫中。n該應用涉及兩個不同的RDBMS中的數(shù)據(jù)源n使用ODBC來開發(fā)應用程序，只要改變應用程序中連接函數(shù)（SQLConnect）的參數(shù)，就可以連接不同RDBMS的驅動程序，連接兩個數(shù)據(jù)源Theory of DatabaseODBC的工作流程（續(xù)）的工作流程（續(xù)）n 在應用程序運行前，已經在KingbaseES和SQL SERVER中分別建立了STUDENT關系表 CREATE TABLE St