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

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

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

SQL

<SQL語句>；例：EXEC

SQL

DROP

TABLE

Student

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

SQL

INCLUDE

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

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

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

Communication

Area,SQLCA）二、主變量（

Host

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

DECLARE

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

DECLARE

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

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

SQL

SELECT

Sno,

Sname,

Ssex,

Sage,

SdeptINTO

:Hsno,

:Hname,

:Hsex,

:Hage,

:HdeptFROM

StudentWHERE

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

SQL

DECLARE

SX

CURSOR

FORSELECT

Sno,Sname,Ssex,Sage

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

StudentWHERE

SDept

=

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

SQL

OPEN

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

SQL

FETCH

SX

INTO

:HSno,

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

SQL

CONNECT

TO

target

[AS

connection-name][USER

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

SQL

DISCONNECT

[

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

SQL

SET

CONNECT

connection-name|DEFAULT五、舉例：帶有嵌入式SQL的一小段C程序/*主變量說明開始*//*主變量說明結(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ū)*/例題：依次檢查某個系的學(xué)生記錄，交互式更新某些學(xué)生年齡/*C語言主程序開/*變量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ù)庫TEST*/18EXEC

SQL

DECLARE

SX

CURSOR

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

Sno,

Sname,

Ssex,

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

Student21WHERE

Sdept

=

:deptname;22

EXEC

SQL

OPEN

SX;*//*打開游標(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更新語句*373839EXEC

SQL

UPDATE

StudentSET

Sage

=

:NEWAGEWHERE

CURRENT

OF

SX

;}

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

SQL

CLOSE

SX;EXEC

SQL

COMMIT

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

EXEC

SQL

DISCONNECT

TEST;}1

EXEC

SQL

BEGIN

DEC

LARE

SECTION;8

EXEC

SQL

END

DECLARE

SECTION;:

EXEC

SQL

INCLUDE

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

SQL

DECLARE

SX

CURSOR

FOREXEC

SQL

OPEN

SX;EXEC

SQL

CLOSE

SX;16

EXEC

SQL

CONNECT

TO

。。。。

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

SQL

COMMIT

WORK;EXEC

SQL

DISCONNECT

TEST;/*提交更新*//*斷開數(shù)據(jù)庫連接*/C語言程序結(jié)束程序框架8.1.3不用游標(biāo)的SQL語句不用游標(biāo)的SQL語句的種類：說明性語句數(shù)據(jù)定義語句數(shù)據(jù)控制語句查詢結(jié)果為單記錄的SELECT語句非CURRENT形式的增刪改語句一、查詢結(jié)果為單記錄的SELECT語句語句格式：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é)生號碼查詢學(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均是主變量，并均已在前面的程序中說明了。使用注意事項：查詢結(jié)果為多條記錄的處理程序出錯，RDBMS會在SQLCA中返回錯誤信息使用主變量INTO子句、WHERE子句的條件表達(dá)式、HAVING短語的條件表達(dá)式均可以使用主變量使用指示變量指示變量只能用于INTO子句中。查詢結(jié)果為空值的處理如果數(shù)據(jù)庫中沒有滿足條件的記錄，系統(tǒng)會自動將相應(yīng)主變量后面的指示變量置為負(fù)值，而不再向該主變量賦值；當(dāng)發(fā)現(xiàn)指示變量值為負(fù)值時，不管主變量為何值，均應(yīng)認(rèn)為主變量值為NULL例2：查詢某個學(xué)生選修某門課程的成績。假設(shè)已將要查詢的學(xué)生的學(xué)號賦給了主變量givensno，將課程號賦給了主變量givencno。EXEC

SQL

SELECT

Sno,

Cno,

GradeINTO

:Hsno,

:Hcno,

:Hgrade:GradeidFROM

SCWHERE

Sno=:givensno

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

SQL

UPDATE

SCSET

Grade=Grade+:RaiseWHERE

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

SQL

UPDATE

SCSET

Grade=:newgradeWHERE

Sno=:givensno

AND

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

SQL

UPDATE

StudentSET

Sage=:Raise:SageidWHERE

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

SQL

DELETEFROM

SCWHERE

Sno=(SELECT

SnoFROM

StudentWHERE

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

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

SQL

INSERTINTO

SC(Sno,

Cno,

Grade)VALUES(:stdno,

:couno,

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

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

SQL

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

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

SQL

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

SQL

FETCH

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

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

SQL

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

SQL

INCLUDE

SQLCA;EXEC

SQL

BEGIN

DECLARE

SECTION;....../*說明主變量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;

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

SQL

OPEN

SX/*打開游標(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語句錯誤，則退出循環(huán)

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

*/......};EXEC

SQL

CLOSE

SX;

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

SQL

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

FOR<SELECT語句>FOR

UPDATE

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

SQL

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

FOR<SELECT語句>FOR

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

CURRENT

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

CURRENT

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

CURRENT

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

SQL

INCLUDE

SQLCA;EXEC

SQL

BEGIN

DECLARE

SECTION;....../*說明主變量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;/*說明游標(biāo)*/EXEC

SQL

OPEN

SX/*打開游標(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語句錯誤，則退出循環(huán)*/printf("%s,

%s,

%s,

%d",

Sno,

Sname,

Ssex,

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

AGE?");

/*問用戶是否要修改*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;...