數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用05

SQL語(yǔ)句提供了數(shù)據(jù)操縱的能力，但不支持結(jié)構(gòu)化編程，當(dāng)要實(shí)現(xiàn)復(fù)雜的應(yīng)用時(shí)，需要數(shù)據(jù)庫(kù)管理系統(tǒng)提供過(guò)程化的編程支持。

Oracle利用PL/SQL（ProcedureLanguage/StructureQueryLanguage，過(guò)程語(yǔ)言）來(lái)進(jìn)行結(jié)構(gòu)化編程。在PL/SQL中，最重要的是存儲(chǔ)過(guò)程和觸發(fā)器。第五章存儲(chǔ)過(guò)程與觸發(fā)器5.1

基本概念5.1.1PL/SQL程序塊將邏輯上相關(guān)的語(yǔ)句組織在一個(gè)程序塊內(nèi)；通過(guò)嵌入或調(diào)用子塊，構(gòu)造功能強(qiáng)大的程序；可將一個(gè)復(fù)雜的問(wèn)題分解成為一組便于管理、定義和實(shí)現(xiàn)的小塊。

PL/SQL(ProceduralLanguage/SQL)是ORACLE對(duì)標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)語(yǔ)言的擴(kuò)展，從版本6開始，ORACLE公司已經(jīng)將PL/SQL整合到ORACLE服務(wù)器和其他工具中。對(duì)SQL的擴(kuò)充：具有為程序開發(fā)而設(shè)計(jì)的特性；

過(guò)程化：第四代語(yǔ)言的強(qiáng)大功能和靈活性與第三代語(yǔ)言的過(guò)程結(jié)構(gòu)的優(yōu)勢(shì)融為一體；

后臺(tái)技術(shù)：嵌入到Oracle服務(wù)器中的，可以把PL/SQL看作ORACLE服務(wù)器內(nèi)的一個(gè)引擎，所以具有很高的執(zhí)行效率和同Oracle數(shù)據(jù)庫(kù)的完美結(jié)合。1.什么是PL/SQL①其他Oracle工具集成在一起，是主要的后臺(tái)開發(fā)工具②更好的性能③過(guò)程化、模塊化④易用性、可維護(hù)性⑤可處理運(yùn)行錯(cuò)誤⑥大量的內(nèi)置程序包PL/SQL的特點(diǎn)

2.PL/SQL開發(fā)環(huán)境工具名稱開發(fā)商Web站點(diǎn)地址SQL*PlusOracle公司RapidSQLEmbarcadero公司Xpediter/SQLCompuwareSQLNavigatorQuestSoftwareTOADQuestSoftwarePL/SQLDeveloper

allroundautomations

SQL-ProgrammerSylvainFaustInternational

3.

PL/SQL的基本構(gòu)成

PL/SQL程序的基本單元是塊(BLOCK)，塊就是實(shí)現(xiàn)一定功能的邏輯模塊。一個(gè)PL/SQL程序由一個(gè)或多個(gè)塊組成。

一個(gè)塊可以包括四個(gè)部分：

(1)DECLARE：聲明部分標(biāo)志。

(2)BEGIN：可執(zhí)行部分標(biāo)志。

(3)EXCEPTION：異常處理部分標(biāo)志。

(4)END；：程序結(jié)束標(biāo)志。Declare

--變量聲明begin

--語(yǔ)句序列exception

--例外處理程序End;/PL/SQL程序結(jié)構(gòu)“--”是注釋符號(hào)，后邊是程序的注釋部分。該部分不編譯執(zhí)行，所以在輸入程序時(shí)可以省略。/*......*/中間也是注釋部分，同“--”注釋方法不同，它可以跨越多行進(jìn)行注釋。

PL/SQL程序的可執(zhí)行語(yǔ)句、SQL語(yǔ)句和END結(jié)束標(biāo)識(shí)都要以分號(hào)結(jié)束。

PL/SQL程序有兩種：命名的PL/SQL程序塊和匿名PL/SQL程序塊PL/SQL程序結(jié)構(gòu)說(shuō)明在PL/SQL模塊中可以使用查詢語(yǔ)句和數(shù)據(jù)操縱語(yǔ)句(即進(jìn)行DML操作)，所以PL/SQL程序是同SQL語(yǔ)言緊密結(jié)合在一起的。在PL/SQL程序中，常見的是使用SELECT語(yǔ)句從數(shù)據(jù)庫(kù)中獲取信息，同直接執(zhí)行SELECT語(yǔ)句不同，在程序中的SELECT語(yǔ)句總是和INTO相配合，INTO后跟用于接收查詢結(jié)果的變量，形式如下：

SELECT列名1，列名2...

INTO變量1，變量2...

FROM表名WHERE條件；

接收查詢結(jié)果的變量類型、順序和個(gè)數(shù)同SELECT語(yǔ)句的字段的類型、順序和個(gè)數(shù)應(yīng)該完全一致。并且SELECT語(yǔ)句返回的數(shù)據(jù)必須是一行，否則將引發(fā)系統(tǒng)錯(cuò)誤。當(dāng)程序要接收返回的多行結(jié)果時(shí)，可以采用游標(biāo)的方法。

使用INSERT、DELETE和UPDATE的語(yǔ)法沒有變化，但在程序中要注意判斷語(yǔ)句執(zhí)行的狀態(tài)，并使用COMMIT或ROLLBACK進(jìn)行事務(wù)處理。

SQL*Plus中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入“/”來(lái)執(zhí)行。執(zhí)行命名的程序塊必須使用execute關(guān)鍵字。在另一個(gè)命名程序塊或匿名程序塊中執(zhí)行命名的PL/SQL程序，不需要execute關(guān)鍵字。

4.

PL/SQL程序的執(zhí)行為了調(diào)試PL/SQL程序塊，可以使用函數(shù)DBMS_OUTPUT.PUT_LINE顯示輸出結(jié)果。DBMS_OUTPUT是Oracle提供的包，該包有如下三個(gè)用于輸出的函數(shù)，用于顯示PL/SQL程序模塊的輸出信息。調(diào)試PL/SQL程序的方法要使用該方法顯示輸出數(shù)據(jù)，在SQL*Plus環(huán)境下要先執(zhí)行一次如下的環(huán)境設(shè)置命令：

SETSERVEROUTPUTON[SIZEn]

用來(lái)打開DBMS_OUTPUT.PUT_LINE函數(shù)的屏幕輸出功能，系統(tǒng)默認(rèn)狀態(tài)是OFF。其中，n表示輸出緩沖區(qū)的大小。n的范圍在2000～1000000之間，默認(rèn)為2000。如果輸出內(nèi)容較多，需要使用SIZEn來(lái)設(shè)置較大的輸出緩沖區(qū)。第一種形式：

DBMS_OUTPUT.PUT(字符串表達(dá)式)；

用于輸出字符串，但不換行。第二種形式：

DBMS_OUTPUT.PUT_LINE(字符串表達(dá)式)；

用于輸出一行字符串信息，并換行。第三種形式：

DBMS_OUTPUT.NEW_LINE；

用來(lái)輸出一個(gè)換行，沒有參數(shù)。DBMS_OUTPUT.PUT格式例5.1查詢雇員編號(hào)為7788的雇員姓名和工資。步驟1：用SCOTT賬戶登錄SQL*Plus。步驟2：執(zhí)行命令：SETSERVEROUTPUTON

步驟3：在輸入?yún)^(qū)輸入以下程序：

<見下頁(yè)>

步驟4：執(zhí)行程序。輸出的結(jié)果是：

7788號(hào)雇員是：SCOTT，工資為：30005.

示例程序

/*這是一個(gè)簡(jiǎn)單的示例程序*/DECLARE

--定義部分標(biāo)識(shí)

v_nameVARCHAR2(10);--定義字符串變量v_name

v_salNUMBER(5);--定義數(shù)值變量v_salBEGIN --可執(zhí)行部分標(biāo)識(shí)

SELECTename,sal

INTOv_name,v_sal

FROM emp

WHEREempno=7788;--在程序中插入的SQL語(yǔ)句

DBMS_OUTPUT.PUT_LINE('7788號(hào)雇員是：'||

v_name||'，工資為：'||to_char(v_sal));

--輸出雇員名和工資END; --結(jié)束標(biāo)識(shí)/5.1.2PL/SQL的變量與運(yùn)算符PL/SQL數(shù)據(jù)類型有四種數(shù)據(jù)類型：標(biāo)量復(fù)合

LOB

引用標(biāo)量數(shù)據(jù)類型沒有內(nèi)部組件，它們大致分為四類：數(shù)字（number）、字符（character）、日期（date）、布爾（boolean）。1.

數(shù)據(jù)類型數(shù)據(jù)類型范圍子類型描述BINARY_INTEGER-2147483647~2147483647NATURALNATURALNPOSITIVEPOSITIVENSIGNTYPE用于存儲(chǔ)單字節(jié)整數(shù)。要求存儲(chǔ)長(zhǎng)度低于NUMBER值。用于限制范圍的子類型(SUBTYPE)：

NATURAL：用于非負(fù)數(shù)；

NATURALN：用于非負(fù)數(shù)和非NULL值；

POSITIVE：只用于正數(shù)；

POSITIVEN：只用于正數(shù)，不能用于NULL值。

SIGNTYPE：只有值：-1、0或1。NUMBER1.0E-130~9.99E125DECDECIMALDOUBLEPRECISIONFLOATINTEGERRICINTNUMERICREALSMALLINT存儲(chǔ)數(shù)字值，包括整數(shù)和浮點(diǎn)數(shù)?？梢赃x擇精度和刻度方式，語(yǔ)法：

number[(p[,s])]缺省的精度p是38，刻度s是0。PLS_INTEGER-2147483647~2147483647與BINARY_INTEGER基本相同，但采用機(jī)器運(yùn)算時(shí)，PLS_INTEGER提供更好的性能。①

數(shù)字?jǐn)?shù)據(jù)類型數(shù)據(jù)類型范圍子類型描述CHARN=2000字節(jié)CHARACTER存儲(chǔ)定長(zhǎng)字符串，若長(zhǎng)度沒有確定，缺省是1。LONG最大長(zhǎng)度：

2GB字節(jié)存儲(chǔ)可變長(zhǎng)度字符串。RAW最大長(zhǎng)度：32767字節(jié)存儲(chǔ)二進(jìn)制數(shù)據(jù)和字節(jié)字符串。當(dāng)在兩個(gè)數(shù)據(jù)庫(kù)之間進(jìn)行傳遞時(shí)，RAW數(shù)據(jù)不存在字符集之間轉(zhuǎn)換。LONGRAW最大長(zhǎng)度：2GB字節(jié)與LONG數(shù)據(jù)類型相似，同樣也存在不能在字符集之間轉(zhuǎn)換。RAWID18字節(jié)與數(shù)據(jù)庫(kù)ROWID偽列類型相同、能夠存儲(chǔ)一個(gè)行標(biāo)示符，可以將行標(biāo)示符看作數(shù)據(jù)庫(kù)中每一行的惟一鍵值。VARCHAR2最大長(zhǎng)度：32767字節(jié)STRINGVARCHAR與VARCHAR數(shù)據(jù)類型相似，存儲(chǔ)可變長(zhǎng)度的字符串。聲明方法與VARCHAR相同。②

字符數(shù)據(jù)類型③

日期和布爾數(shù)據(jù)類型④

LOB數(shù)據(jù)類型數(shù)據(jù)類型范圍描述BOOLEANTRUE/FALSE存儲(chǔ)邏輯值TRUE或FALSE，無(wú)參數(shù)。DATE公元前：01/01/4712~公元：12/31/9999存儲(chǔ)固定長(zhǎng)度的日期和時(shí)間值，日期值中包含時(shí)間。數(shù)據(jù)類型范圍子類型描述LOB最大長(zhǎng)度：4GB字節(jié)BLOBCLOBNCLOB大對(duì)象類型，用來(lái)存儲(chǔ)非結(jié)構(gòu)化數(shù)據(jù)。BFILE大小由操作系統(tǒng)決定在數(shù)據(jù)庫(kù)外部保存大型二進(jìn)制對(duì)象文件。數(shù)據(jù)類型描述RECORD類似于C語(yǔ)言中的結(jié)構(gòu)，提供了處理分立但又是作為一個(gè)整體單元的相關(guān)變量的一種機(jī)制。TABLE類似于C語(yǔ)言中的數(shù)組，從語(yǔ)法上講，它們與數(shù)組的處理方式相類似。但是它們，它們實(shí)際上是不同的。PL/SQL表類似于數(shù)據(jù)庫(kù)表，它有兩列——KEY和VALUE。KEY的類型是BINARY_INTEGER，而VALUE的類型是在定義中指定的類型。⑤

復(fù)合數(shù)據(jù)類型2.

變量變量的作用是用來(lái)存儲(chǔ)數(shù)據(jù)，可以在過(guò)程語(yǔ)句中使用。變量在聲明部分可以進(jìn)行初始化，即賦予初值。變量在定義的同時(shí)也可以將其說(shuō)明成常量并賦予固定的值。變量有六種形式：一般變量類型變量宿主變量記錄變量記錄類型變量

TABLE類型變量以字母開頭，后跟其他的字符序列，字符序列中可以包含字母、數(shù)值、下劃線等符號(hào)，最大長(zhǎng)度為30個(gè)字符，不區(qū)分大小寫。不能使用Oracle的保留字作為變量名。變量名不要和在程序中引用的字段名相重，如果相重，變量名會(huì)被當(dāng)作列名來(lái)使用。變量的命名規(guī)則①

一般變量

變量名[CONSTANT]類型標(biāo)識(shí)符

[NOTNULL][:=值|DEFAULT值];

關(guān)鍵字CONSTANT用來(lái)說(shuō)明定義的變量是常量，如果是常量，必須有賦值部分進(jìn)行賦值；關(guān)鍵值NOTNULL用來(lái)說(shuō)明變量不能為空；

:=值|DEFAULT值，用來(lái)為變量賦初值。變量名:=值|PL/SQL表達(dá)式;

通過(guò)賦值語(yǔ)句給變量賦值；

通過(guò)鍵盤輸入給變量賦值；通過(guò)SELECT…INTO…給變量賦值。變量賦值②根據(jù)表的字段定義變量變量名表名.字段名%TYPE;

根據(jù)數(shù)據(jù)庫(kù)表的字段定義變量，其定義的變量的數(shù)據(jù)類型和長(zhǎng)度與數(shù)據(jù)庫(kù)表中的列保持一致；當(dāng)數(shù)據(jù)庫(kù)的字段數(shù)據(jù)類型進(jìn)行了修改，那么該變量的定義也自動(dòng)使用新的數(shù)據(jù)類型；變量的數(shù)據(jù)類型和大小是在編譯執(zhí)行時(shí)決定。③綁定變量（宿主變量）VARIABLE變量名數(shù)據(jù)類型

SQL*Plus環(huán)境下使用的變量，可以在程序中使用；宿主變量的使用是在宿主變量前加上冒號(hào)；

:宿主變量名在SQL*Plus環(huán)境下顯示該變量要用系統(tǒng)的PRINT命令。注意：末尾沒有;號(hào)④記錄變量記錄變量名表名%ROWTYPE;

記錄變量包含若干個(gè)字段，在結(jié)構(gòu)上同表的一個(gè)記錄相同；記錄變量的字段名就是表的字段名，數(shù)據(jù)類型也一致；獲得記錄變量的字段的方法：

記錄變量名.字段名⑤記錄類型變量Type記錄類型名isrecord(

field1type1[notnull]{default|:=}expr1;

field2type2[notnull]{default|:=}expr2;

…

)

記錄數(shù)據(jù)類型和表的行結(jié)構(gòu)非常相似，一行數(shù)據(jù)通常包括許多不同的字段；必須使用TYPE語(yǔ)句來(lái)創(chuàng)建自己的數(shù)據(jù)類型；記錄類型變量的使用：

記錄類型變量名.字段名⑥TABLE類型變量TYPE表類型名ISTABLEOF數(shù)據(jù)類型

[NOTNULL]INDEXBYBINARY_INTEGER;

要定義TABLE變量，需要先定義TABLE數(shù)據(jù)類型；此數(shù)據(jù)類型自動(dòng)帶有BINARY_INTEGER型的索引；可用來(lái)存儲(chǔ)可變長(zhǎng)度的一維數(shù)組數(shù)據(jù)，即數(shù)組中的數(shù)據(jù)動(dòng)態(tài)地增長(zhǎng)；通過(guò)使用下標(biāo)來(lái)引用TABLE變量的元素。3.

運(yùn)算符和函數(shù)

PL/SQL的運(yùn)算符主要有算術(shù)運(yùn)算符、關(guān)系運(yùn)算符、邏輯運(yùn)算符和字符串運(yùn)算符。

PL/SQL中可以使用絕大部分Oracle函數(shù)。但是組函數(shù)(如AVG()、MIN()、MAX()等)只能出現(xiàn)在SQL語(yǔ)句中，不能在其他語(yǔ)句中使用。類型轉(zhuǎn)換在很多情況下是自動(dòng)的，在不能進(jìn)行自動(dòng)類型轉(zhuǎn)換的場(chǎng)合需要使用轉(zhuǎn)換函數(shù)。運(yùn)算符及優(yōu)先級(jí)優(yōu)先級(jí)運(yùn)算符描述1**乘方2＋、－正負(fù)號(hào)3*、/乘除4+、-、||加減、字符串連接5=、<、>、<=、>=、!=、~=、^=、isnull、like、between…and、in(…)比較6Not邏輯非7And邏輯與8Or邏輯或賦值語(yǔ)句語(yǔ)法：變量:=表達(dá)式；

在一個(gè)特定的PL/SQL語(yǔ)句中僅能夠有一個(gè)賦值。表達(dá)式

表達(dá)式作為一個(gè)句子是無(wú)效的，他必須是一條語(yǔ)句的一部分，是右值。字符表達(dá)式

||運(yùn)算符4.

PL/SQL表達(dá)式5.1.3PL/SQL的控制語(yǔ)句

PL/SQL程序控制語(yǔ)句同其它的編程語(yǔ)言一樣有三種：順序結(jié)構(gòu)分支結(jié)構(gòu)循環(huán)結(jié)構(gòu)1.

分支結(jié)構(gòu)

PL/SQL分支結(jié)構(gòu)語(yǔ)句有兩種：

IF語(yǔ)句

根據(jù)條件可以改變程序的邏輯流程

CASE語(yǔ)句

適用于分情況的多分支處理①

IF語(yǔ)句

IF語(yǔ)句格式：

IF條件1THEN

語(yǔ)句序列1；

[ELSIF條件2THEN

語(yǔ)句序列2；

ELSE

語(yǔ)句序列n；]

ENDIF;根據(jù)具體情況，IF語(yǔ)句分支結(jié)構(gòu)可以有以下幾種形式：

IF-THEN-ENDIF;IF-THEN-ELSE-ENDIF;IF-THEN-ELSIF-ELSE-ENDIF;②

CASE語(yǔ)句

CASE語(yǔ)句格式：

CASE選擇變量名

WHEN表達(dá)式1THEN

語(yǔ)句序列1

WHEN表達(dá)式2THEN

語(yǔ)句序列2

……

WHEN表達(dá)式nTHEN

語(yǔ)句序列n

ELSE

語(yǔ)句序列n+1

ENDCASE;在Oracle中，CASE結(jié)構(gòu)還能以賦值表達(dá)式的形式出現(xiàn)，它根據(jù)選擇變量的值求得不同的結(jié)果：變量=CASE選擇變量名

WHEN表達(dá)式1THEN值1

WHEN表達(dá)式2THEN值2

……

WHEN表達(dá)式nTHEN值n

ELSE值n+1

ENDCASE;表達(dá)式結(jié)構(gòu)CASE語(yǔ)句在Oracle中，還提供了一種搜索CASE結(jié)構(gòu)，它沒有選擇變量，直接判斷條件表達(dá)式的值，根據(jù)條件表達(dá)式?jīng)Q定轉(zhuǎn)向：

CASE

WHEN條件表達(dá)式1THEN

語(yǔ)句序列1

WHEN條件表達(dá)式2THEN

語(yǔ)句序列2

……

WHEN條件表達(dá)式nTHEN

語(yǔ)句序列n

ELSE

語(yǔ)句序列n+1

ENDCASE;搜索CASE結(jié)構(gòu)2.

循環(huán)結(jié)構(gòu)循環(huán)結(jié)構(gòu)是最重要的程序控制結(jié)構(gòu)，用來(lái)控制反復(fù)執(zhí)行一段程序。

PL/SQL循環(huán)結(jié)構(gòu)可劃分為以下3種：

基本LOOP循環(huán)

FORLOOP循環(huán)

WHILELOOP循環(huán)①

基本LOOP循環(huán)

語(yǔ)句格式：

LOOP --循環(huán)起始標(biāo)識(shí)

語(yǔ)句序列1；

語(yǔ)句序列2；

EXIT[WHEN條件];

ENDLOOP; --循環(huán)結(jié)束標(biāo)識(shí)功能：反復(fù)執(zhí)行LOOP與ENDLOOP之間的語(yǔ)句。

EXIT用于在循環(huán)過(guò)程中退出循環(huán)，WHEN用于定義EXIT的退出條件。如果沒有WHEN條件，遇到EXIT語(yǔ)句則無(wú)條件退出循環(huán)。②

FORLOOP循環(huán)

FOR循環(huán)是固定次數(shù)循環(huán)。語(yǔ)句格式：

FOR控制變量in[REVERSE]下限..上限

LOOP

語(yǔ)句序列1；

語(yǔ)句序列2；

ENDLOOP;注意：循環(huán)控制變量是隱含定義的，不需要聲明。③

WHILELOOP循環(huán)

WHILE循環(huán)是有條件循環(huán)。語(yǔ)句格式：

WHILE條件

LOOP

語(yǔ)句序列1；

語(yǔ)句序列2；

ENDLOOP; 當(dāng)條件滿足時(shí)，執(zhí)行循環(huán)體；當(dāng)條件不滿足時(shí)，則結(jié)束循環(huán)。如果第一次判斷條件為假，則不執(zhí)行循環(huán)體。5.1.4異常處理

PL/SQL中使用異常和相關(guān)異常處理器來(lái)滿足錯(cuò)誤處理需求，而不是在一個(gè)程序體內(nèi)嵌入錯(cuò)誤處理程序。異常就是已命名的錯(cuò)誤條件。當(dāng)PL/SQL程序檢測(cè)到一個(gè)錯(cuò)誤時(shí)，產(chǎn)生一個(gè)已命名的異常，并將控制傳遞給與主程序體分離的相關(guān)的異常處理程序。1.

Oracle錯(cuò)誤處理機(jī)制錯(cuò)誤處理部分位于程序的可執(zhí)行部分之后，是由WHEN語(yǔ)句引導(dǎo)的多個(gè)分支構(gòu)成的。

EXCEPTION

WHEN錯(cuò)誤1[OR錯(cuò)誤2]THEN

語(yǔ)句序列1

WHEN錯(cuò)誤3[OR錯(cuò)誤4]THEN

語(yǔ)句序列2

……

WHENOTHERS

語(yǔ)句序列n

END;錯(cuò)誤處理語(yǔ)法格式錯(cuò)誤是在標(biāo)準(zhǔn)包中由系統(tǒng)預(yù)定義的標(biāo)準(zhǔn)錯(cuò)誤，或是由用戶在程序的說(shuō)明部分自定義的錯(cuò)誤；語(yǔ)句序列就是不同分支的錯(cuò)誤處理部分；說(shuō)明：凡是出現(xiàn)在WHEN后面的錯(cuò)誤都是可以捕捉到的錯(cuò)誤，其他未被捕捉到的錯(cuò)誤，將在WHENOTHERS部分進(jìn)行統(tǒng)一處理。OTHENS必須是EXCEPTION部分的最后一個(gè)錯(cuò)誤處理分支；如要在該分支中進(jìn)一步判斷錯(cuò)誤種類，可以通過(guò)使用預(yù)定義函數(shù)SQLCODE()和SQLERRM()來(lái)獲得系統(tǒng)錯(cuò)誤號(hào)和錯(cuò)誤信息；如果在程序的子塊中發(fā)生了錯(cuò)誤，但子塊沒有錯(cuò)誤處理部分，則錯(cuò)誤會(huì)傳遞到主程序中。2.

異常的類型在PL/SQL中，有以下3種異常：預(yù)定義異常非預(yù)定義異常用戶定義的異常錯(cuò)誤名稱錯(cuò)誤代碼異常號(hào)錯(cuò)誤含義CURSOR_ALREADY_OPENORA_06511-06511試圖打開已經(jīng)打開的游標(biāo)INVALID_CURSORORA_01001-01001試圖使用沒有打開的游標(biāo)DUP_VAL_ON_INDEXORA_00001

-00001保存重復(fù)值到惟一索引約束的列中ZERO_DIVIDEORA_01476-01476發(fā)生除數(shù)為零的除法錯(cuò)誤INVALID_NUMBERORA_01722-01722試圖對(duì)無(wú)效字符進(jìn)行數(shù)值轉(zhuǎn)換ROWTYPE_MISMATCHORA_06504-06504主變量和游標(biāo)的類型不兼容VALUE_ERRORORA_06502-06502轉(zhuǎn)換、截?cái)嗷蛩阈g(shù)運(yùn)算發(fā)生錯(cuò)誤TOO_MANY_ROWSORA_01422-01422SELECT…INTO…語(yǔ)句返回多于一行的數(shù)據(jù)NO_DATA_FOUNDORA_01403-01403SELECT…INTO…語(yǔ)句沒有數(shù)據(jù)返回TIMEOUT_ON_RESOURCEORA_00051-00051等待資源時(shí)發(fā)生超時(shí)錯(cuò)誤TRANSACTION_BACKED_OUTORA_00060-00060由于死鎖，提交失敗STORAGE_ERRORORA_06500-06500發(fā)生內(nèi)存錯(cuò)誤PROGRAM_ERRORORA_06501-06501發(fā)生PL/SQL內(nèi)部錯(cuò)誤NOT_LOGGED_ONORA_01012-01012試圖操作未連接的數(shù)據(jù)庫(kù)LOGIN_DENIEDORA_01017-01017在連接時(shí)提供了無(wú)效用戶名或口令①

Oracle預(yù)定義異常②

非預(yù)定義異常如果一個(gè)系統(tǒng)錯(cuò)誤沒有在標(biāo)準(zhǔn)包中定義，則需要在聲明部分定義，語(yǔ)法如下：

錯(cuò)誤名EXCEPTION;

然后通過(guò)編譯指示PRAGMAEXCEPTION_INIT來(lái)將一個(gè)定義的錯(cuò)誤同一個(gè)特別的Oracle錯(cuò)誤代碼相關(guān)聯(lián)，就可以同系統(tǒng)預(yù)定義的錯(cuò)誤一樣使用了。語(yǔ)法如下：PRAGMAEXCEPTION_INIT(錯(cuò)誤名，-錯(cuò)誤代碼)；③

用戶定義異常

程序設(shè)計(jì)者可以利用引發(fā)異常的機(jī)制來(lái)進(jìn)行程序設(shè)計(jì)，自己定義異常類型?？梢栽诼暶鞑糠侄x新的異常類型。語(yǔ)法如下：錯(cuò)誤名EXCEPTION;

用戶定義的錯(cuò)誤不能由系統(tǒng)來(lái)觸發(fā)，必須由程序顯式地觸發(fā)，觸發(fā)的語(yǔ)法是：RAISE錯(cuò)誤名；3.

異常處理過(guò)程在PL/SQL中，異常處理分下列3個(gè)步驟進(jìn)行：在聲明部分為錯(cuò)誤定義異常，包括非預(yù)定義異常和用戶定義異常；在執(zhí)行過(guò)程中當(dāng)錯(cuò)誤產(chǎn)生時(shí)拋出與錯(cuò)誤對(duì)應(yīng)的異常；在異常處理部分通過(guò)異常處理捕獲異常，并進(jìn)行異常處理。由于系統(tǒng)可以自動(dòng)識(shí)別Oracle內(nèi)部錯(cuò)誤，因此當(dāng)錯(cuò)誤產(chǎn)生時(shí)系統(tǒng)會(huì)自動(dòng)拋出與之對(duì)應(yīng)的預(yù)定義異?；蚍穷A(yù)定義異常。但是，系統(tǒng)無(wú)法識(shí)別用戶定義錯(cuò)誤，因此當(dāng)用戶錯(cuò)誤產(chǎn)生時(shí)，需要用戶手動(dòng)拋出與之對(duì)應(yīng)的異常。異常的拋出例5.2用戶注冊(cè)檢查，其數(shù)據(jù)庫(kù)表結(jié)構(gòu)如下所示，編寫PL/SQL程序進(jìn)行用戶登錄檢查，并在tout表中記錄檢查結(jié)果。用戶名和密碼正確，記錄“l(fā)oginok”；密碼錯(cuò)誤記錄“passworderror”；用戶名錯(cuò)誤記錄“useriderror”。4.

示例程序Udps…msg……Useridpasswd…tin：tout：userlog：第五章作業(yè)：(第1次)1.雇員表：emp(eno,ename)，編寫PL/SQL程序查詢編號(hào)為1234的雇員名字并輸出該名字。要求：當(dāng)不存在該雇員時(shí)，輸出提示信息“編號(hào)錯(cuò)誤，沒有找到相應(yīng)雇員！”，當(dāng)發(fā)生其他異常時(shí)，輸出提示信息“發(fā)生其他錯(cuò)誤！”。2.P.1472。思考題1.P.1471。5.2

Oracle存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程和函數(shù)也是一種PL/SQL塊，是存入數(shù)據(jù)庫(kù)的PL/SQL塊。但存儲(chǔ)過(guò)程和函數(shù)不同于已經(jīng)介紹過(guò)的PL/SQL程序，我們通常把PL/SQL程序稱為無(wú)名塊，而存儲(chǔ)過(guò)程和函數(shù)是以命名的方式存儲(chǔ)于數(shù)據(jù)庫(kù)中的。存儲(chǔ)過(guò)程和函數(shù)還是數(shù)據(jù)庫(kù)系統(tǒng)中的一個(gè)對(duì)象，可以在數(shù)據(jù)字典中查詢到它的詳細(xì)地細(xì)節(jié)。存儲(chǔ)過(guò)程的優(yōu)點(diǎn)存儲(chǔ)過(guò)程可以看作是一個(gè)公用模塊。一個(gè)重復(fù)使用的功能，可以設(shè)計(jì)成為存儲(chǔ)過(guò)程或函數(shù)；存儲(chǔ)過(guò)程需要進(jìn)行編譯，以排除語(yǔ)法錯(cuò)誤，只有編譯通過(guò)才能調(diào)用；存儲(chǔ)過(guò)程以命名的數(shù)據(jù)庫(kù)對(duì)象形式存儲(chǔ)于數(shù)據(jù)庫(kù)當(dāng)中；存儲(chǔ)過(guò)程可由數(shù)據(jù)庫(kù)提供安全保證，只有被授權(quán)的用戶或創(chuàng)建者本身才能執(zhí)行存儲(chǔ)過(guò)程；用戶編寫的PL/SQL程序或其他存儲(chǔ)過(guò)程都可以調(diào)用它；可以傳遞參數(shù)給存儲(chǔ)過(guò)程，也可以有返回值。1.

創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建一個(gè)存儲(chǔ)過(guò)程需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系統(tǒng)權(quán)限。基本語(yǔ)句如下：

CREATE[ORREPLACE]PROCEDURE存儲(chǔ)過(guò)程名

[(參數(shù)1[{IN|OUT|INOUT}]數(shù)據(jù)類型

[{:=|DEFAULT}表達(dá)式]...)]

[AUTHID{CURRENT_USER|DESIGNER}]

{AS|IS}

[說(shuō)明部分]

BEGIN

可執(zhí)行部分

[EXCEPTION

錯(cuò)誤處理部分]

END[過(guò)程名];

/參數(shù)類型說(shuō)明IN(只讀)定義一個(gè)輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲(chǔ)過(guò)程。當(dāng)調(diào)用過(guò)程時(shí)，實(shí)參的值將傳入過(guò)程。在該過(guò)程內(nèi)部，形參類似PL/SQL使用的常數(shù)，即該值具有只讀屬性，不能對(duì)其修改。當(dāng)該過(guò)程結(jié)束時(shí)，控制將返回到調(diào)用環(huán)境，這時(shí)對(duì)應(yīng)的形參沒有改變。OUT(只寫)定義一個(gè)輸出參數(shù)變量，用于從存儲(chǔ)過(guò)程獲取數(shù)據(jù)。當(dāng)過(guò)程被調(diào)用時(shí)，實(shí)參具有的任何值都將被忽略不計(jì)。在該過(guò)程內(nèi)部，形參的作用類似于沒有初始化的PL/SQL變量，其值為空（NULL）。該變量具有讀寫屬性。當(dāng)該過(guò)程結(jié)束時(shí)，控制將返回到調(diào)用環(huán)境，形參的內(nèi)容將賦予對(duì)應(yīng)的實(shí)參。INOUT(可讀寫)定義一個(gè)輸入、輸出參數(shù)變量，兼有以上兩者的功能。該模式是模式IN和OUT的組合。調(diào)用過(guò)程時(shí)，實(shí)參的值將被傳遞到該過(guò)程中。在該過(guò)程內(nèi)部，形參相當(dāng)于初始化的變量，并具有讀寫屬性。當(dāng)該過(guò)程結(jié)束時(shí)，控制將返回到調(diào)用環(huán)境，形參的內(nèi)容將賦予對(duì)應(yīng)的實(shí)參。參數(shù)模式不能強(qiáng)制指定形參參數(shù)的CHAR和VARCHAR2的長(zhǎng)度，以及指定NUMBER參數(shù)的精度或小數(shù)點(diǎn)后位數(shù)（可以從實(shí)參中獲得）；可以使用%TYPE來(lái)說(shuō)明形參。如果一個(gè)形參是用%TYPE聲明的，并且說(shuō)明%TYPE的變量也是強(qiáng)制類型的話，則該強(qiáng)制說(shuō)明將作用在形參上而不是實(shí)參上。說(shuō)明：2.

刪除和重新編譯存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程的人是過(guò)程的創(chuàng)建者或者擁有DROPANYPROCEDURE系統(tǒng)權(quán)限的人。DROPPROCEDURE存儲(chǔ)過(guò)程名；如果要重新編譯一個(gè)存儲(chǔ)過(guò)程，則只能是過(guò)程的創(chuàng)建者或者擁有ALTERANYPROCEDURE系統(tǒng)權(quán)限的人。ALTERPROCEDURE存儲(chǔ)過(guò)程名COMPILE；3.

執(zhí)行存儲(chǔ)過(guò)程執(zhí)行(或調(diào)用)存儲(chǔ)過(guò)程的人是過(guò)程的創(chuàng)建者或是擁有EXECUTEANYPROCEDURE系統(tǒng)權(quán)限的人或是被擁有者授予EXECUTE權(quán)限的人。方法1：

EXECUTE模式名.存儲(chǔ)過(guò)程名[(參數(shù)1,…)];方法2：

BEGIN

模式名.存儲(chǔ)過(guò)程名[(參數(shù)1,…)];

END;參數(shù)的值由調(diào)用者傳遞，傳遞的參數(shù)的個(gè)數(shù)、類型和順序應(yīng)該和定義的一致。如果順序不一致，可以采用以下調(diào)用方法：參數(shù)名=>參數(shù)值表達(dá)式參數(shù)可以是變量、常量或表達(dá)式；如果是調(diào)用本賬戶下的存儲(chǔ)過(guò)程，則模式名可以省略。要調(diào)用其他賬戶編寫的存儲(chǔ)過(guò)程，則模式名必須要添加。說(shuō)明：例5.3用戶注冊(cè)檢查，其數(shù)據(jù)庫(kù)表結(jié)構(gòu)如下所示，編寫存儲(chǔ)過(guò)程進(jìn)行用戶登錄檢查，并記錄檢查結(jié)果（同例2）。

(1)輸入?yún)?shù)1為用戶名；

(2)輸入?yún)?shù)2為密碼；

(3)輸出參數(shù)為：用戶名和密碼正確，返回值為0；密碼錯(cuò)誤返回值為-1；用戶名錯(cuò)誤返回值為-2。4.

示例程序msg……Useridpasswd…tout：userlog：5.3

Oracle包

包是用來(lái)存儲(chǔ)相關(guān)程序結(jié)構(gòu)的對(duì)象，它存儲(chǔ)于數(shù)據(jù)字典中。包由兩個(gè)分離的部分組成：包頭(PACKAGE)：包的說(shuō)明部分，是對(duì)外的操作接口，對(duì)應(yīng)用是可見的；包體(PACKAGEBODY)：包的代碼和實(shí)現(xiàn)部分，對(duì)應(yīng)用來(lái)說(shuō)是不可見的黑盒。<包的介紹略>5.4

觸發(fā)器

觸發(fā)器類似于過(guò)程和函數(shù)，它們都是擁有聲明、執(zhí)行和異常處理過(guò)程的帶名PL/SQL塊，觸發(fā)器必須在數(shù)據(jù)庫(kù)中以獨(dú)立對(duì)象的身份存儲(chǔ)。執(zhí)行觸發(fā)器的操作就是“啟動(dòng)”觸發(fā)器。觸發(fā)器必須由事件才能觸發(fā)。觸發(fā)事件發(fā)生時(shí)就會(huì)隱式地執(zhí)行該觸發(fā)器，并且觸發(fā)器不接受參數(shù)。觸發(fā)器又叫做事件—條件—?jiǎng)幼饕?guī)則。觸發(fā)器是當(dāng)特定的系統(tǒng)事件（如對(duì)一個(gè)表的增、刪、改，事務(wù)的結(jié)束）發(fā)生時(shí)，對(duì)規(guī)則的條件進(jìn)行檢查，如果條件成立則執(zhí)行規(guī)則中的動(dòng)作，否則不執(zhí)行該動(dòng)作。觸發(fā)事件的操作可能執(zhí)行也可能不執(zhí)行。存儲(chǔ)過(guò)程觸發(fā)器調(diào)用執(zhí)行通過(guò)存儲(chǔ)過(guò)程名字而被直接調(diào)用通過(guò)事件觸發(fā)而被執(zhí)行參數(shù)傳遞可以傳遞參數(shù)不接受參數(shù)存儲(chǔ)過(guò)程與觸發(fā)器的比較觸發(fā)器的作用主要有以下幾點(diǎn)：維護(hù)不可能在表創(chuàng)建時(shí)刻通過(guò)聲明性約束進(jìn)行的復(fù)雜的完整性約束限制；完整性限制是當(dāng)被限制的對(duì)象發(fā)生變化時(shí)，系統(tǒng)就去檢查該對(duì)象變化后能否滿足限制條件，如果不能滿足，就拒絕引起這種變化的操作。提供透明的審計(jì)記錄，通過(guò)記錄所進(jìn)行的修改以及誰(shuí)進(jìn)行了修改來(lái)審計(jì)表中的信息；利用觸發(fā)器還可以做很多其他工作，例如：自動(dòng)產(chǎn)生導(dǎo)出列的值；實(shí)施復(fù)雜的安全授權(quán)；執(zhí)行復(fù)雜的業(yè)務(wù)規(guī)則；可阻止非法的事務(wù)處理；表被修改的時(shí)候，自動(dòng)給其他需要執(zhí)行操作的程序發(fā)信號(hào)。5.4.1觸發(fā)器的種類和觸發(fā)事件

觸發(fā)器事件可分為3類：

DML事件

DDL事件數(shù)據(jù)庫(kù)事件觸發(fā)器類型可分為4類：

DML觸發(fā)器

DDL觸發(fā)器替代觸發(fā)器數(shù)據(jù)庫(kù)事件觸發(fā)器本課程只介紹DML觸發(fā)器種類關(guān)鍵字含義DML事件(3種)INSERT在表或視圖中插入數(shù)據(jù)時(shí)觸發(fā)UPDATE修改表或視圖中的數(shù)據(jù)時(shí)觸發(fā)DELETE在刪除表或視圖中的數(shù)據(jù)時(shí)觸發(fā)DDL事件(3種)CREATE在創(chuàng)建新對(duì)象時(shí)觸發(fā)ALTER修改數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)對(duì)象時(shí)觸發(fā)DROP刪除對(duì)象時(shí)觸發(fā)數(shù)據(jù)庫(kù)事件(5種)STARTUP數(shù)據(jù)打開時(shí)觸發(fā)SHUTDOWN在使用NORMAL或IMMEDIATE選項(xiàng)關(guān)閉數(shù)據(jù)庫(kù)時(shí)觸發(fā)LOGON當(dāng)用戶連接到數(shù)據(jù)庫(kù)并建立會(huì)話時(shí)觸發(fā)LOGOFF當(dāng)一個(gè)會(huì)話從數(shù)據(jù)庫(kù)中斷開時(shí)觸發(fā)SERVERERROR發(fā)生服務(wù)器錯(cuò)誤時(shí)觸發(fā)觸發(fā)器事件種類簡(jiǎn)稱作用數(shù)據(jù)操縱語(yǔ)言觸發(fā)器DML觸發(fā)器創(chuàng)建在表上，由DML事件引發(fā)的觸發(fā)器替代觸發(fā)器INSTEADOF觸發(fā)器創(chuàng)建在視圖上，用來(lái)替換對(duì)視圖進(jìn)行的插入、刪除和修改操作數(shù)據(jù)定義語(yǔ)言觸發(fā)器DDL觸發(fā)器定義在模式上，觸發(fā)事件是數(shù)據(jù)庫(kù)對(duì)象的創(chuàng)建和修改數(shù)據(jù)庫(kù)事件觸發(fā)器—定義在整個(gè)數(shù)據(jù)庫(kù)或模式上，觸發(fā)事件是數(shù)據(jù)庫(kù)事件觸發(fā)器的類型和作用5.4.2DML觸發(fā)器

DML觸發(fā)器是定義在表上的觸發(fā)器，由DML事件引發(fā)。編寫DML觸發(fā)器的要素是：確定觸發(fā)的表，即在其上定義觸發(fā)器的表；確定觸發(fā)的事件，DML觸發(fā)器的觸發(fā)事件有INSERT、UPDATE和DELETE三種；確定觸發(fā)時(shí)間。觸發(fā)的時(shí)間有BEFORE和AFTER兩種，分別表示觸發(fā)動(dòng)作發(fā)生在DML語(yǔ)句執(zhí)行之前和語(yǔ)句執(zhí)行之后；確定觸發(fā)級(jí)別，有語(yǔ)句級(jí)觸發(fā)器和行級(jí)觸發(fā)器兩種。創(chuàng)建DML觸發(fā)器需要CREATETRIGGER系統(tǒng)權(quán)限。創(chuàng)建DML觸發(fā)器的語(yǔ)法如下：CREATE[ORREPLACE]TRIGGER觸發(fā)器名

{BEFORE|AFTER|INSTEADOF}觸發(fā)事件1[OR觸發(fā)事件2...]

ON表名

WHEN觸發(fā)條件

[FOREACHROW]

DECLARE

聲明部分

BEGIN

主體部分

END觸發(fā)器名;

/1.

創(chuàng)建DML觸發(fā)器1.創(chuàng)建者：表的擁有者2.觸發(fā)器名3.表名：觸發(fā)器的目標(biāo)表4.觸發(fā)事件：INSERT、DELETE、UPDATE

對(duì)于UPDATE事件，可以用以下形式表示對(duì)某些列的修改會(huì)引起觸發(fā)器的動(dòng)作：

UPDATEOF列名1，列名2...5.觸發(fā)器類型行級(jí)觸發(fā)器（FOREACHROW）語(yǔ)句級(jí)觸發(fā)器（FOREACHSTATEMENT）定義觸發(fā)器的語(yǔ)法說(shuō)明類型取值說(shuō)明語(yǔ)句INSERT、UPDATE、DELETE定義哪種DML語(yǔ)句會(huì)使得觸發(fā)器點(diǎn)火定時(shí)BEFORE或AFTER定義是否在語(yǔ)句執(zhí)行以前或以后點(diǎn)火觸發(fā)器級(jí)別行或語(yǔ)句如果觸發(fā)器是行級(jí)觸發(fā)器，那么它僅對(duì)由觸發(fā)語(yǔ)句影響的每一行點(diǎn)火一次。如果觸發(fā)器是語(yǔ)句級(jí)觸發(fā)器，那么它會(huì)在語(yǔ)句之前或之后點(diǎn)火一次。行級(jí)觸發(fā)器是由觸發(fā)器定義中的FOREACHROW子句標(biāo)識(shí)的。DML觸發(fā)器類型觸發(fā)器主體是觸發(fā)器的主要處理代碼。WHEN子句的觸發(fā)條件如果被使用的話，將首先進(jìn)行求值。觸發(fā)器的主體僅當(dāng)此條件求值為“TRUE”時(shí)才被執(zhí)行；如果有多個(gè)觸發(fā)器被定義成為相同時(shí)間、相同事件觸發(fā)，且最后定義的觸發(fā)器是有效的，則最后定義的觸發(fā)器被觸發(fā)，其他觸發(fā)器不執(zhí)行；觸發(fā)器體內(nèi)禁止使用COMMIT、ROLLBACK、SAVEPOINT語(yǔ)句，也禁止直接或間接地調(diào)用含有上述語(yǔ)句的存儲(chǔ)過(guò)程；說(shuō)明：每個(gè)觸發(fā)器有兩個(gè)特殊的表：插入表（INSERTED）和刪除表（DELETED）。這兩個(gè)表是只讀的，不允許用戶直接對(duì)其修改，即用戶不能向這兩個(gè)表寫入內(nèi)容，但可以引用表中的數(shù)據(jù)；一個(gè)觸發(fā)器可由多個(gè)不同的DML操作觸發(fā)。在觸發(fā)器中，可用INSERTING、DELETING、UPDATING謂詞來(lái)區(qū)別不同的DML操作。這些謂詞可以在IF分支條件語(yǔ)句中作為判斷條件來(lái)使用；

在行級(jí)觸發(fā)器中，用:new和:old(稱為偽記錄)來(lái)訪問(wèn)數(shù)據(jù)變更前后的值。但要注意，INSERT語(yǔ)句插入一條新記錄，所以沒有:old記錄，而DELETE語(yǔ)句刪除掉一條已經(jīng)存在的記錄，所以沒有:new記錄。UPDATE語(yǔ)句既有:old記錄，也有:new記錄，分別代表修改前后的記錄。引用具體的某一列的值的方法是：

:old.字段名或:new.字段名