數(shù)據(jù)庫(kù)系統(tǒng)概論(王珊第四版)-第八章-

第八章數(shù)據(jù)庫(kù)編程嵌入式SQL存儲(chǔ)過(guò)程

ODBC編程8.1嵌入式SQL引入嵌入式SQL的原因SQL語(yǔ)言是非過(guò)程性語(yǔ)言，缺少流程控制能力；8.1.1嵌入式SQL的處理過(guò)程嵌入式SQL將SQL語(yǔ)言嵌入程序設(shè)計(jì)語(yǔ)言即宿主語(yǔ)言中，將SQL語(yǔ)言訪問(wèn)數(shù)據(jù)庫(kù)的功能和宿主語(yǔ)言的數(shù)據(jù)處理功能相結(jié)合，這樣使用的SQL稱為嵌入式SQL(Embedded

SQL,ESQL)嵌入式SQL的基本處理過(guò)程主語(yǔ)言程序含ESQL語(yǔ)句RDMS的預(yù)處理程序ESQL語(yǔ)句轉(zhuǎn)換為函數(shù)主語(yǔ)言編譯程序目標(biāo)語(yǔ)言程序?yàn)榱藚^(qū)分SQL語(yǔ)句與主語(yǔ)言語(yǔ)句，所有SQL語(yǔ)句都需要加前綴，一般格式為：EXEC

SQL

<SQL語(yǔ)句>；例：EXEC

SQL

DROP

TABLE

Student

；一般形式8.1.2嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信SQL語(yǔ)句:描述性的面向集合的語(yǔ)句-負(fù)責(zé)操縱數(shù)據(jù)庫(kù)高級(jí)語(yǔ)言語(yǔ)句:過(guò)程性的面向記錄的語(yǔ)句-負(fù)責(zé)控制程序流程工作單元之間的通信方式SQL通信區(qū)向主語(yǔ)言傳遞SQL語(yǔ)句的執(zhí)行狀態(tài)信息主語(yǔ)言能夠據(jù)此控制程序流程主變量主語(yǔ)言向SQL語(yǔ)句提供參數(shù)將SQL語(yǔ)句查詢數(shù)據(jù)庫(kù)的結(jié)果交主語(yǔ)言進(jìn)一步處理游標(biāo)解決集合性操作語(yǔ)言與過(guò)程性操作語(yǔ)言的不匹配定義SQLCA：EXEC

SQL

INCLUDE

SQLCA使用SQLCA：SQLCA中有一個(gè)存放每次執(zhí)行SQL語(yǔ)句后返回代碼的變量SQLCODE如果SQLCODE等于預(yù)定義的常量SUCCESS，則表示

SQL語(yǔ)句成功，否則表示出錯(cuò)應(yīng)用程序每執(zhí)行完一條SQL語(yǔ)句之后都應(yīng)該測(cè)試一下

SQLCODE的值，以了解該SQL語(yǔ)句執(zhí)行情況并做相應(yīng)處理一、SQL通信區(qū)（SQL

Communication

Area,SQLCA）二、主變量（

Host

Variable）主變量的分類(lèi)與作用輸入主變量：由應(yīng)用程序賦值，SQL引用。輸出主變量：SQL對(duì)其賦值或設(shè)置狀態(tài)信息，返回給應(yīng)用程序。SQL語(yǔ)句使用宿主語(yǔ)言的程序變量來(lái)輸入/輸出數(shù)據(jù)，這樣的變量簡(jiǎn)稱為主變量.指示變量:整型變量用來(lái)“指示”所指主變量的值或條件用途:輸入主變量可以利用指示變量賦空值輸出主變量可以利用指示變量檢測(cè)出是否空值，值是否被截?cái)嘣赟QL語(yǔ)句中說(shuō)明主變量和指示變量BEGIN

DECLARE

SECTION.................. (說(shuō)明主變量和指示變量).........END

DECLARE

SECTION在SQL語(yǔ)句中使用主變量SQL語(yǔ)句中的主變量名前要加冒號(hào)（:）作為標(biāo)志在SQL語(yǔ)句使用指示變量指示變量前也必須加冒號(hào)（:）標(biāo)志必須緊跟在所指主變量之后例如：:Hgrade

:Gradeid例:根據(jù)學(xué)生號(hào)碼查詢學(xué)生信息。假設(shè)已將要查詢的學(xué)生的學(xué)號(hào)給了主變量givensnoEXEC

SQL

SELECT

Sno,

Sname,

Ssex,

Sage,

SdeptINTO

:Hsno,

:Hname,

:Hsex,

:Hage,

:HdeptFROM

StudentWHERE

Sno=:givensno;Hsno,Hname,Hsex,Hage,Hdept和givensno均是主變量，并均已在前面的程序中說(shuō)明了。三、游標(biāo)（Cursor）為什么要使用游標(biāo)當(dāng)查詢結(jié)果超過(guò)一個(gè)元組時(shí)，不能一次性將結(jié)果值賦給宿主語(yǔ)言的變量，因?yàn)橹髯兞績(jī)H能保存一個(gè)數(shù)據(jù)，而不是一組數(shù)據(jù)。游標(biāo)的概念游標(biāo)是系統(tǒng)為用戶開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL的執(zhí)行結(jié)果。?游標(biāo)的定義和使用定義游標(biāo)EXEC

SQL

DECLARE

SX

CURSOR

FORSELECT

Sno,Sname,Ssex,Sage

/*SX對(duì)應(yīng)語(yǔ)句的執(zhí)行結(jié)果FROM

StudentWHERE

SDept

=

:deptname;打開(kāi)游標(biāo)EXEC

SQL

OPEN

SX;執(zhí)行SELECT語(yǔ)句，將結(jié)果放入結(jié)果集中。推進(jìn)游標(biāo)EXEC

SQL

FETCH

SX

INTO

:HSno,

….;移動(dòng)指針，改變結(jié)果集的當(dāng)前記錄。通過(guò)游標(biāo)更新數(shù)據(jù)關(guān)閉游標(biāo)四、建立和關(guān)閉數(shù)據(jù)庫(kù)連接建立數(shù)據(jù)庫(kù)連接：EXEC

SQL

CONNECT

TO

target

[AS

connection-name][USER

user-name]target－要連接的數(shù)據(jù)庫(kù)服務(wù)器?？梢允且粋€(gè)常見(jiàn)的服務(wù)器標(biāo)識(shí)串，如dbname@<hostname>:<port>;或是包含服務(wù)器標(biāo)識(shí)的SQL串常量，也可以是DEFAULT。connection-name－可選的連接名，連接必須是一個(gè)有效的標(biāo)識(shí)符，用來(lái)識(shí)別一個(gè)程序內(nèi)同時(shí)建立的多個(gè)連接。關(guān)閉數(shù)據(jù)庫(kù)連接：EXEC

SQL

DISCONNECT

[

connection]修改當(dāng)前連接EXEC

SQL

SET

CONNECT

connection-name|DEFAULT五、舉例：帶有嵌入式SQL的一小段C程序/*主變量說(shuō)明開(kāi)始*//*主變量說(shuō)明結(jié)束*/EXEC

SQL

BEGIN

DEC

LARE

SECTION;char

deptname[64];char

HSno[64];char

HSname[64];char

HSsex[64];int

HSage;int

NEWAGE;EXEC

SQL

END

DECLARE

SECTION;long

SQLCODE;:

EXEC

SQL

INCLUDE

sqlca;/*定義SQL通信區(qū)*/例題：依次檢查某個(gè)系的學(xué)生記錄，交互式更新某些學(xué)生年齡/*C語(yǔ)言主程序開(kāi)/*變量yn代表yes或noint

main(void){

int

count

=

0;char

yn;printf("Please

choose

the

department

name(CS/MA/IS):

");scanf("%s",deptname);

/*為主變量deptname賦EXEC

SQL

CONNECT

TO

TEST@localhost:54321

USER"SYSTEM"/"MANAGER";

/*連接數(shù)據(jù)庫(kù)TEST*/18EXEC

SQL

DECLARE

SX

CURSOR

FOR/*定義游標(biāo)*CSELECT

Sno,

Sname,

Ssex,

Sage/*SX對(duì)應(yīng)語(yǔ)句的執(zhí)行結(jié)DFROM

Student21WHERE

Sdept

=

:deptname;22

EXEC

SQL

OPEN

SX;*//*打開(kāi)游標(biāo)SX便指向查詢結(jié)果的第一行G for(;;)

/*用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄*/24

{

EXEC

SQL

FETCH

SX

INTO

:HSno,

:HSname,

:HSsex,:HSage;/*推進(jìn)游標(biāo)，將當(dāng)前數(shù)據(jù)放入主變量*/。。。。。。。。。。。。。If

(yn

==

‘y’

||yn

==

‘Y’){。。。。。。。。。。。。。/*嵌入式SQL更新語(yǔ)句*373839EXEC

SQL

UPDATE

StudentSET

Sage

=

:NEWAGEWHERE

CURRENT

OF

SX

;}

/*對(duì)當(dāng)前游標(biāo)指向的學(xué)生年齡進(jìn)行更新*//*關(guān)閉游標(biāo)SX不再和查詢結(jié)果對(duì)應(yīng)*/}EXEC

SQL

CLOSE

SX;EXEC

SQL

COMMIT

WORK;/*提交更新*//*斷開(kāi)數(shù)據(jù)庫(kù)連接*/42

EXEC

SQL

DISCONNECT

TEST;}1

EXEC

SQL

BEGIN

DEC

LARE

SECTION;8

EXEC

SQL

END

DECLARE

SECTION;:

EXEC

SQL

INCLUDE

sqlca;/*主變量說(shuō)明開(kāi)始*//*主變量說(shuō)明結(jié)束*//*定義SQL通信區(qū)*/C語(yǔ)言程序開(kāi)始/*定義游標(biāo)*/EXEC

SQL

DECLARE

SX

CURSOR

FOREXEC

SQL

OPEN

SX;EXEC

SQL

CLOSE

SX;16

EXEC

SQL

CONNECT

TO

。。。。

/*連接數(shù)據(jù)庫(kù)TEST*/18F4041EXEC

SQL

COMMIT

WORK;EXEC

SQL

DISCONNECT

TEST;/*提交更新*//*斷開(kāi)數(shù)據(jù)庫(kù)連接*/C語(yǔ)言程序結(jié)束程序框架8.1.3不用游標(biāo)的SQL語(yǔ)句不用游標(biāo)的SQL語(yǔ)句的種類(lèi)：說(shuō)明性語(yǔ)句數(shù)據(jù)定義語(yǔ)句數(shù)據(jù)控制語(yǔ)句查詢結(jié)果為單記錄的SELECT語(yǔ)句非CURRENT形式的增刪改語(yǔ)句一、查詢結(jié)果為單記錄的SELECT語(yǔ)句語(yǔ)句格式：EXEC

SQL

SELECT

[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]...INTO <主變量>[<指示變量>]

[,<主變量>[<指示變量>]]..FROM <表名或視圖名>[,<表名或視圖名>]

...[WHERE<條件表達(dá)式>][GROUP

BY<列名1>[HAVING<條件表達(dá)式>]][ORDER

BY<列名2>[ASC|DESC]];例1：根據(jù)學(xué)生號(hào)碼查詢學(xué)生信息。假設(shè)已將要查詢的學(xué)生的學(xué)賦給了主變量givensnoEXEC

SQL

SELECT

Sno,

Sname,

Ssex,

Sage,

Sdept

INTO

:Hsno,

:Hname,

:Hsex,

:Hage,

:HdeptFROM

StudentWHERE

Sno=:givensno;Hsno,Hname,Hsex,Hage,Hdept和givensno均是主變量，并均已在前面的程序中說(shuō)明了。使用注意事項(xiàng)：查詢結(jié)果為多條記錄的處理程序出錯(cuò)，RDBMS會(huì)在SQLCA中返回錯(cuò)誤信息使用主變量INTO子句、WHERE子句的條件表達(dá)式、HAVING短語(yǔ)的條件表達(dá)式均可以使用主變量使用指示變量指示變量只能用于INTO子句中。查詢結(jié)果為空值的處理如果數(shù)據(jù)庫(kù)中沒(méi)有滿足條件的記錄，系統(tǒng)會(huì)自動(dòng)將相應(yīng)主變量后面的指示變量置為負(fù)值，而不再向該主變量賦值；當(dāng)發(fā)現(xiàn)指示變量值為負(fù)值時(shí)，不管主變量為何值，均應(yīng)認(rèn)為主變量值為NULL例2：查詢某個(gè)學(xué)生選修某門(mén)課程的成績(jī)。假設(shè)已將要查詢的學(xué)生的學(xué)號(hào)賦給了主變量givensno，將課程號(hào)賦給了主變量givencno。EXEC

SQL

SELECT

Sno,

Cno,

GradeINTO

:Hsno,

:Hcno,

:Hgrade:GradeidFROM

SCWHERE

Sno=:givensno

ANDCno=:givencno;如果Gradeid<0，不論Hgrade為何值，均認(rèn)為該學(xué)生成績(jī)?yōu)榭罩?！二、非CURRENT形式的增刪語(yǔ)句1.非CURRENT形式的UPDATE語(yǔ)句：使用主變量——SET子句、WHERE子句使用指示變量——SET子句非CURRENT形式的UPDATE語(yǔ)句可以操作多條元組例1：將全體學(xué)生1號(hào)課程的考試成績(jī)?cè)黾尤舾煞?。假設(shè)增加的分?jǐn)?shù)已賦給主變量RaiseEXEC

SQL

UPDATE

SCSET

Grade=Grade+:RaiseWHERE

Cno="1";例2：修改某個(gè)學(xué)生1號(hào)課程的成績(jī)。假設(shè)該學(xué)生的學(xué)號(hào)已賦給主變量givensno，修改后的成績(jī)已賦給主變量newgrade。EXEC

SQL

UPDATE

SCSET

Grade=:newgradeWHERE

Sno=:givensno

AND

Cno="1";例3：將計(jì)算機(jī)系全體學(xué)生年齡置NULL值Sageid=-1;EXEC

SQL

UPDATE

StudentSET

Sage=:Raise:SageidWHERE

Sdept="CS";2.非CURRENT形式的DELETE語(yǔ)句使用主變量——WHERE子句非CURRENT形式的DELETE語(yǔ)句可以操作多條元組例4：某個(gè)學(xué)生退學(xué)了，現(xiàn)要將有關(guān)他的所有選課記錄刪除掉。假設(shè)該學(xué)生的姓名已賦給主變量stdnameEXEC

SQL

DELETEFROM

SCWHERE

Sno=(SELECT

SnoFROM

StudentWHERE

Sname=:stdname);3.非CURRENT形式的INSERT語(yǔ)句使用主變量——VALUES子句使用指示變量——VALUES子句非CURRENT形式的INSERT語(yǔ)句一次只能輸入一條元組例5：某個(gè)學(xué)生新選修了某門(mén)課程，將有關(guān)記錄插入SC表，假設(shè)學(xué)生的學(xué)號(hào)已賦給主變量stdno，課程號(hào)已賦給主變量couno。gradeid=-1;

/*用作指示變量，賦為負(fù)值*/EXEC

SQL

INSERTINTO

SC(Sno,

Cno,

Grade)VALUES(:stdno,

:couno,

:gr:gradeid);8.1.4使用游標(biāo)的SQL語(yǔ)句必須使用游標(biāo)的SQL語(yǔ)句：查詢結(jié)果為多條記錄的SELECT語(yǔ)句

CURRENT形式的UPDATE、DELETE語(yǔ)句一、查詢結(jié)果為多條記錄的SELECT語(yǔ)句使用游標(biāo)的步驟：說(shuō)明游標(biāo)打開(kāi)游標(biāo)移動(dòng)游標(biāo)指針，然后取當(dāng)前記錄關(guān)閉游標(biāo)說(shuō)明游標(biāo)使用DECLARE語(yǔ)句:EXEC

SQL

DECLARE<游標(biāo)名>CURSORFOR

<SELECT語(yǔ)句>;功能:是一條說(shuō)明性語(yǔ)句，這時(shí)DBMS并不執(zhí)行SELECT指定的查詢操作。打開(kāi)游標(biāo):使用OPEN語(yǔ)句EXEC

SQL

OPEN<游標(biāo)名>;功能:打開(kāi)游標(biāo)實(shí)際上是執(zhí)行相應(yīng)的SELECT語(yǔ)句，把所有滿足查詢條件的記錄從指定表取到緩沖區(qū)中。這時(shí)游標(biāo)處于活動(dòng)狀態(tài)，指針指向查詢結(jié)果集中第一條記錄之前3.推進(jìn)游標(biāo)指針并取當(dāng)前記錄:使用FETCH語(yǔ)句EXEC

SQL

FETCH

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

NEXT|PRIOR|FIRST|LAST：指定推動(dòng)游標(biāo)指針的方式，缺省值為NEXT4.關(guān)閉游標(biāo):使用CLOSE語(yǔ)句EXEC

SQL

CLOSE<游標(biāo)名>;功能:關(guān)閉游標(biāo)，釋放結(jié)果集占用的緩沖區(qū)及其他資源說(shuō)明：游標(biāo)被關(guān)閉后，就不再和原來(lái)的查詢結(jié)果集相聯(lián)系。被關(guān)閉的游標(biāo)可以再次被打開(kāi)，與新的查詢結(jié)果相聯(lián)系例1：查詢某個(gè)系全體學(xué)生的信息（學(xué)號(hào)、姓名、性別和年齡）。要查詢的系名由用戶在程序運(yùn)行過(guò)程中指定，放在主變量deptname中......EXEC

SQL

INCLUDE

SQLCA;EXEC

SQL

BEGIN

DECLARE

SECTION;....../*說(shuō)明主變量deptname,HSno,HSname,HSsex,HSage等*/......EXEC

SQL

END

DECLARE

SECTION;......gets(deptname);

/*為主變量deptname賦值*/EXEC

SQL

DECLARE

SX

CURSOR

FORSELECT

Sno,

Sname,

Ssex,

SageFROM

StudentWHERE

SDept=:deptname;

/*說(shuō)明游標(biāo)*/EXEC

SQL

OPEN

SX/*打開(kāi)游標(biāo)*/WHILE(1)/*用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄*/{

EXEC

SQL

FETCH

SX

INTO

:HSno,

:HSname,

:HSsex,

:HSagif

(sqlca.sqlcode

<>

SUCCESS)break;/*若所有查詢結(jié)果均已處理完或出現(xiàn)SQL語(yǔ)句錯(cuò)誤，則退出循環(huán)

*//*由主語(yǔ)言語(yǔ)句進(jìn)行進(jìn)一步處理

*/......};EXEC

SQL

CLOSE

SX;

/*關(guān)閉游標(biāo)*/......二、CURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句CURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句的用途用帶游標(biāo)的SELECT語(yǔ)句查出所有滿足條件的記錄進(jìn)一步找出要修改或刪除的記錄用CURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句修改或刪除之步驟DECLARE：說(shuō)明游標(biāo)OPEN：打開(kāi)游標(biāo)，把所有滿足查詢條件的記錄從指定表取至緩沖區(qū)FETCH：推進(jìn)游標(biāo)指針，并把當(dāng)前記錄從緩沖區(qū)中取出來(lái)送至主變量檢查該記錄是否是要修改或刪除的記錄，是則處理之重復(fù)第(3)和(4)步，用逐條取出結(jié)果集中的行進(jìn)行判斷和理CLOSE：關(guān)閉游標(biāo)，釋放結(jié)果集占用的緩沖區(qū)和其他資源說(shuō)明游標(biāo)：為UPDATE語(yǔ)句說(shuō)明游標(biāo)EXEC

SQL

DECLARE<游標(biāo)名>CURSOR

FOR<SELECT語(yǔ)句>FOR

UPDATE

OF<列名>;為DELETE語(yǔ)句說(shuō)明游標(biāo)EXEC

SQL

DECLARE<游標(biāo)名>CURSOR

FOR<SELECT語(yǔ)句>FOR

DELETE;修改或刪除當(dāng)前記錄<UPDATE語(yǔ)句>WHERE

CURRENT

OF<游標(biāo)名><DELETE語(yǔ)句>WHERE

CURRENT

OF<游標(biāo)名>WHERE

CURRENT

OF<游標(biāo)名>子句表示修改或刪除的是該游標(biāo)中最近一次取出的記錄例2：對(duì)某個(gè)系的學(xué)生信息，根據(jù)用戶的要求修改其中某些人的年齡字段。思路:查詢某個(gè)系全體學(xué)生的信息(要查詢的系名由主變量deptname指定）;然后根據(jù)用戶的要求修改其中某些記錄的年齡字段......EXEC

SQL

INCLUDE

SQLCA;EXEC

SQL

BEGIN

DECLARE

SECTION;....../*說(shuō)明主變量deptname,HSno,Hsname,…,NEWAge等*/......EXEC

SQL

END

DECLARE

SECTION;......gets(deptname);

/*為主變量deptname賦值*/......EXEC

SQL

DECLARE

SX

CURSOR

FORSELECT

Sno,

Sname,

Ssex,

SageFROM

StudentWHERE

SDept=:deptnameFOR

UPDATE

OF

Sage;/*說(shuō)明游標(biāo)*/EXEC

SQL

OPEN

SX/*打開(kāi)游標(biāo)*WHILE(1){

/*用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄*EXEC

SQL

FETCH

SX

INTO

:HSno,

:HSname,

:HSsex,

:HSif

(sqlca.sqlcode

<>

SUCCESS)break;/*若所有查詢結(jié)果均已處理完或出現(xiàn)SQL語(yǔ)句錯(cuò)誤，則退出循環(huán)*/printf("%s,

%s,

%s,

%d",

Sno,

Sname,

Ssex,

Sage);/*顯示該記錄*/printf("UPDATE

AGE?");

/*問(wèn)用戶是否要修改*scanf("%c",&yn);if

(yn="y"

or

yn="Y"){printf("INPUT

NEW

AGE:

");scanf("%d",&NEWAge);/*需要修改*//*輸入新的年齡值*/EXEC

SQL

UPDATE

StudentSET

Sage=:NEWAgeWHERE

CURRENT

OF

SX;/*修改當(dāng)前記錄的年齡字段*/};};EXEC

SQL

CLOSE

SX;...