第八章數(shù)據(jù)庫(kù)編程-數(shù)據(jù)庫(kù)原理

第八章數(shù)據(jù)庫(kù)編程8.1嵌入式SQL8.2存儲(chǔ)過(guò)程8.3ODBC編程8.1嵌入式SQL建立數(shù)據(jù)庫(kù)后就要開(kāi)發(fā)應(yīng)用系統(tǒng)了。標(biāo)準(zhǔn)化SQL是非過(guò)程化的查詢(xún)語(yǔ)言，具有操作統(tǒng)一、面向集合、功能豐富、使用簡(jiǎn)單等多項(xiàng)優(yōu)點(diǎn)。但和程序設(shè)計(jì)語(yǔ)言相比，高度非過(guò)程化的優(yōu)點(diǎn)同時(shí)也成了它的缺點(diǎn)：缺少流程控制能力，難以實(shí)現(xiàn)業(yè)務(wù)中的邏輯控制，SQL編程技術(shù)可以有效克服這一缺點(diǎn)，提高應(yīng)用系統(tǒng)和RDBMS間互操作性。應(yīng)用系統(tǒng)中使用SQL編程來(lái)訪問(wèn)和管理數(shù)據(jù)庫(kù)中數(shù)據(jù)的方式主要有:嵌入式SQL、PL/SQL(存儲(chǔ)過(guò)程)、ODBC編程、JDBC編程和OLEDB編程等方式。嵌入式SQL是將SQL語(yǔ)句嵌入程序設(shè)計(jì)語(yǔ)言中，被嵌入的程序設(shè)計(jì)語(yǔ)言，如C，C++，PB，JAVA，VB等稱(chēng)謂宿主語(yǔ)言，簡(jiǎn)稱(chēng)主語(yǔ)言。對(duì)ESQL，RDBMS一般采用預(yù)編譯方法處理，即由RDBMS的預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描，識(shí)別出ESQL語(yǔ)句，把它們轉(zhuǎn)換成語(yǔ)言調(diào)用語(yǔ)句，以使主語(yǔ)言編譯程序能識(shí)別它們，然后由主語(yǔ)言的編譯程序?qū)⒓兊闹髡Z(yǔ)言程序編譯成目標(biāo)碼。在ESQL中，為了能夠區(qū)分SQL語(yǔ)句和主語(yǔ)言語(yǔ)句，所有SQL語(yǔ)句都必須加標(biāo)志。但不同主語(yǔ)言所規(guī)定加的標(biāo)志不同。8.1.1嵌入式SQL的處理過(guò)程8.1.2嵌入式SQL與主語(yǔ)言的通信數(shù)據(jù)庫(kù)工作單元與源程序工作單元之間的通信主要包括：1、向主語(yǔ)言傳遞SQL語(yǔ)句執(zhí)行狀態(tài)信息，使主語(yǔ)言能夠據(jù)此信息控制程序流程，主要用SQL通信區(qū)SQLCommunicationArea（簡(jiǎn)稱(chēng)SQLCA）實(shí)現(xiàn)；2、主語(yǔ)言向SQL語(yǔ)句提供參數(shù)，主要用主變量（HostVariable)實(shí)現(xiàn)；3、將SQL語(yǔ)句查詢(xún)數(shù)據(jù)庫(kù)的結(jié)果交給主語(yǔ)言處理，主要用主變量和游標(biāo)（Cursor)實(shí)現(xiàn)。一、SQL通信區(qū)SQL語(yǔ)句執(zhí)行后，系統(tǒng)要反饋給主語(yǔ)言若干信息主要包括描述系統(tǒng)當(dāng)前工作狀態(tài)的運(yùn)行環(huán)境的各種數(shù)據(jù)。這些信息將送到SQLCA中。主語(yǔ)言從SQLCA中取出這些狀態(tài)信息，據(jù)些決定接下來(lái)的的流程。SQLCA在應(yīng)用程序中要事先加以定義，一般都是系統(tǒng)級(jí)變量。SQLCA中有一個(gè)變量SQLCODE用來(lái)存放每次執(zhí)行SQL語(yǔ)句后返回的狀態(tài)代碼。主語(yǔ)言每執(zhí)行完一條SQL語(yǔ)句之后都不得應(yīng)該測(cè)試一下SQLCODE的值，以了解該SQL語(yǔ)句執(zhí)行情況并做相應(yīng)處理。二、主變量嵌入式SQL語(yǔ)句中可以使用主語(yǔ)言的程序變量來(lái)輸入或輸出數(shù)據(jù)。SQL語(yǔ)句中使用的主語(yǔ)言程序變量簡(jiǎn)稱(chēng)為主變量。主變量根據(jù)其作用的不同，分為輸入主變量和輸出主變量。輸入主變量由主語(yǔ)言對(duì)其賦值，SQL引用；輸出主變量由SQL語(yǔ)句對(duì)其賦值或設(shè)置狀態(tài)信息，返回給主語(yǔ)言。一個(gè)主變量可以附帶一個(gè)任選的指示變量(IndicatorVariable)。指示變量是一個(gè)整型變量，用來(lái)“指示”所指主變量的值或條件。指示變量可以指示輸入主變量是否為空值，可以檢測(cè)輸出主變量是否是空值，值是否被截?cái)?。所有主變量的指示變量必須在SQL語(yǔ)句{}之間進(jìn)行說(shuō)明。說(shuō)明之后，主變量可以在SQL語(yǔ)句中任何一個(gè)能夠使用表達(dá)式的地方出現(xiàn)，為了與數(shù)據(jù)庫(kù)對(duì)象名區(qū)別，SQL語(yǔ)句中的主變量名和指示變量前要加“：”作為標(biāo)志。三、游標(biāo)SQL是面向集合的，一條SQL語(yǔ)句可以產(chǎn)生或處理多條記錄。而主語(yǔ)言是面向記錄的，一組變量一次只能存取一條記錄。所以需要用游標(biāo)來(lái)協(xié)調(diào)這兩種不同的處理方式。游標(biāo)是系統(tǒng)為用戶(hù)開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果，每個(gè)游標(biāo)都有一個(gè)名字，用戶(hù)可以通過(guò)游標(biāo)逐一獲取記錄，并賦給主變量，交由主語(yǔ)言進(jìn)一步處理。四、建立和關(guān)閉數(shù)據(jù)庫(kù)連接嵌入式SQL程序要訪問(wèn)數(shù)據(jù)庫(kù)必須先連接數(shù)據(jù)庫(kù)。RDBMS根據(jù)用戶(hù)信息對(duì)連接請(qǐng)求進(jìn)行合法性驗(yàn)證，只有通過(guò)身份驗(yàn)證，才能建立一個(gè)合法連接。1、建立數(shù)據(jù)庫(kù)連接2、關(guān)閉數(shù)據(jù)庫(kù)連接當(dāng)某個(gè)連接上的所有數(shù)據(jù)庫(kù)操作完成后，應(yīng)用程序應(yīng)該主動(dòng)釋放所占用的連接資源。五、程序?qū)嵗?，以PB為主語(yǔ)言進(jìn)行講解8.1.3不用游標(biāo)的SQL語(yǔ)句有的ESQL語(yǔ)句不需要使用游標(biāo)，它們是：說(shuō)明性語(yǔ)句、數(shù)據(jù)定義語(yǔ)句、數(shù)據(jù)控制語(yǔ)句、查詢(xún)結(jié)果為單記錄的SELECT語(yǔ)句、非CURRENT形式的增刪語(yǔ)句。一、查詢(xún)結(jié)果為單記錄的SELECT語(yǔ)句例：根據(jù)學(xué)生學(xué)號(hào)查詢(xún)學(xué)生信息，假設(shè)已經(jīng)把要查詢(xún)的學(xué)號(hào)賦給了主變量givensnoEXECSQLSELECTSNO,SNAME,SSEX,SAGE,SDEPTINTO:HSNO,:HNAME,:HSEX,:HAGE,:HDEPTFROMSTUDENTWHERESNO=:givensno;二、非CURRENT形式的增刪改語(yǔ)句有些增刪語(yǔ)句不需要使用游標(biāo)，不是CURRENT形式的，在UPDATE的SET子句和WHERE子句中可以使用主變量，SET子句還可以使用指示變量。EXECSQLUPDATEscSETgrade=:newgradeWHEREsno=:givensno;sageid=-1;EXECSQLUPDATEstudentSETsage=NULLWHEREsdept='CS';EXECSQLDELETEFROMscWHEREsno={SELECTsnoFROMstudentwheresname=:stuname);8.4.1使用游標(biāo)的SQL語(yǔ)句必須使用游標(biāo)的SQL的語(yǔ)句有：查詢(xún)結(jié)果為多條記錄的SELECT語(yǔ)句、CURRENT形式的UPDATE和DELETE語(yǔ)句。一、查詢(xún)結(jié)果為多條記錄的SELECT語(yǔ)句（1）用DELCARE說(shuō)明游標(biāo)；（2）用OPEN語(yǔ)句打開(kāi)游標(biāo)；（3）用FETCH語(yǔ)句推進(jìn)游標(biāo)，一般用一循環(huán)結(jié)構(gòu)；（4）使用完后，用CLOSE關(guān)閉游標(biāo)。游標(biāo)關(guān)閉后，就不再和原來(lái)的查詢(xún)結(jié)果集相聯(lián)系。但關(guān)閉的游標(biāo)可以再次打開(kāi)使用，方法同上。二、CURRENT形式的UPDATE和DELETE語(yǔ)句UPDATE和DELETE語(yǔ)句都是集合操作，如果只想修改或刪除其中的某個(gè)記錄，則需要用帶游標(biāo)的SELECT語(yǔ)句查出所有滿(mǎn)足條件的記錄，從中進(jìn)一步找出要修改或刪除的記錄，然后用CURRENT形式的UPDATE和DELETE語(yǔ)句操作。即要用子句WHERECURRNETOF<游標(biāo)名>8.1.5動(dòng)態(tài)SQL動(dòng)態(tài)SQL方法允許主語(yǔ)言在運(yùn)行過(guò)程中臨時(shí)“組裝”SQL語(yǔ)句。動(dòng)態(tài)SQL支持動(dòng)態(tài)組裝SQL語(yǔ)句和動(dòng)態(tài)參數(shù)兩種形式，給開(kāi)發(fā)者提供設(shè)計(jì)任意SQL語(yǔ)句的能力。一、使用SQL語(yǔ)句主變量二、動(dòng)態(tài)參數(shù)1.聲明SQL語(yǔ)句主變量2.準(zhǔn)備SQL語(yǔ)句(PREPARE)3.執(zhí)行準(zhǔn)備好的語(yǔ)句(EXECUTE)8.2存儲(chǔ)過(guò)程SQL99標(biāo)準(zhǔn)中給出了SQL-invokedroutines的概念,它可以分為存儲(chǔ)過(guò)程(SQL-invokedprocedure)和函數(shù)(SQL-invokedfunction)兩類(lèi)。8.2.1PL/SQL的塊結(jié)構(gòu)基本的SQL是高度非過(guò)程化的語(yǔ)言。ESQL將SQL語(yǔ)句嵌入程序設(shè)計(jì)語(yǔ)言，借助高級(jí)語(yǔ)言的控制功能實(shí)現(xiàn)過(guò)程化。PL/SQL程序的基本結(jié)構(gòu)是塊。所有的PL/SQL程序都是由塊組成的。這些塊之間可以互相嵌套，每個(gè)塊完成一個(gè)邏輯操作。定義部分{執(zhí)行部分{DECLARE變量、常量、游標(biāo)、異常等/*定義的變量、常量等只能在該基本塊中使用，當(dāng)基本塊執(zhí)行結(jié)束時(shí)，定義就不再存在。*/BEGINSQL語(yǔ)句、PL/SQL的流程控制語(yǔ)句EXCEPTION異常處理部分/*在出現(xiàn)異常時(shí)，采取相應(yīng)的措施*/END；8.2.2變量和常量的定義1.PL/SQL中定義變量的語(yǔ)法形式是變量名數(shù)據(jù)類(lèi)型[[NOTNULL]]：=初值表達(dá)式或變量名數(shù)據(jù)類(lèi)型[[NOTNULL]初值表達(dá)式]2.常量的定義類(lèi)似于變量的定義常量名數(shù)據(jù)類(lèi)型CONSTANT：=常量表達(dá)式常量必須要給一個(gè)值，并且該值在存在期間或常量的作用哉內(nèi)不能改變。3.賦值語(yǔ)句變量名稱(chēng)：=表達(dá)式8.2.3控制語(yǔ)句PL/SQL提供了流程控制語(yǔ)句，使用時(shí)要參考具體的產(chǎn)品手冊(cè)的語(yǔ)法規(guī)則一、條件控制語(yǔ)句1.IF<條件表達(dá)式>THEN{語(yǔ)句序列}ENDIF2.IF<條件表達(dá)式>THEN{語(yǔ)句序列1}ELSE{語(yǔ)句序列2}ENDIF3.在THEN和ELSE子句中還可以嵌套使用IF語(yǔ)句。二、循環(huán)控制語(yǔ)句1、最簡(jiǎn)單的循環(huán)語(yǔ)句LOOPLOOP{循環(huán)體，一組PL/SQL語(yǔ)句}ENDLOOP；多數(shù)RDBMS的PL/SQL都提供了EXIT、BREAK等循環(huán)結(jié)束語(yǔ)句，以保證LOOP語(yǔ)句能夠在適當(dāng)?shù)臈l件下結(jié)束。2、WHILE-LOOPWHILE<條件> LOOP{語(yǔ)句序列}ENDLOOP；3、FOR-LOOPFOR計(jì)數(shù)器IN[步長(zhǎng)]下界…上界LOOP{語(yǔ)句序列}ENDLOOP三、錯(cuò)誤處理如果PL/SQL在執(zhí)行時(shí)出現(xiàn)異常，則應(yīng)該讓程序在產(chǎn)生異常的語(yǔ)句處停下來(lái)，根據(jù)異常的類(lèi)型去執(zhí)行異常處理。使用時(shí)要根據(jù)具體DBMS系統(tǒng)來(lái)進(jìn)行處理。8.2.4存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程和函數(shù)是命名塊，它們被編譯后保存在數(shù)據(jù)庫(kù)中，可以被反復(fù)調(diào)用，運(yùn)行速度快。一、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)1、由于存儲(chǔ)過(guò)程不像解釋執(zhí)行的SQL語(yǔ)句那樣在提出操作請(qǐng)求時(shí)才進(jìn)行語(yǔ)法分析和優(yōu)化工作，因而運(yùn)行效率高，它提供了在服務(wù)器端快速執(zhí)行SQL語(yǔ)句的有效途徑。2、存儲(chǔ)過(guò)程降低了客戶(hù)機(jī)和服務(wù)器之間的通信量?？蛻?hù)機(jī)上的應(yīng)用程序只要通過(guò)網(wǎng)絡(luò)向服務(wù)器發(fā)出存儲(chǔ)過(guò)程的名字的參數(shù)，就可以讓RDBMS執(zhí)行許多條SQL語(yǔ)句，并執(zhí)行數(shù)據(jù)處理，只有最終處理結(jié)果才返回客戶(hù)端。3、方便實(shí)施企業(yè)規(guī)則。可以把企業(yè)規(guī)則的運(yùn)算程序?qū)懗纱鎯?chǔ)過(guò)程放入數(shù)據(jù)庫(kù)服務(wù)器中，由RDBMS管理，既有利于集中控制，又能方便地進(jìn)行維護(hù)。當(dāng)用戶(hù)規(guī)則發(fā)生變化時(shí)只要修改存儲(chǔ)過(guò)程，無(wú)須修改其他應(yīng)用程序。二、存儲(chǔ)過(guò)程的用戶(hù)接口1、創(chuàng)建存儲(chǔ)過(guò)程

CREATEProcedure<過(guò)程名>（[參數(shù)1，參數(shù)2，…]）AS<{語(yǔ)句序列}>2、執(zhí)行存儲(chǔ)過(guò)程

CALL/PERFORMProcedure<過(guò)程名>（[參數(shù)1，參數(shù)2，…]）3、刪除存儲(chǔ)過(guò)程DROPProcedure<過(guò)程名>三、游標(biāo)和嵌入式SQL一樣，在PL/SQL中如果SELECT語(yǔ)句只返回一條記錄，可以將該結(jié)果存放到變量中。當(dāng)查詢(xún)返回多條記錄時(shí)，就要使用游標(biāo)對(duì)結(jié)果集進(jìn)行處理。一個(gè)游標(biāo)與一個(gè)SQL語(yǔ)句相關(guān)聯(lián)。8.3ODBC編程8.3.1數(shù)據(jù)庫(kù)互連概述提出和產(chǎn)生ODBC的原因是不同的數(shù)據(jù)庫(kù)管理系統(tǒng)的存在。目前廣泛使用的RDBMS有多種，盡管這些系統(tǒng)都是關(guān)系數(shù)據(jù)庫(kù)，也都遵循SQL標(biāo)準(zhǔn)，但不同的系統(tǒng)有許多差異。因此，在某個(gè)RDBMS下編寫(xiě)的應(yīng)用程序就不能在另一個(gè)RDBMS下運(yùn)行，適應(yīng)性和可移植性較差。更加重要的是，許多應(yīng)用程序需要共享多個(gè)部門(mén)的數(shù)據(jù)資源，訪問(wèn)不同的RDBMS。為此，用戶(hù)需要有連接不同RDBMS的方法和技術(shù)，使數(shù)據(jù)庫(kù)系統(tǒng)“開(kāi)放”，能夠“數(shù)據(jù)庫(kù)互連”。ODBC是微軟公司開(kāi)放服務(wù)體系（WindowsOpenServicesArchitecture,WOSA）中有關(guān)數(shù)據(jù)庫(kù)的一個(gè)組成部分，它建立了一組規(guī)范，并提供一組訪問(wèn)數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)API。作為規(guī)范它具有兩重功效：規(guī)范應(yīng)用開(kāi)發(fā)；規(guī)范RDBMS應(yīng)用接口。8.3.2ODBC工作原理概述ODBC開(kāi)發(fā)應(yīng)用系統(tǒng)的體系結(jié)構(gòu)，由四部分組成：一、應(yīng)用程序應(yīng)用程序提供用戶(hù)界面、應(yīng)用邏輯和事務(wù)邏輯。使用開(kāi)發(fā)數(shù)據(jù)庫(kù)應(yīng)用程序時(shí)，應(yīng)用程序調(diào)用的是標(biāo)準(zhǔn)的ODBC函數(shù)和SQL語(yǔ)句。應(yīng)用層使用ODBCAPI調(diào)用接口與數(shù)據(jù)庫(kù)進(jìn)行交互。ODBC應(yīng)用程序包括的內(nèi)容有：請(qǐng)求連接數(shù)據(jù)庫(kù)；向數(shù)據(jù)源發(fā)送SQL語(yǔ)句；為SQL語(yǔ)句執(zhí)行結(jié)果分配存儲(chǔ)空間定義所讀取的數(shù)據(jù)格式；獲取數(shù)據(jù)庫(kù)操作結(jié)果或處理錯(cuò)誤；進(jìn)行數(shù)據(jù)處理并向用戶(hù)提交處理結(jié)果；請(qǐng)求事務(wù)的提交和回滾操作；斷開(kāi)與數(shù)據(jù)源的連接。二、驅(qū)動(dòng)程序管理器驅(qū)動(dòng)程序管理是用來(lái)管理各種驅(qū)動(dòng)程序的。驅(qū)動(dòng)程序管理器是由微軟公司提供，它包含在ODBC32.DLL中，對(duì)用戶(hù)是透明的。它管理應(yīng)用程序和驅(qū)動(dòng)程序之間的通信。驅(qū)動(dòng)程序管理可以建立、配置或刪除數(shù)據(jù)源，并查看系統(tǒng)當(dāng)前所安裝的數(shù)據(jù)庫(kù)ODBC驅(qū)動(dòng)程序。三、數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序ODBC通過(guò)驅(qū)動(dòng)程序來(lái)提供應(yīng)用系統(tǒng)與數(shù)據(jù)庫(kù)平臺(tái)的獨(dú)立性。ODBC應(yīng)用程序不能直接存取數(shù)據(jù)庫(kù)，其各種操作請(qǐng)求由驅(qū)動(dòng)程序管理器提交給某個(gè)RDBMS的ODBC驅(qū)動(dòng)程序，通過(guò)調(diào)用驅(qū)動(dòng)程序支持的函數(shù)來(lái)存取數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)的操作結(jié)果也通過(guò)驅(qū)動(dòng)程序返回給應(yīng)用程序。四、ODBC數(shù)據(jù)源管理數(shù)據(jù)源是最終用戶(hù)需要訪問(wèn)的數(shù)據(jù)，包含了數(shù)據(jù)庫(kù)位置和數(shù)據(jù)庫(kù)類(lèi)型等信息，實(shí)際上是一種數(shù)據(jù)連接的抽象。ODBC給每個(gè)被訪問(wèn)的數(shù)據(jù)源指定唯一的數(shù)據(jù)源名（DataSourceName,DSN）。在連接中，用數(shù)據(jù)源名來(lái)代表用戶(hù)名、服務(wù)器名、所連接的數(shù)據(jù)庫(kù)名等。最終用戶(hù)無(wú)需知道DBMS或其他數(shù)據(jù)庫(kù)管理軟件