第6章

PL/SQL6.1

PL/SQL語言簡介

6.2PL/SQL語言的基本語法

6.3控制結構

6.4動態SQL語句

6.5游標

6.6異常處理

6.7小結習題六上機實驗六

6.1PL/SQL語言簡介

PL/SQL是一種高級數據庫程序設計語言。它是Oracle對標準數據庫語言的擴展,是過程語言(ProceduralLanguage)與結構化查詢語言(SQL)結合而成的編程語言。它支持多種數據類型，如大對象和集合類型，可使用條件和循環等控制結構，可用于創建存儲過程、觸發器和程序包，還可以處理業務規則、數據庫事件或給SQL語句的執行添加程序邏輯。另外，PL/SQL還支持許多增強的功能，包括集合類型、面向對象的程序設計和異常處理等。利用

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

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

PL/SQL程序塊包含三個基本部分：聲明部分、執行部分和異常處理部分。PL/SQL程序塊的基本結構如下：

[DECLARE]

--declarationstatements聲明部分

BEGIN

--executablestatements可執行部分

[EXCEPTION]

--exceptionstatements異常處理部分

END說明：

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

可執行部分：這部分由關鍵字BEGIN開始，以END為結束標識，包含對數據庫的數據操縱語句和各種流程控制語句。所有可執行語句都放在這一部分，其他的PL/SQL塊也可以放在這一部分。

異常處理部分：這部分包含在執行部分中，是可選的，由關鍵字EXCEPTION開始，包含對程序執行中產生的異常情況的處理程序。

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

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

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

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

【例6.1】

用一個完整的PL/SQL塊實現查詢雇員號為“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常量值

常量值也稱為常數。在

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

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

1．數字常數

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

2．字符和字符串常數

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

3．布爾常數

布爾常數是系統預先定義好的值，包括

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

4．日期常數

日期常數為

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

6.2.2變量聲明

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

PL/SQL程序塊的聲明部分對其進行聲明，目的是為它分配內存空間。

語法：

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

說明：

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

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

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

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

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

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

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

例如：

numconstantintegerdefault4;

strconstantchar(12):='Helloworld!';

v_telvarchar2(15);6.2.3數據類型

PL/SQL提供的4種數據類型為：標量數據類型、LOB類型、復合類型、引用數據類型。

1.標量數據類型

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

1)數字數據類型

數字數據類型存儲的數據為數字，用此數據類型存儲的數據可用于計算。此類型包括BINARY_INTEGER、NUMBER和PLS_INTEGER。

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

①NATURAL：可以限制變量存儲非負整數值。

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

③POSITIVE：可以限制變量存儲正整數。

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

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

(2)NUMBER：用于存儲整數、定點數和浮點數，以十進制格式進行存儲。它便于存儲，但是在計算上，系統會自動將它轉換為二進制格式進行運算。

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

NUMBER數據類型包括以下子類型：

①

DECIMAL：用于聲明最高精度為38位的十進制數字的定點數。

②FLOAT：用于聲明最高精度為126位的二進制數字的浮點數。

③INTEGER：用于聲明最高精度為38位的十進制數字的整數。

④REAL：用于聲明最高精度為63位的二進制數字的浮點數。

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

2)字符數據類型

字符數據類型用于存儲字符串或字符數據。字符數據類型包括以下幾種。

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

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

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

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

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

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

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

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

3)BOOLEAN

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

4)

DATE

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

2．LOB類型

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

DBMS_LOB.LOB。數據類型可分為以下四類：

(1)BFILE；

(2)BLOB；

(3)CLOB；

(4)NCLOB。

3．復合類型

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

1)屬性類型

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

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

empcodeemp.empno%TYPE;

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

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

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

emp_exemp%ROWTYPE;

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

2)記錄類型

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

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

TYPE<記錄類型名>ISRECORD(

<數據項

1><數據類型>[NOTNULL[:=<表達式

1>]],

<數據項

2><數據類型>[NOTNULL[:=<表達式

2>]],

<數據項

n><數據類型>[NOTNULL[:=<表達式

n>]]);

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

【例6.2】

將學生信息定義為記錄類型。

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為數據項，分別對應

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

emp_rec為

emp_record_type類型的變量名。

3)表類型

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

TYPE<表類型名>ISTABLEOF<數據類型>INDEXBYBINARY_INTEGER;

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

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

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

【例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)數組類型

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

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

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

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

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

4．引用類型

PL/SQL語言中的引用類型是用戶定義的指向某一數據緩沖區的指針，與

C語言中的指針類似。游標即為

PL/SQL語言的引用類型。詳細內容將在6.5節講述。

6.2.4表達式

PL/SQL語言常見的表達式分為算術表達式、字符表達式、關系表達式和邏輯表達式四種。

1．算術表達式

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

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

2．字符表達式

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

例如，“PL”‖“/SQL”的結果為“PL/SQL”。

3．關系表達式

關系表達式是由字符表達式或者算術表達式與關系運算符組成的。關系表達式的格式如下：

<表達式><關系運算符><表達式>

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

關系運算符主要有六種：

<、

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

謂詞操作符

LIKE、BETWEEN和

IN也可以作為關系運算符。

4．邏輯表達式

邏輯表達式由關系表達式和邏輯運算符組成。邏輯表達式的運算結果為邏輯值。邏輯運算符包括NOT、OR和AND。邏輯運算符的運算優先次序為

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

<關系表達式><邏輯運算符><關系表達式>

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

6.2.5綁定變量

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

VARIABLEvariablenamedatatype

例如：

SQL>variablegnonumber

當用VARIABLE命令聲明一個數字變量時，不使用精度和標度值。聲明VARCHAR2類型的變量時，不使用長度。在SQL*Plus環境中，用PRINT命令來顯示主機變量的值。

Oracle能夠重復利用執行計劃的方法就是采用綁定變量。綁定變量的實質是用于替代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語句來聲明。程序塊用冒號(:)前綴引用。局部變量不需使用冒號前綴。程序將給局部變量賦值3，將它翻倍后賦值給綁定變量g_num，然后使用PRINT語句顯示結果。

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

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

6.2.6PL/SQL中的替換變量

PL/SQL沒有輸入能力?！?”加標識符即為替換變量，通過替換變量可以在PL/SQL中進行輸入，并可以方便地達到創建通用腳本的目的。

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

將上述例子用替換變量實現。

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過程已成功完成。

當使用替換變量時，輸出結果會顯示進行替換的那些行?？梢允褂胹etverifyoff命令取消這些行。

SQL>SQL>setverifyoff

SQL>/

輸入

p_num的值：5

PL/SQL過程已成功完成。

SQL>printg_num

G_NUM

-------

10例如，創建通用腳本，在Employees表中插入數據。

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

SQL>/

輸入

empno的值:7811

輸入

ename的值:'jamw'

輸入

sal的值:3000

輸入

deptno的值:30

已創建

1行。

6.3控

制

結

構

6.3.1條件控制

條件控制用于根據條件執行一系列語句，包括IF語句和CASE語句。

1．IF語句

1)

IF…THEN

語法：

IFconditionTHEN

Statements1;

Statements2;

ENDIF…

2)

IF…THEN...ELSE

語法:

IFconditionTHEN

Statements1;

Statements2;

ELSE

Statements1;

Statements2;

ENDIF……如果條件condition為TRUE，則執行THEN到ELSE之間的語句；否則，執行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，則執行statement1，然后執行statement5；否則判斷condition2是否為TRUE,若為TRUE，則執行statement2，然后執行statement5。對于condition3也是相同的，如果condition1、condition2、condition3都不成立，那么將執行statement4，然后執行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語句用于根據條件將單個變量或表達式與多個值進行比較。在執行CASE語句前，該語句先計算選擇器的值。CASE語句使用選擇器與WHEN字句中的表達式匹配。語法如下：

CASE選擇器

WHEN表達式1THEN執行語句1；

WHEN表達式2THEN執行語句2；

…

WHEN表達式NTHEN執行語句N；

ELSE執行語句N＋1；

ENDCASE;

當選擇器的值與WHEN子句中的表達式相等時，執行對應的THEN子句部分的語句。

【例6.6】

更新相應部門的員工的補貼。

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子句中的各個比較表達式，找到第一個為TRUE的表達式，然后執行對應的語句序列。語法如下：

CASE

WHEN表達式1THEN執行語句1；

WHEN表達式2THEN執行語句2；

…

WHEN表達式NTHEN執行語句N；

ELSE執行語句N＋1；

ENDCASE；

6.3.2循環控制

循環控制用于重復執行一系列語句。循環結構共有三種類型，分別是基本循環、WHILE循環和FOR循環。

1．基本循環

基本循環的形式是LOOP語句，LOOP和ENDLOOP之間的語句將無限次地執行。

語法：

LOOP

statements;

…

EXIT[WHENcondition];

ENDLOOP;

【例6.7】

使用基本循環。X初始值為100，循環累加10，當X>1000時退出循環。

SQL>DECLARE

XINT:=100;

YINT;

BEGIN

LOOP

X:=X+10;

EXITWHENX>1000;

ENDLOOP;

Y:=X;

END;

/

2．WHILE循環

對于WHILE循環結構，如果條件結果值為TRUE，則執行循環體內的語句；如果條件結果值為FALSE，則結束循環，執行ENDLOOP之后的語句。

語法：

WHILEconditionLOOP

statement1;

statement2;

…

ENDLOOP;

【例6.8】

使用WHILE循環。X的初始值為100，循環累加10，當X>1000時退出循環。

SQL>declare

Xnumber:=100;

Ynumber:=0;

BEGIN

WHILEX<=1000

LOOP

X:=X+10;

ENDLOOP;

Y:=X;

END;

/

3．FOR循環

LOOP循環和WHILE循環的循環次數事先是不知道的，它取決于循環條件；而FOR循環的循環次數是已知的。

語法：

FORcounterIN[REVERSE]start_range...end_rangeLOOP

statements;

ENDLOOP;

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

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

【例6.9】

使用FOR循環。該例程循環10累加10，累計10次后退出。

SQL>DECLARE

xnumber:=100;

ynumber:=0;

BEGIN

FORv_counterin1..10loop

x:=x+10;

ENDLOOP;

y:=x;

END;6.3.3順序控制

順序控制用于按順序執行語句。用戶可以使用標簽使程序獲得更好的可讀性。程序塊或循環都可以被標記。標簽的形式是<<>>。

1．標記程序塊

<<LABEL_NAME>>

[DECLARE]

BEGIN

[EXCEPTION]

END<<label_name>>………

2．GOTO語句

語法：

GOTOLABEL;

執行GOTO語句時，控制會立即轉到由標簽標記的語句。PL/SQL中對GOTO語句有一些限制。對于塊、循環、IF語句而言，從外層跳轉到內層是非法的。

【例6.10】

使用GOTO語句。當員工薪水小于800時轉到update標簽處提高員工薪水100，否則轉至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動態SQL語句

一般的PL/SQL程序設計中，在DML和事務控制的語句中可以直接使用SQL，但是DDL語句及系統控制語句卻不能在PL/SQL中直接使用，要想實現在PL/SQL中使用DDL語句及系統控制語句，可以通過使用動態SQL來實現。下面介紹什么是動態SQL和靜態SQL。所謂靜態SQL，指在PL/SQL塊中使用的SQL語句在編譯時是明確的，執行的是確定對象。動態SQL是指在PL/SQL塊編譯時SQL語句是不確定的，可根據用戶輸入參數的不同而執行不同的操作。動態SQL語句一般由一些SQL語句(如INSERT、UPDATE、DELETE和SELECT等DML語句與CREATETABLE等DDL語句)組成。編譯程序對動態語句部分不進行處理，只是在程序運行時動態地創建語句，對語句進行語法分析并執行該語句。

在Oracle中，動態SQL可以通過本地動態SQL來執行，也可以通過DBMS_SQL包來執行。

執行本地動態SQL的語法如下：

EXECUTEIMMEDIATEdynamic_sql_statement

[INTOdefine_variable_list]

[USINGbind_argument_list]

說明：dynamic_sql_statement是動態語句；INTO子句接受SELECT語句選擇的記錄值；USING用于綁定輸入參數變量。

【例6.11】

執行本地動態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游

標

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

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

6.5.1隱式游標

PL/SQL為所有數據操縱語句和單行查詢語句隱式聲明游標，對于此類游標，用戶不能直接命名和控制。當運行數據操縱語句時，

PL/SQL打開一個內建游標并處理結果(游標是維護查詢結果的內存中的一個區域，游標在運行數據操縱語句時打開，完成后關閉)。Oracle預先定義一個名為SQL的隱式游標，通過檢查隱式游標的屬性可以獲得與最近執行的SQL語句相關的信息。

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

1．%FOUND和%NOTFOUND

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

●TRUE：INSERT；

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

●TRUE：SELECTINTO至少返回一行。

當%FOUND為TRUE時,%NOTFOUND為FALSE。

2．%ROWCOUNT

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

3．%ISOPEN

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

6.5.2顯式游標

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

1．聲明游標

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

CURSORcursor_nameISselect_statement;

說明：cursor_name用于指定游標的名稱；select_statement用于指定游標所對應的查詢語句。

【例6.12】

使用聲明游標。

DELCARE

CURSORC_EMPISSELECTempno,ename,salary

FROMemp

WHEREsalary>2000

ORDERBYename;

…

BEGIN

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

2．打開游標

打開游標時，Oracle會執行游標所對應的查詢語句，并將查詢語句的結果暫存到結果集中。打開游標的語法如下：

OPENcursor_name

說明：cursor_name是在聲明部分定義的游標名。

3．提取數據

在打開游標之后，從游標得到一行數據使用FETCH命令。每一次提取數據后，游標都指向結果集的下一行。

語法：

FETCHcursor_nameINTOvariable[,variable,...]

對于SELECT定義的游標的每一列，FETCH變量列表都應該有一個變量與之相對應，變量的類型也要相同。

【例6.13】

使用游標提取數據。

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;

/

運行結果如下：

SQL>SalaryofEmployeeSMITHis800

SalaryofEmployeeALLENis1600

SalaryofEmployeeWARDis1250

…

4．關閉游標

在提取并處理了結果集的所有數據后，就可以關閉游標并釋放結果集，語法如下：

CLOSEcursor_name

5．帶參數的游標

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

語法：

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

定義參數的語法：

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

OPENcursor_name[value[,value]…];

說明：參數值可以是文字或變量。

【例6.14】

使用帶參數的游標。

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使用游標更新或刪除當前行數據

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

【例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)程序執行后，再次查詢EMP表中部門號為20的員工目前的薪水發現，每個員工的薪水已經提高了10%。

SQL>selectempno,deptno,salfromempwheredeptno=20;

EMPNODEPTNO SAL

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

736920 880

756620 3272.5

778820 3300

787620 1210

790220 3300

800020 55006.5.4循環游標

循環游標可以簡化顯式游標的處理代碼。在使用循環游標時，Oracle會隱含地打開游標、提取游標數據并關閉游標。語法如下：

FORrecord_nameIN

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

|(query_difinition)

LOOP

statements

ENDLOOP;說明：corsor_name是已經定義的游標名；record_name是PL/SQL聲明的記錄變量。此變量的屬性聲明為%ROWTYPE類型，作用域在FOR循環之內。

循環游標的特性如下：

游標FOR循環自動聲明一個游標行，打開游標，從游標中取出行并在游標中的最后一行取出后關閉游標的變量或記錄。

下面我們用FOR循環重寫例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/運行結果如下：

SQL>Department:10-ACCOUNTING

Name:CLARKsalary:2450

Name:KINGsalary:5000

Name:MILLERsalary:1300

ToltalSalaryfordept:8750

Department:20-RESEARCH

Name:ADAMSsalary:1100

…

6.5.5REF游標

隱式游標和顯式游標都是靜態定義的，當用戶使用它們的時候查詢語句已經確定。如果用戶需要在運行的時候動態決定執行何種查詢，則可以使用REF游標和游標變量。

創建REF游標變量需要兩個步驟：聲明REF游標類型和聲明REF游標類型的游標變量。聲明REF游標的語法如下：

TYPE

ref_cursor_name

IS

REF

CURSOR

[RETURN

return_type]

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

在PL/SQL代碼段中可如下定義強類型游標：

Declare

Type

refcur_t

is

ref

cursor;

Type

emp_refcur_t

is

ref

cursor

return

employee%rowtype;

Begin

Null;

End;

/

強類型舉例：

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游標的變量

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/運行結果如下：

SQL>chendonny'sjobis:President

NeenaKochhar'sjobis:AdministrationVicePresident

LexDeHaan'sjobis:AdministrationVicePresident

AlexanderHunold'sjobis:Programmer

… 6.6異

常

處

理

6.6.1預定義異常

系統預定義異常處理是針對

PL/SQL程序編譯、執行過程中發生的系統預定義異常問題進行處理的程序。無論是違反

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

6-1所示。

表6-1Oracle常用的系統預定義異常

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

EXCEPTION

WHENexception_nameTHEN

Codeforhandingexception_name