數(shù)據(jù)庫(kù)系統(tǒng)原理-第九章 服務(wù)器環(huán)境下的SQL

1、第第9章章 服務(wù)器環(huán)境下的服務(wù)器環(huán)境下的SQL 9.1 三層體系結(jié)構(gòu)9.2 SQL環(huán)境9.3 SQL/宿主語(yǔ)言接口9.4 存儲(chǔ)過(guò)程9.5 使用調(diào)用層接口Page 19.1 三層體系結(jié)構(gòu) 具有三種不同而又相互聯(lián)系的功能： Web服務(wù)器 指那些連接客戶(hù)端和數(shù)據(jù)庫(kù)系統(tǒng)的進(jìn)程 應(yīng)用服務(wù)器 系統(tǒng)要做的所有操作。 數(shù)據(jù)庫(kù)服務(wù)器 這些進(jìn)程運(yùn)行DBMS并執(zhí)行應(yīng)用服務(wù)器請(qǐng)求的查詢(xún)和更新。Page 2Web服務(wù)器 Web服務(wù)器進(jìn)程管理與用戶(hù)交互，當(dāng)用戶(hù)發(fā)起連接時(shí)，可通過(guò)打開(kāi)URL，Web服務(wù)器則響應(yīng)用戶(hù)請(qǐng)求。之后用戶(hù)便成為Web服務(wù)器進(jìn)程的一個(gè)客戶(hù)端。Page 3應(yīng)用層 應(yīng)用層的工作是將數(shù)據(jù)作為響應(yīng)從數(shù)據(jù)庫(kù)發(fā)回給

2、那些從Web服務(wù)器獲得的請(qǐng)求。每個(gè)Web服務(wù)器的進(jìn)程可以調(diào)用一個(gè)或多個(gè)應(yīng)用層的進(jìn)程來(lái)處理請(qǐng)求。 被應(yīng)用層執(zhí)行的操作通常稱(chēng)為數(shù)據(jù)庫(kù)運(yùn)行結(jié)構(gòu)的交易邏輯。即應(yīng)用層是根據(jù)推理潛在客戶(hù)的請(qǐng)求響應(yīng)和實(shí)現(xiàn)這些推理的策略來(lái)設(shè)計(jì)。Page 4數(shù)據(jù)庫(kù)層 數(shù)據(jù)庫(kù)層具有多個(gè)進(jìn)程，它們能被分配到多臺(tái)機(jī)器，也可以在一臺(tái)機(jī)器上共同工作。數(shù)據(jù)庫(kù)層負(fù)責(zé)執(zhí)行來(lái)自應(yīng)用層請(qǐng)求的查詢(xún)，另外提供一些數(shù)據(jù)緩沖。 由于與數(shù)據(jù)庫(kù)連接的時(shí)間不能忽略，通常保持一定數(shù)量的連接處于開(kāi)放狀態(tài)，同時(shí)允許應(yīng)用進(jìn)程共享這些連接。為避免應(yīng)用進(jìn)程之間的一些異常交互，每一個(gè)應(yīng)用進(jìn)程必須返回它獲得的連接狀態(tài)。Page 569.2 SQL環(huán)境 SQL環(huán)境是SQL中數(shù)據(jù)

3、所在的框架，為數(shù)據(jù)操作提供支持，即一個(gè)運(yùn)行環(huán)境(如DBMS運(yùn)行環(huán)境)。 數(shù)據(jù)庫(kù)的所有元素，包括表、視圖、觸發(fā)器等，都是在SQL環(huán)境中定義的。這些元素組成了層次層次性結(jié)構(gòu)性結(jié)構(gòu)： 模式模式 目錄目錄 簇簇71. 模式 模式是組織的基本單元。 模式是表、視圖、斷言、觸發(fā)器等模式元素的集合。 模式聲明的形式模式聲明的形式CREATE SCHEMA 模式聲明如圖模式聲明如圖9-381. 模式(續(xù)) CREATE SCHEMA 提供了在SQL語(yǔ)句中創(chuàng)建表、視圖以及授予對(duì)象權(quán)限的方法，并區(qū)別重名，即屬于不同模式的模式元素可以重名，可用“模式名.模式元素名”來(lái)引用某個(gè)模式元素。 沒(méi)有必要一次就聲明完所有的模

4、式。可以使用合適的CREATE、DROP或ALTER語(yǔ)句來(lái)修改或增加模式 使用SET SCHEMA語(yǔ)句改變“當(dāng)前”的模式。 例如： SET SCHEMA MovieSchama ; 將使圖9-3描述的模式作為當(dāng)前模式92. 目錄 模式的集合。每個(gè)目錄有一個(gè)或多個(gè)模式，目錄中的模式名必須唯一，每個(gè)目錄包含一個(gè)叫INFORMATION_SCHEMA的特殊模式，這個(gè)模式包含了該目錄中所有模式的信息。 沒(méi)有CREATE CATALOG語(yǔ)句，只規(guī)定了設(shè)設(shè)置當(dāng)前目錄的語(yǔ)句：置當(dāng)前目錄的語(yǔ)句： SET CATALOG ； 對(duì)模式的操作(新建/修改模式等)都是針對(duì)當(dāng)前目錄10模式元素的全名 . . 例如：例如

5、：目錄MovieCatalog中的模式MovieSchema的表Movies的引用如下：MovieCatalog . MovieSchema . Movies 如果目錄是缺省的或是當(dāng)前的目錄，則可省去目錄名 如果模式也是缺省的或當(dāng)前的模式，那么模式部分也可以省去，這樣只留下元素自己的名稱(chēng)。 然而，當(dāng)需要訪問(wèn)當(dāng)前模式或目錄以外的元素時(shí)，就不得不使用完全名。113. 簇 簇是目錄的集合。每個(gè)用戶(hù)有一個(gè)關(guān)聯(lián)的簇：用戶(hù)可訪問(wèn)的所有目錄的集合。簇是被提交的查詢(xún)的最大范圍，故在一定程度上，簇是特定用戶(hù)所看到的“數(shù)據(jù)庫(kù)”。12SQL環(huán)境中的客戶(hù)端和服務(wù)器 依照SQL標(biāo)準(zhǔn)，SQL環(huán)境有兩種特殊的進(jìn)程： SQL

6、 服務(wù)器：服務(wù)器：支持對(duì)數(shù)據(jù)庫(kù)元素的操作，充當(dāng)數(shù)據(jù)庫(kù)服務(wù)器的角色。 SQL客戶(hù)端：客戶(hù)端：允許用戶(hù)連接到服務(wù)器上，對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作 例如： 服務(wù)器運(yùn)行在一個(gè)大的存儲(chǔ)數(shù)據(jù)庫(kù)的主機(jī)上，而客戶(hù)端運(yùn)行在其他主機(jī)上，也許是遠(yuǎn)離服務(wù)器的個(gè)人工作站，也許是和服務(wù)器運(yùn)行在同一臺(tái)主機(jī)上。13連接 如果在SQL客戶(hù)端主機(jī)上運(yùn)行包含SQL 的程序，則可通過(guò)下面的SQL語(yǔ)句打開(kāi)客戶(hù)端和服務(wù)器間的連接： CONNECT TO AS AUTHORIZATION 依賴(lài)于安裝?？捎妹Q(chēng)DEFAULT將用戶(hù)連接到任何被作為“缺省服務(wù)器”安裝的SQL服務(wù)器。如果僅是在SQL客戶(hù)端主機(jī)上執(zhí)行SQL語(yǔ)句，也可為自己建立一個(gè)缺省連接

7、可在以后用于引用連接。原因是SQL允許用戶(hù)打開(kāi)好幾個(gè)連接，但任何時(shí)候只有一個(gè)連接有效 AUTHORIZATION授權(quán)子句授權(quán)子句后跟隨著用戶(hù)名和密碼。14引用連接 將將conn1切換為有效連接：切換為有效連接：SET CONNECTION conn1; 任何當(dāng)前有效的連接進(jìn)入休眠狀態(tài)后，只有用下任何當(dāng)前有效的連接進(jìn)入休眠狀態(tài)后，只有用下列語(yǔ)句顯式地調(diào)用才能將其激活：列語(yǔ)句顯式地調(diào)用才能將其激活：SET CONNECTION conn1; 斷開(kāi)連接時(shí)斷開(kāi)連接時(shí)conn1被中止，不是休眠，也不能被激被中止，不是休眠，也不能被激活活:DISCONNECT conn1; 注：如果連接創(chuàng)建后再也不被引用

8、，CONNECT TO子句中的AS和連接名可以省略15會(huì)話(huà) 連接有效時(shí)，執(zhí)行的連接有效時(shí)，執(zhí)行的SQL操作形成了一個(gè)會(huì)話(huà)操作形成了一個(gè)會(huì)話(huà) 會(huì)話(huà)和創(chuàng)建它的連接具有相同的生命周期會(huì)話(huà)和創(chuàng)建它的連接具有相同的生命周期。例如: 當(dāng)連接處于休眠狀態(tài)時(shí)，它的會(huì)話(huà)也處于休眠態(tài) SET CONNECTION語(yǔ)句可激活連接，同時(shí)激活相應(yīng)會(huì)話(huà)。連接和會(huì)話(huà)是客戶(hù)和服務(wù)器間鏈路的兩個(gè)方面16會(huì)話(huà) 每個(gè)會(huì)話(huà)有一個(gè)當(dāng)前目錄和該目錄中的一個(gè)當(dāng)前每個(gè)會(huì)話(huà)有一個(gè)當(dāng)前目錄和該目錄中的一個(gè)當(dāng)前模式模式 由語(yǔ)句SET SCHEMA和SET CATALOG進(jìn)行設(shè)置 每個(gè)會(huì)話(huà)都有一個(gè)授權(quán)用戶(hù)17模塊 模塊是對(duì)應(yīng)用程序而言的，SQL標(biāo)準(zhǔn)

9、提出三種模塊： 普通普通SQL界面：界面：用戶(hù)可以鍵入SQL服務(wù)器執(zhí)行的SQL語(yǔ)句。這種模式下，每個(gè)查詢(xún)或其他語(yǔ)句本身是一個(gè)模塊。 嵌套嵌套SQL：預(yù)處理器將這個(gè)嵌套的SQL語(yǔ)句轉(zhuǎn)變?yōu)镾QL系統(tǒng)的對(duì)應(yīng)函數(shù)或過(guò)程調(diào)用。編譯后的宿主語(yǔ)言程序(包括這些函數(shù)調(diào)用)是一個(gè)模塊。 真模塊：真模塊：含有存儲(chǔ)函數(shù)或過(guò)程集合的模塊。這些函數(shù)|過(guò)程部分是宿主語(yǔ)言代碼，部分是SQL語(yǔ)句，它們之間可通過(guò)參數(shù)或共享變量進(jìn)行通訊。 僅要求SQL的實(shí)現(xiàn)中至少提供一種類(lèi)型給用戶(hù)。189.3 SQL/宿主語(yǔ)言接口 用宿主語(yǔ)言編程，且用到了一些并非宿主語(yǔ)言的嵌套SQL語(yǔ)句,將整個(gè)程序提交給預(yù)處理器。 預(yù)處理器將嵌套SQL語(yǔ)句轉(zhuǎn)化

10、為調(diào)用函數(shù)，此函數(shù)把SQL語(yǔ)句當(dāng)作字符串參數(shù)，并且執(zhí)行這個(gè)SQL語(yǔ)句 經(jīng)過(guò)預(yù)處理的宿主語(yǔ)言程序隨后以通常的方式編譯19包含SQL語(yǔ)句的典型編程系統(tǒng) DBMS銷(xiāo)售商提供了必要的函數(shù)定義庫(kù)。這樣實(shí)現(xiàn)SQL的函數(shù)被執(zhí)行，并且整個(gè)程序像一個(gè)整體一樣運(yùn)作。 還有另一種方式：程序員直接用宿主語(yǔ)言編寫(xiě)程序，只是在必要時(shí)使用這些函數(shù)調(diào)用。這就是SQL編程的第3種方式“調(diào)用級(jí)界面/CLI”20阻抗不匹配問(wèn)題 連接SQL語(yǔ)句和常規(guī)編程語(yǔ)言的基本問(wèn)題就是阻抗不匹配：即SQL數(shù)據(jù)模式與編程語(yǔ)言模式差別非常大。 SQL：關(guān)系數(shù)據(jù)模型，不使用指針、結(jié)構(gòu)體、循環(huán)和轉(zhuǎn)移 常規(guī)編程語(yǔ)言：整型、實(shí)型、算術(shù)性、字符型、指針、記錄、

11、數(shù)組無(wú)法直接表示集合 因此，在SQL和常規(guī)編程語(yǔ)言之間不能直接轉(zhuǎn)移數(shù)據(jù)21SQL與常規(guī)編程語(yǔ)言二者缺一不可 SQL與常規(guī)編程語(yǔ)言二者缺一不可 SQL在很大程度上幫助程序員編寫(xiě)數(shù)據(jù)庫(kù)操作 而很多重要工作(如算法實(shí)現(xiàn))又離不開(kāi)常規(guī)編程語(yǔ)言 必須設(shè)計(jì)一種機(jī)制(例如提供接口)，允許程序的開(kāi)發(fā)既可以使用SQL，也可以使用常規(guī)編程語(yǔ)言(宿主語(yǔ)言)22SQL/宿主語(yǔ)言接口 數(shù)據(jù)庫(kù)只能由SQL語(yǔ)句訪問(wèn) 在數(shù)據(jù)庫(kù)和宿主語(yǔ)言程序之間的信息交換是通過(guò)共享變量共享變量實(shí)現(xiàn)的。這種變量允許出現(xiàn)在宿主語(yǔ)言和SQL語(yǔ)句中。 SQL中共享變量前面要加上冒號(hào)作為前綴 在宿主語(yǔ)言中這些變量并不需要冒號(hào)23關(guān)鍵字EXEC SQL

12、在宿主語(yǔ)言中使用SQL語(yǔ)句時(shí)，通過(guò)SQL語(yǔ)句前面的關(guān)鍵字EXEC SQL提示預(yù)處理器將有SQL代碼進(jìn)入。 系統(tǒng)將預(yù)處理這些語(yǔ)句，用宿主語(yǔ)言中合適的函數(shù)調(diào)用來(lái)代替這些語(yǔ)句，并充分利用與SQL相關(guān)的函數(shù)庫(kù)。24特殊變量SQLSTATE 在SQL標(biāo)準(zhǔn)中，SQLSTATE這個(gè)特殊變量用于連接宿主語(yǔ)言程序與SQL執(zhí)行系統(tǒng)。 SQLSTATE是五個(gè)字符的數(shù)組類(lèi)型。 每次調(diào)用SQL的庫(kù)函數(shù)，向SQLSTATE變量中存放一個(gè)代碼，該代碼表示調(diào)用過(guò)程中出現(xiàn)的問(wèn)題。25SQLSTATE變量的值 SQL標(biāo)準(zhǔn)同時(shí)指定了大量的五個(gè)字符的代碼和它們的意義。例如： 00000(五個(gè)零五個(gè)零): 沒(méi)有產(chǎn)生任何錯(cuò)誤 02000

13、:沒(méi)找到作為SQL查詢(xún)結(jié)果組成部分的元組。 這個(gè)代碼非常重要：它允許在宿主語(yǔ)言程序中創(chuàng)建一個(gè)循環(huán)并且每執(zhí)行一次循環(huán)檢查一個(gè)元組，當(dāng)關(guān)系中最后一個(gè)元組被檢查后中止該循環(huán)。26DECLARE節(jié) EXEC SQL BEGIN DECLARE SECTION； 共享變量的聲明共享變量的聲明 EXEC SQL END DECLARE SECTION； 聲明節(jié)中的變量聲明形式可以是宿主語(yǔ)言要求的任何形式。 為使聲明的變量有意義，共享變量類(lèi)型必須是宿主語(yǔ)言和SQL都可以處理的 如整型、實(shí)型和字符型，或者數(shù)組類(lèi)型27示例9.3EXEC SQL BEGIN DECLARE SECTION； char studi

14、oName50，studioAddr256； char SQLSTATE6；EXEC SQL END DECLARE SECTION；28使用共享變量后面是嵌套的后面是嵌套的SQL語(yǔ)句語(yǔ)句共享變量在共享變量在SQL中須加冒中須加冒號(hào)作前綴號(hào)作前綴29SQL語(yǔ)句的嵌入 任何不返回結(jié)果的SQL語(yǔ)句(即非查詢(xún)語(yǔ)句)都可以用EXEC SQL為前綴直接嵌入到宿主語(yǔ)言中 包括INSERT、DELETE和UPDATE語(yǔ)句以及那些創(chuàng)建、修改或者刪除表和視圖等模式元素的語(yǔ)句。 然而由于“阻抗不匹配”，select-from-where查詢(xún)不能直接嵌套到宿主語(yǔ)言。 查詢(xún)產(chǎn)生的結(jié)果是元組包，但是大多數(shù)宿主語(yǔ)言均不直

15、接支持集合或包數(shù)據(jù)類(lèi)型。30查詢(xún)嵌入宿主語(yǔ)言的機(jī)制 為了將查詢(xún)結(jié)果與宿主語(yǔ)言程序相連接，嵌套SQL有兩種機(jī)制可選擇： (1) 單元組選擇語(yǔ)句單元組選擇語(yǔ)句：只有一個(gè)結(jié)果元組的查詢(xún)可將該元組存儲(chǔ)到共享變量中，一個(gè)變量對(duì)應(yīng)元組的一個(gè)分量。 (2) 游標(biāo)：游標(biāo)：如果為查詢(xún)聲明一個(gè)游標(biāo)，那么產(chǎn)生多于一個(gè)元組的查詢(xún)就可以執(zhí)行了。游標(biāo)范圍覆蓋了結(jié)果關(guān)系中的所有元組，每個(gè)元組依次被提取到共享變量中，并由宿主語(yǔ)言進(jìn)行處理。31(1) 單元組選擇語(yǔ)句 單元組選擇的形式類(lèi)似于普通的select-from-where語(yǔ)句，只是SELECT子句后緊跟著關(guān)鍵字INTO和一連串的共享變量(以冒號(hào)作為前綴)。 如果查詢(xún)結(jié)果

16、是個(gè)單一元組，那么這個(gè)元組的分量將分配給這些變量并成為它們的值。 如果結(jié)果沒(méi)有元組或者多于一個(gè)元組，那么不會(huì)分配給這些共享變量，同時(shí)一個(gè)相應(yīng)的錯(cuò)誤碼被寫(xiě)入到SQLSTATE變量中。32單元組選擇示例Page 3233(2) 游標(biāo) 游標(biāo)聲明游標(biāo)聲明 EXEC SQL DECLARECURSOR FOR 其中查詢(xún)可以是通常的select-from-where查詢(xún)或者關(guān)系名。游標(biāo)范圍覆蓋該查詢(xún)產(chǎn)生的關(guān)系元組。 打開(kāi)游標(biāo)打開(kāi)游標(biāo) (初始化初始化) EXEC SQL OPEN 這個(gè)語(yǔ)句初始化游標(biāo)的位置，使游標(biāo)指向其覆蓋的那個(gè)關(guān)系中的第一個(gè)元組，并從那里開(kāi)始檢索。34(2) 游標(biāo)(續(xù)1) 一次或者多次使用

17、一次或者多次使用fetch子句子句 EXEC SQL FETCH FROMINTO 目的是得到游標(biāo)覆蓋的那個(gè)關(guān)系中的下一個(gè)元組。 每個(gè)關(guān)系元組的屬性對(duì)應(yīng)列表里的一個(gè)變量。假如有一個(gè)可獲取的元組，那么該元組相應(yīng)的分量將賦值給對(duì)應(yīng)的變量。如果元組已經(jīng)被遍歷過(guò)了，那么不會(huì)返回任何元組，且SQLSTATE被賦值為02000，表示“沒(méi)有發(fā)現(xiàn)任何元組”。35(2) 游標(biāo)(續(xù)2) 關(guān)閉游標(biāo)關(guān)閉游標(biāo) EXEC SQL CLOSE 這條語(yǔ)句關(guān)閉游標(biāo)，游標(biāo)將不再覆蓋關(guān)系的元組。 然而，游標(biāo)可由另外一條OPEN語(yǔ)句重新初始化，它將重新覆蓋這個(gè)關(guān)系的元組。36游標(biāo)示例 分別計(jì)算凈資產(chǎn)位數(shù)從1-14的出品人個(gè)數(shù)Page

18、 3637游標(biāo)更新 當(dāng)游標(biāo)遍歷一個(gè)基本表的元組時(shí)，不僅可以讀和處理每個(gè)元組的值，也可以修改或者刪除當(dāng)前元組。 這里的UPDATE和DELETE語(yǔ)句，要求其WHERE子句只能是WHERE CURRENT OF，其后跟著游標(biāo)的名稱(chēng)。38游標(biāo)更新示例39防止并發(fā)更新 不希望游標(biāo)讀取的元組被并發(fā)的變化所影響，而強(qiáng)調(diào)統(tǒng)計(jì)是針對(duì)某個(gè)時(shí)刻已存在的關(guān)系。 對(duì)游標(biāo)所讀取關(guān)系進(jìn)行修改的語(yǔ)句，在游標(biāo)讀取元組前就已徹底完成或在游標(biāo)讀取元組后再運(yùn)行 為了保證這一點(diǎn)，對(duì)于并發(fā)變化可以將游標(biāo)聲明為對(duì)并發(fā)修改不敏感(insensitive)。例如： EXEC SQL DECLARE execCursor INSENSITIV

19、E CURSOR FOR SELECT netWorth FROM MovieExec ; 保證在execCursor打開(kāi)和關(guān)閉之間，對(duì)關(guān)系MovieExec所作的變化不會(huì)影響游標(biāo)提取到的元組集合。40將游標(biāo)聲明為READ ONLY 若確信關(guān)系R上的游標(biāo)不會(huì)改變R，則該游標(biāo)可與R的不敏感游標(biāo)同時(shí)運(yùn)行。 將游標(biāo)聲明為FOR READ ONLY，那么數(shù)據(jù)庫(kù)系統(tǒng)可以保證基本關(guān)系R不會(huì)因?yàn)樽x取游標(biāo)而修改了關(guān)系R。 EXEC SQL DECLARE execCursor CURSOR FOR SELECT netWorth FROM MovieExec FOR READ ONLY; 這樣，任何試圖通過(guò)游

20、標(biāo)execCursor所做出的關(guān)系修改都會(huì)產(chǎn)生錯(cuò)誤。41動(dòng)態(tài)SQL 目前為止，嵌套在宿主語(yǔ)言中的SQL模型都是在宿主語(yǔ)言程序中特定的特定的SQL查詢(xún)和命令。 嵌套SQL的另一種形式是自身可以被宿主語(yǔ)言處理的語(yǔ)句。這種語(yǔ)句編譯時(shí)不可知編譯時(shí)不可知，因此，不能被SQL預(yù)處理器和宿主語(yǔ)言編譯器處理。 例如：例如：程序提示用戶(hù)輸入SQL查詢(xún)，然后讀這個(gè)查詢(xún)并執(zhí)行。 查詢(xún)被讀到后，將立即進(jìn)行語(yǔ)法分析，并且由SQL系統(tǒng)尋找適合執(zhí)行該查詢(xún)的方式。42動(dòng)態(tài)SQL的執(zhí)行 1. EXEC SQL PREPARE V FROM V是SQL變量，是其值為字符串的任意一條宿主語(yǔ)言表達(dá)式，該字符串被當(dāng)作SQL語(yǔ)句。 宿主

21、語(yǔ)言程序指導(dǎo)SQL系統(tǒng)接受剛讀到的字符串，經(jīng)語(yǔ)法分析后將其轉(zhuǎn)化為可執(zhí)行SQL語(yǔ)句, 用V表達(dá) 2. EXEC SQL EXECUTE V 執(zhí)行V所代表的SQL語(yǔ)句。43動(dòng)態(tài)SQL的執(zhí)行(2) 上述兩個(gè)步驟也可以合二為一 EXEC SQL EXECUTE IMMEDIATE +字符串表達(dá)式字符串表達(dá)式 如果一條語(yǔ)句被編譯一次，然后執(zhí)行很多次時(shí)，就會(huì)看到合并這兩步是不利的。 使用EXECUTE IMMEDIATE ，每次語(yǔ)句執(zhí)行時(shí)都要付出準(zhǔn)備該語(yǔ)句的代價(jià)，而不是只付出一次。44動(dòng)態(tài)SQL示例既然查詢(xún)只執(zhí)行一次，那么既然查詢(xún)只執(zhí)行一次，那么6)7)可替代為：可替代為：EXEC SQL EXECUTE

22、 IMMEDIATE:query459.4 存儲(chǔ)過(guò)程 Persistent Stored Modules: SQL/PSM(簡(jiǎn)寫(xiě)為簡(jiǎn)寫(xiě)為PSM) 允許用簡(jiǎn)單通用的語(yǔ)言編寫(xiě)過(guò)程并且將它們存儲(chǔ)在數(shù)據(jù)庫(kù)中，作為模式的一部分。這些過(guò)程可以在數(shù)據(jù)庫(kù)中完成不能用SQL單獨(dú)完成的處理。 每個(gè)商用性DBMS均向用戶(hù)提供了自身的PSM擴(kuò)展。本書(shū)描述的是SQL/PSM標(biāo)準(zhǔn)，表明這些功能的主要思想，幫助大家理解任何與特定系統(tǒng)相關(guān)的該類(lèi)語(yǔ)言。46PSM模塊 PSM中定義了模塊(modules)，該模塊是如下內(nèi)容的集合： 函數(shù)和過(guò)程定義 臨時(shí)關(guān)系聲明 其他可選聲明471. 創(chuàng)建PSM函數(shù)和過(guò)程 過(guò)程聲明過(guò)程聲明 PSM

23、過(guò)程的參數(shù)是模式前綴模式前綴-參數(shù)名參數(shù)名-參數(shù)類(lèi)型參數(shù)類(lèi)型三元組 模式前綴模式前綴為IN|OUT|INOUT。這三個(gè)關(guān)鍵字分別表明參數(shù)是僅輸入的僅輸入的|僅輸出的僅輸出的|既可輸入又可輸出既可輸入又可輸出的的。缺省前綴是IN，可省略。CREATE PROCEDURE () 可選的局部變量聲明可選的局部變量聲明 ； 定義過(guò)程的可執(zhí)行的代碼體定義過(guò)程的可執(zhí)行的代碼體48創(chuàng)建PSM函數(shù)和過(guò)程 函數(shù)聲明函數(shù)聲明 函數(shù)定義與過(guò)程定義的不同之處 使用保留字使用保留字FUNCTION； 必須指定返回值的類(lèi)型；必須指定返回值的類(lèi)型； 函數(shù)的參數(shù)只可以是函數(shù)的參數(shù)只可以是IN模式。模式。PSM阻止了函數(shù)中的副

24、作 用，從函數(shù)中得到信息的唯一方式是通過(guò)函數(shù)的返回值。 在過(guò)程定義中常指出IN模式，但函數(shù)參數(shù)中不指明。CREATE FUNCTION () RETURNS ；49存儲(chǔ)過(guò)程示例 存儲(chǔ)過(guò)程中SQL語(yǔ)句的限制：只允許查詢(xún)進(jìn)行單元組選擇語(yǔ)句和基于游標(biāo)的訪問(wèn)。 PSM過(guò)程將新舊兩個(gè)地址作為其參數(shù)，并且用新地址替換MovieStar中每一個(gè)舊地址。變量類(lèi)型與變量類(lèi)型與MovieStar模式定模式定義中義中address的類(lèi)的類(lèi)型相匹配型相匹配參數(shù)名可作常量參數(shù)名可作常量PSM過(guò)程和函數(shù)中過(guò)程和函數(shù)中的參數(shù)或別的局部的參數(shù)或別的局部變量不要求加冒號(hào)變量不要求加冒號(hào)502. PSM中的簡(jiǎn)單語(yǔ)句格式 調(diào)用語(yǔ)句

25、 返回語(yǔ)句 局部變量聲明 賦值語(yǔ)句 語(yǔ)句組 語(yǔ)句標(biāo)號(hào)51(1)過(guò)程調(diào)用的語(yǔ)句 CALL() 這種調(diào)用語(yǔ)句在不同的地方使用不同的形式： 在宿主語(yǔ)言中的調(diào)用形式在宿主語(yǔ)言中的調(diào)用形式 例如: EXEC SQL CALL Foo( :x, 3)； 作為另一個(gè)作為另一個(gè)PSM函數(shù)或過(guò)程的語(yǔ)句。函數(shù)或過(guò)程的語(yǔ)句。 作為發(fā)送給基本作為發(fā)送給基本SQL界面的界面的SQL命令。命令。 例如：把語(yǔ)句CALL Foo(1,3)；發(fā)送給該界面，并分別用 1和3作為賦值過(guò)程的兩個(gè)參數(shù)，然后執(zhí)行存儲(chǔ)過(guò)程Foo 注意：這里不允許調(diào)用函數(shù)。在PSM中調(diào)用函數(shù)應(yīng)使用函數(shù)名和匹配的參數(shù)作為表達(dá)式的一部分。52(2)返回語(yǔ)句 R

26、ETURN； 該語(yǔ)句只能出現(xiàn)在函數(shù)中。它計(jì)算表達(dá)式的值，并將函數(shù)的返回值設(shè)置為該計(jì)算結(jié)果。 和普通編程語(yǔ)言不同的是： PSM的返回語(yǔ)句不結(jié)束這個(gè)函數(shù)。 甚至，它將繼續(xù)控制后面的語(yǔ)句，而且在函數(shù)完成之前返回值都可能會(huì)改變。53(3)局部變量聲明 DECLARE； 用給定的類(lèi)型聲明給定名稱(chēng)的變量。 這個(gè)變量是局部的，在函數(shù)或者過(guò)程運(yùn)行后，DBMS不再保存其值。 函數(shù)或過(guò)程體中的聲明必須在可執(zhí)行語(yǔ)句之前54(4)賦值語(yǔ)句 SET=; 除了引導(dǎo)保留字SET外，PSM中的賦值和別的語(yǔ)言完全相似。計(jì)算等號(hào)右邊表達(dá)式的值，將其賦值給左邊的變量。 表達(dá)式可以是NULL，甚至可以是查詢(xún)，只要該查詢(xún)是返回一個(gè)單值

27、。55(5)語(yǔ)句組 語(yǔ)句組以分號(hào)結(jié)束，并置于保留字BEGIN和END之間。 這種構(gòu)造被當(dāng)作單個(gè)語(yǔ)句，可以出現(xiàn)在任何單個(gè)語(yǔ)句可以出現(xiàn)的地方。 特別是，由于過(guò)程或函數(shù)體相當(dāng)于單個(gè)語(yǔ)句，所以在過(guò)程和函數(shù)體中可插入任何語(yǔ)句序列，只要它們被置于BEGIN和END之間。56(6)語(yǔ)句標(biāo)號(hào) 語(yǔ)句標(biāo)號(hào)：用名字(標(biāo)號(hào)名)和冒號(hào)作為前綴來(lái)標(biāo)識(shí)語(yǔ)句。573. 分支語(yǔ)句(IF語(yǔ)句) 其形式與C和其他類(lèi)似語(yǔ)言的不同是： 用保留字END IF結(jié)束 嵌套在IF語(yǔ)句中的ELSE子句以單詞ELSEIF開(kāi)始可選可選任何布爾類(lèi)型的表達(dá)式任何布爾類(lèi)型的表達(dá)式語(yǔ)句列表由以分號(hào)結(jié)束語(yǔ)句列表由以分號(hào)結(jié)束的語(yǔ)句構(gòu)成，但不必置的語(yǔ)句構(gòu)成，但不

28、必置于于BEGINEND之間之間Page 5758課本示例P236例9.12 編寫(xiě)一個(gè)關(guān)于年份y和電影公司s的函數(shù)，它返回一個(gè)布爾值，其值為T(mén)RUE當(dāng)且僅當(dāng)電影公司s在第y年至少制作了一部喜劇電影，或其該年沒(méi)有制作任何電影594. PSM中select-from-where查詢(xún)方式 (1)子查詢(xún)可用于條件語(yǔ)句中，或者一般而言，在SQL中任何地方使用子查詢(xún)都是合法的。Page 59604. PSM中select-from-where查詢(xún)方式 (2)返回單一值的查詢(xún)可用在賦值語(yǔ)句的右邊。 (3)PSM中單元組選擇語(yǔ)句是合法語(yǔ)句。INTO子句將變量賦值為單個(gè)返回元組的分量。這些變量可以是局部變量或P

29、SM過(guò)程的參數(shù)。 SET presNetWorth=(SELECT netWorth FROM Studio,MovieExec WHERE presC#=cert# AND S=studioName);614. PSM中select-from-where查詢(xún)方式 (4)聲明和使用游標(biāo)。下面幾點(diǎn)是不同的： 語(yǔ)句中不出現(xiàn)EXEC SQL 局部變量不使用冒號(hào)前綴 使用游標(biāo)的例子將被延遲到下一節(jié)學(xué)習(xí)了PSM循環(huán)語(yǔ)句之后625. PSM中的LOOP循環(huán) LOOP END LOOP； 若對(duì)LOOP語(yǔ)句設(shè)置循環(huán)標(biāo)識(shí)，可使用下面的語(yǔ)句中斷循環(huán)： LEAVE; 循環(huán)中可用游標(biāo)讀取元組，當(dāng)沒(méi)

30、有更多元組時(shí)，就希望離開(kāi)這個(gè)循環(huán)。對(duì)于表示沒(méi)有找到元組的SQLSTATE值( 02000)，可以定義一個(gè)條件名： DECLARE Not_Found CONDITION FOR SQLSTATE 02000； 更一般地，可以用如下語(yǔ)句聲明表示任何希望與SQLSTATE值相對(duì)應(yīng)的標(biāo)識(shí)作為條件： DECLARE CONDITION FOR SQLSTATE ;63PSM循環(huán)示例 本例的PSM過(guò)程將電影公司名稱(chēng)s作為輸入?yún)?shù)，并且用輸出參數(shù)mean和variance給出電影公司s擁有的所有電影長(zhǎng)度的平均值和方差。Page 63646.PSM中的FOR循環(huán) PSM中的FOR循環(huán)結(jié)構(gòu)，唯一重要的目的是游

31、標(biāo)的迭代。65FOR循環(huán)示例Page 6566PSM中的其他循環(huán) PSM中也有while和repeat循環(huán)，其含義與C相同。也就是說(shuō)，可以創(chuàng)建如下形式的循環(huán) WHILEDO END WHILE； 或者這種形式的循環(huán)： REPEAT UNTIL END REPEAT;677. PSM的異常處理 SQL系統(tǒng)通過(guò)在長(zhǎng)為5個(gè)字符的字符串SQLSTATE變量中設(shè)置非零數(shù)字序列來(lái)表明錯(cuò)誤條件。例如： 02000表示“沒(méi)有找到元組” 21000表示單元組選擇返回了多個(gè)元組 PSM可以聲明稱(chēng)為異常處理異常處理的代碼。即在語(yǔ)句或語(yǔ)句組執(zhí)行過(guò)程中，當(dāng)錯(cuò)誤代碼列表中的任何一個(gè)出現(xiàn)在SQLSTATE中時(shí)，就調(diào)用異常處

32、理。68異常處理的組成 每一個(gè)異常處理都和一個(gè)由BEGINEND描述的代碼塊有關(guān)。處理過(guò)程出現(xiàn)在代碼塊中，并且僅僅針對(duì)代碼塊中的語(yǔ)句。 異常處理的組成異常處理的組成 一組異常條件：當(dāng)這些條件成立時(shí)調(diào)用異常處理 當(dāng)異常發(fā)生時(shí)，與該異常相關(guān)聯(lián)的執(zhí)行代碼 指明處理器完成處理后的轉(zhuǎn)移去處。69異常處理聲明的形式 DECLARE HANDLER FOR 有3種選擇： CONTINUE：表示執(zhí)行異常處理聲明中的語(yǔ)句之后，繼續(xù)執(zhí)行產(chǎn)生異常的語(yǔ)句之后的那條語(yǔ)句。 EXIT：表示執(zhí)行異常處理語(yǔ)句后，控制離開(kāi)聲明異常處理的BEGINEND塊，下一步執(zhí)行該代碼塊之后的語(yǔ)句 UNDO：與EXIT差不多，區(qū)別在于到目前為止，已執(zhí)行的該塊語(yǔ)句對(duì)數(shù)據(jù)庫(kù)或局部變量產(chǎn)生的變化都被撤消 由逗號(hào)分隔的條件的列表，可以是被聲明的條件，也可以是SQLSTATE和5位字符串的表達(dá)式。70異常示例 編寫(xiě)一個(gè)PSM函數(shù)，以電影片名作為參數(shù)，返回電影的年份。如果該片名的電影不存在或是不止一個(gè)的 話(huà)，則返回NULL。如果有多條語(yǔ)句，則放在如果有多條語(yǔ)句，則放在BEGINEND之間之間718. 使用PSM函數(shù)和過(guò)程 在嵌套的SQL程序、PSM代碼或提供給基本界面的普通S