ORACLE PL/SQL編程詳解

SQL語(yǔ)言只是訪問(wèn)、操作數(shù)據(jù)庫(kù)的語(yǔ)言，并不是一種具有流程控制的程序設(shè)計(jì)語(yǔ)言，而只有程序設(shè)計(jì)語(yǔ)言才能用于應(yīng)用軟件的開(kāi)發(fā)。PL /SQL是一種高級(jí)數(shù)據(jù)庫(kù)程序設(shè)計(jì)語(yǔ)言，該語(yǔ)言專門用于在各種環(huán)境下對(duì)ORACLE數(shù)據(jù)庫(kù)進(jìn)行訪問(wèn)。由于該語(yǔ)言集成于數(shù)據(jù)庫(kù)服務(wù)器中，所以PL/SQL代碼可以對(duì)數(shù)據(jù)進(jìn)行快速高效的處理。除此之外，可以在ORACLE數(shù)據(jù)庫(kù)的某些客戶端工具中，使用PL/SQL語(yǔ)言也是該語(yǔ)言的一個(gè)特點(diǎn)。本章的主要內(nèi)容是討論引入PL/SQL語(yǔ)言的必要性和該語(yǔ)言的主要特點(diǎn)，以及了解PL/SQL語(yǔ)言的重要性和數(shù)據(jù)庫(kù)版本問(wèn)題。還要介紹一些貫穿全書(shū)的更詳細(xì)的高級(jí)概念，并在本章的最后就我們?cè)诒緯?shū)案例中使用的數(shù)據(jù)庫(kù)表的若干約定做一說(shuō)明。

1.1 SQL與PL/SQL

1.1.1 什么是PL/SQL?

PL/SQL是 Procedure Language & Structured Query Language 的縮寫。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)標(biāo)準(zhǔn)的產(chǎn)品。PL/SQL是對(duì)SQL語(yǔ)言存儲(chǔ)過(guò)程語(yǔ)言的擴(kuò)展。從ORACLE6以后，ORACLE的RDBMS附帶了PL/SQL。它現(xiàn)在已經(jīng)成為一種過(guò)程處理語(yǔ)言，簡(jiǎn)稱PL/SQL。目前的PL/SQL包括兩部分，一部分是數(shù)據(jù)庫(kù)引擎部分；另一部分是可嵌入到許多產(chǎn)品（如C語(yǔ)言，JAVA語(yǔ)言等）工具中的獨(dú)立引擎?？梢詫⑦@兩部分稱為：數(shù)據(jù)庫(kù)PL/SQL和工具PL/SQL。兩者的編程非常相似。都具有編程結(jié)構(gòu)、語(yǔ)法和邏輯機(jī)制。工具PL/SQL另外還增加了用于支持工具（如ORACLE Forms）的句法，如：在窗體上設(shè)置按鈕等。本章主要介紹數(shù)據(jù)庫(kù)PL/SQL內(nèi)容。

1.2 PL/SQL的優(yōu)點(diǎn)或特征

1.2.1 有利于客戶/服務(wù)器環(huán)境應(yīng)用的運(yùn)行

對(duì)于客戶/服務(wù)器環(huán)境來(lái)說(shuō)，真正的瓶頸是網(wǎng)絡(luò)上。無(wú)論網(wǎng)絡(luò)多快，只要客戶端與服務(wù)器進(jìn)行大量的數(shù)據(jù)交換。應(yīng)用運(yùn)行的效率自然就回受到影響。如果使用PL/SQL進(jìn)行編程，將這種具有大量數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端來(lái)執(zhí)行。自然就省去了數(shù)據(jù)在網(wǎng)上的傳輸時(shí)間。

1.2.2 適合于客戶環(huán)境

PL/SQL由于分為數(shù)據(jù)庫(kù)PL/SQL部分和工具PL/SQL。對(duì)于客戶端來(lái)說(shuō)，PL/SQL可以嵌套到相應(yīng)的工具中，客戶端程序可以執(zhí)行本地包含PL/SQL部分，也可以向服務(wù)發(fā)SQL命令或激活服務(wù)器端的PL/SQL程序運(yùn)行。

1.2.3 過(guò)程化

PL/SQL是Oracle在標(biāo)準(zhǔn)SQL上的過(guò)程性擴(kuò)展，不僅允許在PL/SQL程序內(nèi)嵌入SQL語(yǔ)句，而且允許使用各種類型的條件分支語(yǔ)句和循環(huán)語(yǔ)句，可以多個(gè)應(yīng)用程序之間共享其解決方案。

1.2.4 模塊化

PL/SQL程序結(jié)構(gòu)是一種描述性很強(qiáng)、界限分明的塊結(jié)構(gòu)、嵌套塊結(jié)構(gòu)，被分成單獨(dú)的過(guò)程、函數(shù)、觸發(fā)器，且可以把它們組合為程序包，提高程序的模塊化能力。

1.2.5 運(yùn)行錯(cuò)誤的可處理性

使用PL/SQL提供的異常處理（EXCEPTION），開(kāi)發(fā)人員可集中處理各種ORACLE錯(cuò)誤和PL/SQL錯(cuò)誤，或處理系統(tǒng)錯(cuò)誤與自定義錯(cuò)誤，以增強(qiáng)應(yīng)用程序的健壯性。

1.2.6 提供大量?jī)?nèi)置程序包

ORACLE提供了大量的內(nèi)置程序包。通過(guò)這些程序包能夠?qū)崿F(xiàn)DBS的一些低層操作、高級(jí)功能，不論對(duì)DBA還是應(yīng)用開(kāi)發(fā)人員都具有重要作用。

當(dāng)然還有其它的一些優(yōu)點(diǎn)如：更好的性能、可移植性和兼容性、可維護(hù)性、易用性與快速性等。
1.3 PL/SQL 可用的SQL語(yǔ)句

PL/SQL是ORACLE系統(tǒng)的核心語(yǔ)言，現(xiàn)在ORACLE的許多部件都是由PL/SQL寫成。在PL/SQL中可以使用的SQL語(yǔ)句有：

INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。

提示：在 PL/SQL中只能用 SQL語(yǔ)句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL中使用DDL(如CREATE table 等)的話，只能以動(dòng)態(tài)的方式來(lái)使用。

l ORACLE 的 PL/SQL 組件在對(duì) PL/SQL 程序進(jìn)行解釋時(shí)，同時(shí)對(duì)在其所使用的表名、列名及數(shù)據(jù)類型進(jìn)行檢查。
l PL/SQL 可以在SQL*PLUS 中使用。
l PL/SQL 可以在高級(jí)語(yǔ)言中使用。
l PL/SQL可以在ORACLE的開(kāi)發(fā)工具中使用(如：SQL Developer或Procedure Builder等)。
l 其它開(kāi)發(fā)工具也可以調(diào)用PL/SQL編寫的過(guò)程和函數(shù)，如Power Builder 等都可以調(diào)用服務(wù)器端的PL/SQL過(guò)程。
1.4 運(yùn)行PL/SQL程序

PL/SQL程序的運(yùn)行是通過(guò)ORACLE中的一個(gè)引擎來(lái)進(jìn)行的。這個(gè)引擎可能在ORACLE的服務(wù)器端，也可能在 ORACLE 應(yīng)用開(kāi)發(fā)的客戶端。引擎執(zhí)行PL/SQL中的過(guò)程性語(yǔ)句，然后將SQL語(yǔ)句發(fā)送給數(shù)據(jù)庫(kù)服務(wù)器來(lái)執(zhí)行。再將結(jié)果返回給執(zhí)行端。

2.1 PL/SQL塊

　　PL/SQL程序由三個(gè)塊組成，即聲明部分、執(zhí)行部分、異常處理部分。

　　PL/SQL塊的結(jié)構(gòu)如下：
DECLARE
--聲明部分: 在此聲明PL/SQL用到的變量,類型及游標(biāo)，以及局部的存儲(chǔ)過(guò)程和函數(shù)
BEGIN
-- 執(zhí)行部分: 過(guò)程及SQL 語(yǔ)句 , 即程序的主要部分
EXCEPTION
-- 執(zhí)行異常部分: 錯(cuò)誤處理
END;
其中：執(zhí)行部分不能省略。

　　PL/SQL塊可以分為三類：

　　1. 無(wú)名塊或匿名塊（anonymous）：動(dòng)態(tài)構(gòu)造，只能執(zhí)行一次，可調(diào)用其它程序，但不能被其它程序調(diào)用。

　　2. 命名塊（named）：是帶有名稱的匿名塊，這個(gè)名稱就是標(biāo)簽。

　　3. 子程序（subprogram）：存儲(chǔ)在數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程、函數(shù)等。當(dāng)在數(shù)據(jù)庫(kù)上建立好后可以在其它程序中調(diào)用它們。

　　4. 觸發(fā)器（Trigger）：當(dāng)數(shù)據(jù)庫(kù)發(fā)生操作時(shí)，會(huì)觸發(fā)一些事件，從而自動(dòng)執(zhí)行相應(yīng)的程序。

　　5. 程序包（package）：存儲(chǔ)在數(shù)據(jù)庫(kù)中的一組子程序、變量定義。在包中的子程序可以被其它程序包或子程序調(diào)用。但如果聲明的是局部子程序，則只能在定義該局部子程序的塊中調(diào)用該局部子程序。
　　

2.2 PL/SQL結(jié)構(gòu)

l PL/SQL塊中可以包含子塊；

l 子塊可以位于 PL/SQL中的任何部分；

l 子塊也即PL/SQL中的一條命令；
2.3 標(biāo)識(shí)符

　　PL/SQL程序設(shè)計(jì)中的標(biāo)識(shí)符定義與SQL 的標(biāo)識(shí)符定義的要求相同。要求和限制有：

l 標(biāo)識(shí)符名不能超過(guò)30字符；

l 第一個(gè)字符必須為字母；

l 不分大小寫；

l 不能用’-‘(減號(hào));

l 不能是SQL保留字。

　　提示: 一般不要把變量名聲明與表中字段名完全一樣,如果這樣可能得到不正確的結(jié)果.

　　例如：下面的例子將會(huì)刪除所有的紀(jì)錄，而不是’EricHu’的記錄；
DECLARE
ename varchar2(20) :='EricHu';
BEGIN
DELETE FROM scott.emp WHERE ename=ename;
END;
　　變量命名在PL/SQL中有特別的講究，建議在系統(tǒng)的設(shè)計(jì)階段就要求所有編程人員共同遵守一定的要求，使得整個(gè)系統(tǒng)的文檔在規(guī)范上達(dá)到要求。下面是建議的命名方法：

2.4 PL/SQL 變量類型

　　在前面的介紹中，有系統(tǒng)的數(shù)據(jù)類型，也可以自定義數(shù)據(jù)類型。下表給出ORACLE類型和PL/SQL中的變量類型的合法使用列表：

2.4.1 變量類型
四類數(shù)據(jù)類型
標(biāo)量類型（SCALAR，或稱基本數(shù)據(jù)類型）：用于保存單個(gè)值，例如字符串，數(shù)字，日期，布爾；
復(fù)合類型（COMPOSITE）：復(fù)合類型可以在內(nèi)部存放多種數(shù)值，類似于多個(gè)變量的集合，例如記錄類型，嵌套表，索引表，可變數(shù)組等；
引用類型（REFERENCE）：用于指向另一個(gè)不同對(duì)象，例如REF CURSOR,REF；
LOB類型：大數(shù)據(jù)類型，最多可以存儲(chǔ)4GB的信息，主要用來(lái)處理二進(jìn)制數(shù)據(jù)；
標(biāo)量類型
標(biāo)量類型也被稱為基本數(shù)據(jù)類型
常見(jiàn)標(biāo)量類型


數(shù)值型
NUMBER數(shù)據(jù)類型
采用十進(jìn)制類型，需將十進(jìn)制轉(zhuǎn)為二進(jìn)制進(jìn)行計(jì)算
定義整型：NUMBER(n);
定義浮點(diǎn)型數(shù)據(jù)：NUMBER(m,n)
實(shí)例1：定義NUMBER變量
SQL> set serveroutput on
SQL> DECLARE
2 v_x NUMBER(3) ; -- 最多只能為3位數(shù)字
3 v_y NUMBER(5,2) ; -- 3位整數(shù)，2位小數(shù)
4 BEGIN
5 v_x := -500 ;
6 v_y := 999.88 ;
7 DBMS_OUTPUT.put_line('v_x = ' || v_x) ;
8 DBMS_OUTPUT.put_line('v_y = ' || v_y) ;
9 DBMS_OUTPUT.put_line('加法運(yùn)算：' || (v_x + v_y)) ; -- 整數(shù) + 浮點(diǎn)數(shù) = 浮點(diǎn)數(shù)
10 END ;
11 /
v_x = -500
v_y = 999.88
加法運(yùn)算：499.88

PL/SQL procedure successfully completed.
BINARY_INTEGER與PLS_INTEGER
說(shuō)明：
兩者具有相同的范圍長(zhǎng)度。與NUMBER比較，占用的范圍更??；
采用二進(jìn)制補(bǔ)碼存儲(chǔ)，運(yùn)算性能比NUMBER高；
兩者區(qū)別：
BINARY_INTEGER操作的數(shù)據(jù)大于其數(shù)據(jù)范圍時(shí)，會(huì)自動(dòng)轉(zhuǎn)換為NUMBER型進(jìn)行保存；
PLS_INTEGER操作的數(shù)據(jù)大于范圍時(shí)，會(huì)拋出異常信息
示例1：驗(yàn)證PLS_INTEGER操作
SQL> DECLARE
2 v_pls1 PLS_INTEGER := 100 ;
3 v_pls2 PLS_INTEGER := 200 ;
4 v_result PLS_INTEGER ;
5 BEGIN
6 v_result := v_pls1 + v_pls2 ;
7 DBMS_OUTPUT.put_line('計(jì)算結(jié)果：' || v_result) ;
8 END ;
9 /
計(jì)算結(jié)果：300

PL/SQL procedure successfully completed.
BINARY_FLOAT與BINARY_DOUBLE
兩者比NUMBER節(jié)約空間，同時(shí)范圍更大，采用二進(jìn)制存儲(chǔ)數(shù)據(jù)；
示例1：驗(yàn)證BINARY_DOUBLE操作
SQL> DECLARE
2 v_float BINARY_FLOAT := 8909.51F ;
3 v_double BINARY_DOUBLE := 8909.51D ;
4 BEGIN
5 v_float := v_float + 1000.16 ;
6 v_double := v_double + 1000.16 ;
7 DBMS_OUTPUT.put_line('BINARY_FLOAT變量?jī)?nèi)容：' || v_float) ;
8 DBMS_OUTPUT.put_line('BINARY_DOUBLE變量?jī)?nèi)容：' || v_double) ;
9 END ;
10 /
BINARY_FLOAT變量?jī)?nèi)容：9.90966992E+003
BINARY_DOUBLE變量?jī)?nèi)容：9.9096700000000001E+003

PL/SQL procedure successfully completed.
兩者定義的常量：這些常量只能在PL/SQL中使用。這些常量分別表示BINARY_FLOAT與BINARY_DOUBLE的數(shù)據(jù)范圍，同時(shí)針對(duì)非數(shù)字與超過(guò)其類型最大值的數(shù)據(jù)標(biāo)記

示例2：觀察表示范圍的常量?jī)?nèi)容
SQL> DECLARE
2 BEGIN
3 DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MIN_NORMAL = ' || BINARY_FLOAT_MIN_NORMAL) ;
4 DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MAX_NORMAL = ' || BINARY_FLOAT_MAX_NORMAL) ;
5 DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MIN_SUBNORMAL = ' || BINARY_FLOAT_MIN_SUBNORMAL) ;
6 DBMS_OUTPUT.put_line('1、BINARY_FLOAT_MAX_SUBNORMAL = ' || BINARY_FLOAT_MAX_SUBNORMAL) ;
7 DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MIN_NORMAL = ' || BINARY_DOUBLE_MIN_NORMAL) ;
8 DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MAX_NORMAL = ' || BINARY_DOUBLE_MAX_NORMAL) ;
9 DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MIN_SUBNORMAL = ' || BINARY_DOUBLE_MIN_SUBNORMAL) ;
10 DBMS_OUTPUT.put_line('2、BINARY_DOUBLE_MAX_SUBNORMAL = ' || BINARY_DOUBLE_MAX_SUBNORMAL) ;
11 END ;
12 /
1、BINARY_FLOAT_MIN_NORMAL = 1.17549435E-038
1、BINARY_FLOAT_MAX_NORMAL = 3.40282347E+038
1、BINARY_FLOAT_MIN_SUBNORMAL = 1.40129846E-045
1、BINARY_FLOAT_MAX_SUBNORMAL = 1.17549421E-038
2、BINARY_DOUBLE_MIN_NORMAL = 2.2250738585072014E-308
2、BINARY_DOUBLE_MAX_NORMAL = 1.7976931348623157E+308
2、BINARY_DOUBLE_MIN_SUBNORMAL = 4.9406564584124654E-324
2、BINARY_DOUBLE_MAX_SUBNORMAL = 2.2250738585072009E-308

PL/SQL procedure successfully completed.
示例3：超過(guò)范圍的計(jì)算
SQL> DECLARE
2 BEGIN
3 DBMS_OUTPUT.put_line('超過(guò)范圍計(jì)算的結(jié)果：' ||
4 BINARY_DOUBLE_MAX_NORMAL * BINARY_DOUBLE_MAX_NORMAL) ;
5 DBMS_OUTPUT.put_line('超過(guò)范圍計(jì)算的結(jié)果：' ||
6 BINARY_DOUBLE_MAX_NORMAL / 0) ;
7 END ;
8 /
超過(guò)范圍計(jì)算的結(jié)果：Inf
超過(guò)范圍計(jì)算的結(jié)果：Inf

PL/SQL procedure successfully completed.
字符型
CHAR與VARCHAR2
說(shuō)明：
CHAR采用定長(zhǎng)方式保存字符串。如果用戶設(shè)置的內(nèi)容不足其定義長(zhǎng)度，則會(huì)自動(dòng)補(bǔ)充空格；
VARCHAR2是可變字符串。如果設(shè)置的內(nèi)容不足其長(zhǎng)度，也不會(huì)為其補(bǔ)充內(nèi)容；
示例1：觀察CHAR和VARCHAR2的區(qū)別
SQL> DECLARE
2 v_info_char CHAR(10) ;
3 v_info_varchar VARCHAR2(10) ;
4 BEGIN
5 v_info_char := 'MLDN' ; -- 長(zhǎng)度不足10個(gè)
6 v_info_varchar := 'java' ; -- 長(zhǎng)度不足10個(gè)
7 DBMS_OUTPUT.put_line('v_info_char內(nèi)容長(zhǎng)度：' || LENGTH(v_info_char)) ;
8 DBMS_OUTPUT.put_line('v_info_varchar內(nèi)容長(zhǎng)度：' || LENGTH(v_info_varchar)) ;
9 END ;
10 /
v_info_char內(nèi)容長(zhǎng)度：10
v_info_varchar內(nèi)容長(zhǎng)度：4

PL/SQL procedure successfully completed.

NCHAR和NVARCHAR2
兩者的特性與CHAR，VARCHAR2一樣。區(qū)別在于它們保存的數(shù)據(jù)為UNICODE編碼，中文與英文都會(huì)變?yōu)槭M(jìn)制編碼保存；
示例1：驗(yàn)證NCHAR和NVARCHAR2
SQL> DECLARE
2 v_info_nchar NCHAR(10) ;
3 v_info_nvarchar NVARCHAR2(10) ;
4 BEGIN
5 v_info_nchar := 'CSDN' ; -- 長(zhǎng)度不足10個(gè)
6 v_info_nvarchar := '高端培訓(xùn)' ; -- 長(zhǎng)度不足10個(gè)
7 DBMS_OUTPUT.put_line('v_info_nchar內(nèi)容長(zhǎng)度：' || LENGTH(v_info_nchar)) ;
8 DBMS_OUTPUT.put_line('v_info_nvarchar內(nèi)容長(zhǎng)度：' || LENGTH(v_info_nvarchar)) ;
9 END ;
10 /
v_info_nchar內(nèi)容長(zhǎng)度：10
v_info_nvarchar內(nèi)容長(zhǎng)度：8

PL/SQL procedure successfully completed.
LONG與LONG RAW
兩者用于向后兼容；
LONG說(shuō)明：
使用LONG的地方都會(huì)使用CLOB或NCLOB；
LONG用于存儲(chǔ)字符流；
可以使用"UTL_RAW.cast_to_varchar2(RAW數(shù)據(jù))"函數(shù)，將RAW轉(zhuǎn)為字符串
LONG RAW說(shuō)明：
使用LONG RAW的地方都替換為BLOB或BILE；
LONG RAW用于存儲(chǔ)二進(jìn)制數(shù)據(jù)流
為L(zhǎng)ONG RAW變量設(shè)置內(nèi)容，要使用"UTL_RAW.cast_to_raw(字符串)"進(jìn)行轉(zhuǎn)換；
示例1：使用LONG和LONG RAW操作
SQL> set serveroutput on
SQL> DECLARE
2 v_info_long LONG ;
3 v_info_longraw LONG RAW ;
4 BEGIN
5 v_info_long := 'CSDN' ; -- 直接設(shè)置字符串
6 v_info_longraw := UTL_RAW.cast_to_raw('高端培訓(xùn)') ; -- 將字符串變?yōu)镽AW
7 DBMS_OUTPUT.put_line('v_info_long內(nèi)容：' || v_info_long) ;
8 DBMS_OUTPUT.put_line('v_info_longraw內(nèi)容：' || UTL_RAW.cast_to_varchar2(v_info_longraw)) ;
9 END ;
10 /
v_info_long內(nèi)容：CSDN
v_info_longraw內(nèi)容：高端培訓(xùn)

PL/SQL procedure successfully completed.
OWID與UROWID
ROWID表示的是一條數(shù)據(jù)的物理行地址，由18個(gè)字符組合而成，與ROWID偽列功能相同；
UROWID具備ROWID的功能，還增加了一個(gè)邏輯行地址，在PL/SQL中應(yīng)將所有的ROWID交給UROWID管理；
示例1：使用ROWID及UROWID
SQL> create synonym emp for scott.emp@CLONEPDB_PLUG;

Synonym created.

SQL> DECLARE
2 v_emp_rowid ROWID ;
3 v_emp_urowid UROWID ;
4 BEGIN
5 SELECT ROWID INTO v_emp_rowid FROM emp WHERE empno=7369 ; -- 取得ROWID
6 SELECT ROWID INTO v_emp_urowid FROM emp WHERE empno=7369 ; -- 取得ROWID
7 DBMS_OUTPUT.put_line('7369雇員的ROWID = ' || v_emp_rowid) ;
8 DBMS_OUTPUT.put_line('7369雇員的UROWID = ' || v_emp_urowid) ;
9 END ;
10 /
7369雇員的ROWID = AAAR7bAALAAAACTAAA
7369雇員的UROWID = AAAR7bAALAAAACTAAA

PL/SQL procedure successfully completed.
日期型
DATE數(shù)據(jù)類型
用來(lái)存儲(chǔ)日期時(shí)間數(shù)據(jù)；
可通過(guò)SYSDATE或SYSTIMESTAMP兩個(gè)偽列來(lái)獲取當(dāng)前的日期時(shí)間；
主要字段索引：

示例1：定義DATE型變量
SQL> DECLARE
2 v_date1 date := SYSDATE;
3 v_date2 date := systimestamp;
4 v_date3 date :=TO_DATE('2015-01-01','YYYY-MM-DD');
5 BEGIN
6 DBMS_OUTPUT.put_line('日期數(shù)據(jù)：' || TO_CHAR(v_date1,'yyyy-mm-dd hh34:mi:ss'));
7 DBMS_OUTPUT.put_line('日期數(shù)據(jù)：' || TO_CHAR(v_date2,'yyyy-mm-dd hh34:mi:ss'));
8 DBMS_OUTPUT.put_line('日期數(shù)據(jù)：' || TO_CHAR(v_date3,'yyyy-mm-dd hh34:mi:ss'));
9 END;
10 /
日期數(shù)據(jù)：2017-12-19 15:22:01
日期數(shù)據(jù)：2017-12-19 15:22:01
日期數(shù)據(jù)：2015-01-01 00:00:00

PL/SQL procedure successfully completed.
TIMESTAMP數(shù)據(jù)類型
該類型與DATE的區(qū)別在于，可以提供更為準(zhǔn)確的時(shí)間。但是要使用SYSTIMESTAMP偽列來(lái)為其賦值；
如果只是使用SYSDATE，那么TIMESTAMP與DATE沒(méi)有任何區(qū)別；
示例1：定義TIMESTAMP型變量
SQL> DECLARE
2 v_timestamp1 TIMESTAMP := SYSDATE;
3 v_timestamp2 TIMESTAMP := SYSTIMESTAMP;
4 v_timestamp3 TIMESTAMP := to_timestamp('2011-12-15 10:40:10.345', 'yyyy-MM-dd HH24:MI:ss.ff');
5 BEGIN
6 DBMS_OUTPUT.put_line('日期數(shù)據(jù)：' || v_timestamp1) ;
7 DBMS_OUTPUT.put_line('日期數(shù)據(jù)：' || v_timestamp2) ;
8 DBMS_OUTPUT.put_line('日期數(shù)據(jù)：' || v_timestamp3) ;
9 END ;
10 /
日期數(shù)據(jù)：19-DEC-17 03.32.31.000000 PM
日期數(shù)據(jù)：19-DEC-17 03.32.31.988000 PM
日期數(shù)據(jù)：15-DEC-11 10.40.10.345000 AM

PL/SQL procedure successfully completed.
TIMESTAMP的兩個(gè)擴(kuò)充子類型：
TIMESTAMP WITH TIME ZONE：包含與格林威治時(shí)間的時(shí)區(qū)偏移量
SQL> DECLARE
2 v_timestamp TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP ;
3 BEGIN
4 DBMS_OUTPUT.put_line(v_timestamp) ;
5 END ;
6 /
19-DEC-17 03.36.53.909000 PM +08:00

PL/SQL procedure successfully completed.
TIMESTAMP WITH LOCAL TIME ZONE：不管是何種時(shí)區(qū)的數(shù)據(jù)，都使用當(dāng)前數(shù)據(jù)庫(kù)的時(shí)區(qū)；
SQL> DECLARE
2 v_timestamp TIMESTAMP WITH LOCAL TIME ZONE := SYSTIMESTAMP ;
3 BEGIN
4 DBMS_OUTPUT.put_line(v_timestamp) ;
5 END ;
6 /
19-DEC-17 03.37.32.894000 PM

PL/SQL procedure successfully completed.
NTERVAL數(shù)據(jù)類型
該類型可以保存兩個(gè)時(shí)間戳之間的時(shí)間間隔，此類型分為兩個(gè)子類型：
INTERVAL YEAR[(年的精度)] TO MONTHS:保存的和操作年與月之間的時(shí)間間隔，用戶可以設(shè)置年的數(shù)據(jù)精度。默認(rèn)值為2；
賦值格式：'年-月'
INTERVAL DAY[(天的精度)] TO SECEND[(秒的精度)]:保存和操作天，時(shí)，分，秒之間的時(shí)間間隔，默認(rèn)為2。秒的默認(rèn)值為6；
賦值格式：'天時(shí)：分：秒.毫秒'
當(dāng)取得時(shí)間間隔后，可利用下列公式進(jìn)行計(jì)算：

示例1：定義INTERVAL YEAR TO MONTHS類型變量
SQL> DECLARE
2 v_interval INTERVAL YEAR(3) TO MONTH := INTERVAL '27-09' YEAR TO MONTH ;
3 BEGIN
4 DBMS_OUTPUT.put_line('時(shí)間間隔：' || v_interval) ;
5 DBMS_OUTPUT.put_line('當(dāng)前時(shí)間戳 + 時(shí)間間隔：' || (SYSTIMESTAMP + v_interval)) ;
6 DBMS_OUTPUT.put_line('當(dāng)前日期 + 時(shí)間間隔：' || (SYSDATE + v_interval)) ;
7 END ;
8 /
時(shí)間間隔：+027-09
當(dāng)前時(shí)間戳 + 時(shí)間間隔：19-SEP-45 03.43.24.742000000 PM +08:00
當(dāng)前日期 + 時(shí)間間隔：2045-09-19 15:43:24

PL/SQL procedure successfully completed.
示例2：定義INTERVAL DAY TO SECOND類型變量
SQL> DECLARE
2 v_interval INTERVAL DAY(6) TO SECOND (3) := INTERVAL '8 18:19:27.367123909' DAY TO SECOND;
3 BEGIN
4 DBMS_OUTPUT.put_line('時(shí)間間隔：' || v_interval) ;
5 DBMS_OUTPUT.put_line('當(dāng)前時(shí)間戳 + 時(shí)間間隔：' || (SYSTIMESTAMP + v_interval)) ;
6 DBMS_OUTPUT.put_line('當(dāng)前日期 + 時(shí)間間隔：' || (SYSDATE + v_interval)) ;
7 END ;
8 /
時(shí)間間隔：+000008 18:19:27.367
當(dāng)前時(shí)間戳 + 時(shí)間間隔：28-DEC-17 10.03.46.657000000 AM +08:00
當(dāng)前日期 + 時(shí)間間隔：2017-12-28 10:03:46

PL/SQL procedure successfully completed.
布爾型
該類型保存TRUE,FALSE,NULL
示例1：定義布爾型變量
SQL> DECLARE
2 v_flag BOOLEAN ;
3 BEGIN
4 v_flag := true ;
5 IF v_flag THEN
6 DBMS_OUTPUT.put_line('條件滿足。') ;
7 END IF ;
8 END ;
9 /
條件滿足。

PL/SQL procedure successfully completed.
子類型
在某一標(biāo)量類型的基礎(chǔ)上定義更多約束，從而創(chuàng)建一個(gè)新的類型，這種新類型被稱為子類型；
創(chuàng)建語(yǔ)法：
subtype 子類型名稱 is 父數(shù)據(jù)類型[(約束)] [not null];
在定義子類型的時(shí)候必須設(shè)置好父數(shù)據(jù)類型。父類型可以是Oracle的各種數(shù)據(jù)類型

示例1：定義NUMBER子類型
SQL> DECLARE
2 SUBTYPE score_subtype IS NUMBER(5,2) NOT NULL ;
3 v_score score_subtype := 99.35 ; --依據(jù)score_subtype類型為v_score變量賦值為99.35
4 BEGIN
5 DBMS_OUTPUT.put_line('成績(jī)?yōu)椋? || v_score) ;
6 END ;
7 /
成績(jī)?yōu)椋?9.35

PL/SQL procedure successfully completed.
示例2：定義VARCHAR2子類型
SQL> DECLARE
2 SUBTYPE string_subtype IS VARCHAR2(200) ;
3 v_company string_subtype ;--聲明一個(gè)子類型為 string_subtype的變量v_company
4 BEGIN
5 v_company := 'test' ;
6 DBMS_OUTPUT.put_line(v_company) ;
7 END ;
8 /
test

PL/SQL procedure successfully completed.

RETURNING子句
SQL> set serveroutput on;
SQL> DECLARE
2 Row_id ROWID;
3 info VARCHAR2(40);
4 BEGIN
5 INSERT INTO dept VALUES (90, '財(cái)務(wù)室', '?？?)
6 RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
7 INTO row_id, info;
8 DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
9 DBMS_OUTPUT.PUT_LINE(info);
10 END;
11 /
ROWID:AAAR7ZAALAAAACFAAA
財(cái)務(wù)室:90:?？?/p>

PL/SQL procedure successfully completed.
RETURNING子句用于檢索INSERT語(yǔ)句中所影響的數(shù)據(jù)行數(shù)，當(dāng)INSERT語(yǔ)句使用VALUES 子句插入數(shù)據(jù)時(shí)，RETURNING 字句還可將列表達(dá)式、ROWID和REF值返回到輸出變量中。在使用RETURNING 子句是應(yīng)注意以下幾點(diǎn)限制：

　　1．不能與DML語(yǔ)句和遠(yuǎn)程對(duì)象一起使用；

　　2．不能檢索LONG 類型信息；

　　3．當(dāng)通過(guò)視圖向基表中插入數(shù)據(jù)時(shí)，只能與單基表視圖一起使用。
例2. 修改一條記錄并顯示
SQL> DECLARE
2 Row_id ROWID;
3 info VARCHAR2(40);
4 BEGIN
5 UPDATE dept SET deptno=10 WHERE DNAME='ACCOUNTING'
6 RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
7 INTO row_id, info;
8 DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
9 DBMS_OUTPUT.PUT_LINE(info);
10 END;
11 /
ROWID:AAAR7ZAALAAAACDAAA
ACCOUNTING:10:NEW YORK

PL/SQL procedure successfully completed.
RETURNING子句用于檢索被修改行的信息。當(dāng)UPDATE語(yǔ)句修改單行數(shù)據(jù)時(shí)，RETURNING 子句可以檢索被修改行的ROWID和REF值，以及行中被修改列的列表達(dá)式，并可將他們存儲(chǔ)到PL/SQL變量或復(fù)合變量中；當(dāng)UPDATE語(yǔ)句修改多行數(shù)據(jù)時(shí)，RETURNING 子句可以將被修改行的ROWID和REF值，以及列表達(dá)式值返回到復(fù)合變量數(shù)組中。在UPDATE中使用RETURNING 子句的限制與INSERT語(yǔ)句中對(duì)RETURNING子句的限制相同。

　　例3.刪除一條記錄并顯示
SQL> DECLARE
2 Row_id ROWID;
3 info VARCHAR2(40);
4 BEGIN
5 DELETE dept WHERE DNAME='OPERATIONS'
6 RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
7 INTO row_id, info;
8 DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
9 DBMS_OUTPUT.PUT_LINE(info);
10 END;
11 /
ROWID:AAAR7ZAALAAAACDAAD
OPERATIONS:40:BOSTON

PL/SQL procedure successfully completed.
RETURNING子句用于檢索被刪除行的信息：當(dāng)DELETE語(yǔ)句刪除單行數(shù)據(jù)時(shí)，RETURNING 子句可以檢索被刪除行的ROWID和REF值，以及被刪除列的列表達(dá)式，并可將他們存儲(chǔ)到PL/SQL變量或復(fù)合變量中；當(dāng)DELETE語(yǔ)句刪除多行數(shù)據(jù)時(shí)，RETURNING 子句可以將被刪除行的ROWID和REF值，以及列表達(dá)式值返回到復(fù)合變量數(shù)組中。在DELETE中使用RETURNING 子句的限制與INSERT語(yǔ)句中對(duì)RETURNING子句的限制相同。

2.4.2 復(fù)合類型

ORACLE 在 PL/SQL 中除了提供象前面介紹的各種類型外,還提供一種稱為復(fù)合類型的類型---記錄和表. 

2.4.2.1 記錄類型

記錄類型類似于C語(yǔ)言中的結(jié)構(gòu)數(shù)據(jù)類型，它把邏輯相關(guān)的、分離的、基本數(shù)據(jù)類型的變量組成一個(gè)整體存儲(chǔ)起來(lái)，它必須包括至少一個(gè)標(biāo)量型或RECORD 數(shù)據(jù)類型的成員，稱作PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但邏輯相關(guān)的信息。在使用記錄數(shù)據(jù)類型變量時(shí)，需要先在聲明部分先定義記錄的組成、記錄的變量，然后在執(zhí)行部分引用該記錄變量本身或其中的成員。

　　定義記錄類型語(yǔ)法如下:

TYPE record_name IS RECORD(
v1 data_type1 [NOT NULL] [:= default_value ],
v2 data_type2 [NOT NULL] [:= default_value ],
......
vn data_typen [NOT NULL] [:= default_value ] );
例1 ：
SQL> DECLARE
2 TYPE test_rec IS RECORD(
3 Name VARCHAR2(30) NOT NULL := '我的自學(xué)',
4 Info VARCHAR2(100));
5 rec_book test_rec;
6 BEGIN
7 rec_book.Info :='Oracle PL/SQL編程;';
8 DBMS_OUTPUT.PUT_LINE(rec_book.Name||' ' ||rec_book.Info);
9 END;
10 /
我的自學(xué) Oracle PL/SQL編程;

PL/SQL procedure successfully completed.
可以用 SELECT語(yǔ)句對(duì)記錄變量進(jìn)行賦值,只要保證記錄字段與查詢結(jié)果列表中的字段相配即可。
例2 ：
SQL> conn hr/hr@pdbtest
Connected.
SQL> set serveroutput on
SQL> DECLARE
2 --定義與employees表中的這幾個(gè)列相同的記錄數(shù)據(jù)類型
3 TYPE RECORD_TYPE_EMPLOYEES IS RECORD(
4 f_name employees.first_name%TYPE,
5 h_date employees.hire_date%TYPE,
6 j_id employees.job_id%TYPE);
7 --聲明一個(gè)該記錄數(shù)據(jù)類型的記錄變量
8 v_emp_record RECORD_TYPE_EMPLOYEES;
9
10 BEGIN
11 SELECT first_name, hire_date, job_id INTO v_emp_record
12 FROM employees
13 WHERE employee_id = &emp_id;
14
15 DBMS_OUTPUT.PUT_LINE('雇員名稱：'||v_emp_record.f_name
16 ||' 雇傭日期：'||v_emp_record.h_date
17 ||' 崗位：'||v_emp_record.j_id);
18 END;
19 /
Enter value for emp_id: 206
old 13: WHERE employee_id = &emp_id;
new 13: WHERE employee_id = 206;
雇員名稱：William 雇傭日期：2002-06-07 00:00:00 崗位：AC_ACCOUNT

PL/SQL procedure successfully completed.
一個(gè)記錄類型的變量只能保存從數(shù)據(jù)庫(kù)中查詢出的一行記錄，若查詢出了多行記錄，就會(huì)出現(xiàn)錯(cuò)誤。
2.4.2.2 數(shù)組類型

數(shù)據(jù)是具有相同數(shù)據(jù)類型的一組成員的集合。每個(gè)成員都有一個(gè)唯一的下標(biāo)，它取決于成員在數(shù)組中的位置。在PL/SQL中，數(shù)組數(shù)據(jù)類型是VARRAY。

定義VARRY數(shù)據(jù)類型語(yǔ)法如下：

TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];
varray_name是VARRAY數(shù)據(jù)類型的名稱，size是下整數(shù)，表示可容納的成員的最大數(shù)量，每個(gè)成員的數(shù)據(jù)類型是element_type。默認(rèn)成員可以取空值，否則需要使用NOT NULL加以限制。對(duì)于VARRAY數(shù)據(jù)類型來(lái)說(shuō)，必須經(jīng)過(guò)三個(gè)步驟，分別是：定義、聲明、初始化。
例1 ：
SQL> DECLARE
2 --定義一個(gè)最多保存5個(gè)VARCHAR(25)數(shù)據(jù)類型成員的VARRAY數(shù)據(jù)類型
3 TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25);
4 --聲明一個(gè)該VARRAY數(shù)據(jù)類型的變量
5 v_reg_varray REG_VARRAY_TYPE;
6
7 BEGIN
8 --用構(gòu)造函數(shù)語(yǔ)法賦予初值
9 v_reg_varray := reg_varray_type
10 ('中國(guó)', '美國(guó)', '英國(guó)', '日本', '法國(guó)');
11
12 DBMS_OUTPUT.PUT_LINE('地區(qū)名稱：'||v_reg_varray(1)||'、'
13 ||v_reg_varray(2)||'、'
14 ||v_reg_varray(3)||'、'
15 ||v_reg_varray(4));
16 DBMS_OUTPUT.PUT_LINE('賦予初值NULL的第5個(gè)成員的值：'||v_reg_varray(5));
17 --用構(gòu)造函數(shù)語(yǔ)法賦予初值后就可以這樣對(duì)成員賦值
18 v_reg_varray(5) := '法國(guó)';
19 DBMS_OUTPUT.PUT_LINE('第5個(gè)成員的值：'||v_reg_varray(5));
20 END;
21 /
地區(qū)名稱：中國(guó)、美國(guó)、英國(guó)、日本
賦予初值NULL的第5個(gè)成員的值：法國(guó)
第5個(gè)成員的值：法國(guó)

PL/SQL procedure successfully completed.
2.4.2.3 使用%TYPE

定義一個(gè)變量，其數(shù)據(jù)類型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量(尤其是表的某一列)的數(shù)據(jù)類型相一致，這時(shí)可以使用%TYPE。

　　使用%TYPE特性的優(yōu)點(diǎn)在于：

　　l 所引用的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類型可以不必知道；

　　l 所引用的數(shù)據(jù)庫(kù)列的數(shù)據(jù)類型可以實(shí)時(shí)改變，容易保持一致，也不用修改PL/SQL程序。

　　例1：
SQL> conn scott/tiger@clonepdb_plug
Connected.
SQL> set serveroutput on
SQL> DECLARE
2 -- 用%TYPE 類型定義與表相配的字段
3 TYPE T_Record IS RECORD(
4 T_no emp.empno%TYPE,
5 T_name emp.ename%TYPE,
6 T_sal emp.sal%TYPE );
7 -- 聲明接收數(shù)據(jù)的變量
8 v_emp T_Record;
9 BEGIN
10 SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7782;
11 DBMS_OUTPUT.PUT_LINE
12 (TO_CHAR(v_emp.t_no)||' '||v_emp.t_name||' ' || TO_CHAR(v_emp.t_sal));
13 END;
14 /
7782 CLARK 2450

PL/SQL procedure successfully completed.
例2：
SQL> DECLARE
2 v_empno emp.empno%TYPE :=&no;
3 Type t_record is record (
4 v_name emp.ename%TYPE,
5 v_sal emp.sal%TYPE,
6 v_date emp.hiredate%TYPE);
7 Rec t_record;
8 BEGIN
9 SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno;
10 DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date);
11 END;
12 /
Enter value for no: 7782
old 2: v_empno emp.empno%TYPE :=&no;
new 2: v_empno emp.empno%TYPE :=7782;
CLARK---2450--1981-06-09 00:00:00

PL/SQL procedure successfully completed.
2.4.3 使用%ROWTYPE

PL/SQL 提供%ROWTYPE操作符, 返回一個(gè)記錄類型, 其數(shù)據(jù)類型和數(shù)據(jù)庫(kù)表的數(shù)據(jù)結(jié)構(gòu)相一致。

　　使用%ROWTYPE特性的優(yōu)點(diǎn)在于：

　　l 所引用的數(shù)據(jù)庫(kù)中列的個(gè)數(shù)和數(shù)據(jù)類型可以不必知道；

　　l 所引用的數(shù)據(jù)庫(kù)中列的個(gè)數(shù)和數(shù)據(jù)類型可以實(shí)時(shí)改變，容易保持一致，也不用修改PL/SQL程序。
例1：
SQL> DECLARE
2 v_empno emp.empno%TYPE :=&no;
3 rec emp%ROWTYPE;
4 BEGIN
5 SELECT * INTO rec FROM emp WHERE empno=v_empno;
6 DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工資:'||rec.sal||'工作時(shí)間:'||rec.hiredate);
7 END;
8 /
Enter value for no: 7782
old 2: v_empno emp.empno%TYPE :=&no;
new 2: v_empno emp.empno%TYPE :=7782;
姓名:CLARK工資:2450工作時(shí)間:1981-06-09 00:00:00

PL/SQL procedure successfully completed.
2.4.4 LOB類型

　　ORACLE提供了LOB (Large OBject)類型，用于存儲(chǔ)大的數(shù)據(jù)對(duì)象的類型。ORACLE目前主要支持BFILE, BLOB, CLOB 及 NCLOB 類型。

BFILE (Movie)

存放大的二進(jìn)制數(shù)據(jù)對(duì)象，這些數(shù)據(jù)文件不放在數(shù)據(jù)庫(kù)里，而是放在操作系統(tǒng)的某個(gè)目錄里，數(shù)據(jù)庫(kù)的表里只存放文件的目錄。 

BLOB(Photo)

存儲(chǔ)大的二進(jìn)制數(shù)據(jù)類型。變量存儲(chǔ)大的二進(jìn)制對(duì)象的位置。大二進(jìn)制對(duì)象的大小<=4GB。 

CLOB(Book)

存儲(chǔ)大的字符數(shù)據(jù)類型。每個(gè)變量存儲(chǔ)大字符對(duì)象的位置，該位置指到大字符數(shù)據(jù)塊。大字符對(duì)象的大小<=4GB。 

NCLOB

存儲(chǔ)大的NCHAR字符數(shù)據(jù)類型。每個(gè)變量存儲(chǔ)大字符對(duì)象的位置，該位置指到大字符數(shù)據(jù)塊。大字符對(duì)象的大小<=4GB。 

2.4.5 BIND 變量

　　綁定變量是在主機(jī)環(huán)境中定義的變量。在PL/SQL 程序中可以使用綁定變量作為他們將要使用的其它變量。為了在PL/SQL 環(huán)境中聲明綁定變量，使用命令VARIABLE。例如：

VARIABLE return_code NUMBER
VARIABLE return_msg VARCHAR2(20)
可以通過(guò)SQLPlus命令中的PRINT 顯示綁定變量的值。例如：
PRINT return_code
PRINT return_msg
例1：
SQL> VARIABLE result NUMBER;
SQL> BEGIN
2 SELECT (sal10)+nvl(comm, 0) INTO :result FROM emp
3 WHERE empno=7844;
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> --然后再執(zhí)行
SQL> PRINT result

RESULT

 15000

2.4.6 PL/SQL 表(TABLE)
定義記錄表（或索引表）數(shù)據(jù)類型。它與記錄類型相似，但它是對(duì)記錄類型的擴(kuò)展。它可以處理多行記錄，類似于高級(jí)中的二維數(shù)組，使得可以在PL/SQL中模仿數(shù)據(jù)庫(kù)中的表。

定義記錄表類型的語(yǔ)法如下：

TYPE table_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2];
　　關(guān)鍵字INDEX BY表示創(chuàng)建一個(gè)主鍵索引，以便引用記錄表變量中的特定行。

例1：
SQL> DECLARE
2 TYPE dept_table_type IS TABLE OF
3 dept%ROWTYPE INDEX BY BINARY_INTEGER;
4 my_dname_table dept_table_type;
5 v_count number(2) :=3;
6 BEGIN
7 FOR int IN 1 .. v_count LOOP
8 SELECT INTO my_dname_table(int) FROM dept WHERE deptno=int10;
9 END LOOP;
10 FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP
11 DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(int).deptno);
12 DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(int).dname);
13 END LOOP;
14 END;
15 /
Department number: 10
Department name: ACCOUNTING
Department number: 20
Department name: RESEARCH
Department number: 30
Department name: SALES

PL/SQL procedure successfully completed.
例2：按一維數(shù)組使用記錄表
SQL> DECLARE
2 --定義記錄表數(shù)據(jù)類型
3 TYPE reg_table_type IS TABLE OF varchar2(25)
4 INDEX BY BINARY_INTEGER;
5 --聲明記錄表數(shù)據(jù)類型的變量
6 v_reg_table REG_TABLE_TYPE;
7
8 BEGIN
9 v_reg_table(1) := 'Europe';
10 v_reg_table(2) := 'Americas';
11 v_reg_table(3) := 'Asia';
12 v_reg_table(4) := 'Middle East and Africa';
13 v_reg_table(5) := 'NULL';
14
15 DBMS_OUTPUT.PUT_LINE('地區(qū)名稱：'||v_reg_table (1)||'、'
16 ||v_reg_table (2)||'、'
17 ||v_reg_table (3)||'、'
18 ||v_reg_table (4));
19 DBMS_OUTPUT.PUT_LINE('第5個(gè)成員的值：'||v_reg_table(5));
20 END;
21 /
地區(qū)名稱：Europe、Americas、Asia、Middle East and Africa
第5個(gè)成員的值：NULL

PL/SQL procedure successfully completed
例3：按二維數(shù)組使用記錄表
SQL> conn hr/hr@pdbtest
Connected.
SQL> set serveroutput on
SQL> DECLARE
2 --定義記錄表數(shù)據(jù)類型
3 TYPE emp_table_type IS TABLE OF employees%ROWTYPE
4 INDEX BY BINARY_INTEGER;
5 --聲明記錄表數(shù)據(jù)類型的變量
6 v_emp_table EMP_TABLE_TYPE;
7 BEGIN
8 SELECT first_name, hire_date, job_id INTO
9 v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id
10 FROM employees WHERE employee_id = 177;
11 SELECT first_name, hire_date, job_id INTO
12 v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id
13 FROM employees WHERE employee_id = 178;
14
15 DBMS_OUTPUT.PUT_LINE('177雇員名稱：'||v_emp_table(1).first_name
16 ||' 雇傭日期：'||v_emp_table(1).hire_date
17 ||' 崗位：'||v_emp_table(1).job_id);
18 DBMS_OUTPUT.PUT_LINE('178雇員名稱：'||v_emp_table(2).first_name
19 ||' 雇傭日期：'||v_emp_table(2).hire_date
20 ||' 崗位：'||v_emp_table(2).job_id);
21 END;
22 /
177雇員名稱：Jack 雇傭日期：2006-04-23 00:00:00 崗位：SA_REP
178雇員名稱：Kimberely 雇傭日期：2007-05-24 00:00:00 崗位：SA_REP

PL/SQL procedure successfully completed.
2.5 運(yùn)算符和表達(dá)式(數(shù)據(jù)定義)

2.5.1 關(guān)系運(yùn)算符

2.5.2 一般運(yùn)算符

2.5.3 邏輯運(yùn)算符

2.6 變量賦值

　　在PL/SQL編程中，變量賦值是一個(gè)值得注意的地方，它的語(yǔ)法如下：

variable := expression ;
　　variable 是一個(gè)PL/SQL變量, expression 是一個(gè)PL/SQL 表達(dá)式.

2.6.1 字符及數(shù)字運(yùn)算特點(diǎn)

　　空值加數(shù)字仍是空值：NULL + < 數(shù)字> = NULL

　　空值加（連接）字符，結(jié)果為字符：NULL || <字符串> = < 字符串>

2.6.2 BOOLEAN 賦值

　　布爾值只有TRUE, FALSE及 NULL 三個(gè)值。
2.6.3 數(shù)據(jù)庫(kù)賦值

　　數(shù)據(jù)庫(kù)賦值是通過(guò) SELECT語(yǔ)句來(lái)完成的，每次執(zhí)行 SELECT語(yǔ)句就賦值一次，一般要求被賦值的變量與SELECT中的列名要一一對(duì)應(yīng)。如：

　　例1：
SQL> DECLARE
2 emp_id emp.empno%TYPE :=7782;
3 emp_name emp.ename%TYPE;
4 wages emp.sal%TYPE;
5 BEGIN
6 SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages
7 FROM emp WHERE empno = emp_id;
8 DBMS_OUTPUT.PUT_LINE(emp_name||'----'||to_char(wages));
9 END;
10 /
CLARK----2450

PL/SQL procedure successfully completed.
提示：不能將SELECT語(yǔ)句中的列賦值給布爾變量。
2.6.4 可轉(zhuǎn)換的類型賦值

l CHAR 轉(zhuǎn)換為 NUMBER：

使用 TO_NUMBER 函數(shù)來(lái)完成字符到數(shù)字的轉(zhuǎn)換，如：

v_total := TO_NUMBER('100.0') + sal;
l NUMBER 轉(zhuǎn)換為CHAR：

使用 TO_CHAR函數(shù)可以實(shí)現(xiàn)數(shù)字到字符的轉(zhuǎn)換，如：

v_comm := TO_CHAR('123.45') || '元' ;
l 字符轉(zhuǎn)換為日期：

使用 TO_DATE函數(shù)可以實(shí)現(xiàn) 字符到日期的轉(zhuǎn)換，如：

v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
l 日期轉(zhuǎn)換為字符

使用 TO_CHAR函數(shù)可以實(shí)現(xiàn)日期到字符的轉(zhuǎn)換，如：

v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh34:mi:ss') ;
2.7 變量作用范圍及可見(jiàn)性

　　在PL/SQL編程中，如果在變量的定義上沒(méi)有做到統(tǒng)一的話，可能會(huì)隱藏一些危險(xiǎn)的錯(cuò)誤，這樣的原因主要是變量的作用范圍所致。變量的作用域是指變量的有效作用范圍，與其它高級(jí)語(yǔ)言類似，PL/SQL的變量作用范圍特點(diǎn)是：

l 變量的作用范圍是在你所引用的程序單元（塊、子程序、包）內(nèi)。即從聲明變量開(kāi)始到該塊的結(jié)束。

l 一個(gè)變量（標(biāo)識(shí)）只能在你所引用的塊內(nèi)是可見(jiàn)的。

l 當(dāng)一個(gè)變量超出了作用范圍，PL/SQL引擎就釋放用來(lái)存放該變量的空間（因?yàn)樗赡懿挥昧耍?/p>

l 在子塊中重新定義該變量后，它的作用僅在該塊內(nèi)。
例1：
SQL> DECLARE
2 Emess char(80);
3 BEGIN
4
5 DECLARE
6 V1 NUMBER(4);
7 BEGIN
8 SELECT empno INTO v1 FROM emp WHERE LOWER(job)='president';
9 DBMS_OUTPUT.PUT_LINE(V1);
10 EXCEPTION
11 When TOO_MANY_ROWS THEN
12 DBMS_OUTPUT.PUT_LINE ('More than one president');
13 END;
14
15 DECLARE
16 V1 NUMBER(4);
17 BEGIN
18 SELECT empno INTO v1 FROM emp WHERE LOWER(job)='manager';
19 EXCEPTION
20 When TOO_MANY_ROWS THEN
21 DBMS_OUTPUT.PUT_LINE ('More than one manager');
22 END;
23
24 EXCEPTION
25 When others THEN
26 Emess:=substr(SQLERRM,1,80);
27 DBMS_OUTPUT.PUT_LINE(emess);
28 END;
29 /
7839
More than one manager

PL/SQL procedure successfully completed.
2.8 注釋

在PL/SQL里，可以使用兩種符號(hào)來(lái)寫注釋，即：

l 使用雙 ‘-‘ ( 減號(hào)) 加注釋

PL/SQL允許用 – 來(lái)寫注釋，它的作用范圍是只能在一行有效。如：

V_Sal NUMBER(12,2); -- 人員的工資變量。
l 使用 / / 來(lái)加一行或多行注釋，如：

//
/ 文件名： /
/ 作 者： /
/ 時(shí) 間： /
//
　　提示：被解釋后存放在數(shù)據(jù)庫(kù)中的 PL/SQL 程序，一般系統(tǒng)自動(dòng)將程序頭部的注釋去掉。只有在 PROCEDURE 之后的注釋才被保留；另外程序中的空行也自動(dòng)被去掉。