Oracle數(shù)據(jù)庫學(xué)習(xí)-數(shù)據(jù)庫8-DataBase

第八章數(shù)據(jù)庫編程8.1

嵌入式SQL8.2存儲(chǔ)過程8.3ODBC編程8.4JDBC8.5ADO簡(jiǎn)介8.1嵌入式SQLSQL語言提供了兩種不同的使用方式：交互式嵌入式將SQL語句嵌入到其它宿主語言編寫的程序中，作為宿主語言的子語言，使宿主語言具備訪問數(shù)據(jù)庫的能力。宿主語言：COBOL,C,Pascal,Fortran,VC++,VB,DEPHI,CB等能嵌入SQL語句的高級(jí)語言。宿主語言本身不能直接識(shí)別SQL語句。

為什么要引入嵌入式SQLSQL語言是非過程性語言事務(wù)處理應(yīng)用需要高級(jí)語言這兩種方式細(xì)節(jié)上有差別，在程序設(shè)計(jì)的環(huán)境下，SQL語句要做某些必要的擴(kuò)充。把SQL嵌入到其他高級(jí)語言中，必須解決的三個(gè)問題：語句識(shí)別——如何區(qū)分SQL語句和主語言語句。數(shù)據(jù)交換——由主語言提出的操作或數(shù)據(jù)，如何讓數(shù)據(jù)庫管理系統(tǒng)執(zhí)行，由數(shù)據(jù)庫查詢出的數(shù)據(jù)如何交由主語言處理。協(xié)調(diào)主語言的單記錄操作和數(shù)據(jù)庫的集合操作。8.1.1嵌入式SQL的處理過程對(duì)宿主型數(shù)據(jù)庫語言SQL(ESQL)，DBMS采用預(yù)編譯方法處理：

1．由DBMS的預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描，識(shí)別出SQL語句

2．把它們轉(zhuǎn)換成主語言調(diào)用語句，以使主語言編譯程序能識(shí)別它

3．最后由主語言的編譯程序?qū)⒄麄€(gè)源程序編譯成目標(biāo)碼。主語言程序含ESQL語句ESQL語句轉(zhuǎn)換為函數(shù)調(diào)用目標(biāo)語言程序RDBMS的預(yù)處理程序主語言編譯程序ESQL基本處理過程8.1.1嵌入式SQL的處理過程為了能夠區(qū)分SQL語句與主語言語句，所有的SQL語句必需要有前綴EXECSQL，以(;)結(jié)束,嵌入式SQL語句的一般形式

EXECSQL<SQL語句>;例：EXECSQLDROPTABLEStudent;8.1.2嵌入式SQL語句與主語言之間的通信將SQL嵌入到高級(jí)語言中混合編程，程序中會(huì)含有兩種不同計(jì)算模型的語句。SQL語句描述性的面向集合的語句負(fù)責(zé)操縱數(shù)據(jù)庫高級(jí)語言語句過程性的面向記錄的語句負(fù)責(zé)控制程序流程嵌入式SQL語句與主語言之間的通信主要是指：描述性面向集合與過程性面向紀(jì)錄（元組）語句之間的通信。

工作單元之間的通信方式SQL通信區(qū)向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息，主語言能夠據(jù)此控制程序流程。主變量

1）主語言向SQL語句提供參數(shù)。

2）將SQL語句查詢數(shù)據(jù)庫的結(jié)果交主語言進(jìn)一步處理。

游標(biāo)解決集合性操作語言與過程性操作語言的不匹配。一、

SQL通信區(qū)SQLCA：

SQLCommunicationArea是一個(gè)數(shù)據(jù)結(jié)構(gòu)——存放狀態(tài)信息供程序讀取。SQLCA的用途SQL語句執(zhí)行后，DBMS反饋給應(yīng)用程序信息描述系統(tǒng)當(dāng)前工作狀態(tài)描述運(yùn)行環(huán)境這些信息將送到SQL通信區(qū)SQLCA中，應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來執(zhí)行的語句定義SQLCA

用EXECSQLINCLUDESQLCA加以定義使用SQLCASQLCA中有一個(gè)存放每次執(zhí)行SQL語句后返回代碼的變量SQLCODE

；如果SQLCODE等于預(yù)定義的常量SUCCESS，則表示SQL語句成功，否則在SQLCODE存放錯(cuò)誤代碼；應(yīng)用程序每執(zhí)行完一條SQL語句之后都應(yīng)該測(cè)試一下SQLCODE的值，以了解該SQL語句執(zhí)行情況并做相應(yīng)處理。二、

主變量什么是主變量嵌入式SQL語句中可以使用主語言的程序變量來輸入或輸出數(shù)據(jù)，在SQL語句中使用的主語言程序變量簡(jiǎn)稱為主變量（HostVariable）主變量的類型輸入主變量---由應(yīng)用程序?qū)ζ滟x值，SQL語句引用。輸出主變量--由SQL語句賦值或設(shè)置狀態(tài)信息，返回給應(yīng)用程序。一個(gè)主變量有可能既是輸入主變量又是輸出主變量定義SQLCA

用EXECSQLINCLUDESQLCA加以定義使用SQLCASQLCA中有一個(gè)存放每次執(zhí)行SQL語句后返回代碼的變量SQLCODE；如果SQLCODE等于預(yù)定義的常量SUCCESS，則表示SQL語句成功，否則在SQLCODE

存放錯(cuò)誤代碼；應(yīng)用程序每執(zhí)行完一條SQL語句之后都應(yīng)該測(cè)試一下SQLCODE的值，以了解該SQL語句執(zhí)行情況并做相應(yīng)處理。指示變量一個(gè)主變量可以附帶一個(gè)指示變量（IndicatorVariable）什么是指示變量整型變量用來“指示”所指主變量的值或條件。指示變量的用途指示變量可以輸入主變量是否為空值指示變量可以檢測(cè)輸出主變量是否為空值，值是否被截?cái)唷Ｔ赟QL語句中使用主變量和指示變量的方法1)說明主變量和指示變量BEGINDECLARESECTION ......... .........(說明主變量和指示變量)

ENDDECLARESECTION2)使用主變量說明之后的主變量可以在SQL語句中任何一個(gè)能夠使用表達(dá)式的地方出現(xiàn)。為了與數(shù)據(jù)庫對(duì)象名（表名、視圖名、列名等）區(qū)別，SQL語句中的主變量名前要加冒號(hào)（:）作為標(biāo)志3)使用指示變量指示變量前也必須加冒號(hào)標(biāo)志必須緊跟在所指主變量之后在SQL語句之外(主語言語句中)使用主變量和指示變量的方法：可以直接引用，不必加冒號(hào)。三、游標(biāo)（cursor）1、為什么要使用游標(biāo)SQL語言與主語言具有不同數(shù)據(jù)處理方式SQL語言是面向集合的，一條SQL語句原則上可以產(chǎn)生或處理多條記錄主語言是面向記錄的，一組主變量一次只能存放一條記錄僅使用主變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求嵌入式SQL引入了游標(biāo)的概念，用來協(xié)調(diào)這兩種不同的處理方式2、什么是游標(biāo)游標(biāo)是系統(tǒng)為用戶開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果每個(gè)游標(biāo)區(qū)都有一個(gè)名字用戶可以用SQL語句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語言進(jìn)一步處理。四、

建立和關(guān)閉數(shù)據(jù)庫連接1、建立數(shù)據(jù)庫連接嵌入式SQL要訪問數(shù)據(jù)庫必須先連接數(shù)據(jù)庫，DBMS根據(jù)用戶信息對(duì)連接請(qǐng)求進(jìn)行合法性驗(yàn)證，只有通過了身份驗(yàn)證，才能建立一個(gè)可用的合法連接。其語句是：EXECSQLCONNECTTOtarget[ASconnection-name][USERuser-name];Target:是連接數(shù)據(jù)庫的數(shù)據(jù)庫服務(wù)器，它可以是一個(gè)常見的服務(wù)器，或是包含服務(wù)器標(biāo)識(shí)的SQL串常量，也可以是DEFAULT。connection-name：是可選擇的連接名，連接必須是一個(gè)有效標(biāo)識(shí)符，主要用來標(biāo)識(shí)整個(gè)程序內(nèi)同時(shí)建立多個(gè)連接，如果整個(gè)程序中只有一個(gè)連接也可以不指定連接名。如果程序運(yùn)行過程中建立了多個(gè)連接，執(zhí)行所有的數(shù)據(jù)庫單元的工作都在該操作提交時(shí)所選擇的當(dāng)前連接上。程序運(yùn)行過程中可以修改當(dāng)前連接，對(duì)應(yīng)的嵌入式SQL語句是：EXECSQLSETCONNECTTOconnection-name|DEFAULT2、關(guān)閉數(shù)據(jù)庫連接當(dāng)某個(gè)連接上的所有數(shù)據(jù)庫操作完成后，應(yīng)用程序應(yīng)該主動(dòng)釋放所占用的連接資源。EXECSQLDISCONNECT[connection];connection：是EXECSQLCONNECT所建立的數(shù)據(jù)庫連接五、程序?qū)嵗齕例1]：依次檢查某個(gè)系學(xué)生的記錄，交互式更新某些學(xué)生的年齡。EXECSQLBEGINDECLARESECTION;/*說明主變量*/

CHARdeptname(20);CHARHSno(9);CHARHSname(20);CHARHSeex(2);INTHSage;INTNEWAGE;EXECSQLENDDECLARESECTION;LongSQLCODE;EXECSQLINCLUDESQLCA;/*定義SQL通信區(qū)*/main(void){intcount=0;charyn;

printf(“Pleasechoosethedepartmentname(CS/MA/IS):”)；

scanf(“%s”,&deptname);EXECSQLCONNECTTOTEST@localhost:54321USER”SYSTEM”/”MANAGER”；

EXECSQLDECLARESXCURSORFOR/*定義游標(biāo)SX*/SELECTSno,Sname,Seex,Sage

/*SX對(duì)應(yīng)的語句*/FROMStudentWHERESdept=:deptname;

EXECSQLOPENSX;

/*打開游標(biāo)SX，指向查詢結(jié)果的第一行*/

for(;;){EXECSQLFETCHSXINTO:Hsno,:Hsname,:Hseex,:HSage;

/*將當(dāng)前數(shù)據(jù)放入主變量并推進(jìn)游標(biāo)指針*/if(sqlca.sqlcode<>SUCCESS)

/*利用SQLCA中的狀態(tài)信息決定何時(shí)退出循環(huán)*/break;if(count++==0)

printf(“\n%-10s%-20s%-10s,%10s\n”,

“Sno”,“Sname”,“Ssex”,“Sage”);

/*如果是第一行,則打印行頭*/

printf("%-10s%-20s%-10s,%-10d",

Sno,Sname,Ssex,Sage);/*顯示該記錄*/

printf("UPDATEAGE(y/n)?");/*問用戶是否要修改*/

Do{scanf("%c",&yn);}

While(yn!='n'&&yn!=‘N'&&yn!=‘y'&&yn!=‘Y');if(yn=‘y’||yn=‘Y’)/*需要修改*/

{printf("INPUTNEWAGE:");

scanf("%d",&NEWAge);/*輸入新的年齡值*/

EXECSQLUPDATEStudentSETSage=:NEWAgeWHERECURRENTOFSX;}/*修改當(dāng)前記錄的年齡字段*/}EXECSQLCLOSESX;

/*關(guān)閉游標(biāo)*/

EXECSQLCOMMITWORK/*提交更新*/EXECSQLDISCONNECTTEST/*斷開數(shù)據(jù)庫連接*/}8.1.3不用游標(biāo)的SQL語句不用游標(biāo)的SQL語句的種類說明性語句數(shù)據(jù)定義語句數(shù)據(jù)控制語句查詢結(jié)果為單記錄的SELECT語句非CURRENT形式的UPDATE語句非CURRENT形式的DELETE語句

INSERT語句一、說明性語句說明性語句是專為在嵌入式SQL中說明主變量、SQLCA等而設(shè)置的。說明主變量1.EXECSQLBEGINDECLARESECTION;2.EXECSQLENDDECLARESECTION;這兩條語句必須配對(duì)出現(xiàn)，相當(dāng)于一個(gè)括號(hào)，兩條語句中間是主變量的說明說明SQLCA3.EXECSQLINCLUDESQLCA二、數(shù)據(jù)定義語句例[2]建立一個(gè)“學(xué)生”表Student

EXECSQLCREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,

SnameCHAR(20),

SsexCHAR(1),SageINT,

SdeptCHAR(15));注：數(shù)據(jù)定義語句中不允許使用主變量例：下列語句是錯(cuò)誤的

EXECSQLDROP TABLE:table_name;三、數(shù)據(jù)控制語句例[3]把查詢Student表權(quán)限授給用戶U1

EXECSQLGRANTSELECTONTABLEStudentTOU1;四、查詢結(jié)果為單記錄的SELECT語句語句格式EXECSQLSELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]...

INTO<主變量>[<指示變量>][,<主變量>[<指示變量>]]...FROM<表名或視圖名>[,<表名或視圖名>]...[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];對(duì)交互式SELECT語句的擴(kuò)充就是多了一個(gè)INTO子句。把從數(shù)據(jù)庫中找到的符合條件的記錄，放到INTO子句指出的主變量中去。

使用注意事項(xiàng)

1.使用主變量

INTO子句

WHERE子句的條件表達(dá)式

HAVING短語的條件表達(dá)式2.使用指示變量指示變量只能用于INTO子句中如果INTO子句中主變量后面跟有指示變量，則當(dāng)查詢得出的某個(gè)數(shù)據(jù)項(xiàng)為空值時(shí)，系統(tǒng)會(huì)自動(dòng)將相應(yīng)主變量后面的指示變量置為負(fù)值，但不向該主變量執(zhí)行賦值操作，即主變量值仍保持執(zhí)行SQL語句之前的值當(dāng)發(fā)現(xiàn)指示變量值為負(fù)值時(shí)，不管主變量為何值，均應(yīng)認(rèn)為主變量值為NULL3.查詢結(jié)果為空集如果數(shù)據(jù)庫中沒有滿足條件的記錄，即查詢結(jié)果為空，則DBMS將SQLCODE的值置為1004.查詢結(jié)果為多條記錄程序出錯(cuò)，DBMS會(huì)在SQLCA中返回錯(cuò)誤信息例[4]根據(jù)學(xué)生號(hào)碼查詢學(xué)生信息。假設(shè)已將要查詢的學(xué)生的學(xué)號(hào)賦給了主變量givensnoEXECSQLSELECTSno,Sname,Ssex,Sage,SdeptINTO:Hsno,:Hname,:Hsex,:Hage,:HdeptFROMStudentWHERESno=:givensno;Hsno,Hname,Hsex,Hage,Hdept和givensno均是主變量，并均已在前面的程序中說明過了。例[5]查詢某個(gè)學(xué)生選修某門課程的成績(jī)。假設(shè)已將要查詢的學(xué)生的學(xué)號(hào)賦給了主變量givensno，將課程號(hào)賦給了主變量givencno。

EXECSQLSELECTSno,Cno,GradeINTO:Hsno,:Hcno,:Hgrade:GradeidFROMSCWHERESno=:givensnoAND

Cno=:givencno;從提高應(yīng)用程序的數(shù)據(jù)獨(dú)立性角度考慮，SELECT語句在任何情況下都應(yīng)該使用游標(biāo)對(duì)于僅返回一行結(jié)果數(shù)據(jù)的SELECT語句雖然可以不使用游標(biāo)但如果以后數(shù)據(jù)庫改變了，該SELECT語句可能會(huì)返回多行數(shù)據(jù)，這時(shí)該語句就會(huì)出錯(cuò)五、非CURRENT形式的增刪改語句非CURRENT形式的UPDATE語句使用主變量

SET子句

WHERE子句使用指示變量

SET子句非CURRENT形式的UPDATE語句可以操作多條元組例[6]修改某個(gè)學(xué)生1號(hào)課程的成績(jī)。假設(shè)該學(xué)生的學(xué)號(hào)已賦給主變量givensno，修改后的成績(jī)已賦給主變量newgrade。

EXECSQLUPDATESCSETGrade=:newgradeWHERESno=:givensno

and

Cno='1';例[7]將計(jì)算機(jī)系全體學(xué)生年齡置NULL值

Sageid=-1;EXECSQLUPDATEStudentSETSage=:Raise:SageidWHERESdept='CS';將指示變量Sageid賦一個(gè)負(fù)值后，無論主變量Raise為何值，DBMS都會(huì)將CS系所有記錄的年齡屬性置空值。它等價(jià)于：

EXECSQLUPDATEStudentSETSage=NULLWHERESdept='CS';例[8]某個(gè)學(xué)生退學(xué)了，現(xiàn)要將有關(guān)他的所有選課記錄刪除掉。假設(shè)該學(xué)生的姓名已賦給主變量stdname

EXECSQLDELETEFROMSCWHERESno=(SELECTSnoFROMStudentWHERESname=:stdname);例[9]某個(gè)學(xué)生新選修了某門課程，將有關(guān)記錄插入SC表假設(shè)學(xué)生的學(xué)號(hào)已賦給主變量stdno，課程號(hào)已賦給主變量couno。

gradeid=-1;EXECSQLINSERTINTOSC(Sno,Cno,Grade)

VALUES(:stdno,:couno,:gr:gradeid);由于該學(xué)生剛選修課程，尚未考試，因此成績(jī)列為空。所以本例中用指示變量指示相應(yīng)的主變量為空值。8.1.4使用游標(biāo)的SQL語句必須使用游標(biāo)的SQL語句查詢結(jié)果為多條記錄的SELECT語句CURRENT形式的UPDATE語句CURRENT形式的DELETE語句一、查詢結(jié)果為多條記錄的SELECT語句使用游標(biāo)的步驟：1.說明游標(biāo)2.打開游標(biāo)3.移動(dòng)游標(biāo)指針，然后取當(dāng)前記錄4.關(guān)閉游標(biāo)1.說明游標(biāo)語句格式

EXECSQLDECLARE<游標(biāo)名>CURSORFOR<SELECT語句>;功能：是一條說明性語句，這時(shí)DBMS并不執(zhí)行SELECT指定的查詢操作。2.打開游標(biāo)語句格式

EXECSQLOPEN<游標(biāo)名>;功能：打開游標(biāo)實(shí)際上是執(zhí)行相應(yīng)的SELECT語句，把所有滿足查詢條件的記錄從指定表取到緩沖區(qū)中，這時(shí)游標(biāo)處于活動(dòng)狀態(tài)，指針指向查詢結(jié)果集中第一條記錄之前。3.移動(dòng)游標(biāo)指針，然后取當(dāng)前記錄語句格式EXECSQLFETCH[[NEXT|PRIOR|FIRST|LAST]FROM]<游標(biāo)名>

INTO<主變量>[<指示變量>][,<主變量>[<指示變量>]]...;功能：指定方向推動(dòng)游標(biāo)指針，然后將緩沖區(qū)中的當(dāng)前記錄取出來送至主變量供主語言進(jìn)一步處理。

NEXT|PRIOR|FIRST|LAST：指定推動(dòng)游標(biāo)指針的方式。NEXT：向前推進(jìn)一條記錄（缺省值）PRIOR：向回退一條記錄FIRST：推向第一條記錄LAST：推向最后一條記錄說明(1)主變量必須與SELECT語句中的目標(biāo)列表達(dá)式具有一一對(duì)應(yīng)關(guān)系(2)FETCH語句通常用在一個(gè)循環(huán)結(jié)構(gòu)中，通過循環(huán)執(zhí)行FETCH語句逐條取出結(jié)果集中的行進(jìn)行處理(3)為進(jìn)一步方便用戶處理數(shù)據(jù)，現(xiàn)在一些關(guān)系數(shù)據(jù)庫管理系統(tǒng)對(duì)FETCH語句做了擴(kuò)充，允許用戶向任意方向以任意步長(zhǎng)移動(dòng)游標(biāo)指針。4.關(guān)閉游標(biāo)語句格式

EXECSQLCLOSE<游標(biāo)名>;功能關(guān)閉游標(biāo)，釋放結(jié)果及占用的緩沖區(qū)及其他資源。說明游標(biāo)被關(guān)閉后，就不再和原來的查詢結(jié)果集相聯(lián)系被關(guān)閉的游標(biāo)可以再次被打開，與新的查詢結(jié)果相聯(lián)系。二、CURRENT形式的UPDATE語句和DELETE語句CURRENT形式的UPDATE語句和DELETE語句的用途.CURRENT形式的UPDATE語句和DELETE語句面向集合的操作;

一次修改或刪除所有滿足條件的記錄.如果只想修改或刪除其中某個(gè)記錄；用帶游標(biāo)的SELECT語句查出所有滿足條件的記錄；從中進(jìn)一步找出要修改或刪除的記錄；用CURRENT形式的UPDATE語句和DELETE語句修改或刪除之。步驟(1)DECLARE說明游標(biāo)(2)OPEN

打開游標(biāo)，把所有滿足查詢條件的記錄從指定表取至緩沖區(qū)(3)FETCH推進(jìn)游標(biāo)指針，并把當(dāng)前記錄從緩沖區(qū)中取出來送至主變量(4)檢查該記錄是否是要修改或刪除的記錄，是則處理之(5)重復(fù)第(3)和(4)步，用逐條取出結(jié)果集中的行進(jìn)行判斷和處理(6)CLOSE

關(guān)閉游標(biāo)，釋放結(jié)果集占用的緩沖區(qū)和其他資源說明游標(biāo)為UPDATE語句說明游標(biāo)語句格式

EXECSQLDECLARE<游標(biāo)名>CURSORFOR<SELECT語句>FORUPDATEOF<列名>;說明：

FORUPDATEOF<列名>短語用于指明檢索出的數(shù)據(jù)在指定列上是可修改的，以便DBMS進(jìn)行并發(fā)控制為DELETE語句說明游標(biāo)語句格式

EXECSQLDECLARE<游標(biāo)名>CURSORFOR<SELECT語句>FORUPDATE;說明：FORUPDATE短語提示DBMS進(jìn)行并發(fā)控制修改或刪除當(dāng)前記錄經(jīng)檢查緩沖區(qū)中記錄是要修改或刪除的記錄，則用UPDATE語句或DELETE語句修改或刪除該記錄語句格式<UPDATE語句>WHERECURRENTOF<游標(biāo)名><DELETE語句>WHERECURRENTOF<游標(biāo)名>WHERECURRENTOF<游標(biāo)名