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

第八章數據庫編程嵌入式SQL存儲過程

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

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

SQL

<SQL語句>；例：EXEC

SQL

DROP

TABLE

Student

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

SQL

INCLUDE

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

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

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

Communication

Area,SQLCA）二、主變量（

Host

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

DECLARE

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

DECLARE

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

:Gradeid例:根據學生號碼查詢學生信息。假設已將要查詢的學生的學號給了主變量givensnoEXEC

SQL

SELECT

Sno,

Sname,

Ssex,

Sage,

SdeptINTO

:Hsno,

:Hname,

:Hsex,

:Hage,

:HdeptFROM

StudentWHERE

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

SQL

DECLARE

SX

CURSOR

FORSELECT

Sno,Sname,Ssex,Sage

/*SX對應語句的執(zhí)行結果FROM

StudentWHERE

SDept

=

:deptname;打開游標EXEC

SQL

OPEN

SX;執(zhí)行SELECT語句，將結果放入結果集中。推進游標EXEC

SQL

FETCH

SX

INTO

:HSno,

….;移動指針，改變結果集的當前記錄。通過游標更新數據關閉游標四、建立和關閉數據庫連接建立數據庫連接：EXEC

SQL

CONNECT

TO

target

[AS

connection-name][USER

user-name]target－要連接的數據庫服務器?？梢允且粋€常見的服務器標識串，如dbname@<hostname>:<port>;或是包含服務器標識的SQL串常量，也可以是DEFAULT。connection-name－可選的連接名，連接必須是一個有效的標識符，用來識別一個程序內同時建立的多個連接。關閉數據庫連接：EXEC

SQL

DISCONNECT

[

connection]修改當前連接EXEC

SQL

SET

CONNECT

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

/*連接數據庫TEST*/18EXEC

SQL

DECLARE

SX

CURSOR

FOR/*定義游標*CSELECT

Sno,

Sname,

Ssex,

Sage/*SX對應語句的執(zhí)行結DFROM

Student21WHERE

Sdept

=

:deptname;22

EXEC

SQL

OPEN

SX;*//*打開游標SX便指向查詢結果的第一行G for(;;)

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

{

EXEC

SQL

FETCH

SX

INTO

:HSno,

:HSname,

:HSsex,:HSage;/*推進游標，將當前數據放入主變量*/。。。。。。。。。。。。。If

(yn

==

‘y’

||yn

==

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

SQL

UPDATE

StudentSET

Sage

=

:NEWAGEWHERE

CURRENT

OF

SX

;}

/*對當前游標指向的學生年齡進行更新*//*關閉游標SX不再和查詢結果對應*/}EXEC

SQL

CLOSE

SX;EXEC

SQL

COMMIT

WORK;/*提交更新*//*斷開數據庫連接*/42

EXEC

SQL

DISCONNECT

TEST;}1

EXEC

SQL

BEGIN

DEC

LARE

SECTION;8

EXEC

SQL

END

DECLARE

SECTION;:

EXEC

SQL

INCLUDE

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

SQL

DECLARE

SX

CURSOR

FOREXEC

SQL

OPEN

SX;EXEC

SQL

CLOSE

SX;16

EXEC

SQL

CONNECT

TO

。。。。

/*連接數據庫TEST*/18F4041EXEC

SQL

COMMIT

WORK;EXEC

SQL

DISCONNECT

TEST;/*提交更新*//*斷開數據庫連接*/C語言程序結束程序框架8.1.3不用游標的SQL語句不用游標的SQL語句的種類：說明性語句數據定義語句數據控制語句查詢結果為單記錄的SELECT語句非CURRENT形式的增刪改語句一、查詢結果為單記錄的SELECT語句語句格式：EXEC

SQL

SELECT

[ALL|DISTINCT]<目標列表達式>[,<目標列表達式>]...INTO <主變量>[<指示變量>]

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

...[WHERE<條件表達式>][GROUP

BY<列名1>[HAVING<條件表達式>]][ORDER

BY<列名2>[ASC|DESC]];例1：根據學生號碼查詢學生信息。假設已將要查詢的學生的學賦給了主變量givensnoEXEC

SQL

SELECT

Sno,

Sname,

Ssex,

Sage,

Sdept

INTO

:Hsno,

:Hname,

:Hsex,

:Hage,

:HdeptFROM

StudentWHERE

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

SQL

SELECT

Sno,

Cno,

GradeINTO

:Hsno,

:Hcno,

:Hgrade:GradeidFROM

SCWHERE

Sno=:givensno

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

SQL

UPDATE

SCSET

Grade=Grade+:RaiseWHERE

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

SQL

UPDATE

SCSET

Grade=:newgradeWHERE

Sno=:givensno

AND

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

SQL

UPDATE

StudentSET

Sage=:Raise:SageidWHERE

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

SQL

DELETEFROM

SCWHERE

Sno=(SELECT

SnoFROM

StudentWHERE

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

/*用作指示變量，賦為負值*/EXEC

SQL

INSERTINTO

SC(Sno,

Cno,

Grade)VALUES(:stdno,

:couno,

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

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

SQL

DECLARE<游標名>CURSORFOR

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

SQL

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

SQL

FETCH

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

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

SQL

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

/*說明游標*/EXEC

SQL

OPEN

SX/*打開游標*/WHILE(1)/*用循環(huán)結構逐條處理結果集中的記錄*/{

EXEC

SQL

FETCH

SX

INTO

:HSno,

:HSname,

:HSsex,

:HSagif

(sqlca.sqlcode

<>

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

*//*由主語言語句進行進一步處理

*/......};EXEC

SQL

CLOSE

SX;

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

SQL

DECLARE<游標名>CURSOR

FOR<SELECT語句>FOR

UPDATE

OF<列名>;為DELETE語句說明游標EXEC

SQL

DECLARE<游標名>CURSOR

FOR<SELECT語句>FOR

DELETE;修改或刪除當前記錄<UPDATE語句>WHERE

CURRENT

OF<游標名><DELETE語句>WHERE

CURRENT

OF<游標名>WHERE

CURRENT

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

SQL

OPEN

SX/*打開游標*WHILE(1){

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

SQL

FETCH

SX

INTO

:HSno,

:HSname,

:HSsex,

:HSif

(sqlca.sqlcode

<>

SUCCESS)break;/*若所有查詢結果均已處理完或出現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;/*修改當前記錄的年齡字段*/};};EXEC

SQL

CLOSE

SX;...