《Oracle數(shù)據(jù)庫應(yīng)用教程》課件第6章

第6章

PL/SQL6.1

PL/SQL語言簡介

6.2PL/SQL語言的基本語法

6.3控制結(jié)構(gòu)

6.4動態(tài)SQL語句

6.5游標(biāo)

6.6異常處理

6.7小結(jié)習(xí)題六上機(jī)實(shí)驗六

6.1PL/SQL語言簡介

PL/SQL是一種高級數(shù)據(jù)庫程序設(shè)計語言。它是Oracle對標(biāo)準(zhǔn)數(shù)據(jù)庫語言的擴(kuò)展,是過程語言(ProceduralLanguage)與結(jié)構(gòu)化查詢語言(SQL)結(jié)合而成的編程語言。它支持多種數(shù)據(jù)類型，如大對象和集合類型，可使用條件和循環(huán)等控制結(jié)構(gòu)，可用于創(chuàng)建存儲過程、觸發(fā)器和程序包，還可以處理業(yè)務(wù)規(guī)則、數(shù)據(jù)庫事件或給SQL語句的執(zhí)行添加程序邏輯。另外，PL/SQL還支持許多增強(qiáng)的功能，包括集合類型、面向?qū)ο蟮某绦蛟O(shè)計和異常處理等。利用

PL/SQL語言編寫的程序也稱為

PL/SQL程序塊。PL/SQL程序塊的基本單位是塊，PL/SQL程序都是由塊組成的。完整的

PL/SQL程序塊包含三個基本部分：聲明部分、執(zhí)行部分和異常處理部分。PL/SQL程序塊的基本結(jié)構(gòu)如下：

[DECLARE]

--declarationstatements聲明部分

BEGIN

--executablestatements可執(zhí)行部分

[EXCEPTION]

--exceptionstatements異常處理部分

END說明：

聲明部分：這部分由關(guān)鍵字DECLARE開始，包含變量和常量的數(shù)據(jù)類型、初始值和游標(biāo)。PL/SQL程序塊中使用的所有變量、常量等需要聲明的內(nèi)容必須在聲明部分中集中定義。如果不需要聲明變量或常量，則可以忽略這一部分。

可執(zhí)行部分：這部分由關(guān)鍵字BEGIN開始，以END為結(jié)束標(biāo)識，包含對數(shù)據(jù)庫的數(shù)據(jù)操縱語句和各種流程控制語句。所有可執(zhí)行語句都放在這一部分，其他的PL/SQL塊也可以放在這一部分。

異常處理部分：這部分包含在執(zhí)行部分中，是可選的，由關(guān)鍵字EXCEPTION開始，包含對程序執(zhí)行中產(chǎn)生的異常情況的處理程序。

上述三個部分中只有執(zhí)行部分是必備的，其他兩個部分可以省略。PL/SQL程序塊可以相互嵌套。

PL/SQL塊中的每一條語句都必須以分號結(jié)束，SQL語句可以是多行的，分號表示該語句的結(jié)束。一行中可以有多條SQL語句，它們之間以分號分隔。每一個PL/SQL塊由BEGIN或DECLARE開始，以END結(jié)束。單行注釋由“—”標(biāo)識，多行注釋由“/*,*/”標(biāo)識。

PL/SQL程序塊可以是一個命名的程序塊，也可以是一個匿名程序塊。匿名程序塊可以用在服務(wù)器端，也可以用在客戶端。

命名程序塊可以出現(xiàn)在其他PL/SQL程序塊的聲明部分，這方面比較明顯的是子程序，子程序可以在執(zhí)行部分引用，也可以在異常處理部分引用。PL/SQL程序塊可被獨(dú)立編譯并存儲在數(shù)據(jù)庫中，任何與數(shù)據(jù)庫相連接的應(yīng)用程序都可以訪問這些存儲的PL/SQL程序塊。

【例6.1】

用一個完整的PL/SQL塊實(shí)現(xiàn)查詢雇員號為“7934”的雇員信息。

Declare

p_salnumber(7,0);

p_commnumber(7,0);

Begin

selectsal,commintop_sal,p_commfromempwhereempno=7934;

Exception

Whenno_data_foundThen

Dbms_output.put_line('員工號不存在');

End; 6.2PL/SQL語言的基本語法

6.2.1常量值

常量值也稱為常數(shù)。在

PL/SQL語言中，常量值包括

4種類型：數(shù)字常數(shù)、字符和字符串常數(shù)、布爾常數(shù)、日期常數(shù)。

1．?dāng)?shù)字常數(shù)

數(shù)字常數(shù)包括整數(shù)和實(shí)數(shù)兩種。數(shù)字常數(shù)可以用科學(xué)計數(shù)法描述。例如，25、-89、0.01、2E-2都是數(shù)字常數(shù)。

2．字符和字符串常數(shù)

字符常數(shù)包括字母(a～z，A～Z)、數(shù)字(0～9)、空格和特殊符號。字符常數(shù)必須放在英文單引號內(nèi)，例如，'a'、'8'、'?'、'-'、'%'、'#'?都是字符常數(shù)。零個或多個字符常數(shù)構(gòu)成字符串常數(shù)，字符串常數(shù)也必須放在英文單引號內(nèi)，例如，'helloworld!'。

3．布爾常數(shù)

布爾常數(shù)是系統(tǒng)預(yù)先定義好的值，包括

TRUE(真)、FALSE(假)和NULL(不確定或空)。

4．日期常數(shù)

日期常數(shù)為

Oracle能夠識別的日期。日期常數(shù)也必須放在英文單引號內(nèi)，例如，'12-六月-1999'、'12-JUN-98'?都是日期常數(shù)。

6.2.2變量聲明

變量和常量由用戶定義。使用變量和常量前需在

PL/SQL程序塊的聲明部分對其進(jìn)行聲明，目的是為它分配內(nèi)存空間。

語法：

<變量|常量名>[CONSTANT]<數(shù)據(jù)類型>[NOTNULL][:=|DEFAULT<初始值>];

說明：

(1)變量名和常量名必須以字母

A～Z開頭，不區(qū)分大小寫，其后跟可選的一個或多個字母、數(shù)字(0～9)、特殊字符($、#或_)，長度不超過

30個字符，變量名和常量名中不能有空格。

(2)CONSTANT是聲明常量的關(guān)鍵字，只在聲明常量時使用。

(3)每一個變量或常量都有一個特定的數(shù)據(jù)類型。

(4)每個變量或常量聲明占一行，行尾使用分號“;”結(jié)束。

(5)常量必須在聲明時賦值。變量在聲明時可以不賦值。如果變量在聲明時沒有賦初值，那么PL/SQL語言自動為其賦值NULL。若在變量聲明中使用了NOTNULL，則表示該變量是非空變量，即必須在聲明時給該變量賦初值，否則會出現(xiàn)編譯錯誤。在PL/SQL程序中，變量值是可以改變的，而常量的值不能改變。變量的作用域是指從聲明開始到PL/SQL程序塊結(jié)束。

例如：

numconstantintegerdefault4;

strconstantchar(12):='Helloworld!';

v_telvarchar2(15);6.2.3數(shù)據(jù)類型

PL/SQL提供的4種數(shù)據(jù)類型為：標(biāo)量數(shù)據(jù)類型、LOB類型、復(fù)合類型、引用數(shù)據(jù)類型。

1.標(biāo)量數(shù)據(jù)類型

標(biāo)量(scalar)數(shù)據(jù)類型沒有內(nèi)部組件，它們大致可分為數(shù)字、字符、布爾值(BOOLEAN)和日期時間值(DATE)等數(shù)據(jù)類型。

1)數(shù)字?jǐn)?shù)據(jù)類型

數(shù)字?jǐn)?shù)據(jù)類型存儲的數(shù)據(jù)為數(shù)字，用此數(shù)據(jù)類型存儲的數(shù)據(jù)可用于計算。此類型包括BINARY_INTEGER、NUMBER和PLS_INTEGER。

(1)BINARY_INTEGER：用于存儲帶符號的整數(shù)，值的范圍為-231-1～231-1。PL/SQL預(yù)定義了以下BINARY_INTEGER的子類型。

①NATURAL：可以限制變量存儲非負(fù)整數(shù)值。

②NATURALN：可以限制變量存儲自然數(shù)，且非空。

③POSITIVE：可以限制變量存儲正整數(shù)。

④POSITIVEN：可以限制變量存儲正整數(shù)，且非空。

⑤SIGNTYPE：可以限制變量只存儲值-1、0、1三個值。

(2)NUMBER：用于存儲整數(shù)、定點(diǎn)數(shù)和浮點(diǎn)數(shù)，以十進(jìn)制格式進(jìn)行存儲。它便于存儲，但是在計算上，系統(tǒng)會自動將它轉(zhuǎn)換為二進(jìn)制格式進(jìn)行運(yùn)算。

定義方式為NUMBER(P，S)。其中，P是精度，最大為38位；S是刻度范圍，可在-84～127間取值。例如，NUMBER(5，2)可以用來存儲-999.99～999.99間的數(shù)值。P、S可以在定義中省略，例如，NUMBER(5)、NUMBER等。

NUMBER數(shù)據(jù)類型包括以下子類型：

①

DECIMAL：用于聲明最高精度為38位的十進(jìn)制數(shù)字的定點(diǎn)數(shù)。

②FLOAT：用于聲明最高精度為126位的二進(jìn)制數(shù)字的浮點(diǎn)數(shù)。

③INTEGER：用于聲明最高精度為38位的十進(jìn)制數(shù)字的整數(shù)。

④REAL：用于聲明最高精度為63位的二進(jìn)制數(shù)字的浮點(diǎn)數(shù)。

(3)?PLS_INTEGER：用于存儲帶符號的整數(shù)。PLS_INTEGER的大小范圍為-231～231。與BINARY_INTEGER基本相同，但采用機(jī)器運(yùn)算時，PLS_INTEGER可提供更好的性能。與NUMBER數(shù)據(jù)類型相比，PLS_INTEGER需要的存儲空間更小。通常建議只要是在PLS_INTEGER數(shù)值范圍內(nèi)的計算都使用此數(shù)據(jù)類型，以提高計算效率。

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

字符數(shù)據(jù)類型用于存儲字符串或字符數(shù)據(jù)。字符數(shù)據(jù)類型包括以下幾種。

(1)CHAR：描述定長的字符串。如果實(shí)際值不夠定義的長度，則系統(tǒng)將以空格填充。它的聲明方式為CHAR(L)，L為字符串長度，缺省值為1，作為變量，其長度最大為32?767個字符。

(2)CHARACTER：存儲定長字符串，如果長度沒有確定，則缺省值為1。

(3)?LONG：存儲可變長度字符串。在數(shù)據(jù)庫存儲中，LONG可以用來保存高達(dá)2GB的數(shù)據(jù)，作為變量，可以表示一個最大長度為32760B的可變字符串。

(4)RAW：類似于CHAR，聲明方式為RAW(L)，L為長度，以字節(jié)為單位，作為數(shù)據(jù)庫列，最大為2000B，作為變量，最大為32767B。RAW用于存儲二進(jìn)制數(shù)據(jù)和字節(jié)字符串，當(dāng)在兩個數(shù)據(jù)庫之間進(jìn)行傳遞時，RAW數(shù)據(jù)不在字符集之間進(jìn)行轉(zhuǎn)換。

(5)LONGRAW：類似于LONG，作為數(shù)據(jù)庫列最大可存儲2GB的數(shù)據(jù)，作為變量，最大為32?760B。同樣地，它也不能在字符集之間進(jìn)行轉(zhuǎn)換。

(6)ROWID：與數(shù)據(jù)庫ROWID類型相同，能夠存儲一個行標(biāo)示符，可以將行標(biāo)示符看做數(shù)據(jù)庫中每一行的唯一鍵值，可以利用ROWIDTOCHAR函數(shù)來將行標(biāo)識轉(zhuǎn)換成為字符。

(7)?VARCHAR2：描述變長字符串。它的聲明方式為VARCHAR2(L)，其中，L為字符串長度，沒有缺省值，作為變量最大為32?767B，作為數(shù)據(jù)存儲在Oracle中最大為4000B。在多字節(jié)語言環(huán)境中，實(shí)際存儲的字符個數(shù)可能小于L值。例如，當(dāng)語言環(huán)境為中文(SIMPLIFIEDCHINESE_CHINA.ZHS16GBK)時，一個VARCHAR2(200)的數(shù)據(jù)列可以保存200個英文字符或者100個漢字字符。

(8)?NCHAR，NVARCHAR：國家字符集，與環(huán)境變量NLS指定的語言集密切相關(guān)，使用方法和CHAR、VARCHAR2相同。

3)BOOLEAN

BOOLEAN用來存儲邏輯值TRUE、FALSE或NULL，無參數(shù)。

4)

DATE

DATE用來存儲固定長的日期和時間值，日期值中包含時間。它支持的日期范圍為公元前4712年1月1日到公元9999年12月31日。日期函數(shù)sysdate返回當(dāng)前日期和時間。

2．LOB類型

LOB(LargeOBject，大對象)數(shù)據(jù)類型用于存儲類似圖像、聲音等大型數(shù)據(jù)對象。LOB數(shù)據(jù)對象可以是二進(jìn)制數(shù)據(jù)，也可以是字符數(shù)據(jù)，其最大長度不超過4GB。LOB數(shù)據(jù)類型支持任意訪問方式，LONG只支持順序訪問方式。LOB存儲在一個單獨(dú)的位置上，同時一個“LOB定位符”(LOBlocator)存儲在原始的表中，該定位符是一個指向?qū)嶋H數(shù)據(jù)的指針。在PL/SQL中操作LOB數(shù)據(jù)對象時可使用Oracle提供的包

DBMS_LOB.LOB。數(shù)據(jù)類型可分為以下四類：

(1)BFILE；

(2)BLOB；

(3)CLOB；

(4)NCLOB。

3．復(fù)合類型

PL/SQL語言的復(fù)合類型是用戶定義的。常用的復(fù)合類型有屬性、記錄、表和數(shù)組。復(fù)合類型是標(biāo)量類型的組合，使用這些數(shù)據(jù)類型可以拓寬應(yīng)用范圍。對于復(fù)合類型，應(yīng)先定義，再聲明，最后才能使用。

1)屬性類型

屬性用于引用數(shù)據(jù)庫列的數(shù)據(jù)類型，以及表示表中一行的記錄類型。屬性類型有以下兩種。

(1)%TYPE：用于引用變量和數(shù)據(jù)庫列的數(shù)據(jù)類型。例如，使用%TYPE聲明變量：

empcodeemp.empno%TYPE;

該段代碼聲明了變量empcode，它的數(shù)據(jù)類型與表emp中的empno列的數(shù)據(jù)類型相同。

(2)%ROWTYPE：用于提供表示表中一行的記錄類型。

例如，使用%ROWTYPE聲明變量：

emp_exemp%ROWTYPE;

該段代碼聲明了變量emp_ex，可以用于存儲從emp中提取的記錄。

2)記錄類型

PL/SQL記錄是由一組相關(guān)的記錄成員組成的，通常用來表示對應(yīng)數(shù)據(jù)庫表中的一行。使用PL/SQL記錄時應(yīng)自定義記錄類型和記錄變量，也可以使用%ROWTYPE屬性定義記錄變量。引用記錄成員時，必須將記錄變量作為前綴。

自定義記錄類型和記錄變量的語法如下：

TYPE<記錄類型名>ISRECORD(

<數(shù)據(jù)項

1><數(shù)據(jù)類型>[NOTNULL[:=<表達(dá)式

1>]],

<數(shù)據(jù)項

2><數(shù)據(jù)類型>[NOTNULL[:=<表達(dá)式

2>]],

<數(shù)據(jù)項

n><數(shù)據(jù)類型>[NOTNULL[:=<表達(dá)式

n>]]);

<記錄變量名><記錄類型名>;……

【例6.2】

將學(xué)生信息定義為記錄類型。

1declare

2typeemp_record_typeisrecord

3(v_enameemp.ename%type,

4v_jobemp.job%type,

5v_salemp.sal%type);

6emp_recemp_record_type;

7begin

8selectename,job,salintoemp_rec

9fromempwhereempno=&eno;

10dbms_output.put_line(emp_rec.v_ename||':'||

11emp_rec.v_job||':'||emp_rec.v_sal);

12?end;

SQL>/

輸入

eno的值:7782

原值

9:fromempwhereempno=&eno;

新值

9:fromempwhereempno=7782;

CLARK:MANAGER:2470

PL/SQL過程已成功完成。

說明：emp_record_type為記錄類型名；v_ename、v_job、v_sal為數(shù)據(jù)項，分別對應(yīng)

emp表中的各個字段；最后聲明

emp_rec為

emp_record_type類型的變量名。

3)表類型

表是一種比較復(fù)雜的數(shù)據(jù)結(jié)構(gòu)，與數(shù)據(jù)庫中的表是有區(qū)別的。數(shù)據(jù)庫表是一種二維表，以數(shù)據(jù)庫表的形式存儲。這里的表是一種復(fù)合數(shù)據(jù)類型，是保存在數(shù)據(jù)緩沖區(qū)中的、沒有特別存儲次序的、可以離散存儲的數(shù)據(jù)結(jié)構(gòu)，它可以是一維的，也可以是二維的。當(dāng)使用PL/SQL表時，首先必須在聲明部分定義該類型和變量，然后在執(zhí)行部分引用該變量。語法：

TYPE<表類型名>ISTABLEOF<數(shù)據(jù)類型>INDEXBYBINARY_INTEGER;

<表變量名><表類型名>;

表類型名是用戶定義的；數(shù)據(jù)類型是表中元素的數(shù)據(jù)類型，表中所有元素的數(shù)據(jù)類型是相同的；索引變量缺省為

BINARY_INTEGER(范圍介于-231-1～231-1之間)類型的變量，用于指定索引表元素下標(biāo)的數(shù)據(jù)類型。

【例6.3】

索引表類型的定義。

SQL>DECLARE

2TYPEename_table_typeISTABLEOFemp.ename%TYPE

3INDEXBYBINARY_INTEGER;

4Ename_tableename_table_type;

5BEGIN

6SELECTenameINTOename_table(1)FROMemp

7WHEREempno=7902;

8Dbms_output.put_line('員工名：'||ename_table(1));

9END;

10/

員工名：FORD

PL/SQL過程已成功完成。

4)數(shù)組類型

數(shù)組也是一種復(fù)合類型。數(shù)組和表類似，不同之處在于，聲明了一個數(shù)組，就確定了數(shù)組中元素的數(shù)目。同時，進(jìn)行數(shù)組存儲時，其元素的次序是固定且連續(xù)的，而且索引變量從

1開始一直到其定義的最大值為止。語法如下：

TYPE<數(shù)組類型名>ISVARRAY(<MAX_SIZE>)OF<數(shù)據(jù)類型>;

<表變量名><表類型名>;

數(shù)組類型名是用戶定義的；數(shù)據(jù)類型是數(shù)組中元素的數(shù)據(jù)類型，所有數(shù)組元素的數(shù)據(jù)類型是一致的；MAX_SIZE指明數(shù)組元素個數(shù)的最大值。

4．引用類型

PL/SQL語言中的引用類型是用戶定義的指向某一數(shù)據(jù)緩沖區(qū)的指針，與

C語言中的指針類似。游標(biāo)即為

PL/SQL語言的引用類型。詳細(xì)內(nèi)容將在6.5節(jié)講述。

6.2.4表達(dá)式

PL/SQL語言常見的表達(dá)式分為算術(shù)表達(dá)式、字符表達(dá)式、關(guān)系表達(dá)式和邏輯表達(dá)式四種。

1．算術(shù)表達(dá)式

算術(shù)表達(dá)式是由數(shù)值型常量、變量、函數(shù)和算術(shù)運(yùn)算符組成的。算術(shù)表達(dá)式的計算結(jié)果是數(shù)值型數(shù)據(jù)，它使用的運(yùn)算符主要包括()、??、?、/、+、－等，運(yùn)算的優(yōu)先次序為

()→**→*、/→+、-。

2．字符表達(dá)式

字符表達(dá)式由字符或字符串型常量、變量、函數(shù)和字符運(yùn)算符組成，字符表達(dá)式的計算結(jié)果仍然是字符型。唯一的字符運(yùn)算符是并置(‖)，這個運(yùn)算符將兩個或者多個字符串連接在一起。如果并置運(yùn)算中的所有操作數(shù)是CHAR類型，那么表達(dá)式的結(jié)果也為CHAR類型。如果所有操作數(shù)都為VARCHAR2類型，那么表達(dá)式的結(jié)果也為VARCHAR2類型。

例如，“PL”‖“/SQL”的結(jié)果為“PL/SQL”。

3．關(guān)系表達(dá)式

關(guān)系表達(dá)式是由字符表達(dá)式或者算術(shù)表達(dá)式與關(guān)系運(yùn)算符組成的。關(guān)系表達(dá)式的格式如下：

<表達(dá)式><關(guān)系運(yùn)算符><表達(dá)式>

關(guān)系運(yùn)算符兩邊表達(dá)式的數(shù)據(jù)類型必須一致，因為只有相同類型的數(shù)據(jù)才能比較。關(guān)系表達(dá)式的運(yùn)算結(jié)果為邏輯值，若關(guān)系表達(dá)式成立，則結(jié)果為真(TRUE)，否則為假(FALSE)。

關(guān)系運(yùn)算符主要有六種：

<、

>、=、<=、>=、!=。

謂詞操作符

LIKE、BETWEEN和

IN也可以作為關(guān)系運(yùn)算符。

4．邏輯表達(dá)式

邏輯表達(dá)式由關(guān)系表達(dá)式和邏輯運(yùn)算符組成。邏輯表達(dá)式的運(yùn)算結(jié)果為邏輯值。邏輯運(yùn)算符包括NOT、OR和AND。邏輯運(yùn)算符的運(yùn)算優(yōu)先次序為

NOT→AND→OR。邏輯表達(dá)式的一般格式如下：

<關(guān)系表達(dá)式><邏輯運(yùn)算符><關(guān)系表達(dá)式>

關(guān)系表達(dá)式和邏輯表達(dá)式實(shí)際上都是布爾表達(dá)式，其值為布爾值(TRUE、FALSE或者NULL)。

6.2.5綁定變量

綁定變量也稱為主機(jī)變量。這些變量在SQL*Plus環(huán)境中聲明，匿名塊不帶任何參數(shù)。綁定變量可以作為參數(shù)傳遞給過程和參數(shù)。聲明綁定變量的語法如下：

VARIABLEvariablenamedatatype

例如：

SQL>variablegnonumber

當(dāng)用VARIABLE命令聲明一個數(shù)字變量時，不使用精度和標(biāo)度值。聲明VARCHAR2類型的變量時，不使用長度。在SQL*Plus環(huán)境中，用PRINT命令來顯示主機(jī)變量的值。

Oracle能夠重復(fù)利用執(zhí)行計劃的方法就是采用綁定變量。綁定變量的實(shí)質(zhì)是用于替代SQL語句中常量的替代變量。綁定變量能夠使得每次提交的SQL語句都完全一樣。

普通SQL語句：

SELECTename,sal,deptnofromempWHEREempno=7369;

SELECTename,sal,deptnofromempWHEREempno=7499;

SELECTename,sal,deptnofromempWHEREempno=7566;含綁定變量的SQL語句：

SQL>SELECTename,sal,deptnofromempWHEREempno=:emp_no;

SQL*Plus中使用綁定變量：

SQL>variableemp_nonumber;

SQL>exec:emp_no:=7900;

PL/SQL過程已成功完成。

SQL>SELECTename,sal,deptnofromempWHEREempno=:emp_no;下面的例子使用兩種變量：局部變量v_num和綁定變量g_num。綁定變量g_num在SQL*Plus中用VARIABLE語句來聲明。程序塊用冒號(:)前綴引用。局部變量不需使用冒號前綴。程序?qū)⒔o局部變量賦值3，將它翻倍后賦值給綁定變量g_num，然后使用PRINT語句顯示結(jié)果。

SQL>variableg_numnumber

SQL>declare

2v_numnumber;

3begin

4v_num:=3;

5:g_num:=v_num*2;

6end;

7/

PL/SQL過程已成功完成。

SQL>printg_num

G_NUM

--------

6

說明：在程序塊中應(yīng)盡量減少使用綁定變量，因為綁定變量會影響性能。在塊中每次訪問一個綁定變量時，PL/SQL引擎都必須停下來向主機(jī)環(huán)境請求綁定變量的值。為減少停頓，可以將變量的值賦給局部變量。

6.2.6PL/SQL中的替換變量

PL/SQL沒有輸入能力?！?”加標(biāo)識符即為替換變量，通過替換變量可以在PL/SQL中進(jìn)行輸入，并可以方便地達(dá)到創(chuàng)建通用腳本的目的。

注意：如果列的數(shù)據(jù)類型為字符或日期型，則應(yīng)用單引號將替換變量括起來。

將上述例子用替換變量實(shí)現(xiàn)。

SQL>variableg_numnumber

SQL>declare

2v_numnumber;

3begin

4v_num:=&p_num;

5:g_num:=v_num*2;

6end;

7/

輸入

p_num的值：5

原值

4：v_num:=&p_num;

新值

4：v_num:=5;

PL/SQL過程已成功完成。

當(dāng)使用替換變量時，輸出結(jié)果會顯示進(jìn)行替換的那些行?？梢允褂胹etverifyoff命令取消這些行。

SQL>SQL>setverifyoff

SQL>/

輸入

p_num的值：5

PL/SQL過程已成功完成。

SQL>printg_num

G_NUM

-------

10例如，創(chuàng)建通用腳本，在Employees表中插入數(shù)據(jù)。

SQL>insertintoemp(empno,ename,sal,deptno)values(&empno,&ename,&sal,&deptno);

SQL>/

輸入

empno的值:7811

輸入

ename的值:'jamw'

輸入

sal的值:3000

輸入

deptno的值:30

已創(chuàng)建

1行。

6.3控

制

結(jié)

構(gòu)

6.3.1條件控制

條件控制用于根據(jù)條件執(zhí)行一系列語句，包括IF語句和CASE語句。

1．IF語句

1)

IF…THEN

語法：

IFconditionTHEN

Statements1;

Statements2;

ENDIF…

2)

IF…THEN...ELSE

語法:

IFconditionTHEN

Statements1;

Statements2;

ELSE

Statements1;

Statements2;

ENDIF……如果條件condition為TRUE，則執(zhí)行THEN到ELSE之間的語句；否則，執(zhí)行ELSE到ENDIF之間的語句。

IF可以嵌套，可以在IF或IF...ELSE語句中使用IF或IF…ELSE語句。

if(a>b)and(a>c)then

g:=a;

else

g:=b;

ifc>gthen

g:=c;

endif

endif

3)

IF…THEN…ELSIF

語法：

IFcondition1THEN

statement1;

ELSIFcondition2THEN

statement2;

ELSIFcondition3THEN

statement3;

ELSE

statement4;

ENDIF;

statement5;如果條件condition1為TRUE，則執(zhí)行statement1，然后執(zhí)行statement5；否則判斷condition2是否為TRUE,若為TRUE，則執(zhí)行statement2，然后執(zhí)行statement5。對于condition3也是相同的，如果condition1、condition2、condition3都不成立，那么將執(zhí)行statement4，然后執(zhí)行statement5。

【例6.4】

為工資小于2000元的員工增加工資200元。

DECLARE

V_salnumber(6,2);

BEGIN

SELECTsalINTOv_salFROMempWHEREename=trim('&&name');

IFv_sal<2000THEN

UPDATEempSETsal=v_sal+200WHEREename=trim('&&name');

ENDIF;

END;

【例6.5】

按照不同的崗位更新員工的工資。

DECLARE

v_jobVARCHAR2(10);

v_salnumber(6,2);

BEGIN

SELECTjob,salINTOv_job,v_salFROMempWHEREempno=&&no;

IFv_job='CLERK'THEN

UPDATEempSETsal=v_sal+200WHEREempno=&&no;

ELSIFv_job='SALESMAN'THEN

UPDATEempSETsal=v_sal+100WHEREempno=&&no;

ELSE

UPDATEempSETsal=v_sal+500WHEREempno=&&no;

ENDIF;

END;

2．CASE語句

CASE語句用于根據(jù)條件將單個變量或表達(dá)式與多個值進(jìn)行比較。在執(zhí)行CASE語句前，該語句先計算選擇器的值。CASE語句使用選擇器與WHEN字句中的表達(dá)式匹配。語法如下：

CASE選擇器

WHEN表達(dá)式1THEN執(zhí)行語句1；

WHEN表達(dá)式2THEN執(zhí)行語句2；

…

WHEN表達(dá)式NTHEN執(zhí)行語句N；

ELSE執(zhí)行語句N＋1；

ENDCASE;

當(dāng)選擇器的值與WHEN子句中的表達(dá)式相等時，執(zhí)行對應(yīng)的THEN子句部分的語句。

【例6.6】

更新相應(yīng)部門的員工的補(bǔ)貼。

DECLARE

V_deptnoemp.deptno%TYPE;

BEGIN

V_deptno:=&n;

CASEv_deptno

When10THEN

UPDATEempSETcomm=100WHEREdeptno=v_deptno;

When20THEN

UPDATEempSETcomm=80WHEREdeptno=v_deptno;

When30THEN

UPDATEempSETcomm=50WHEREdeptno=v_deptno;

When40THEN

UPDATEempSETcomm=30WHEREdeptno=v_deptno;

ELSE

Dbms_output.put_line(‘不存在該部門!’);

ENDCASE;

END;

CASE語句還有另外一種形式，即不使用選擇器，而是計算WHEN子句中的各個比較表達(dá)式，找到第一個為TRUE的表達(dá)式，然后執(zhí)行對應(yīng)的語句序列。語法如下：

CASE

WHEN表達(dá)式1THEN執(zhí)行語句1；

WHEN表達(dá)式2THEN執(zhí)行語句2；

…

WHEN表達(dá)式NTHEN執(zhí)行語句N；

ELSE執(zhí)行語句N＋1；

ENDCASE；

6.3.2循環(huán)控制

循環(huán)控制用于重復(fù)執(zhí)行一系列語句。循環(huán)結(jié)構(gòu)共有三種類型，分別是基本循環(huán)、WHILE循環(huán)和FOR循環(huán)。

1．基本循環(huán)

基本循環(huán)的形式是LOOP語句，LOOP和ENDLOOP之間的語句將無限次地執(zhí)行。

語法：

LOOP

statements;

…

EXIT[WHENcondition];

ENDLOOP;

【例6.7】

使用基本循環(huán)。X初始值為100，循環(huán)累加10，當(dāng)X>1000時退出循環(huán)。

SQL>DECLARE

XINT:=100;

YINT;

BEGIN

LOOP

X:=X+10;

EXITWHENX>1000;

ENDLOOP;

Y:=X;

END;

/

2．WHILE循環(huán)

對于WHILE循環(huán)結(jié)構(gòu)，如果條件結(jié)果值為TRUE，則執(zhí)行循環(huán)體內(nèi)的語句；如果條件結(jié)果值為FALSE，則結(jié)束循環(huán)，執(zhí)行ENDLOOP之后的語句。

語法：

WHILEconditionLOOP

statement1;

statement2;

…

ENDLOOP;

【例6.8】

使用WHILE循環(huán)。X的初始值為100，循環(huán)累加10，當(dāng)X>1000時退出循環(huán)。

SQL>declare

Xnumber:=100;

Ynumber:=0;

BEGIN

WHILEX<=1000

LOOP

X:=X+10;

ENDLOOP;

Y:=X;

END;

/

3．FOR循環(huán)

LOOP循環(huán)和WHILE循環(huán)的循環(huán)次數(shù)事先是不知道的，它取決于循環(huán)條件；而FOR循環(huán)的循環(huán)次數(shù)是已知的。

語法：

FORcounterIN[REVERSE]start_range...end_rangeLOOP

statements;

ENDLOOP;

說明：counter是一個隱式聲明的變量，初始值是

start_range，第二個值是start_range+1，直到end_range。如果start_range等于end_range，那么循環(huán)將執(zhí)行一次。如果使用了REVERSE關(guān)鍵字，那么該循環(huán)的范圍將是一個降序。

【例6.9】

使用FOR循環(huán)。該例程循環(huán)10累加10，累計10次后退出。

SQL>DECLARE

xnumber:=100;

ynumber:=0;

BEGIN

FORv_counterin1..10loop

x:=x+10;

ENDLOOP;

y:=x;

END;6.3.3順序控制

順序控制用于按順序執(zhí)行語句。用戶可以使用標(biāo)簽使程序獲得更好的可讀性。程序塊或循環(huán)都可以被標(biāo)記。標(biāo)簽的形式是<<>>。

1．標(biāo)記程序塊

<<LABEL_NAME>>

[DECLARE]

BEGIN

[EXCEPTION]

END<<label_name>>………

2．GOTO語句

語法：

GOTOLABEL;

執(zhí)行GOTO語句時，控制會立即轉(zhuǎn)到由標(biāo)簽標(biāo)記的語句。PL/SQL中對GOTO語句有一些限制。對于塊、循環(huán)、IF語句而言，從外層跳轉(zhuǎn)到內(nèi)層是非法的。

【例6.10】

使用GOTO語句。當(dāng)員工薪水小于800時轉(zhuǎn)到update標(biāo)簽處提高員工薪水100，否則轉(zhuǎn)至quit，什么也不做。

SQL>declare

salaryemp.sal%type;

begin

selectsalintosalaryfromempwhereempno=7369;

ifsalary<800then

gotoupdat;

else

gotoquit;

endif;

<<updat>>

updateempsetsal=salary+100whereempno=7369;

<<quit>>

NULL;

end;

/

6.4動態(tài)SQL語句

一般的PL/SQL程序設(shè)計中，在DML和事務(wù)控制的語句中可以直接使用SQL，但是DDL語句及系統(tǒng)控制語句卻不能在PL/SQL中直接使用，要想實(shí)現(xiàn)在PL/SQL中使用DDL語句及系統(tǒng)控制語句，可以通過使用動態(tài)SQL來實(shí)現(xiàn)。下面介紹什么是動態(tài)SQL和靜態(tài)SQL。所謂靜態(tài)SQL，指在PL/SQL塊中使用的SQL語句在編譯時是明確的，執(zhí)行的是確定對象。動態(tài)SQL是指在PL/SQL塊編譯時SQL語句是不確定的，可根據(jù)用戶輸入?yún)?shù)的不同而執(zhí)行不同的操作。動態(tài)SQL語句一般由一些SQL語句(如INSERT、UPDATE、DELETE和SELECT等DML語句與CREATETABLE等DDL語句)組成。編譯程序?qū)討B(tài)語句部分不進(jìn)行處理，只是在程序運(yùn)行時動態(tài)地創(chuàng)建語句，對語句進(jìn)行語法分析并執(zhí)行該語句。

在Oracle中，動態(tài)SQL可以通過本地動態(tài)SQL來執(zhí)行，也可以通過DBMS_SQL包來執(zhí)行。

執(zhí)行本地動態(tài)SQL的語法如下：

EXECUTEIMMEDIATEdynamic_sql_statement

[INTOdefine_variable_list]

[USINGbind_argument_list]

說明：dynamic_sql_statement是動態(tài)語句；INTO子句接受SELECT語句選擇的記錄值；USING用于綁定輸入?yún)?shù)變量。

【例6.11】

執(zhí)行本地動態(tài)SQL的使用方法。

SQL>DECLARE

sql_statementvarchar2(1000);

emp_idnumber(4):=7369;

emp_recemp%rowtype;

BEGIN

EXECUTEIMMEDIATE'CREATETABLECOM(idNUMBER,comNUMBER)';

sql_statement:='SELECT*FROMempWHEREempno=:id';

EXECUTEIMMEDIATEsql_statementINTOemp_recUSINGemp_id;

END;

/ 6.5游

標(biāo)

當(dāng)在PL/SQL塊中執(zhí)行查詢語句和數(shù)據(jù)操縱語句時，Oracle會為其分配上下文區(qū)。游標(biāo)(CURSOR)是指向上下文區(qū)的指針。游標(biāo)是用戶定義的引用類型，它能夠根據(jù)查詢條件從數(shù)據(jù)庫表中查詢出一組記錄，將其作為一個臨時表放置在數(shù)據(jù)緩沖區(qū)中，以游標(biāo)作指針，逐行對記錄數(shù)據(jù)進(jìn)行操作。

對于數(shù)據(jù)操縱語句和單行查詢語句來說，Oracle會為它們分配隱含游標(biāo)。為了處理查詢語句返回的多行數(shù)據(jù)，必須使用顯式游標(biāo)。

6.5.1隱式游標(biāo)

PL/SQL為所有數(shù)據(jù)操縱語句和單行查詢語句隱式聲明游標(biāo)，對于此類游標(biāo)，用戶不能直接命名和控制。當(dāng)運(yùn)行數(shù)據(jù)操縱語句時，

PL/SQL打開一個內(nèi)建游標(biāo)并處理結(jié)果(游標(biāo)是維護(hù)查詢結(jié)果的內(nèi)存中的一個區(qū)域，游標(biāo)在運(yùn)行數(shù)據(jù)操縱語句時打開，完成后關(guān)閉)。Oracle預(yù)先定義一個名為SQL的隱式游標(biāo)，通過檢查隱式游標(biāo)的屬性可以獲得與最近執(zhí)行的SQL語句相關(guān)的信息。

數(shù)據(jù)操縱語句的結(jié)果保存在四個游標(biāo)屬性中，這些屬性用于控制程序流程或者了解程序的狀態(tài)。隱式游標(biāo)的屬性包括：%FOUND、%NOTFOUND、%ROWCOUNT和%ISOPEN。其中，%FOUND、%NOTFOUND和%ISOPEN是布爾值；%ROWCOUNT是整數(shù)值。

1．%FOUND和%NOTFOUND

在執(zhí)行任何數(shù)據(jù)操縱語句前，%FOUND和%NOTFOUND的值都是NULL。在執(zhí)行數(shù)據(jù)操縱語句后，%FOUND的屬性值將是：

●TRUE：INSERT；

●TRUE：DELETE和UPDATE，至少有一行被DELETE或UPDATE；

●TRUE：SELECTINTO至少返回一行。

當(dāng)%FOUND為TRUE時,%NOTFOUND為FALSE。

2．%ROWCOUNT

在執(zhí)行任何數(shù)據(jù)操縱語句之前，%ROWCOUNT的值都是NULL。對于SELECTINTO語句，如果執(zhí)行成功，則%ROWCOUNT的值為1；如果沒有成功，則%ROWCOUNT的值為0，同時產(chǎn)生一個異常的NO_DATA_FOUND。

3．%ISOPEN

%ISOPEN是一個布爾值，如果游標(biāo)打開，則為TRUE；如果游標(biāo)關(guān)閉，則為FALSE。對于隱式游標(biāo)而言，%ISOPEN總是FALSE，這是因為隱式游標(biāo)在DML語句執(zhí)行時打開，在結(jié)束時就立即關(guān)閉。

6.5.2顯式游標(biāo)

當(dāng)查詢返回結(jié)果超過一行時，就需要一個顯式游標(biāo)，此時用戶不能使用SELECTINTO語句。PL/SQL管理隱式游標(biāo)，當(dāng)查詢開始時隱式游標(biāo)打開，當(dāng)查詢結(jié)束時隱式游標(biāo)自動關(guān)閉。顯式游標(biāo)在PL/SQL塊的聲明部分聲明，在執(zhí)行部分或異常處理部分打開、取數(shù)據(jù)和關(guān)閉。說明：這里提到的游標(biāo)無特別說明通常是指顯式游標(biāo)。

1．聲明游標(biāo)

要在程序中使用游標(biāo)，首先必須聲明游標(biāo)。語法如下：

CURSORcursor_nameISselect_statement;

說明：cursor_name用于指定游標(biāo)的名稱；select_statement用于指定游標(biāo)所對應(yīng)的查詢語句。

【例6.12】

使用聲明游標(biāo)。

DELCARE

CURSORC_EMPISSELECTempno,ename,salary

FROMemp

WHEREsalary>2000

ORDERBYename;

…

BEGIN

在游標(biāo)的定義中，SELECT語句可以從視圖或多個表中選擇列，甚至可以使用*來選擇所有的列。

2．打開游標(biāo)

打開游標(biāo)時，Oracle會執(zhí)行游標(biāo)所對應(yīng)的查詢語句，并將查詢語句的結(jié)果暫存到結(jié)果集中。打開游標(biāo)的語法如下：

OPENcursor_name

說明：cursor_name是在聲明部分定義的游標(biāo)名。

3．提取數(shù)據(jù)

在打開游標(biāo)之后，從游標(biāo)得到一行數(shù)據(jù)使用FETCH命令。每一次提取數(shù)據(jù)后，游標(biāo)都指向結(jié)果集的下一行。

語法：

FETCHcursor_nameINTOvariable[,variable,...]

對于SELECT定義的游標(biāo)的每一列，F(xiàn)ETCH變量列表都應(yīng)該有一個變量與之相對應(yīng)，變量的類型也要相同。

【例6.13】

使用游標(biāo)提取數(shù)據(jù)。

SQL>SETSERVEROUTPUTON

DECLARE

v_enameEMP.ENAME%TYPE;

v_salaryEMP.SAL%TYPE;

CURSORc_empISSELECTename,salFROMemp;

BEGIN

OPENc_emp;

LOOP

FETCHc_empINTOv_ename,v_salary;

EXITWHENc_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);

endloop;

END;

/

運(yùn)行結(jié)果如下：

SQL>SalaryofEmployeeSMITHis800

SalaryofEmployeeALLENis1600

SalaryofEmployeeWARDis1250

…

4．關(guān)閉游標(biāo)

在提取并處理了結(jié)果集的所有數(shù)據(jù)后，就可以關(guān)閉游標(biāo)并釋放結(jié)果集，語法如下：

CLOSEcursor_name

5．帶參數(shù)的游標(biāo)

與存儲過程和函數(shù)相似，可以將參數(shù)傳遞給游標(biāo)并在查詢中使用。這對于處理在某種條件下打開游標(biāo)的情況非常有用。

語法：

CURSORcursor_name[(parameter[,parameter],...)]ISselect_statement;

定義參數(shù)的語法：

Parameter_name[IN]data_type[{:=|DEFAULT}value]與存儲過程不同的是，游標(biāo)只能接受傳遞的值，而不能返回值。參數(shù)只定義數(shù)據(jù)類型，沒有大小。另外，可以給參數(shù)設(shè)定一個缺省值，當(dāng)沒有參數(shù)值傳遞給游標(biāo)時，就使用缺省值。游標(biāo)中定義的參數(shù)只是一個占位符，在別處引用該參數(shù)不一定可靠。在打開游標(biāo)時給參數(shù)賦值。打開帶參數(shù)游標(biāo)的語法如下：

OPENcursor_name[value[,value]…];

說明：參數(shù)值可以是文字或變量。

【例6.14】

使用帶參數(shù)的游標(biāo)。

SQL>DECLARE

2CURSORc_deptISSELECT*FROMdeptORDERBYdeptno;

3CURSORc_emp(p_deptVARCHAR2)IS

4SELECTename,sal

5FROMemp

6WHEREdeptno=p_dept

7ORDERBYename;

8r_deptDEPT%ROWTYPE;

9v_enameEMP.ENAME%TYPE;

10v_salaryEMP.SAL%TYPE;

11v_tot_salaryEMP.SAL%TYPE;

12BEGIN

13OPENc_dept;

14LOOP

15FETCHc_deptINTOr_dept;

16EXITWHENc_dept%NOTFOUND;

17DBMS_OUTPUT.PUT_LINE(‘Department:’||r_dept.deptno||‘-’||r_dept.dname);

18v_tot_salary:=0;

19OPENc_emp(r_dept.deptno);

20 LOOP

21FETCHc_empINTOv_ename,v_salary;

6.5.3使用游標(biāo)更新或刪除當(dāng)前行數(shù)據(jù)

當(dāng)程序從游標(biāo)的結(jié)果集中取出單個行時，它訪問的是游標(biāo)的當(dāng)前行。如果在處理過程中需要刪除或更新行，則可以利用UPDATE或DELETE語句和WHERE條件中特殊的CURRENTOF子句來處理游標(biāo)的當(dāng)前行。

【例6.15】

利用UPDATE語句和WHERE條件中的CURRENTOF子句將EMP表中部門號為20的員工的薪水提高10%。

(1)查詢EMP表中部門號為20的員工目前的薪水。

SQL>selectempno,deptno,salfromempwheredeptno=20;

EMPNODEPTNO SAL

----------------------------

736920800

7566202975

7788203000

7876201100

7902203000

8000205000

(2)編寫程序。利用UPDATE語句和WHERE條件中的CURRENTOF子句編寫程序。

SQL>declare

2cursorsalcur(depnonumber)is

3selectsalfromempwheredeptno=depnoforupdateofsal;

4new_salnumber;

5begin

6forcurrentsalinsalcur(20)loop

7new_sal:=currentsal.sal;

8updateempsetsal=1.1*new_salwherecurrentofsalcur;

9endloop;

10commit;

11end;

12/

PL/SQL過程已成功完成。

(3)程序執(zhí)行后，再次查詢EMP表中部門號為20的員工目前的薪水發(fā)現(xiàn)，每個員工的薪水已經(jīng)提高了10%。

SQL>selectempno,deptno,salfromempwheredeptno=20;

EMPNODEPTNO SAL

---------------------------

736920 880

756620 3272.5

778820 3300

787620 1210

790220 3300

800020 55006.5.4循環(huán)游標(biāo)

循環(huán)游標(biāo)可以簡化顯式游標(biāo)的處理代碼。在使用循環(huán)游標(biāo)時，Oracle會隱含地打開游標(biāo)、提取游標(biāo)數(shù)據(jù)并關(guān)閉游標(biāo)。語法如下：

FORrecord_nameIN

(corsor_name[(parameter[,parameter]...)]

|(query_difinition)

LOOP

statements

ENDLOOP;說明：corsor_name是已經(jīng)定義的游標(biāo)名；record_name是PL/SQL聲明的記錄變量。此變量的屬性聲明為%ROWTYPE類型，作用域在FOR循環(huán)之內(nèi)。

循環(huán)游標(biāo)的特性如下：

游標(biāo)FOR循環(huán)自動聲明一個游標(biāo)行，打開游標(biāo)，從游標(biāo)中取出行并在游標(biāo)中的最后一行取出后關(guān)閉游標(biāo)的變量或記錄。

下面我們用FOR循環(huán)重寫例6.14的程序：

SQL>DECLARE

2CURSORc_deptISSELECTdeptno,dnameFROMdeptORDERBYdeptno;

3CURSORc_emp(p_deptVARCHAR2)IS

4SELECTename,sal

5FROMemp

6WHEREdeptno=p_dept

7ORDERBYename;

8v_tot_salaryEMP.SAL%TYPE;

9BEGIN

10FORr_deptINc_deptLOOP

11DBMS_OUTPUT.PUT_LINE('Department:'||r_dept.deptno||'-'||r_dept.dname);

12v_tot_salary:=0;

13FORr_empINc_emp(r_dept.deptno)LOOP

14DBMS_OUTPUT.PUT_LINE('Name:'||r_emp.ename||'salary:'||r_emp.sal);

15v_tot_salary:=v_tot_salary+r_emp.sal;

16ENDLOOP;

17DBMS_OUTPUT.PUT_LINE('ToltalSalaryfordept:'||v_tot_salary);

18ENDLOOP;

19END;

20/運(yùn)行結(jié)果如下：

SQL>Department:10-ACCOUNTING

Name:CLARKsalary:2450

Name:KINGsalary:5000

Name:MILLERsalary:1300

ToltalSalaryfordept:8750

Department:20-RESEARCH

Name:ADAMSsalary:1100

…

6.5.5REF游標(biāo)

隱式游標(biāo)和顯式游標(biāo)都是靜態(tài)定義的，當(dāng)用戶使用它們的時候查詢語句已經(jīng)確定。如果用戶需要在運(yùn)行的時候動態(tài)決定執(zhí)行何種查詢，則可以使用REF游標(biāo)和游標(biāo)變量。

創(chuàng)建REF游標(biāo)變量需要兩個步驟：聲明REF游標(biāo)類型和聲明REF游標(biāo)類型的游標(biāo)變量。聲明REF游標(biāo)的語法如下：

TYPE

ref_cursor_name

IS

REF

CURSOR

[RETURN

return_type]

說明：RETURN語句為可選子句，用于指定游標(biāo)提取結(jié)果集的返回類型。上述程序包括RETURN語句表示為強(qiáng)類型REF游標(biāo),不包括RETURN語句表示為弱類型REF游標(biāo)，該方法可以獲取任何結(jié)果集。

在PL/SQL代碼段中可如下定義強(qiáng)類型游標(biāo)：

Declare

Type

refcur_t

is

ref

cursor;

Type

emp_refcur_t

is

ref

cursor

return

employee%rowtype;

Begin

Null;

End;

/

強(qiáng)類型舉例：

SQL>connhr/hr;

已連接。

SQL>Declare

2--聲明記錄類型

3typeemp_job_recisrecord(

4employee_idnumber,

5employee_namevarchar2(50),

6job_titlevarchar2(30)

7);

8--聲明REFCURSOR，返回值為該記錄類型

9typeemp_job_refcur_typeisrefcursor

10returnemp_job_rec;

11--定義REFCURSOR游標(biāo)的變量

12emp_refcuremp_job_refcur_type;

13emp_jobemp_job_rec;

14begin

15openemp_refcurfor

16selecte.employee_id,

17e.first_name||''||e.last_name"employee_name",

18j.job_title

19fromemployeese,jobsj

20wheree.job_id=j.job_idandrownum<11orderby1;

21fetchemp_refcurintoemp_job;

22whileemp_refcur%foundloop

23dbms_output.put_line(emp_job.employee_name||'''sjobis:'||emp_job.job_title);

24fetchemp_refcurintoemp_job;

25endloop;

26end;

27/運(yùn)行結(jié)果如下：

SQL>chendonny'sjobis:President

NeenaKochhar'sjobis:AdministrationVicePresident

LexDeHaan'sjobis:AdministrationVicePresident

AlexanderHunold'sjobis:Programmer

… 6.6異

常

處

理

6.6.1預(yù)定義異常

系統(tǒng)預(yù)定義異常處理是針對

PL/SQL程序編譯、執(zhí)行過程中發(fā)生的系統(tǒng)預(yù)定義異常問題進(jìn)行處理的程序。無論是違反

Oracle規(guī)則，還是超出系統(tǒng)規(guī)定的限度，都會引發(fā)系統(tǒng)異常。系統(tǒng)預(yù)定義異常處理一般由系統(tǒng)自動觸發(fā)，也可以利用后面介紹的自定義異常的觸發(fā)方法來顯式觸發(fā)系統(tǒng)預(yù)定義異常。Oracle常用的系統(tǒng)預(yù)定義異常如表

6-1所示。

表6-1Oracle常用的系統(tǒng)預(yù)定義異常

PL/SQL程序塊的異常部分包含程序處理錯誤的代碼。當(dāng)異常被拋出時，一個異常陷阱就自動發(fā)生，程序控制離開執(zhí)行部分轉(zhuǎn)入異常部分，一旦程序進(jìn)入異常部分就不能再回到同一塊的執(zhí)行部分。下面是異常部分的一般語法。

EXCEPTION

WHENexception_nameTHEN

Codeforhandingexception_name