??前言：本博客僅作記錄學習使用，部分圖片出自網絡，如有侵犯您的權益，請聯系刪除

PL/SQL（Procedural Language/SQL）是Oracle數據庫中的一種過程化編程語言，構建于SQL之上，允許編寫包含SQL語句的程序。它結合了SQL的數據操縱和查詢能力與過程化編程的控制結構，如IF語句和LOOP語句，支持變量定義和數據傳遞，能夠實現復雜的業務邏輯。作為Oracle的專用語言，PL/SQL是對標準SQL的擴展，增強了數據庫編程的靈活性和功能。

一、PL/SQL塊結構

PL/SQL程序以塊（block）為基本單位，整個PL/SQL塊分為3部分，即聲明部分（用DECLARE開頭）、執行部分（以BEGIN開頭）和異常處理部分（以EXCEPTION開頭）。其中，執行部分是必須的，其他兩個可選

標準PL/SQL塊的語法格式如下：

?[DECLARE]       --聲明部分，可選BEGIN           --執行部分，必須[EXCEPTION]     --異常處理部分END

1、聲明部分

由關鍵字DECLARE開始，到BEGIN關鍵字結束。在這里可以聲明PL/SQL程序塊中用到的變量、常量和游標等。注意，在某個PL/SQL塊中聲明的內容只能再當前塊使用

2、執行部分

以關鍵字BEGIN開始，結束方式有兩種：如果PL/SQL塊中的代碼在運行時出現異常，則執行完異常處理部分的代碼就結束；如果沒有使用異常處理或PL/SQL塊未出現異常，則以關鍵字END結束。執行部分是整個PL/SQL塊的主體，主要的邏輯控制和運算都在這部分被完成，所以在執行部分可以包含多個PL/SQL語句和SQL語句

3、異常處理部分

以關鍵字EXCEPTION開始，在該關鍵字所包含的代碼被執行完畢后，整個PL/SQL塊也將結束。在執行過程中，可能會產生意想不到的錯誤，可以在異常處理部分通過編寫一定量的代碼來糾正錯誤或給一些提示，甚至將各數據回退到異常前。對于可能出現的多種異常情況，可以使用WHEN THEN語句來實現多分支判斷，然后在每個分支下通過編寫代碼來處理相應的異常。

對于PL/SQL塊中的語句，每一條PL/SQL語句都必須以分號結束，而每條PL/SQL語句均可以被寫成多行的形式，同樣必須使用分號來結束；另外，一行中也可以有多條PL/SQL語句，但是它們之間必須以分號分隔。

示例：定義一個PL/SQL塊，計算兩個正數的和與這兩個整數的差的商：

?--實現在服務端顯示執行結果set serveroutput on--PL/SQL塊declarea int:=100;b int:=200;c number;beginc:=(a+b)/(a-b);dbms_output.put_line(c);exceptionwhen zero_divide thendbmx_output.put_line('除數不允許為零!');end;

二、代碼注釋和標識符

1、單行注釋

單行注釋由兩個連接字符"--"開始，后面緊跟著注釋內容

?set serveroutput ondeclareNum_sal number;         --聲明一個注釋變量Var_ename varchar2(20); --聲明一個字符串變量beginselect ename,sal into Var_ename,Num_sal from emp    --檢索指定的值并存儲到變量中where empno=7369;dbms_output.put_line(Var_ename||'的工資是'||Num_sal);   --輸出變量中值end;

2、多行注釋

多行注釋由"/*"開頭，以"*/"結尾，這種多行注釋的方法在大多數的編程語言中是相同的

?set serveroutput ondeclareNum_sal number;             /*聲明一個數值變量*/Var_ename varchar2(20);     /*聲明一個字符串變量*/begin/*檢索指定的值并存儲到變量中*/select ename,sal into Var_ename,Num_sal from empwhere empno=7369;/*輸出變量中的值*/dbms_output.put_line(Var_ename||'的工資是'||Num_sal);end

3、標識符

標識符（identifier）用于定義PL/SQL塊和程序單元的名稱；通過使用標識符，可以定義常量、變量、異常、顯示游標、游標變量、參數、子程序以及包的名稱。當使用標識符定義PL/SQL塊或程序單元時，需要滿足以下規則：

• 當定義變量、常量時，每行只能定義一個變量或者常量（行終止符為";"）
• ~，名稱必須以英文字符（A-Z,a-z)開始，并且最大長度為30個字符。如果以其他字符開始，那么必須使用雙引號引住。
• 名稱只能使用A-Z,a-z,0-9以及符號_、$和#。如果使用其他字符，那么必須使用雙引號引住
• 名稱不能使用Oracle關鍵字，若需要則以雙引號引住

所有PL/SQL程序元素都是由一些字符序列組合而成的，而這些字符序列中的字符都必須取自PL/SQL所允許使用的字符集：

• 大寫和小寫字母：A-Z或a-z
• 數字：0-9
• 非顯示的字符：制表符、空格和按Enterjian
• 數學符號：+、-、*、/、<、>、=等
• 間隔符：()、{}、[]、？、！、;、:、@、#、%、$、&等

4、文本

文本指實際數值的文字，包括數字文本、字符文本、布爾文本、日期時間文本、字符串文本等

• 數字文本：整數或者浮點數。編寫PL/SQL代碼時，可以十一科學計數法和冪操作符(**)表示。這只適用與PL/SQL語句，不適用SQL語句
• 字符文本：用單引號引住的單個字符。這些字符可以是PL/SQL支持的所有字符
• 布爾文本：通常指BOLLEAN值（TRUE、FALSE和NULL），主要用在條件表達式中
• 日期時間文本：指日期和時間值。日期文本必須用單引號引住，并且日期值必須與日期格式和日期語言匹配
• 字符串文本：由兩個或兩個以上字符組成的多個字符值。字符串文本必須由單引號引住，

在Oracle Database 10g之前，如果字符串文本包含單引號，必須使用兩個單引號表示：例如

?string_vat:= 'I''m a string,you''re a string.';

在Oracle Database 10g之后，增加可以使用其他分隔符（[]、{}、<>）賦值、需要在分隔符前后加上單引號，而且需要帶有前綴q：

?string_var:= q'[I'm a string,you're a string.]';

三、基本數據類型

1、數組類型

數組類型包括NUMBER、BINARY_INTEGER和PLS_INTEGER3種類型

• NUMBER類型的變量可以存儲整數或浮點數；
• BINARY_INTEGER和PLS_INTEGER類型的變量只能存儲整數

NUMBER類型還可以通過NUMBER(p,s)的形式來格式化數字，其中，參數p表示精度，參數s表示刻度范圍。精度是指數值中所有有效數字的個數，而刻度范圍是指小數點右邊小數位的個數，在這里精度和刻度范圍都是可選的。

?--聲明一個精度為8且刻度范圍為3的標識金額的變量Num_MoneyNum_Money NUMBER(8,3);

PL/SQL子類型，是與NUMBER類型等價的類型別名，甚至可以說是NUMBER類型的多種重命名形式；這些等價的子類型主要包括DEC、DECIMAL、DOUBLE、INTEGER、INT、NUMERIC、SMALLINT、BINARY_INTEGER、PLS_INTEGER等。

2、字符類型

字符類型主要包括VARCHAR2、CHAR、LONG、NCHAR和HVARCHAR2，用來存儲字符串或字符數據。

• VARCHAR2類型：PL/SQL中的VARCHAR2類型和數據庫類型中VARCHAR2比較類似，用于存儲可變長度的字符串，其格式：

?VARCHAR2(maxlength)

參數maxlength表示可存儲字符串的最大長度，定義變量時必須給出（因為VARCHAR2類型沒有默認的最大長度），最大值32767字節。

注意：數據庫類型中的VARCHAR2的最大長度是4000字節，所以一個長度大于4000字節的PL/SQL中的類型VARCHAR2變量不可以賦值給給數據庫中的一個VARCHAR2變量，而只能賦值給LONG類型的數據庫變量

• CHAR類型：表示指定長度的字符串，其語法格式如下：

?CHAR(maxlength)

CHAR類型的默認最大長度為1。與VARCHAR2不同，maxlength可以不指定，默認為1。如果賦給CAHR類型的值不足maxlength， 則會在其后面用空格補全，這也是不同于VARCHAR2的地方

注意：數據庫類型中的CHAR只有2000字節，所以如果PL/SQL中的CAHR類型變量的長度大于2000字節，則不能賦給數據庫中的CHAR。

• LONG類型：表示可變的字符串，最大長度是32 767字節。數據庫類型中的LONG最大長度可達2GB。
• NCHAR和NVARCHAR2類型：PL/SQL 8.0以后加入的類型，它們長度根據國字符集來確定。

3、日期類型

日期類型只有一種，即DATE類型，用來存儲日期和時間信息。DATE類型的存儲空間是7字節，分別使用1字節存儲世紀、年、月、天、小時、分鐘和秒。

4、布爾類型

布爾類型也只有一種，即BOOLEAN類型，主要用于程序的流程控制和業務邏輯判斷，其變量值可以是TRUE、FALSE或NULL中的一種。

特殊數據類型

5、特殊數據類型

5.1、%TYPE類型

使用%TYPE關鍵字 可以聲明一個與指定列相同的數據類型，它通常緊跟在指定列名的后面

?--聲明一個與emp表中job列的數據類型完全相同的變量var_jobdeclarevar_job emp.job%type;

使用%TYPE定義變量有兩個好處：

• 無需手動指定數據類型
• 自動適配數據類型變化

?--使用%TYPE類型的變量輸出emp表中編號為7369的員工名稱和職務信息set serveroutput ondeclarevar_ename emp.ename%type;   --聲明與ename列類型相同的變量var_job emp.job%type;       --聲明與job列類型相同的變量beginselect ename,jobinto var_ename,var_jobfrom empwhere empno=7369;       --檢索數據，并保存在變量中dbms_output.puty_line(var_ename||'的職務是'||var_job);  --輸出變量的值end;

注意：由于INFO子句中的變量只能存儲一個單獨的值，因此要求SELECT子句只能返回一行數據，這個由WHERE子句進行了限定。若SELECT子句返回多行數據，則代碼運行后會返回錯誤信息

5.2、RECORD類型

在PL/SQL中，RECORD類型是一種復合數據類型，用于在單個變量中存儲多個相關字段，每個字段可以有不同的數據類型。RECORD類型常用于從數據庫表或多列查詢中檢索數據，并將這些數據存儲在一個單一的變量中，便于操作和傳遞。

?TYPE record_type IS RECORD      --record_type表示要定義的記錄數據類型名稱(var_menber1 data_type [not_null] [:=default_value],  ?--data_type:表示成員變量的數據類型...var_membern data_type [not_null] [:=default_value])

記錄類型的聲明類似于C或C++中的結構類型，并且成員變量的聲明與普通PL/SQL變量的聲明相同。

聲明一個記錄類型 emp_type，然后使用該類型的變量存儲emp表中的一條記錄信息，并輸出這條記錄信息，代碼：

?declaretype emp_type is record         --聲明RECORD類型emp_type(var_ename varchar2(20),         --定義字段/成員變量var_job varchar2(20),var_sal number);empinfo emp_type;       --定義變量beginselect ename,job,salinto empinfofrom empwhere empno=7369;       --檢索數據/*輸出員工信息*/dbms_output.put_line('員工'||empinfo.var_ename||'的職務是'||empinfo.var_job||'、工資是'empinfo.var_sal);end;

5.3、%ROWTYPE類型

%ROWTYPE類型的變量結合了"%TYPE類型"和"記錄類型"變量的優點，它可以根據數據表中行的結構定義一種特殊的數據類型，用來存儲從數據表中檢索到的一行數據，其語法：

?rowVar_name table_name%ROWTYPE;

?--示例:聲明一個%ROWTYPE類型的變量rowVAR_emp,然后使用該變量存儲emp表中的一行數據declarerowVar_emp emp%rowType; --定義能夠存儲emp表中一行數據的變量rowVar_empbeginselect *into rowVar_empfrom empwhere empno=7369;   --檢索數據/*輸出員工信息*/dbms_output.put_line('員工'||rowVar_emp.empno||'，職務是'||rowVar_emp.job);end;

6、自定義變量和常量

6.1、定義常量

<變量名><數據類型>[(長度):=<初始值>];

?--示例:定義一個用于存儲國家名稱的可變字符串變量var_countryname,該變量的最大長度是50，并且該變量初始值是"中國"var_countryname varchar2(50):= '中國';

6.2、變量的初始化

PL/SQL定義了一個未初始化變量應該存儲的內容，其值被賦值為NULL

6.3、PL/SQL表達式

（1）字符表達式

唯一的字符運算符就是并置運算符 "||"，它的作用是把幾個字符串連在一起，如表達式'Hello'||'World'||'!'的值就等于'Hello Wordl!'。

（2）布爾表達式

PL/SQL控制結構都涉及布爾表達式。布爾表達式是一個判斷結果為真還是假的條件表達式，值有TRUE、FALSE或NULL；

布爾表達式有3個布爾運算符，即AND、OR和NOT，與高級語言中的邏輯運算符一樣，它的操作對象時布爾變量或表達式

此外，BETWEEN操作符可以劃定一個范圍，在范圍內則為真，否則為假；

IN操作符判斷某一元素是否屬于某個集合，屬于則問真，不屬于則為假

7、流程控制語句

控制語句	說明
if...then	判斷IF正確，則執行THEN
if...then...else	判斷IF正確，則執行THEN，否則執行ELSE
if...then...elsif	嵌套式判斷
case	有邏輯地從數值中做出選擇
loop...exit...END	循環控制，用判斷語句執行EXIT
loop...exit when...END	同上，當WHRN為真時執行EXIT
while...loop...END	當WHILE為真時循環
for...in...loop...END	已知循環次數的循環
goto	無條件轉向控制

7.1、選擇語句

（1）IF...THEN語句

只做一種情況或條件的判斷，其語法：

?IF <condition_expression> THEN ?

?plsql_sentence ?

?END IF;

?--定義兩個字符串變量，賦值，使用IF...THEN比較其長度，并輸出結果set serveroutput ondeclarevar_name1 varchar2(50);     --定義兩個字符串變量var_name2 varchar2(50);beginvar_name1:='Wast';      --給兩個字符串變量賦值var_name2:='xiaofei';if length(var_name1) < length(var_name2) then   --比較長度大小/*輸出比較后的結果*/dbms_output.put_line('字符串"'||var_name1||'"的長度比字符串"'||var_name2||'"的長度小')end if;end;

如果IF后面的條件表達式存在"并且"或者"非"等邏輯運算，則可以使用AND、OR、NOT等邏輯運算符。另外，如果要判斷IF后面的條件表達式的值是否為空值，則需要在條件表達式中使用is和null關鍵字，如：

?if last_name is null then...end if;

（2）IF...THEN...ELSE語句

可以實現判斷兩種情況，只有IF后面的條件表達式為FALSE，程序就會執行ELSE語句下面的PL/SQL語句，其格式：

IF < condition_expression> THEN ?

plsql_sentence1; ?

ELSE ?plsql_sentence2; ?

END IF;

?--只有年齡大于或等于60歲，才可以申請退休的功能，否則程序會提示不可申請退休set serveroutput ondeclareage int:=55;beginif age >= 60 thendbms_output.put_line('您可以申請退休了!');elsedbms_output.put_line('您小于60歲，不可以申請退休!')end if;end;

（3）IF...THEN...ELSIF語句

實現多分支判斷選擇，使程序的判斷條件更豐富。如果該語句中的哪個判斷分支的表達式為TRUE，那么程序就會執行對應的PL/SQL語句，其格式：

?IF < condition_expression1 > THEN ?

?plsql_sentence_1; ?

?ELSIF < condition_expression2 > ?

?THEN plsql_sentence_2;

? ... ?

?ELSE plsql_sentence_n;

?END IF;

?--首先指定一個月份數值，然后使用其語句判斷它所屬的季節，并輸出季節信息:set serveroutput ondeclaremonth int:=6;beginif month >= 0 and month <= 3 thendbms_output.put_line(month||'月是春季');elsif month >= 4 and month <= 6 thendbms_output.put_line(month||'月是夏季');elsif month >= 7 and month <= 9 thendbms_output.put_line(month||'月是秋季');elsif month >= 10 and month <= 12 thendbms_output.put_line(month||'月是冬季');elsedbms_output.put_line('對不起，月份不合法!');end if;end;

（4）case語句

CASE語句的執行方式與IF..THEN...ELSIF語句十分相似。在CASE關鍵字的后面有一個選擇器，它通常是一個變量，程序就是從這個選擇器開始執行

?CASE < selector>

?WHTN <expression_1> THEN plsql_sentence_1;

?WHTN <expression_2> THEN plsql_sentence_2;

?...

?WHTN <expression_n> THEN plsql_sentence_n;

?[ELSE plsql_sentence;]

?END CASE;

?--示例:指定一個季度數的值，然后使用CASE語句判斷它所包含的月份信息并輸出set serveroutput ondeclaresession int:=3;aboutInfo varchar2(50);begincase seasonwhen 1 thenaboutInfo := season||'季度包括1,2,3月份';case seasonwhen 2 thenaboutInfo := season||'季度包括4,5,6月份';case seasonwhen 3 thenaboutInfo := season||'季度包括7,8,9月份';case seasonwhen 4 thenaboutInfo := season||'季度包括10,11,12月份';else aboutInfo := season||'季度不合法';end case;dbms_output.put_line(aboutinfo);end;

在執行多種情況判斷時，建議使用CASE語句替換IF...THEN...ELSIF語句。

7.2、循環語句

（1）LOOP語句

LOOP語句會先執行一次循環體，然后判斷EXIT WHEN關鍵字后面的條件表達式的值是TRUE還是FALSE。如果是TRUE，程序會退出循環體；否則再次執行循環體。這使得程序至少能執行一次循環體，其格式：

?LOOP

?plsql_sentence;

?EXIT WHEN end_condition_exp

?END LOOP

?--示例:使用LOOP計算前100個自然數的和，并輸出到屏幕set serveroutput ondeclaresum_i int:= 0;i int:= 0;beginloopi:=i+1;sum_i = sum_i+i;exit when i =100;   --當循環到100時退出循環體end loop;dbms_output.put_line('前100個自然數的和是:'||sum_i);end;

（2）WHILE語句

WHILE語句根據它的條件表達式的值執行零次或多次循環體，在每次執行循環體之前，首先要判斷條件表達式的值是否為TRUE，若為TRUE，則程序執行循環體；否則退出WHILE循環，然后繼續執行WHILE語句后面的其他代碼，其語法：

?WHILE condition_expression LOOP

?plsql_sentence;

?END LOOP;

?--示例:使用WHILE語句計算前100個自然數的和，并輸出到屏幕set serveroutput ondeclaresum_i int := 0;i int := 0;beginwhile i<=99 loop    --當i的值等于100時，程序退出WHILE循環i:=i+1sum_i := sum_i+i,end loop;dbms_output.pu_line('前100個自然數的和是:'||sum_i);end;

（3）FOR語句

FOR語句時一個可預置循環次數的循環控制語句，它有一個循環計數器，通常是一個整型變量，通過這個循環計數器來控制循環執行的次數。該計數器可以從小到大進行記錄，也可相反。另外，該計數器值的合法性由上限值和下限值控制，若計數器值在上限值和下限值的范圍內，則程序執行循環；否則終止；其格式：

?FOR variable_counter_name in [REVERSE] lower_limit..upper_limit LOOP

?plsql_sentence;

?END LOOP;

?--示例:使用FOR語句計算前100個自然數中偶數之和，并輸出到屏幕set serveroutput ondeclaresum_i := 0;beginfor i in reverse 1..100 loop    --遍歷前100個自然數if mod(i,2)=0 then              --判斷是否為偶數sum_i:=sum_i+i;                 --計算偶數和end if;end loop;dbms_output.put_line('前100個自然數中偶數之和是:'||sum_i);end;

（4）GOTO語句

?GOTO label;

這個是無條件轉向語句。當執行GOTO語句時，控制程序會立即轉到由標簽標識的語句中。其中，label是在PL/SQL中定義的符號。標簽使用雙箭頭括號（<<>>）括起來

?--示例..  --程序其他部分<<goto_mark>>       --定義了一個轉向標簽goto_mark..  --程序其他部分IF no>98050 THENGOTO goto_mark;     --如果條件成立，則轉向goto_mark繼續執行..  --程序其他部分

8、游標

游標提供了一種從表中檢索數據并進行操作的靈活手段，游標主要用在服務器上，處理由客戶端發送給服務端的SQL語句，或是批處理、存儲過程、觸發器中的數據處理請求。游標的作用就相當于指針，通過游標PL/SQL程序可以一次處理查詢結果集中的一行，并可以對該數值執行特定操作。

在Oralce中，通過游標操作數據主要使用顯式游標和隱式游標。另外還包括具有引用類型特性的REF游標

8.1、基本原理

在PL/SQL塊中執行SELECT、INSERT、UPDATE和DELETE語句時，Oracle會在內存中為其分配上下文區，即一個緩沖區。游標是指向該區的一個指針，或是命名一個工作區，或是一種結構化數據類型。游標為應用程序提供了一種具有對多行數據查詢結果集中的每一行數據分別進行單獨處理的方法，是設計嵌入式SQL語句的應用程序的常用編程方法。

8.2、顯式游標

顯式游標是由用戶聲明和操作的一種游標，通常用于操作查詢結果集，使用它處理數據的步驟包括聲明游標、打開游標、讀取游標和關閉游標4個步驟。其中，讀取游標可能需要反復操作，因為游標每次只能讀取一行數據，所以對于多條記錄，需要反復讀取。

（1）聲明游標

聲明游標主要包括指定游標名稱和為游標提供結果集的SELECT語句，其格式：

?CURSOR cur_name[(input_parameter1[,input_paramater2]...)] [RETURN ret_type]

?IS select_ setence; ??

?--cur_name:表名所聲明的游標名稱

?--ret_type:表名執行游標操作后的返回值類型，這是一個可選項

?--select_sentence:游標所使用的SELECT語句，為游標的反復讀取提供了結果集

?--input_paramater1:作為游標的"輸入參數"可以有多個，這是一個可選項。它指定用戶在打開游標后向游標中傳遞的值，該參數的定義和初始化格式如下:

?para_name [IN] DATATYPE [{:= | DEFAULT} para_value]

?--示例:聲明游標，用來讀取emp表中職務為銷售員(SALESMAN)的員工信息declarecursor cur_emp(var_job in varchar2:='SALESMAN')is select empno,ename,salfrom empwhere job=var_job;

在上述代碼中，首先聲明了一個名稱為cur_emp的游標，并定義了一個輸入參數var_job（類型為varchar2，但不可以指定長度，否則程序報錯），該參數用來存儲員工的職務（初始值為SALESMAN）；然后使用SELECT語句檢索得到職務是銷售員的結果集，以等待游標逐行讀取它。

（2）打開游標

游標聲明完后，必須打開游標才能使用，其格式：

?OPEN cur_name[(para_value[,para_value2]...)];

打開游標就是執行定義的SELECT語句。執行完畢，將查詢結果裝入內存，游標停在查詢結果的首部（注意，并不是第一行）。打開一個游標時，會完成以下幾件事：

• 檢查聯編變量的取值
• 根據聯編變量的取值，確定活動集
• 活動集的指針指向第一行

?--打開游標cur_emp，給游標的"輸入參數"賦值為"MANAGER"OPEN cur_emp('MANAGER');        --若省略('MANAGER')則表示使用默認值

（3）讀取游標

當打開一個游標后，就可以讀取游標中的數據了，讀取游標就是逐行將結果集中的數據保存到變量中。讀取游標使用FETCH...INTO語句，其格式：

?FETCH cur_name INTO {variable};

在游標中包含一個數據行指針，它用來指向當前數據行。剛打開游標時，指針指向結果集中的第一行，當使用FETCH...INTO語句讀取數據完畢之后，游標中的指針將自動指向下一行數據。這樣，就可以在循環結構中使用FETCH...INTO來讀取數據，知道指針指向結果集中最后一條記錄為止，這時游標的%FOUND屬性值為FALSE；

?--聲明一個檢索emp表中員工信息的游標，然后打開游標，并指定檢索職務是MANAGER的員工信息，接著使用FETCH...INTO語句和WHILE循環語句讀取游標中的所有員工信息，最后輸出讀取的員工信息set serveroutput ondeclare/*聲明游標,檢索員工信息*/cursor cur_emp(var_job in varchar2:='SALESMAN')is select empno,ename,salfrom empwhere job=var_job;type record_emp is record   --聲明一個記錄類型(/*定義當前記錄的成員變量*/var_empno emp.empno%type,var_ename emp.ename%type,var_sal emp.sal%type);emp_row record_emp;     --聲明一個record_emp類型的變量beginopen cur_emp('MANAGER');    --打開游標fetch cur_emp into emp_row;  --先讓指針指向結果集中的第一行，并將值報錯到emp_row中while cur_emp%found loopdbms_output.put_line(emp_row.var_ename||'的編號是'||emp_row.var_empno||',工資是'||emp_row.var_sal);fetch cur_emp into emp_row; --讓指針指向結果集中的下一行，并將值保存到emp_row中end loop;close cur_emp;      --關閉游標end;/

（4）關閉游標

當所有活動集都被檢索以后，游標就應該被關閉。其語法：

CLOSE cur_name;

一旦關閉了游標，SELECT操作就會被關閉，并釋放占用的內存區。如果再從游標提取數據就是非法的，這樣做會產生以下兩種錯誤：

?ORA-1001:Invalid CUSOR      --非法游標ORA-1002:FETCH out of sequence  --超出界限

8.3、隱式游標

在執行一個SQL語句時，Oracle會自動創建一個隱式游標，這個游標是內存中處理該語句的工作區域。隱式游標主要是處理數據操作語語句（如UPDATE、DELETE語句）的執行結果，當然在特殊情況下，也可以處理SELECT語句的查詢結果。由于隱式游標也有屬性，因此當使用隱式游標的屬性時，需要在屬性前面加上隱式游標的默認名稱-SQL

在實際的PL/SQL編程中，經常使用隱式游標來判斷更新數據行或刪除數據行的情況

?--示例:把emp表中銷售員的工資上調20%，然后使用隱式游標SQL的%ROWCOUNT屬性輸出上調工資的員工數量set serveroutput on?beginupdate empset sal=sal*(1+0.2)where job='SALESMAN';if sql%notfound thendbms_output.put_line('沒有員工需要上調工資');else    --若UPDATE語句沒有影響到一行數據dbms_output.put_line('有'||sql%rowcount||'個員工工資上調20%');end if;end;/

8.4、游標的屬性

無論是顯式游標還是隱式游標，都具有%FOUND、%NOTFOUND、%ROWCOUNT和%ISOPEN 4個屬性，通過這4個屬性可以獲知SQL語句的執行結果以及該游標的狀態信息

游標屬性只能用在PL/SQL的流程控制語句內，而不能用在SQL語句內。

（1）是否找到游標（%FOUND）

布爾型屬性，如果SQL語句至少影響到一行數據，則該屬性為TRUE否則為FALSE，檢查此屬性可以判斷是否結束游標使用

?open cur_emp;                               --打開游標fetch cur_emp into var_ename,var_job;       --將第一行數據放入變量中，游標后移loopexit when not cur_emp%found;            --使用了%FOUND屬性end loop;

在隱式游標中%FOUND屬性的引用方法是SQL%FOUND：

delete from emp where empno = emp_id;		--emp_id為一個有值變量
if sql%found then	--刪除成功則將該行員工編號寫入success表中insert into success values(empno);
else		--失敗則寫入fail表中insert into fail values(empno);
end if;

（2）是否沒找到游標（%FOUND）

與%FOUND相反，這里省略

（3）游標行數（%ROWCOUNT）

%ROWCOUNT屬性 記錄了游標抽取過的記錄行數，也可以理解為當前游標所在的行號。這個屬性在循環判斷中也很有用，使得不必抽取所有記錄行就可以中斷游標操作。

?loopfetch cur_emp into var_empno,var_ename,var_job;exit when cur_emp%rowcount = 10;    --只抽取10條記錄...end loop;

還可以使用FOR語句控制游標的循環，系統隱含地定義了一個數據類型為ROWCOUNT的記錄，作為循環計數器，將隱式地打開和關閉游標

（4）游標是否打開（%ISOPEN）

%ISOPEN屬性 表示游標是否處理打開狀態。在實際應用中，使用一個游標前，第一步往往是檢查它的%ISOPEN屬性，看其是否已打開，若沒有，要打開游標再往下操作。

?if cur_emp%isopen thenfetch cur_emp into var_empno,var_ename,var_job;elseopen cur_emp;end if;

在隱式游標中此屬性的引用方法是SQL%ISOPEN。隱式游標中SQL%ISOPEN屬性總為TRUE，因此在隱式游標中不用打開和關閉游標，也不用檢查其打開狀態

（5）參數化游標

在定義游標時，可以帶上參數，使得在使用游標時，根據參數不同所選中的數據行業不同，達到動態使用的目的

?--聲明一個游標，用于檢索指定員工編號的員工信息，然后使用游標的%FOUND屬性來判斷是否檢索到指定員工編號的員工信息set serveroutput ondeclarevar_ename varchar2(50); var_job varchar2(50);/*聲明游標，檢索指定員工編號的員工信息*/cursor cur_empis select ename.jobfrom empwhere empno=7499;beginopen cur_emp;fetch cur_emp into var_ename,var_job;if cur_emp%found thendbms_output.put_line('編號是7499的員工名稱為:'||var_ename||'，職務是:'||var_job);elsedbms_output.put_line('無數據記錄');end if;end;/

8.5、游標變量

（1）聲明游標變量

游標變量時一種引用類型。當程序運行時，他們可以指向不同的存儲單元。如果要使用引用類型，首先要生命該變量，然后使用相應的存儲單元必須被分配。PL/SQL中的引用類型變量通過下述語法進行聲明：

?REF type

type是已經被定義的類型。REF關鍵字指明新的類型必須是一個指向經過定義的類型的指針。因此，游標變量可以使用的類型就是REF CURSOR。定義一個游標變量其語法：

?TYPE <類型名> IS REF CURSORRETURN <返回類型>

游標變量的返回類型必須是一個記錄類型。它可以被顯式聲明為一個用戶定義的列表，或隱式使用%ROWTYPE進行聲明。在定義了引用類型后就可以聲明該變量了

?set serveroutput ondeclaretype t_StudentRef is ref cursor     --定義使用%ROWTYPEreturn students%rowtype;type t_AbstractstudentsRecord is record(    --定義新的記錄類型sname students.sname%type,sex students.sex%type);v_AbstractStudentsRecord t_AbstractstudentsRecord;type t_AbstractStudentsRed is ref corsor    --使用記錄類型的游標變量return t_AbstractStudentsRecord;type t_NameRef2 is ref cursor       --另一類型定義return v_AbstractStudentsRecord%type;v_StudentCV t_StudentsRef;      --聲明上述類型的游標變量v_AbstractStudentCV t_AbstractStudentsRef;

在上述代碼中極少的游標變量時受限的，它的返回類型只能是特定類型。而在PL/SQL語句中，還有一種非受限游標變量，它在聲明時沒有return子句，一個非受限游標變量可以為任何查詢打開

?declare--定義非受限游標變量type t_FlexibleRefIS ref cursor;--游標變量V_CURSORVar t_FlexiableRef;

（2）打開游標變量

如果要將一個游標變量與一個特定的SELECT語句相關聯，需要使用OPEN FOR語句

?OPEN<游標變量>FOR<SELECT語句>;

如果游標變量是受限的，則SELECT語句的返回類型必須與游標變量所受限的記錄類型匹配，如果不匹配，則Oracle會返回錯誤ORA_6504。

?--打開游標變量v_StudentSCVDECLARETYPE t_StudentRef IS REF CURSOR     --定義使用%ROWTYPERETURN STUDENTS%ROWTYPE;v_StudentSCV t_StudentRef;      --定義新的記錄類型  BEGINOPEN v_StudentSCV FORSELECT * FROM STUDENTS;END;

（3）關閉游標變量

游標變量的關閉和靜態游標的關閉類型，均使用CLOSE語句。關閉已經關閉的游標變量是非法的。

8.6、通過FOR語句循環游標

在使用隱式游標或顯式游標處理具有多行數據的結果集時，可以配合使用FOR語句來完成。在使用FOR語句遍歷游標中的數據時，可以把它的計時器看做一個自動的RECORD類型的變量

在FOR語句中變量隱式游標中的數據時，通常在關鍵字IN的后面提供由SELECT語句檢索的結果集，在檢索結果集的過程中，Oracle系統會自動提供一個隱式的游標SQL

?--使用隱式游標和FOR語句檢索出的職務是銷售員的員工信息并輸出set serveroutput onbeginfor emp_record in (select empno,ename,sal from emp where job='SALESMAN')    --遍歷隱式游標中的記錄loopdbms_output.put('員工編號:'||emp_record.empno); dbms_output.put(':員工名稱:'||emp_record.ename);    dbms_output.put_line(':員工工資:'||emp_record.sal); end loop;end;/

在FOR語句中變量顯式游標中的數據時，通常在關鍵字IN的后面提供游標的名稱，其格式：

?FOR var_auto_record IN cur_name LOOP

?plsqlsentence;

?END LOOP;

?--使用顯式游標和FOR語句檢索部門編號是30的員工信息并輸出set serveroutput ondeclarecursor cur_emp is select * from empwhere deptno = 30;      --檢索部門編號為30的員工信息beginfor emp_record in cur_emp       --遍歷員工信息loopdbms_output.put('員工編號:'||emp_record.empno);dbms_output.put(':員工名稱:'||emp_record.ename);dbms_output.put_line(': 員工職務:'||emp_reocrd.job);end loop;end;/

注意：在使用游標（包括顯式和隱式）的FOR循環中，可以聲明游標，但不用進行打開、讀取和關閉游標等操作，這些由Oracle自動完成

9、異常處理

9.1、異常處理方法

（1）預定義異常處理

每當PL/SQL程序違反了Oracle的規則或超出系統的限制時，系統就自動地產生內部異常。每個Oracle異常都有一個號碼，但異常必須按名個處理。因此PL/SQL對那些常見的異常預定義了異常名。

（2）用戶自定義異常

• 異常聲明：包括預定義異常和用戶自定義異常。用戶定義的異常只能再PL/SQL塊的聲明部分進行聲明，聲明方式與變量聲明類似
• 拋出異常：使用RAISE語句顯式地提出
• 為內部異常命名：必須使用OTHERS處理程序或用偽命令EXCEPTION_INIT來處理未命名的內部異常

注意：異常是一種狀態而不是一個對象，因此異常名不能出現在賦值語句或SQL語句中。PRAGMA EXCEPTION_INIT的作用是將一個異常名與一個Oracle錯誤號碼聯系起來。因此，用戶就可以按名稱引用任何內部異常，并為它編寫一個特定的處理程序。

9.2、異常處理語法

（1）聲明異常

?exception_name EXCEPTION

（2）為內部異常命名

?PRAGE EXCEPTION_INIT(exception_name.ORA_errornumber);

（3）異常定義

?DECLAREexception_name EXCEPTION;BEGINIF condition THENRAISE exception_name;END IF;EXCEPTIONWHERE exception_name THENstatement;END;

（4）異常處理

?SET SERVEROUTPUT ONEXCEPTIONWHEN exception1 THENstatement1WHEN exception2 THENstatement2...WHEN OTHERS THENstatement3

（5）使用SQLCODE和SQLERRM函數定義提示信息

?DBMS_OUTPUT.PUT_LINE('錯誤號:'||SQLCODE);DBMS_OUTPUT.PUT_LINE('錯誤號:'||SQLERRM);

9.3、預定義異常

以下是一些在Oracle官方文檔中查找預定義異常的常用鏈接：

1. Oracle Database PL/SQL Language Reference
2. Oracle Database PL/SQL Packages and Types Reference

3. Oracle Database Error Messages

?--示例:使用SELECT INTO語句檢索emp表中部門編號為10的員工記錄信息，然后使用TOO_MANY_ROWS預定義異常捕獲錯誤信息并輸出set serveroutput ondeclarevar_empno number;           --定義變量，存儲員工編號var_ename varchar2(50);     --定義變量，存儲員工名稱beginselect empno,ename into var_empno,var_enamefrom empwhere deptno=10;            --檢索部門編號為10的員工信息if sql%found then           --若檢索成功，則輸出員工信息dbms_output.put_line('員工編號:'||var_empno||':員工名稱'||var_ename);end if;exception           --捕獲異常when too_many_rows thendbms_output.put_line('返回記錄超過一行');when no_data_found thendbms_output.put_line('無數據記錄');end;/

9.4、自定義異常

（1）錯誤編號異常

錯誤編號是指在Oracle系統發生錯誤時，系統會顯示錯誤號和相關描述信息的異常。雖然直接使用錯誤編號也可以完成異常處理 ，但錯誤編號較為抽象，不易于用戶理解和記憶。對于這種異常，首先在PL/SQL塊的聲明部分（DECLARE部分）使用EXCEPTION類型定義一個異常變量名，然后使用語句PRAGMA EXCEPTION_INIT為"錯誤編號"關聯"這個異常變量名"，接下來就可以像對待系統預定義異常一樣處理了。

?--插入一條已存在的記錄，會報錯insert into dept values(10,'研發部','QINGDAO');--首先定義錯誤編號"00001"的異常處理，然后向dept表中插入一條能夠違反"唯一約束條件"的記錄，最后在EXCEPTION代碼體中輸出異常提示信息set serveroutput ondeclareprimary_iterant exception;pragma exception_init(primary_iterant,-00001);begin/*向dept表中插入一條與已有主鍵值重復的記錄，以便引發異常*/insert into dept values(10,'研發部','青島');exception

（2）業務邏輯異常

在實際的應用中，程序開發人員可以根據具體的業務邏輯規則自定義一個異常。這樣，當用戶操作違反業務邏輯規則時，就會引發一個異常，從而中斷程序的正常執行，并轉到自定義的異常處理部分。

無論是預定義異常，還是錯誤編號異常，都是由Oracle系統判斷的錯誤，但業務邏輯異常是Oracle系統本身無法知道的，這樣就需要一個引發異常的機制，引發業務邏輯異常通常使用RAISE語句來實現。當引發一個異常時，控制就會轉到EXCEPTION異常處理部分執行異常處理語句。業務邏輯異常首先要在DECLARE部分使用EXCEPTION類型聲明一個異常變量，然后在BEGIN部分根據一定的業務邏輯規則執行RAISE語句，最后在EXCEPTION部分編寫異常處理語句

?--自定義一個異常變量,向dept表中插入數據時，若判斷loc字段的值為NULL，則使用RAISE語句引發異常，并將程序的執行流程轉入EXCEPTION部分中進行處理set serveroutput ondeclarenull_exception exception;       --聲明一個EXCEPTION類型的異常變量dept_row dept%rowtype;          --聲明ROWTYPE類型的變量dept_rowbegindept_row.deptno := 66;          --給部門編號變量賦值dept_row.dname := '公關部';     ?--給部門名稱變量賦值insert into deptvalues(dept_row.deptno,dept_row.dname,dept_row.loc);    --插入一條記錄if dept_row.loc is null then        --如果判斷loc變量的值為NULLraise null_exception;           --引發NULL異常，程序轉入EXCEPTION部分end if;exceptionwhen null_exception then            --當RAISE引發的異常是NULL_EXCEPTION時dbms_output.put_line('loc字段的值不許為null'); --輸出異常提示信息rollback;           --回滾插入的數據記錄end;

學習永無止境，讓我們共同進步！！