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

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

2、 to Database System4.7 嵌入式嵌入式SQLv SQL語(yǔ)言提供了兩種不同的使用方式： 交互式 嵌入式v 為什么要引入嵌入式SQL SQL語(yǔ)言是非過(guò)程性語(yǔ)言 事務(wù)處理應(yīng)用需要高級(jí)語(yǔ)言An Introduction to Database System4.7 嵌入式嵌入式SQL4.7.1 嵌入式嵌入式SQL的一般形式的一般形式4.7.2 嵌入式嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信語(yǔ)句與主語(yǔ)言之間的通信4.7.3 不使用游標(biāo)的不使用游標(biāo)的SQL語(yǔ)句語(yǔ)句An Introduction to Database System4.7.1 嵌入式嵌入式SQL的一般形式的一般形式v主語(yǔ)言 嵌入

3、式SQL是將SQL語(yǔ)句嵌入程序設(shè)計(jì)語(yǔ)言中，被嵌入的程序設(shè)計(jì)語(yǔ)言，如C、C+、Java，稱(chēng)為宿主語(yǔ)言，簡(jiǎn)稱(chēng)主語(yǔ)言。v處理過(guò)程 預(yù)編譯方法An Introduction to Database System嵌入式嵌入式SQL的處理過(guò)程（續(xù)）的處理過(guò)程（續(xù)） 主語(yǔ)言程序 +嵌入式SQL語(yǔ)句RDBMS的預(yù)處理程序ESQL語(yǔ)句轉(zhuǎn)換為函數(shù)調(diào)用主語(yǔ)言編譯程序目標(biāo)語(yǔ)言程序ESQL基本處理過(guò)程An Introduction to Database System嵌入式嵌入式SQL的處理過(guò)程（續(xù)）的處理過(guò)程（續(xù)） v為了區(qū)分SQL語(yǔ)句與主語(yǔ)言語(yǔ)句， 所有SQL語(yǔ)句必須加前綴EXEC SQL，以(;)結(jié)束: EXEC

4、 SQL ;An Introduction to Database System4.7 嵌入式嵌入式SQL4.7.1 嵌入式嵌入式SQL的處理過(guò)程的處理過(guò)程4.7.2 嵌入式嵌入式SQL與主語(yǔ)言的通信與主語(yǔ)言的通信4.7.3 不使用游標(biāo)的不使用游標(biāo)的SQL語(yǔ)句語(yǔ)句4.7.4 使用游標(biāo)的使用游標(biāo)的SQL語(yǔ)句語(yǔ)句4.7.5 動(dòng)態(tài)動(dòng)態(tài)SQL4.7.6 小結(jié)小結(jié)An Introduction to Database System4.7.2 嵌入式嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信語(yǔ)句與主語(yǔ)言之間的通信v 將SQL嵌入到高級(jí)語(yǔ)言中混合編程，程序中會(huì)含有兩種不同計(jì)算模型的語(yǔ)句nSQL語(yǔ)句 描述性的面向集合

5、的語(yǔ)句 負(fù)責(zé)操縱數(shù)據(jù)庫(kù)n高級(jí)語(yǔ)言語(yǔ)句 過(guò)程性的面向記錄的語(yǔ)句 負(fù)責(zé)控制程序流程n它們之間應(yīng)該如何通信？An Introduction to Database System嵌入式嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信語(yǔ)句與主語(yǔ)言之間的通信（續(xù)）（續(xù)） v 數(shù)據(jù)庫(kù)工作單元與源程序工作單元之間的通信：n1. SQL通信區(qū)向主語(yǔ)言傳遞SQL語(yǔ)句的執(zhí)行狀態(tài)信息使主語(yǔ)言能夠據(jù)此控制程序流程n2. 主變量主語(yǔ)言向SQL語(yǔ)句提供參數(shù)將SQL語(yǔ)句查詢(xún)數(shù)據(jù)庫(kù)的結(jié)果交主語(yǔ)言進(jìn)一步處理n3. 游標(biāo)解決集合性操作語(yǔ)言與過(guò)程性操作語(yǔ)言的不匹配An Introduction to Database System一、一、SQL通

6、信區(qū)通信區(qū)v SQLCA： SQL Communication AreanSQLCA是一個(gè)數(shù)據(jù)結(jié)構(gòu)v SQLCA的用途nSQL語(yǔ)句執(zhí)行后，RDBMS反饋給應(yīng)用程序信息 描述系統(tǒng)當(dāng)前工作狀態(tài) 描述運(yùn)行環(huán)境n這些信息將送到SQL通信區(qū)SQLCA中n應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來(lái)執(zhí)行的語(yǔ)句An Introduction to Database SystemSQL通信區(qū)通信區(qū)v SQLCA使用方法： 定義SQLCA 用EXEC SQL INCLUDE SQLCA定義 使用SQLCASQLCA中有一個(gè)存放每次執(zhí)行SQL語(yǔ)句后返回代碼的變量SQLCODE如果SQLCODE等于預(yù)定義

7、的常量SUCCESS，則表示SQL語(yǔ)句成功，否則表示出錯(cuò)應(yīng)用程序每執(zhí)行完一條SQL 語(yǔ)句之后都應(yīng)該測(cè)試一下SQLCODE的值，以了解該SQL語(yǔ)句執(zhí)行情況并做相應(yīng)處理An Introduction to Database System二、主變量二、主變量 v主變量 嵌入式SQL語(yǔ)句中可以使用主語(yǔ)言的程序變量來(lái)輸入或輸出數(shù)據(jù) 在SQL語(yǔ)句中使用的主語(yǔ)言程序變量簡(jiǎn)稱(chēng)為主變量（Host Variable）An Introduction to Database System主變量（續(xù)）主變量（續(xù)） v主變量的類(lèi)型 輸入主變量 輸出主變量 一個(gè)主變量有可能既是輸入主變量又是輸出主變量An Introduc

8、tion to Database System主變量（續(xù)）主變量（續(xù)） v指示變量： 一個(gè)主變量可以附帶一個(gè)指示變量（Indicator Variable） 指示變量的用途 判斷主變量是否為空。An Introduction to Database System主變量（續(xù)）主變量（續(xù)） v在SQL語(yǔ)句中使用主變量和指示變量的方法 1) 說(shuō)明主變量和指示變量BEGIN DECLARE SECTION. . (說(shuō)明主變量和指示變量).END DECLARE SECTIONAn Introduction to Database System主變量（續(xù)）主變量（續(xù)） 2) 使用主變量說(shuō)明之后的主變量可

9、以在SQL語(yǔ)句中任何一個(gè)能夠使用表達(dá)式的地方出現(xiàn)為了與數(shù)據(jù)庫(kù)對(duì)象名（表名、視圖名、列名等）區(qū)別，SQL語(yǔ)句中的主變量名前要加冒號(hào)（:）作為標(biāo)志 3) 使用指示變量 指示變量前也必須加冒號(hào)標(biāo)志 必須緊跟在所指主變量之后An Introduction to Database System主變量（續(xù)）主變量（續(xù)） v在SQL語(yǔ)句之外(主語(yǔ)言語(yǔ)句中)使用主變量和指示變量的方法 可以直接引用，不必加冒號(hào)An Introduction to Database System三、游標(biāo)（三、游標(biāo)（cursor）v 為什么要使用游標(biāo) SQL語(yǔ)言與主語(yǔ)言具有不同數(shù)據(jù)處理方式 SQL語(yǔ)言是面向集合的，一條SQL語(yǔ)句原

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

11、 DECLARE CURSOR FOR ； （2）打開(kāi)游標(biāo) EXEC SQL OPEN An Introduction to Database System 游標(biāo)（續(xù)）游標(biāo)（續(xù)）v游標(biāo)使用步驟 （3）移動(dòng)游標(biāo)指針，然后取當(dāng)前記錄 EXEC SQL FETCH INTO ， ； （4）關(guān)閉游標(biāo) EXEC SQL CLOSE An Introduction to Database System 游標(biāo)（續(xù)）游標(biāo)（續(xù)）An Introduction to Database System4.7 嵌入式嵌入式SQL4.7.1 嵌入式嵌入式SQL的處理過(guò)程的處理過(guò)程4.7.2 嵌入式嵌入式SQL語(yǔ)句與主語(yǔ)言

12、之間的通信語(yǔ)句與主語(yǔ)言之間的通信4.7.3 不使用游標(biāo)的不使用游標(biāo)的SQL語(yǔ)句語(yǔ)句4.7.4 使用游標(biāo)的使用游標(biāo)的SQL語(yǔ)句語(yǔ)句4.7.5 動(dòng)態(tài)動(dòng)態(tài)SQL4.7.6 小結(jié)小結(jié)An Introduction to Database System4.7.3 不用游標(biāo)的不用游標(biāo)的SQL語(yǔ)句語(yǔ)句v不用游標(biāo)的SQL語(yǔ)句的種類(lèi) 說(shuō)明性語(yǔ)句 數(shù)據(jù)定義語(yǔ)句 數(shù)據(jù)控制語(yǔ)句 查詢(xún)結(jié)果為單記錄的SELECT語(yǔ)句An Introduction to Database System不用游標(biāo)的不用游標(biāo)的SQL語(yǔ)句（續(xù)）語(yǔ)句（續(xù)）v一、查詢(xún)結(jié)果為單記錄的SELECT語(yǔ)句 v二、非CURRENT形式的增刪改語(yǔ)句An Intr

13、oduction to Database System一、查詢(xún)結(jié)果為單記錄的一、查詢(xún)結(jié)果為單記錄的SELECT語(yǔ)句語(yǔ)句v這類(lèi)語(yǔ)句不需要使用游標(biāo)，只需要用INTO子句指定存放查詢(xún)結(jié)果的主變量 例2 根據(jù)學(xué)生號(hào)碼查詢(xún)學(xué)生信息。假設(shè)已經(jīng)把要查詢(xún)的學(xué)生的學(xué)號(hào)賦給了主變量givensno。EXEC SQL SELECT Sno，Sname，Ssex，Sage，Sdept INTO :Hsno， : Hname ，:Hsex，:Hage，:Hdept FROM Student WHERE Sno=:givensno；An Introduction to Database System查詢(xún)結(jié)果為單記錄的查詢(xún)

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

15、，Cno，Grade INTO :Hsno，:Hcno，:Hgrade:Gradeid /*指示變量Gradeid*/ FROM SC WHERE Sno=:givensno AND Cno=:givencno；如果如果Gradeid 0，不論，不論Hgrade為何值，均認(rèn)為該學(xué)生成績(jī)?yōu)榭罩?。為何值，均認(rèn)為該學(xué)生成績(jī)?yōu)榭罩怠n Introduction to Database System二、非二、非CURRENT形式的增刪改語(yǔ)句形式的增刪改語(yǔ)句v 在UPDATE的SET子句和WHERE子句中可以使用主變量，SET子句還可以使用指示變量 例4 修改某個(gè)學(xué)生選修1號(hào)課程的成績(jī)。EXEC SQL

16、 UPDATE SC SET Grade=:newgrade /*修改的成績(jī)已賦給主變量*/ WHERE Sno=:givensno； /*學(xué)號(hào)賦給主變量givensno*/ An Introduction to Database System非非CURRENT形式的增刪改語(yǔ)句（續(xù)）形式的增刪改語(yǔ)句（續(xù)）例5 將計(jì)算機(jī)系全體學(xué)生年齡置NULL值。 Sageid=-1； EXEC SQL UPDATE Student SET Sage=:Raise :Sageid WHERE Sdept= CS； 將指示變量Sageid賦一個(gè)負(fù)值后，無(wú)論主變量Raise為何值，RDBMS都會(huì)將CS系所有學(xué)生的年

17、齡置空值 。等價(jià)于： EXEC SQL UPDATE Student SET Sage=NULL WHERE Sdept= CS；An Introduction to Database System非非CURRENT形式的增刪改語(yǔ)句（續(xù)）形式的增刪改語(yǔ)句（續(xù)）例6 某個(gè)學(xué)生退學(xué)了，現(xiàn)要將有關(guān)他的所有選課記錄刪除掉。假設(shè)該學(xué)生的姓名已賦給主變量stdname。 EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname)； An Introduction to Database System非非

18、CURRENT形式的增刪改語(yǔ)句（續(xù)）形式的增刪改語(yǔ)句（續(xù)）例7 某個(gè)學(xué)生新選修了某門(mén)課程，將有關(guān)記錄插入SC表中。假設(shè)插入的學(xué)號(hào)已賦給主變量stdno，課程號(hào)已賦給主變量couno。 gradeid=-1； /*用作指示變量，賦為負(fù)值*/EXEC SQL INSERT INTO SC(Sno，Cno，Grade) VALUES(:stdno，:couno，:gr :gradeid)； 由于該學(xué)生剛選修課程，成績(jī)應(yīng)為空，所以要把指示變量賦為負(fù)值由于該學(xué)生剛選修課程，成績(jī)應(yīng)為空，所以要把指示變量賦為負(fù)值 An Introduction to Database System第四章第四章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)

19、準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1 SQL概述概述4.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫(kù)課程數(shù)據(jù)庫(kù)4.3 數(shù)據(jù)定義數(shù)據(jù)定義4.4 數(shù)據(jù)查詢(xún)數(shù)據(jù)查詢(xún)4.5 數(shù)據(jù)更新數(shù)據(jù)更新4.6 視圖視圖4.7 嵌入式嵌入式SQL4.8 *使用游標(biāo)的使用游標(biāo)的SQL語(yǔ)句語(yǔ)句4.9 *動(dòng)態(tài)動(dòng)態(tài)SQL4.10 *PL/SQL與存儲(chǔ)過(guò)程與存儲(chǔ)過(guò)程An Introduction to Database System4.8 *使用游標(biāo)的使用游標(biāo)的SQL語(yǔ)句語(yǔ)句v4.8.1 查詢(xún)結(jié)果為多條記錄的查詢(xún)結(jié)果為多條記錄的SELECT語(yǔ)句語(yǔ)句v4.8.2 CURRENT形式的形式的UPDATE和和DELETE語(yǔ)句語(yǔ)句An Introduc

20、tion to Database System一、一、 查詢(xún)結(jié)果為多條記錄的查詢(xún)結(jié)果為多條記錄的SELECT語(yǔ)句語(yǔ)句v使用游標(biāo)的步驟使用游標(biāo)的步驟1. 說(shuō)明游標(biāo)說(shuō)明游標(biāo)2. 打開(kāi)游標(biāo)打開(kāi)游標(biāo)3.推進(jìn)游標(biāo)指針并取當(dāng)前記錄推進(jìn)游標(biāo)指針并取當(dāng)前記錄 4. 關(guān)閉游標(biāo)關(guān)閉游標(biāo)An Introduction to Database System1. 說(shuō)明游標(biāo)說(shuō)明游標(biāo)v使用使用DECLARE語(yǔ)句語(yǔ)句v語(yǔ)句格式語(yǔ)句格式EXEC SQL DECLARE CURSOR FOR ;v功能功能 是一條說(shuō)明性語(yǔ)句，這時(shí)是一條說(shuō)明性語(yǔ)句，這時(shí)DBMS并不執(zhí)行并不執(zhí)行SELECT指定指定的查詢(xún)操作。的查詢(xún)操作。An Int

21、roduction to Database System2. 打開(kāi)游標(biāo)打開(kāi)游標(biāo)v 使用使用OPEN語(yǔ)句語(yǔ)句v 語(yǔ)句格式語(yǔ)句格式 EXEC SQL OPEN ;v 功能功能 打開(kāi)游標(biāo)實(shí)際上是執(zhí)行相應(yīng)的打開(kāi)游標(biāo)實(shí)際上是執(zhí)行相應(yīng)的SELECT語(yǔ)句，把所有滿(mǎn)語(yǔ)句，把所有滿(mǎn)足查詢(xún)條件的記錄從指定表取到緩沖區(qū)中足查詢(xún)條件的記錄從指定表取到緩沖區(qū)中 這時(shí)游標(biāo)處于活動(dòng)狀態(tài)，指針指向查詢(xún)結(jié)果集中第一條這時(shí)游標(biāo)處于活動(dòng)狀態(tài)，指針指向查詢(xún)結(jié)果集中第一條記錄記錄An Introduction to Database System3.推進(jìn)游標(biāo)指針并取當(dāng)前記錄推進(jìn)游標(biāo)指針并取當(dāng)前記錄 v使用使用FETCH語(yǔ)句語(yǔ)句v語(yǔ)句格

22、式語(yǔ)句格式 EXEC SQL FETCH NEXT|PRIOR| FIRST|LAST FROM INTO ,.;An Introduction to Database System推進(jìn)游標(biāo)指針并取當(dāng)前記錄（續(xù)）推進(jìn)游標(biāo)指針并取當(dāng)前記錄（續(xù)）v 功能功能 指定方向推動(dòng)游標(biāo)指針，然后將緩沖區(qū)中的當(dāng)前記錄取指定方向推動(dòng)游標(biāo)指針，然后將緩沖區(qū)中的當(dāng)前記錄取出來(lái)送至主變量供主語(yǔ)言進(jìn)一步處理出來(lái)送至主變量供主語(yǔ)言進(jìn)一步處理 NEXT|PRIOR|FIRST|LAST：指定推動(dòng)游標(biāo)指針的方式：指定推動(dòng)游標(biāo)指針的方式 NEXT：向前推進(jìn)一條記錄：向前推進(jìn)一條記錄 PRIOR：向回退一條記錄：向回退一條記錄

23、FIRST：推向第一條記錄：推向第一條記錄 LAST：推向最后一條記錄：推向最后一條記錄 缺省值為缺省值為NEXTAn Introduction to Database System4. 關(guān)閉游標(biāo)關(guān)閉游標(biāo)v 使用使用CLOSE語(yǔ)句語(yǔ)句v 語(yǔ)句格式語(yǔ)句格式 EXEC SQL CLOSE ;v 功能功能 關(guān)閉游標(biāo)，釋放結(jié)果集占用的緩沖區(qū)及其他資源關(guān)閉游標(biāo)，釋放結(jié)果集占用的緩沖區(qū)及其他資源v 說(shuō)明說(shuō)明 游標(biāo)被關(guān)閉后，就不再和原來(lái)的查詢(xún)結(jié)果集相聯(lián)系游標(biāo)被關(guān)閉后，就不再和原來(lái)的查詢(xún)結(jié)果集相聯(lián)系 被關(guān)閉的游標(biāo)可以再次被打開(kāi)，與新的查詢(xún)結(jié)果相聯(lián)系被關(guān)閉的游標(biāo)可以再次被打開(kāi)，與新的查詢(xún)結(jié)果相聯(lián)系A(chǔ)n Intr

24、oduction to Database System二、二、CURRENT形式的形式的UPDATE語(yǔ)句和語(yǔ)句和DELETE語(yǔ)句語(yǔ)句vCURRENT形式的形式的UPDATE語(yǔ)句和語(yǔ)句和DELETE語(yǔ)句語(yǔ)句的用途的用途 面向集合的操作面向集合的操作 一次修改或刪除所有滿(mǎn)足條件的記錄一次修改或刪除所有滿(mǎn)足條件的記錄An Introduction to Database SystemCURRENT形式的形式的UPDATE語(yǔ)句和語(yǔ)句和DELETE語(yǔ)句語(yǔ)句(續(xù)續(xù)) 如果只想修改或刪除其中某個(gè)記錄如果只想修改或刪除其中某個(gè)記錄用帶游標(biāo)的用帶游標(biāo)的SELECT語(yǔ)句查出所有滿(mǎn)足條件的記錄語(yǔ)句查出所有滿(mǎn)足條件的

25、記錄從中進(jìn)一步找出要修改或刪除的記錄從中進(jìn)一步找出要修改或刪除的記錄用用CURRENT形式的形式的UPDATE語(yǔ)句和語(yǔ)句和DELETE語(yǔ)句修改或語(yǔ)句修改或刪除之刪除之UPDATE語(yǔ)句和語(yǔ)句和DELETE語(yǔ)句中的子句語(yǔ)句中的子句： WHERE CURRENT OF 表示修改或刪除的是最近一次取出的記錄，即游標(biāo)指針指向表示修改或刪除的是最近一次取出的記錄，即游標(biāo)指針指向的記錄的記錄 An Introduction to Database SystemCURRENT形式的形式的UPDATE語(yǔ)句和語(yǔ)句和DELETE語(yǔ)句語(yǔ)句(續(xù)續(xù))v不能使用不能使用CURRENT形式的形式的UPDATE語(yǔ)句和語(yǔ)句和DE

26、LETE語(yǔ)句語(yǔ)句 : 當(dāng)游標(biāo)定義中的當(dāng)游標(biāo)定義中的SELECT語(yǔ)句帶有語(yǔ)句帶有UNION或或ORDER BY子句子句 該該SELECT語(yǔ)句相當(dāng)于定義了一個(gè)不可更新的視圖語(yǔ)句相當(dāng)于定義了一個(gè)不可更新的視圖 An Introduction to Database System第四章第四章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1 SQL概述概述4.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫(kù)課程數(shù)據(jù)庫(kù)4.3 數(shù)據(jù)定義數(shù)據(jù)定義4.4 數(shù)據(jù)查詢(xún)數(shù)據(jù)查詢(xún)4.5 數(shù)據(jù)更新數(shù)據(jù)更新4.6 視圖視圖4.7 嵌入式嵌入式SQL4.8 *使用游標(biāo)的使用游標(biāo)的SQL語(yǔ)句語(yǔ)句4.9 *動(dòng)態(tài)動(dòng)態(tài)SQL4.10 *PL/SQL與存儲(chǔ)

27、過(guò)程與存儲(chǔ)過(guò)程An Introduction to Database System 4.9 動(dòng)態(tài)動(dòng)態(tài)SQLv 靜態(tài)嵌入式SQL 靜態(tài)嵌入式SQL語(yǔ)句能夠滿(mǎn)足一般要求 無(wú)法滿(mǎn)足要到執(zhí)行時(shí)才能夠確定要提交的SQL語(yǔ)句 v 動(dòng)態(tài)嵌入式SQL 允許在程序運(yùn)行過(guò)程中臨時(shí)“組裝”SQL語(yǔ)句 支持動(dòng)態(tài)組裝SQL語(yǔ)句和動(dòng)態(tài)參數(shù)兩種形式 An Introduction to Database System動(dòng)態(tài)動(dòng)態(tài)SQL簡(jiǎn)介（續(xù)）簡(jiǎn)介（續(xù)）v一、使用SQL語(yǔ)句主變量 v二、動(dòng)態(tài)參數(shù)An Introduction to Database System一、使用一、使用SQL語(yǔ)句主變量語(yǔ)句主變量vSQL語(yǔ)句主變量： 程

28、序主變量包含的內(nèi)容是SQL語(yǔ)句的內(nèi)容，而不是原來(lái)保存數(shù)據(jù)的輸入或輸出變量 SQL語(yǔ)句主變量在程序執(zhí)行期間可以設(shè)定不同的SQL語(yǔ)句，然后立即執(zhí)行 An Introduction to Database System使用使用SQL語(yǔ)句主變量（續(xù)）語(yǔ)句主變量（續(xù)）例9 創(chuàng)建基本表TESTEXEC SQL BEGIN DECLARE SECTION;const char *stmt = CREATE TABLE test(a int); /* SQL語(yǔ)句主變量 */EXEC SQL END DECLARE SECTION;. .EXEC SQL EXECUTE IMMEDIATE :stmt; /*

29、執(zhí)行語(yǔ)句 */An Introduction to Database System二、動(dòng)態(tài)參數(shù)二、動(dòng)態(tài)參數(shù)v動(dòng)態(tài)參數(shù) SQL語(yǔ)句中的可變?cè)?使用參數(shù)符號(hào)(?)表示該位置的數(shù)據(jù)在運(yùn)行時(shí)設(shè)定v和主變量的區(qū)別 動(dòng)態(tài)參數(shù)的輸入不是編譯時(shí)完成綁定 而是通過(guò) (prepare)語(yǔ)句準(zhǔn)備主變量和執(zhí)行(execute)時(shí)綁定數(shù)據(jù)或主變量來(lái)完成 An Introduction to Database System動(dòng)態(tài)參數(shù)（續(xù)）動(dòng)態(tài)參數(shù)（續(xù)）v使用動(dòng)態(tài)參數(shù)的步驟：1.聲明SQL語(yǔ)句主變量。 const char *stmt = INSERT INTO test VALUES(?);2.準(zhǔn)備SQL語(yǔ)句(PREPA

30、RE)。 EXEC SQL PREPARE FROM ;An Introduction to Database System動(dòng)態(tài)參數(shù)（續(xù)）動(dòng)態(tài)參數(shù)（續(xù)）v使用動(dòng)態(tài)參數(shù)的步驟（續(xù)）： 3.執(zhí)行準(zhǔn)備好的語(yǔ)句(EXECUTE)EXEC SQL EXECUTE INTO USING ;An Introduction to Database System動(dòng)態(tài)參數(shù)（續(xù)）動(dòng)態(tài)參數(shù)（續(xù)）例10向TEST中插入元組。EXEC SQL BEGIN DECLARE SECTION;const char *stmt = INSERT INTO test VALUES(?); /*聲明SQL主變量 */EXEC SQL

31、 END DECLARE SECTION;. .EXEC SQL PREPARE mystmt FROM :stmt; /* 準(zhǔn)備語(yǔ)句 */. .EXEC SQL EXECUTE mystmt USING 100; /* 執(zhí)行語(yǔ)句 */EXEC SQL EXECUTE mystmt USING 200; /* 執(zhí)行語(yǔ)句 */An Introduction to Database System第四章第四章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1 SQL概述概述4.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫(kù)課程數(shù)據(jù)庫(kù)4.3 數(shù)據(jù)定義數(shù)據(jù)定義4.4 數(shù)據(jù)查詢(xún)數(shù)據(jù)查詢(xún)4.5 數(shù)據(jù)更新數(shù)據(jù)更新4.6 視圖視圖4

32、.7 嵌入式嵌入式SQL4.8 *使用游標(biāo)的使用游標(biāo)的SQL語(yǔ)句語(yǔ)句4.9 *動(dòng)態(tài)動(dòng)態(tài)SQL4.10 *PL/SQL與存儲(chǔ)過(guò)程與存儲(chǔ)過(guò)程An Introduction to Database System4.10 *PL/SQL與存儲(chǔ)過(guò)程與存儲(chǔ)過(guò)程v為了增強(qiáng)SQL語(yǔ)言的處理能力，為了提高數(shù)據(jù)庫(kù)系統(tǒng)的性能，許多數(shù)據(jù)庫(kù)廠(chǎng)家擴(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ò)程。An Introduction to Database System4.10 存儲(chǔ)

33、過(guò)程存儲(chǔ)過(guò)程4.10.1 PL/SQL與存儲(chǔ)過(guò)程概述與存儲(chǔ)過(guò)程概述4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)4.10.3 控制結(jié)構(gòu)控制結(jié)構(gòu)4.10.4 異常處理異常處理4.10.5 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.6 游標(biāo)游標(biāo)4.10.7 觸發(fā)器觸發(fā)器An Introduction to Database System4.10.1 PL/SQL與存儲(chǔ)過(guò)程概述與存儲(chǔ)過(guò)程概述vPL/SQL ： (1)什么是PL/SQL PL/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ǔ)在

34、數(shù)據(jù)庫(kù)服務(wù)器中的過(guò)程。它們成為SQL服務(wù)器模塊，使用時(shí)只要調(diào)用即可。An Introduction to Database System4.10 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.1 PL/SQL與存儲(chǔ)過(guò)程概述與存儲(chǔ)過(guò)程概述4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)4.10.3 控制結(jié)構(gòu)控制結(jié)構(gòu)4.10.4 異常處理異常處理4.10.5 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.6 游標(biāo)游標(biāo)4.10.7 觸發(fā)器觸發(fā)器An Introduction to Database System4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)vPL/SQL塊可以分為3部分，即聲明部分、可執(zhí)行部分和異常處理部分。其中執(zhí)行部分是必

35、須的，其他兩個(gè)部分可選。 An Introduction to Database System4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)vPL/SOL塊的基本結(jié)構(gòu)：塊的基本結(jié)構(gòu)： 1.聲明部分聲明部分 DECLARE -變量、常量、游標(biāo)、異常等 n定義的變量、常量等只能在該基本塊中使用n當(dāng)基本塊執(zhí)行結(jié)束時(shí)，定義就不再存在A(yíng)n Introduction to Database System變量常量的定義變量常量的定義1）. PL/SQL中定義變量的語(yǔ)法形式是中定義變量的語(yǔ)法形式是: 變量名 數(shù)據(jù)類(lèi)型 NOT NULL:=初值表達(dá)式 V1 varchar2(20);2）. 常量的定義類(lèi)似于變量

36、的定義常量的定義類(lèi)似于變量的定義: 常量名 數(shù)據(jù)類(lèi)型 CONSTANT :=常量表達(dá)式 常量必須要給一個(gè)值，并且該值在存在期間或常量的作用域內(nèi)不能改變。如果試圖修改它，PL/SQL將返回一個(gè)異常。3）. 賦值語(yǔ)句賦值語(yǔ)句 變量名稱(chēng):=表達(dá)式An Introduction to Database System4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)vPL/SOL塊的基本結(jié)構(gòu)塊的基本結(jié)構(gòu)(續(xù)續(xù))： 2.可執(zhí)行部分可執(zhí)行部分 BEGIN -SQL語(yǔ)句、PL/SQL的流程控制語(yǔ)句 EXCEPTION -異常處理部分 END；An Introduction to Database System4.

37、10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)vPL/SOL塊的基本結(jié)構(gòu)塊的基本結(jié)構(gòu)(續(xù)續(xù))： 3.異常處理部分異常處理部分 exception 這一部分是可選的，在這一部分中處理異?；蝈e(cuò)誤，對(duì)異常處理的詳細(xì)討論在后面進(jìn)行。An Introduction to Database System4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)例例4-103 向課程Course表中插入10門(mén)課程基本信息，課程編號(hào)從0001到0010。DECLARE CNO NUMBER;BEGINCNO:=0001;LOOPINSERT INTO Course(CNO) VALUES(TO_CHAR(CNO, 9999)

38、；CNO:=CNO+1;EXIT WHEN CNO0010;END LOOP;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(ERROR);END;An Introduction to Database System4.10 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.1 PL/SQL與存儲(chǔ)過(guò)程概述與存儲(chǔ)過(guò)程概述4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)4.10.3 控制結(jié)構(gòu)控制結(jié)構(gòu)4.10.4 異常處理異常處理4.10.5 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.6 游標(biāo)游標(biāo)4.10.7 觸發(fā)器觸發(fā)器An Introduction to Database System4.10

39、.3 控制結(jié)構(gòu)控制結(jié)構(gòu) vPL/SQL 功能：功能：一、條件控制語(yǔ)句二、循環(huán)控制語(yǔ)句 三、錯(cuò)誤處理 An Introduction to Database System控制結(jié)構(gòu)（續(xù)）控制結(jié)構(gòu)（續(xù)）v一、一、 條件控制語(yǔ)句條件控制語(yǔ)句 IF-THEN， IF-THEN-ELSE和嵌套的和嵌套的IF語(yǔ)句語(yǔ)句 1. IF condition THEN Sequence_of_statements; END IF 2. IF condition THEN Sequence_of_statements1; ELSE Sequence_of_statements2; END IF;3. 在THEN和ELS

40、E子句中還可以再包括IF語(yǔ)句，即IF語(yǔ)句可以嵌套 An Introduction to Database System控制結(jié)構(gòu)（續(xù)）控制結(jié)構(gòu)（續(xù)）二、循環(huán)控制語(yǔ)句二、循環(huán)控制語(yǔ)句 LOOP， WHILE-LOOP和和FOR-LOOP 1.最簡(jiǎn)單的循環(huán)語(yǔ)句最簡(jiǎn)單的循環(huán)語(yǔ)句LOOP LOOP Sequence_of_statements; END LOOP; 多數(shù)數(shù)據(jù)庫(kù)服務(wù)器的PL/SQL都提供EXIT、BREAK或LEAVE等循環(huán)結(jié)束語(yǔ)句，保證LOOP語(yǔ)句塊能夠結(jié)束。An Introduction to Database System控制結(jié)構(gòu)（續(xù)）控制結(jié)構(gòu)（續(xù)）二、循環(huán)控制語(yǔ)句（續(xù)）二、循環(huán)控制

41、語(yǔ)句（續(xù)）2. WHILE-LOOP WHILE condition LOOP Sequence_of_statements;END LOOP;n每次執(zhí)行循環(huán)體語(yǔ)句之前，首先對(duì)條件進(jìn)行求值n如果條件為真，則執(zhí)行循環(huán)體內(nèi)的語(yǔ)句序列。n如果條件為假，則跳過(guò)循環(huán)并把控制傳遞給下一個(gè)語(yǔ)句 3. FOR-LOOP FOR count IN REVERSEbound1 bound2 LOOP Sequence_of_statements;END LOOP;An Introduction to Database System控制結(jié)構(gòu)（續(xù)）控制結(jié)構(gòu)（續(xù)）v三、錯(cuò)誤處理： 如果PL/SQL在執(zhí)行時(shí)出現(xiàn)異常，則應(yīng)

42、該讓程序在產(chǎn)生異常的語(yǔ)句處停下來(lái)，根據(jù)異常的類(lèi)型去執(zhí)行異常處理語(yǔ)句 An Introduction to Database System4.10 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.1 PL/SQL與存儲(chǔ)過(guò)程概述與存儲(chǔ)過(guò)程概述4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)4.10.3 控制結(jié)構(gòu)控制結(jié)構(gòu)4.10.4 異常處理異常處理4.10.5 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.6 游標(biāo)游標(biāo)4.10.7 觸發(fā)器觸發(fā)器An Introduction to Database System4.10.4 異常處理異常處理v異常處理分為系統(tǒng)預(yù)定義異常處理和自定義異常處理兩部分。v系統(tǒng)預(yù)定義異常處理是針對(duì)PL/SQL程序編譯

43、、執(zhí)行過(guò)程中發(fā)生的問(wèn)題進(jìn)行處理的程序。 讀者可以嘗試修改正常運(yùn)行的程序，并執(zhí)行修改后的程序，就可以發(fā)現(xiàn)調(diào)用了哪些異常處理。 An Introduction to Database System4.10.4 異常處理異常處理例例4-104 定義名為GRADEERROR的異常，在SC表中查找Sno等于0001的記錄，將他的成績(jī)存入變量tempGrade中，判斷tempGrade值，若不在0和100之間，說(shuō)明該學(xué)生的成績(jī)值有問(wèn)題，則激活異常處理，顯示相應(yīng)的提示信息。SET SERVEROUTPUT ONDECLAREGRADEERROR EXCEPTION;tempGrade NUMBER;BEGI

44、NSELECT GRADE INTO tempGradeFROM SCWHERE Sno=0001;IF tempGrade 100 THENRAISE GRADEERROR;END IF;EXCEPTIONWHEN GRADEERROR THENDBMS_OUTPUT.PUT_LINE(成績(jī)超出范圍);END;An Introduction to Database System4.10 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.1 PL/SQL與存儲(chǔ)過(guò)程概述與存儲(chǔ)過(guò)程概述4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)4.10.3 控制結(jié)構(gòu)控制結(jié)構(gòu)4.10.4 異常處理異常處理4.10.5 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4

45、.10.6 游標(biāo)游標(biāo)4.10.7 觸發(fā)器觸發(fā)器An Introduction to Database System存儲(chǔ)過(guò)程（續(xù)）存儲(chǔ)過(guò)程（續(xù)）v一、 存儲(chǔ)過(guò)程的優(yōu)點(diǎn) v二、 存儲(chǔ)過(guò)程的用戶(hù)接口 An Introduction to Database System存儲(chǔ)過(guò)程（續(xù)）存儲(chǔ)過(guò)程（續(xù)）v存儲(chǔ)過(guò)程：由PL/SQL語(yǔ)句書(shū)寫(xiě)的過(guò)程，經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，使用時(shí)只要調(diào)用即可。 v一、存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：1. 運(yùn)行效率高2. 降低了客戶(hù)機(jī)和服務(wù)器之間的通信量 3. 方便實(shí)施企業(yè)規(guī)則An Introduction to Database System存儲(chǔ)過(guò)程（續(xù)）存儲(chǔ)過(guò)程（續(xù)）v二、 存儲(chǔ)過(guò)

46、程的用戶(hù)接口：1. 創(chuàng)建存儲(chǔ)過(guò)程 2. 執(zhí)行存儲(chǔ)過(guò)程 3. 刪除存儲(chǔ)過(guò)程 An Introduction to Database System二、二、 存儲(chǔ)過(guò)程的用戶(hù)接口存儲(chǔ)過(guò)程的用戶(hù)接口v1. 創(chuàng)建存儲(chǔ)過(guò)程：創(chuàng)建存儲(chǔ)過(guò)程：CREATE Procedure 過(guò)程名（參數(shù)1，參數(shù)2，.） AS；n過(guò)程名：數(shù)據(jù)庫(kù)服務(wù)器合法的對(duì)象標(biāo)識(shí)n參數(shù)列表：用名字來(lái)標(biāo)識(shí)調(diào)用時(shí)給出的參數(shù)值，必須指定值的數(shù)據(jù)類(lèi)型。參數(shù)也可以定義輸入?yún)?shù)、輸出參數(shù)或輸入/輸出參數(shù)。默認(rèn)為輸入?yún)?shù)。n過(guò)程體：是一個(gè)。包括聲明部分和可執(zhí)行語(yǔ)句部分 An Introduction to Database System存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)

47、）存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）例11 利用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)下面的應(yīng)用: 從一個(gè)賬戶(hù)轉(zhuǎn)指定數(shù)額的款項(xiàng)到另一個(gè)賬戶(hù)中。 CREATE PROCEDURE TRANSFER(inAccount INT， outAccount INT， amount FLOAT) AS DECLARE totalDeposit FLOAT; BEGIN /* 檢查轉(zhuǎn)出賬戶(hù)的余額 */ SELECT total INTO totalDeposit FROM ACCOUNT WHERE ACCOUNTNUM=outAccount; IF totalDeposit IS NULL THEN /* 賬戶(hù)不存在或賬戶(hù)中沒(méi)有存款 */

48、 ROLLBACK; RETURN; END IF; An Introduction to Database System存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)） IF totalDeposit amount THEN /* 賬戶(hù)賬戶(hù)存款不足 */ ROLLBACK; RETURN;END IF; UPDATE account SET total=total-amount WHERE ACCOUNTNUM=outAccount; /* 修改轉(zhuǎn)出賬戶(hù)，減去轉(zhuǎn)出額 */ UPDATE account SET total=total + amount WHERE ACCOUNTNUM=inA

49、ccount; /* 修改轉(zhuǎn)入賬戶(hù)，增加轉(zhuǎn)出額 */COMMIT; /* 提交轉(zhuǎn)賬事務(wù) */END;An Introduction to Database System存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）v重命名存儲(chǔ)過(guò)程 ALTER Procedure 過(guò)程名1 RENAME TO 過(guò)程名2;An Introduction to Database System存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）v2. 執(zhí)行存儲(chǔ)過(guò)程：執(zhí)行存儲(chǔ)過(guò)程： CALL/PERFORM Procedure 過(guò)程名(參數(shù)1，參數(shù)2，.)；n使用CALL或者PERFORM等方式激活存儲(chǔ)過(guò)程的執(zhí)行。n在P

50、L/SQL中，數(shù)據(jù)庫(kù)服務(wù)器支持在過(guò)程體中調(diào)用其他存儲(chǔ)過(guò)程例12從賬戶(hù)01003815868轉(zhuǎn)一萬(wàn)元到01003813828賬戶(hù)中。 CALL Procedure TRANSFER(01003813828，01003815868，10000)； An Introduction to Database System存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）存儲(chǔ)過(guò)程的用戶(hù)接口（續(xù)）3. 刪除存儲(chǔ)過(guò)程 DROP PROCEDURE 過(guò)程名（）；An Introduction to Database System4.10 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程4.10.1 PL/SQL與存儲(chǔ)過(guò)程概述與存儲(chǔ)過(guò)程概述4.10.2 PL/SQL的程序結(jié)構(gòu)的程序結(jié)構(gòu)4.10.3 控制結(jié)構(gòu)控制結(jié)構(gòu)4.10.4