new第四章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL(三)

數(shù)據(jù)庫(kù)系統(tǒng)原理

第四章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQLAnIntroductiontoDatabaseSystem第四章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1SQL概述4.2學(xué)生-課程數(shù)據(jù)庫(kù)4.3數(shù)據(jù)定義4.4數(shù)據(jù)查詢4.5數(shù)據(jù)更新4.6視圖4.7嵌入式SQL4.8*使用游標(biāo)的SQL語(yǔ)句4.9*動(dòng)態(tài)SQL4.10*PL/SQL與存儲(chǔ)過(guò)程AnIntroductiontoDatabaseSystem4.7嵌入式SQLSQL語(yǔ)言提供了兩種不同的使用方式：交互式嵌入式為什么要引入嵌入式SQLSQL語(yǔ)言是非過(guò)程性語(yǔ)言事務(wù)處理應(yīng)用需要高級(jí)語(yǔ)言AnIntroductiontoDatabaseSystem4.7嵌入式SQL4.7.1嵌入式SQL的一般形式4.7.2嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信4.7.3不使用游標(biāo)的SQL語(yǔ)句AnIntroductiontoDatabaseSystem4.7.1嵌入式SQL的一般形式主語(yǔ)言嵌入式SQL是將SQL語(yǔ)句嵌入程序設(shè)計(jì)語(yǔ)言中，被嵌入的程序設(shè)計(jì)語(yǔ)言，如C、C++、Java，稱為宿主語(yǔ)言，簡(jiǎn)稱主語(yǔ)言。處理過(guò)程預(yù)編譯方法AnIntroductiontoDatabaseSystem嵌入式SQL的處理過(guò)程（續(xù)）主語(yǔ)言程序

+嵌入式SQL語(yǔ)句RDBMS的預(yù)處理程序ESQL語(yǔ)句轉(zhuǎn)換為函數(shù)調(diào)用主語(yǔ)言編譯程序目標(biāo)語(yǔ)言程序ESQL基本處理過(guò)程AnIntroductiontoDatabaseSystem嵌入式SQL的處理過(guò)程（續(xù)）為了區(qū)分SQL語(yǔ)句與主語(yǔ)言語(yǔ)句，所有SQL語(yǔ)句必須加前綴EXECSQL，以(;)結(jié)束:EXECSQL<SQL語(yǔ)句>;AnIntroductiontoDatabaseSystem4.7嵌入式SQL4.7.1嵌入式SQL的處理過(guò)程4.7.2嵌入式SQL與主語(yǔ)言的通信4.7.3不使用游標(biāo)的SQL語(yǔ)句4.7.4使用游標(biāo)的SQL語(yǔ)句4.7.5動(dòng)態(tài)SQL4.7.6小結(jié)AnIntroductiontoDatabaseSystem4.7.2嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信將SQL嵌入到高級(jí)語(yǔ)言中混合編程，程序中會(huì)含有兩種不同計(jì)算模型的語(yǔ)句SQL語(yǔ)句描述性的面向集合的語(yǔ)句負(fù)責(zé)操縱數(shù)據(jù)庫(kù)高級(jí)語(yǔ)言語(yǔ)句過(guò)程性的面向記錄的語(yǔ)句負(fù)責(zé)控制程序流程它們之間應(yīng)該如何通信？AnIntroductiontoDatabaseSystem嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信（續(xù)）數(shù)據(jù)庫(kù)工作單元與源程序工作單元之間的通信：1.SQL通信區(qū)向主語(yǔ)言傳遞SQL語(yǔ)句的執(zhí)行狀態(tài)信息使主語(yǔ)言能夠據(jù)此控制程序流程2.主變量主語(yǔ)言向SQL語(yǔ)句提供參數(shù)將SQL語(yǔ)句查詢數(shù)據(jù)庫(kù)的結(jié)果交主語(yǔ)言進(jìn)一步處理3.游標(biāo)解決集合性操作語(yǔ)言與過(guò)程性操作語(yǔ)言的不匹配AnIntroductiontoDatabaseSystem一、SQL通信區(qū)SQLCA：SQLCommunicationAreaSQLCA是一個(gè)數(shù)據(jù)結(jié)構(gòu)SQLCA的用途SQL語(yǔ)句執(zhí)行后，RDBMS反饋給應(yīng)用程序信息

描述系統(tǒng)當(dāng)前工作狀態(tài)描述運(yùn)行環(huán)境這些信息將送到SQL通信區(qū)SQLCA中應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來(lái)執(zhí)行的語(yǔ)句AnIntroductiontoDatabaseSystemSQL通信區(qū)SQLCA使用方法：定義SQLCA

用EXECSQLINCLUDESQLCA定義使用SQLCASQLCA中有一個(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)處理AnIntroductiontoDatabaseSystem二、主變量主變量嵌入式SQL語(yǔ)句中可以使用主語(yǔ)言的程序變量來(lái)輸入或輸出數(shù)據(jù)在SQL語(yǔ)句中使用的主語(yǔ)言程序變量簡(jiǎn)稱為主變量（HostVariable）AnIntroductiontoDatabaseSystem主變量（續(xù)）

主變量的類型輸入主變量輸出主變量一個(gè)主變量有可能既是輸入主變量又是輸出主變量AnIntroductiontoDatabaseSystem主變量（續(xù)）

指示變量：一個(gè)主變量可以附帶一個(gè)指示變量（IndicatorVariable）指示變量的用途

判斷主變量是否為空。AnIntroductiontoDatabaseSystem主變量（續(xù)）

在SQL語(yǔ)句中使用主變量和指示變量的方法1)說(shuō)明主變量和指示變量BEGINDECLARESECTION ......... .........(說(shuō)明主變量和指示變量) .........ENDDECLARESECTIONAnIntroductiontoDatabaseSystem主變量（續(xù)）

2)使用主變量說(shuō)明之后的主變量可以在SQL語(yǔ)句中任何一個(gè)能夠使用表達(dá)式的地方出現(xiàn)為了與數(shù)據(jù)庫(kù)對(duì)象名（表名、視圖名、列名等）區(qū)別，SQL語(yǔ)句中的主變量名前要加冒號(hào)（:）作為標(biāo)志3)使用指示變量

指示變量前也必須加冒號(hào)標(biāo)志必須緊跟在所指主變量之后AnIntroductiontoDatabaseSystem主變量（續(xù)）

在SQL語(yǔ)句之外(主語(yǔ)言語(yǔ)句中)使用主變量和指示變量的方法可以直接引用，不必加冒號(hào)AnIntroductiontoDatabaseSystem三、游標(biāo)（cursor）為什么要使用游標(biāo)

SQL語(yǔ)言與主語(yǔ)言具有不同數(shù)據(jù)處理方式SQL語(yǔ)言是面向集合的，一條SQL語(yǔ)句原則上可以產(chǎn)生或處理多條記錄主語(yǔ)言是面向記錄的，一組主變量一次只能存放一條記錄僅使用主變量并不能完全滿足SQL語(yǔ)句向應(yīng)用程序輸出數(shù)據(jù)的要求嵌入式SQL引入了游標(biāo)的概念，用來(lái)協(xié)調(diào)這兩種不同的處理方式AnIntroductiontoDatabaseSystem游標(biāo)（續(xù)）游標(biāo)游標(biāo)是系統(tǒng)為用戶開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果每個(gè)游標(biāo)區(qū)都有一個(gè)名字用戶可以用SQL語(yǔ)句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語(yǔ)言進(jìn)一步處理AnIntroductiontoDatabaseSystem游標(biāo)（續(xù)）游標(biāo)使用步驟（1）聲明游標(biāo)

EXECSQLDECLARE<游標(biāo)名>CURSORFOR<SELECT語(yǔ)句>；（2）打開(kāi)游標(biāo)

EXECSQLOPEN<游標(biāo)名>AnIntroductiontoDatabaseSystem游標(biāo)（續(xù)）游標(biāo)使用步驟（3）移動(dòng)游標(biāo)指針，然后取當(dāng)前記錄

EXECSQLFETCH<游標(biāo)名>INTO<主變量>[<指示變量>]，[<主變量>[<指示變量>]]；（4）關(guān)閉游標(biāo)

EXECSQLCLOSE<游標(biāo)名>AnIntroductiontoDatabaseSystem游標(biāo)（續(xù)）AnIntroductiontoDatabaseSystem4.7嵌入式SQL4.7.1嵌入式SQL的處理過(guò)程4.7.2嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信4.7.3不使用游標(biāo)的SQL語(yǔ)句4.7.4使用游標(biāo)的SQL語(yǔ)句4.7.5動(dòng)態(tài)SQL4.7.6小結(jié)AnIntroductiontoDatabaseSystem4.7.3不用游標(biāo)的SQL語(yǔ)句不用游標(biāo)的SQL語(yǔ)句的種類說(shuō)明性語(yǔ)句數(shù)據(jù)定義語(yǔ)句數(shù)據(jù)控制語(yǔ)句查詢結(jié)果為單記錄的SELECT語(yǔ)句AnIntroductiontoDatabaseSystem不用游標(biāo)的SQL語(yǔ)句（續(xù)）一、查詢結(jié)果為單記錄的SELECT語(yǔ)句二、非CURRENT形式的增刪改語(yǔ)句AnIntroductiontoDatabaseSystem一、查詢結(jié)果為單記錄的SELECT語(yǔ)句這類語(yǔ)句不需要使用游標(biāo)，只需要用INTO子句指定存放查詢結(jié)果的主變量[例2]根據(jù)學(xué)生號(hào)碼查詢學(xué)生信息。假設(shè)已經(jīng)把要查詢的學(xué)生的學(xué)號(hào)賦給了主變量givensno。EXECSQLSELECTSno，Sname，Ssex，Sage，Sdept INTO:Hsno，:

Hname

，:Hsex，:Hage，:HdeptFROMStudentWHERESno=:givensno；AnIntroductiontoDatabaseSystem查詢結(jié)果為單記錄的SELECT語(yǔ)句（續(xù)）(1)INTO子句、WHERE子句和HAVING短語(yǔ)的條件表達(dá)式中均可以使用主變量(2)查詢返回的記錄中，可能某些列為空值NULL。(3)如果查詢結(jié)果實(shí)際上并不是單條記錄，而是多條記錄，則程序出錯(cuò)，RDBMS會(huì)在SQLCA中返回錯(cuò)誤信息AnIntroductiontoDatabaseSystem查詢結(jié)果為單記錄的SELECT語(yǔ)句（續(xù)）[例3]查詢某個(gè)學(xué)生選修某門(mén)課程的成績(jī)。假設(shè)已經(jīng)把將要查詢的學(xué)生的學(xué)號(hào)賦給了主變量givensno，將課程號(hào)賦給了主變量givencno。

EXECSQLSELECTSno，Cno，GradeINTO:Hsno，:Hcno，:Hgrade:Gradeid

/*指示變量Gradeid*/FROMSCWHERESno=:givensnoANDCno=:givencno；

如果Gradeid<0，不論Hgrade為何值，均認(rèn)為該學(xué)生成績(jī)?yōu)榭罩?。AnIntroductiontoDatabaseSystem二、非CURRENT形式的增刪改語(yǔ)句在UPDATE的SET子句和WHERE子句中可以使用主變量，SET子句還可以使用指示變量[例4]修改某個(gè)學(xué)生選修1號(hào)課程的成績(jī)。

EXECSQLUPDATESCSETGrade=:newgrade

/*修改的成績(jī)已賦給主變量*/WHERESno=:givensno；/*學(xué)號(hào)賦給主變量givensno*/AnIntroductiontoDatabaseSystem非CURRENT形式的增刪改語(yǔ)句（續(xù)）[例5]將計(jì)算機(jī)系全體學(xué)生年齡置NULL值。

Sageid=-1；

EXECSQLUPDATEStudentSETSage=:Raise:SageidWHERESdept=‘CS’；

將指示變量Sageid賦一個(gè)負(fù)值后，無(wú)論主變量Raise為何值，RDBMS都會(huì)將CS系所有學(xué)生的年齡置空值。等價(jià)于：

EXECSQLUPDATEStudentSETSage=NULLWHERESdept='CS'；AnIntroductiontoDatabaseSystem非CURRENT形式的增刪改語(yǔ)句（續(xù)）[例6]某個(gè)學(xué)生退學(xué)了，現(xiàn)要將有關(guān)他的所有選課記錄刪除掉。假設(shè)該學(xué)生的姓名已賦給主變量stdname。

EXECSQLDELETEFROMSCWHERESno=(SELECTSnoFROMStudentWHERESname=:stdname)；AnIntroductiontoDatabaseSystem非CURRENT形式的增刪改語(yǔ)句（續(xù)）[例7]某個(gè)學(xué)生新選修了某門(mén)課程，將有關(guān)記錄插入SC表中。假設(shè)插入的學(xué)號(hào)已賦給主變量stdno，課程號(hào)已賦給主變量couno。

gradeid=-1；/*用作指示變量，賦為負(fù)值*/ EXECSQLINSERTINTOSC(Sno，Cno，Grade)

VALUES(:stdno，:couno，:gr:gradeid)；

由于該學(xué)生剛選修課程，成績(jī)應(yīng)為空，所以要把指示變量賦為負(fù)值A(chǔ)nIntroductiontoDatabaseSystem第四章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1SQL概述4.2學(xué)生-課程數(shù)據(jù)庫(kù)4.3數(shù)據(jù)定義4.4數(shù)據(jù)查詢4.5數(shù)據(jù)更新4.6視圖4.7嵌入式SQL4.8*使用游標(biāo)的SQL語(yǔ)句4.9*動(dòng)態(tài)SQL4.10*PL/SQL與存儲(chǔ)過(guò)程AnIntroductiontoDatabaseSystem4.8*使用游標(biāo)的SQL語(yǔ)句4.8.1查詢結(jié)果為多條記錄的SELECT語(yǔ)句4.8.2CURRENT形式的UPDATE和DELETE語(yǔ)句AnIntroductiontoDatabaseSystem一、查詢結(jié)果為多條記錄的SELECT語(yǔ)句使用游標(biāo)的步驟1.說(shuō)明游標(biāo)2.打開(kāi)游標(biāo)3.推進(jìn)游標(biāo)指針并取當(dāng)前記錄4.關(guān)閉游標(biāo)AnIntroductiontoDatabaseSystem1.說(shuō)明游標(biāo)使用DECLARE語(yǔ)句語(yǔ)句格式

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

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

使用FETCH語(yǔ)句語(yǔ)句格式

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

NEXT：向前推進(jìn)一條記錄

PRIOR：向回退一條記錄

FIRST：推向第一條記錄

LAST：推向最后一條記錄缺省值為NEXTAnIntroductiontoDatabaseSystem4.關(guān)閉游標(biāo)使用CLOSE語(yǔ)句語(yǔ)句格式

EXECSQLCLOSE<游標(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)系A(chǔ)nIntroductiontoDatabaseSystem二、CURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句CURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句的用途面向集合的操作一次修改或刪除所有滿足條件的記錄AnIntroductiontoDatabaseSystemCURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句(續(xù))如果只想修改或刪除其中某個(gè)記錄用帶游標(biāo)的SELECT語(yǔ)句查出所有滿足條件的記錄從中進(jìn)一步找出要修改或刪除的記錄用CURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句修改或刪除之UPDATE語(yǔ)句和DELETE語(yǔ)句中的子句：

WHERECURRENTOF<游標(biāo)名>表示修改或刪除的是最近一次取出的記錄，即游標(biāo)指針指向的記錄AnIntroductiontoDatabaseSystemCURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句(續(xù))不能使用CURRENT形式的UPDATE語(yǔ)句和DELETE語(yǔ)句:當(dāng)游標(biāo)定義中的SELECT語(yǔ)句帶有UNION或ORDERBY子句該SELECT語(yǔ)句相當(dāng)于定義了一個(gè)不可更新的視圖

AnIntroductiontoDatabaseSystem第四章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1SQL概述4.2學(xué)生-課程數(shù)據(jù)庫(kù)4.3數(shù)據(jù)定義4.4數(shù)據(jù)查詢4.5數(shù)據(jù)更新4.6視圖4.7嵌入式SQL4.8*使用游標(biāo)的SQL語(yǔ)句4.9*動(dòng)態(tài)SQL4.10*PL/SQL與存儲(chǔ)過(guò)程AnIntroductiontoDatabaseSystem

4.9動(dòng)態(tài)SQL靜態(tài)嵌入式SQL靜態(tài)嵌入式SQL語(yǔ)句能夠滿足一般要求無(wú)法滿足要到執(zhí)行時(shí)才能夠確定要提交的SQL語(yǔ)句動(dòng)態(tài)嵌入式SQL允許在程序運(yùn)行過(guò)程中臨時(shí)“組裝”SQL語(yǔ)句支持動(dòng)態(tài)組裝SQL語(yǔ)句和動(dòng)態(tài)參數(shù)兩種形式AnIntroductiontoDatabaseSystem動(dòng)態(tài)SQL簡(jiǎn)介（續(xù)）一、使用SQL語(yǔ)句主變量二、動(dòng)態(tài)參數(shù)AnIntroductiontoDatabaseSystem一、使用SQL語(yǔ)句主變量SQL語(yǔ)句主變量：程序主變量包含的內(nèi)容是SQL語(yǔ)句的內(nèi)容，而不是原來(lái)保存數(shù)據(jù)的輸入或輸出變量SQL語(yǔ)句主變量在程序執(zhí)行期間可以設(shè)定不同的SQL語(yǔ)句，然后立即執(zhí)行AnIntroductiontoDatabaseSystem使用SQL語(yǔ)句主變量（續(xù)）[例9]創(chuàng)建基本表TESTEXECSQLBEGINDECLARESECTION;constchar*stmt="CREATETABLEtest(aint);";/*SQL語(yǔ)句主變量*/EXECSQLENDDECLARESECTION;......EXECSQLEXECUTEIMMEDIATE:stmt; /*執(zhí)行語(yǔ)句*/AnIntroductiontoDatabaseSystem二、動(dòng)態(tài)參數(shù)動(dòng)態(tài)參數(shù)SQL語(yǔ)句中的可變?cè)厥褂脜?shù)符號(hào)(?)表示該位置的數(shù)據(jù)在運(yùn)行時(shí)設(shè)定和主變量的區(qū)別動(dòng)態(tài)參數(shù)的輸入不是編譯時(shí)完成綁定而是通過(guò)(prepare)語(yǔ)句準(zhǔn)備主變量和執(zhí)行(execute)時(shí)綁定數(shù)據(jù)或主變量來(lái)完成AnIntroductiontoDatabaseSystem動(dòng)態(tài)參數(shù)（續(xù)）使用動(dòng)態(tài)參數(shù)的步驟：

1.聲明SQL語(yǔ)句主變量。constchar*stmt="INSERTINTOtestVALUES(?);";

2.準(zhǔn)備SQL語(yǔ)句(PREPARE)。

EXECSQLPREPARE<語(yǔ)句名>FROM<SQL語(yǔ)句主變量>;

AnIntroductiontoDatabaseSystem動(dòng)態(tài)參數(shù)（續(xù)）使用動(dòng)態(tài)參數(shù)的步驟（續(xù)）：

3.執(zhí)行準(zhǔn)備好的語(yǔ)句(EXECUTE)

EXECSQLEXECUTE<語(yǔ)句名>[INTO<主變量表>][USING< 主變量或常量>];AnIntroductiontoDatabaseSystem動(dòng)態(tài)參數(shù)（續(xù)）[例10]向TEST中插入元組。EXECSQLBEGINDECLARESECTION;constchar*stmt="INSERTINTOtestVALUES(?);";/*聲明SQL主變量*/EXECSQLENDDECLARESECTION;......EXECSQLPREPAREmystmtFROM:stmt;/*準(zhǔn)備語(yǔ)句*/......EXECSQLEXECUTEmystmtUSING100;/*執(zhí)行語(yǔ)句*/EXECSQLEXECUTEmystmtUSING200;/*執(zhí)行語(yǔ)句*/AnIntroductiontoDatabaseSystem第四章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1SQL概述4.2學(xué)生-課程數(shù)據(jù)庫(kù)4.3數(shù)據(jù)定義4.4數(shù)據(jù)查詢4.5數(shù)據(jù)更新4.6視圖4.7嵌入式SQL4.8*使用游標(biāo)的SQL語(yǔ)句4.9*動(dòng)態(tài)SQL4.10*PL/SQL與存儲(chǔ)過(guò)程AnIntroductiontoDatabaseSystem4.10*PL/SQL與存儲(chǔ)過(guò)程為了增強(qiáng)SQL語(yǔ)言的處理能力，為了提高數(shù)據(jù)庫(kù)系統(tǒng)的性能，許多數(shù)據(jù)庫(kù)廠家擴(kuò)展了SQL語(yǔ)言的流程控制功能，使它成為過(guò)程性的SQL語(yǔ)言，如Sybase的T-SQL，Oracle的PL/SQL等。1996年，國(guó)際標(biāo)準(zhǔn)化組織把這部分功能加進(jìn)了SQL92標(biāo)準(zhǔn)之中，這就是存儲(chǔ)過(guò)程。AnIntroductiontoDatabaseSystem4.10存儲(chǔ)過(guò)程4.10.1PL/SQL與存儲(chǔ)過(guò)程概述4.10.2PL/SQL的程序結(jié)構(gòu)4.10.3控制結(jié)構(gòu)4.10.4異常處理4.10.5存儲(chǔ)過(guò)程4.10.6游標(biāo)4.10.7觸發(fā)器AnIntroductiontoDatabaseSystem4.10.1PL/SQL與存儲(chǔ)過(guò)程概述PL/SQL：(1)什么是PL/SQLPL/SQL是SQL過(guò)程化的擴(kuò)展,也就是擴(kuò)展了控制語(yǔ)句，使得SQL具有程序模塊結(jié)構(gòu)語(yǔ)言的特征，包括異常處理功能。(2)存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是由PL/SQL書(shū)寫(xiě)的，經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中的過(guò)程。它們成為SQL服務(wù)器模塊，使用時(shí)只要調(diào)用即可。AnIntroductiontoDatabaseSystem4.10存儲(chǔ)過(guò)程4.10.1PL/SQL與存儲(chǔ)過(guò)程概述4.10.2PL/SQL的程序結(jié)構(gòu)4.10.3控制結(jié)構(gòu)4.10.4異常處理4.10.5存儲(chǔ)過(guò)程4.10.6游標(biāo)4.10.7觸發(fā)器AnIntroductiontoDatabaseSystem4.10.2PL/SQL的程序結(jié)構(gòu)PL/SQL塊可以分為3部分，即聲明部分、可執(zhí)行部分和異常處理部分。其中執(zhí)行部分是必須的，其他兩個(gè)部分可選。AnIntroductiontoDatabaseSystem4.10.2PL/SQL的程序結(jié)構(gòu)PL/SOL塊的基本結(jié)構(gòu)：

1.聲明部分

DECLARE ------變量、常量、游標(biāo)、異常等定義的變量、常量等只能在該基本塊中使用當(dāng)基本塊執(zhí)行結(jié)束時(shí)，定義就不再存在AnIntroductiontoDatabaseSystem變量常量的定義1）.PL/SQL中定義變量的語(yǔ)法形式是:

變量名數(shù)據(jù)類型［［NOTNULL］:=初值表達(dá)式］

V1varchar2(20);2）.常量的定義類似于變量的定義:

常量名數(shù)據(jù)類型CONSTANT:=常量表達(dá)式 常量必須要給一個(gè)值，并且該值在存在期間或常量的作用域內(nèi)不能改變。如果試圖修改它，PL/SQL將返回一個(gè)異常。3）.賦值語(yǔ)句

變量名稱:=表達(dá)式AnIntroductiontoDatabaseSystem4.10.2PL/SQL的程序結(jié)構(gòu)PL/SOL塊的基本結(jié)構(gòu)(續(xù))：

2.可執(zhí)行部分

BEGIN ------SQL語(yǔ)句、PL/SQL的流程控制語(yǔ)句

EXCEPTION------異常處理部分

END；AnIntroductiontoDatabaseSystem4.10.2PL/SQL的程序結(jié)構(gòu)PL/SOL塊的基本結(jié)構(gòu)(續(xù))：

3.異常處理部分

exception

這一部分是可選的，在這一部分中處理異常或錯(cuò)誤，對(duì)異常處理的詳細(xì)討論在后面進(jìn)行。AnIntroductiontoDatabaseSystem4.10.2PL/SQL的程序結(jié)構(gòu)例4-103

向課程Course表中插入10門(mén)課程基本信息，課程編號(hào)從0001到0010。DECLARECNONUMBER;BEGINCNO:=0001;LOOPINSERTINTOCourse(CNO)VALUES(TO_CHAR(CNO,'9999'))；CNO:=CNO+1;EXITWHENCNO>0010;ENDLOOP;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('ERROR');END;AnIntroductiontoDatabaseSystem4.10存儲(chǔ)過(guò)程4.10.1PL/SQL與存儲(chǔ)過(guò)程概述4.10.2PL/SQL的程序結(jié)構(gòu)4.10.3控制結(jié)構(gòu)4.10.4異常處理4.10.5存儲(chǔ)過(guò)程4.10.6游標(biāo)4.10.7觸發(fā)器AnIntroductiontoDatabaseSystem4.10.3控制結(jié)構(gòu)PL/SQL

功能：一、條件控制語(yǔ)句二、循環(huán)控制語(yǔ)句三、錯(cuò)誤處理AnIntroductiontoDatabaseSystem控制結(jié)構(gòu)（續(xù)）一、條件控制語(yǔ)句

IF-THEN，IF-THEN-ELSE和嵌套的IF語(yǔ)句

1.IFconditionTHEN

Sequence_of_statements; ENDIF 2.IFconditionTHEN

Sequence_of_statements1; ELSE

Sequence_of_statements2;

ENDIF; 3.在THEN和ELSE子句中還可以再包括IF語(yǔ)句，即IF語(yǔ)句可以嵌套

AnIntroductiontoDatabaseSystem控制結(jié)構(gòu)（續(xù)）二、循環(huán)控制語(yǔ)句

LOOP，WHILE-LOOP和FOR-LOOP

1.最簡(jiǎn)單的循環(huán)語(yǔ)句LOOP

LOOP Sequence_of_statements;

ENDLOOP;

多數(shù)數(shù)據(jù)庫(kù)服務(wù)器的PL/SQL都提供EXIT、BREAK或LEAVE等循環(huán)結(jié)束語(yǔ)句，保證LOOP語(yǔ)句塊能夠結(jié)束。AnIntroductiontoDatabaseSystem控制結(jié)構(gòu)（續(xù)）二、循環(huán)控制語(yǔ)句（續(xù)）2.WHILE-LOOP

WHILEconditionLOOP

Sequence_of_statements;

ENDLOOP;每次執(zhí)行循環(huán)體語(yǔ)句之前，首先對(duì)條件進(jìn)行求值如果條件為真，則執(zhí)行循環(huán)體內(nèi)的語(yǔ)句序列。如果條件為假，則跳過(guò)循環(huán)并把控制傳遞給下一個(gè)語(yǔ)句3.FOR-LOOP

FORcountIN［REVERSE］bound1…bound2LOOP

Sequence_of_statements;

ENDLOOP;AnIntroductiontoDatabaseSystem控制結(jié)構(gòu)（續(xù)）三、錯(cuò)誤處理：如果PL/SQL在執(zhí)行時(shí)出現(xiàn)異常，則應(yīng)該讓程序在產(chǎn)生異常的語(yǔ)句處停下來(lái)，根據(jù)異常的類型去執(zhí)行異常處理語(yǔ)句AnIntroductiontoDatabaseSystem4.10存儲(chǔ)過(guò)程4.10.1PL/SQL與存儲(chǔ)過(guò)程概述4.10.2PL/SQL的程序結(jié)構(gòu)4.10.3控制結(jié)構(gòu)4.10.4異常處理4.10.5存儲(chǔ)過(guò)程4.10.6游標(biāo)4.10.7觸發(fā)器AnIntroductiontoDatabaseSystem4.10.4異常處理異常處理分為系統(tǒng)預(yù)定義異常處理和自定義異常處理兩部分。系統(tǒng)預(yù)定義異常處理是針對(duì)PL/SQL程序編譯、執(zhí)行過(guò)程中發(fā)生的問(wèn)題進(jìn)行處理的程序。讀者可以嘗試修改正常運(yùn)行的程序，并執(zhí)行修改后的程序，就可以發(fā)現(xiàn)調(diào)用了哪些異常處理。

AnIntroductiontoDatabaseSystem4.10.4異常處理例4-104

定義名為GRADEERROR的異常，在SC表中查找Sno等于0001的記錄，將他的成績(jī)存入變量tempGrade中，判斷tempGrade值，若不在0和100之間，說(shuō)明該學(xué)生的成績(jī)值有問(wèn)題，則激活異常處理，顯示相應(yīng)的提示信息。SETSERVEROUTPUTONDECLAREGRADEERROREXCEPTION;tempGradeNUMBER;BEGINSELECTGRADEINTOtempGradeFROMSCWHERESno='0001';IFtempGrade<0ORtempGrade>100THENRAISEGRADEERROR;ENDIF;EXCEPTIONWHENGRADEERRORTHENDBMS_OUTPUT.PUT_LINE('成績(jī)超出范圍');END;AnIntroductiontoDatabaseSystem4.10存儲(chǔ)過(guò)程4.10.1PL/SQL與存儲(chǔ)過(guò)程概述4.10.2PL/SQL的程序結(jié)構(gòu)4.10.3控制結(jié)構(gòu)4.10.4異常處理4.10.5存儲(chǔ)過(guò)程4.10.6游標(biāo)4.10.7觸發(fā)器AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程（續(xù)）一、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)二、存儲(chǔ)過(guò)程的用戶接口AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程（續(xù)）存儲(chǔ)過(guò)程：由PL/SQL語(yǔ)句書(shū)寫(xiě)的過(guò)程，經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，使用時(shí)只要調(diào)用即可。一、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：

1.運(yùn)行效率高

2.降低了客戶機(jī)和服務(wù)器之間的通信量

3.方便實(shí)施企業(yè)規(guī)則AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程（續(xù)）二、存儲(chǔ)過(guò)程的用戶接口：

1.創(chuàng)建存儲(chǔ)過(guò)程

2.執(zhí)行存儲(chǔ)過(guò)程

3.刪除存儲(chǔ)過(guò)程AnIntroductiontoDatabaseSystem二、存儲(chǔ)過(guò)程的用戶接口1.創(chuàng)建存儲(chǔ)過(guò)程：

CREATEProcedure過(guò)程名（［參數(shù)1，參數(shù)2，...］）AS <PL/SQL塊>；過(guò)程名：數(shù)據(jù)庫(kù)服務(wù)器合法的對(duì)象標(biāo)識(shí)參數(shù)列表：用名字來(lái)標(biāo)識(shí)調(diào)用時(shí)給出的參數(shù)值，必須指定值的數(shù)據(jù)類型。參數(shù)也可以定義輸入?yún)?shù)、輸出參數(shù)或輸入/輸出參數(shù)。默認(rèn)為輸入?yún)?shù)。過(guò)程體：是一個(gè)<PL/SQL塊>。包括聲明部分和可執(zhí)行語(yǔ)句部分

AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程的用戶接口（續(xù)）［例11］利用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)下面的應(yīng)用:從一個(gè)賬戶轉(zhuǎn)指定數(shù)額的款項(xiàng)到另一個(gè)賬戶中。

CREATEPROCEDURETRANSFER(inAccountINT，

outAccountINT，amountFLOAT) ASDECLARE

totalDepositFLOAT; BEGIN/*檢查轉(zhuǎn)出賬戶的余額*/ SELECTtotalINTOtotalDeposit

FROMACCOUNTWHEREACCOUNTNUM=outAccount; IFtotalDepositISNULLTHEN/*賬戶不存在或賬戶中沒(méi)有存款*/ ROLLBACK; RETURN; ENDIF;AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程的用戶接口（續(xù)）

IFtotalDeposit<amountTHEN /*賬戶賬戶存款不足*/ ROLLBACK; RETURN; ENDIF; UPDATEaccountSETtotal=total-amount

WHEREACCOUNTNUM=outAccount; /*修改轉(zhuǎn)出賬戶，減去轉(zhuǎn)出額*/UPDATEaccountSETtotal=total+amountWHEREACCOUNTNUM=inAccount; /*修改轉(zhuǎn)入賬戶，增加轉(zhuǎn)出額*/ COMMIT; /*提交轉(zhuǎn)賬事務(wù)*/ END;AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程的用戶接口（續(xù)）重命名存儲(chǔ)過(guò)程

ALTERProcedure過(guò)程名1RENAMETO過(guò)程名2;AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程的用戶接口（續(xù)）2.執(zhí)行存儲(chǔ)過(guò)程：

CALL/PERFORMProcedure過(guò)程名(［參數(shù)1，參數(shù)2，...］)；使用CALL或者PERFORM等方式激活存儲(chǔ)過(guò)程的執(zhí)行。在PL/SQL中，數(shù)據(jù)庫(kù)服務(wù)器支持在過(guò)程體中調(diào)用其他存儲(chǔ)過(guò)程［例12］從賬戶01003815868轉(zhuǎn)一萬(wàn)元到01003813828賬戶中。

CALLProcedureTRANSFER(01003813828，01003815868，10000)；

AnIntroductiontoDatabaseSystem存儲(chǔ)過(guò)程的用戶接口（續(xù)）3.刪除存儲(chǔ)過(guò)程

DROPPROCEDURE過(guò)程名（）；AnIntroductiontoDatabaseSystem4.10存儲(chǔ)過(guò)程4.10.1PL/SQL與存儲(chǔ)過(guò)程概述4.10.2PL/SQL的程序結(jié)構(gòu)4.10.3控制結(jié)構(gòu)4.10.4異常處理4.10.5存儲(chǔ)過(guò)程4.10.6游標(biāo)4.10.7觸發(fā)器AnIntroductiontoDatabaseSy