|
概述：
PL/SQL是ORACLE對(duì)標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)語(yǔ)言的擴(kuò)展，ORACLE公司已經(jīng)將PL/SQL整合到ORACLE
服務(wù)器和其他工具中了,，近幾年中更多的開發(fā)人員和DBA開始使用PL/SQL,，本文將講述PL/SQL基礎(chǔ)語(yǔ)法，結(jié)構(gòu)和組件,、以及如何設(shè)計(jì)并執(zhí)行一個(gè)PL/SQL程序,。文中最后感謝FineReport的收集與整理。FineReport---報(bào)表行業(yè)領(lǐng)跑者!
目錄
PL/SQL-------------------------------------------------------------3
優(yōu)點(diǎn)-----------------------------------------------------------3
PL/SQL塊結(jié)構(gòu)--------------------------------------------------
3
聲明部分-------------------------------------------------------4
執(zhí)行部分-------------------------------------------------------4
異常處理部分---------------------------------------------------4
PL/SQL塊語(yǔ)法---------------------------------------------------4
PL/SQL塊的命名和匿名-------------------------------------------4
函數(shù)-----------------------------------------------------------5
過(guò)程-----------------------------------------------------------5
包-------------------------------------------------------------5
觸發(fā)器---------------------------------------------------------5
變量和常量---------------------------------------------------------6
變量-----------------------------------------------------------6
常量-----------------------------------------------------------7
執(zhí)行部分-------------------------------------------------------10
執(zhí)行一個(gè)PL/SQL塊----------------------------------------------10
PL/SQL
從版本6開始PL/SQL就被可靠的整合到ORACLE中了,，一旦掌握PL/SQL的優(yōu)點(diǎn)以及其獨(dú)有的數(shù)據(jù)管理的便利性,，那么你很難想象ORACLE缺了PL/SQL的情形。PL/SQL
不是一個(gè)獨(dú)立的產(chǎn)品,，他是一個(gè)整合到ORACLE服務(wù)器和ORACLE工具中的技術(shù),，可以把PL/SQL看作ORACLE服務(wù)器內(nèi)的一個(gè)引擎，sql語(yǔ)句執(zhí)行者處理單個(gè)的sql語(yǔ)句,，PL/SQL引擎處理PL/SQL程序塊,。當(dāng)PL/SQL程序塊在PL/SQL引擎處理時(shí)，ORACLE服務(wù)器中的SQL語(yǔ)句執(zhí)行器處理pl/sql程序塊中的SQL語(yǔ)句,。
優(yōu)點(diǎn)：
. PL/SQL是一種高性能的基于事務(wù)處理的語(yǔ)言,，能運(yùn)行在任何ORACLE環(huán)境中，支持所有數(shù)據(jù)處理命令,。通過(guò)使用PL/SQL程序單元處理SQL的數(shù)據(jù)定義和數(shù)據(jù)控制元素,。
. PL/SQL支持所有SQL數(shù)據(jù)類型和所有SQL函數(shù)，同時(shí)支持所有ORACLE對(duì)象類型
. PL/SQL塊可以被命名和存儲(chǔ)在ORACLE服務(wù)器中,，同時(shí)也能被其他的PL/SQL程序或SQL命令調(diào)用,，任何客戶/服務(wù)器工具都能訪問(wèn)PL/SQL程序，具有很好的可重用性,。
. 可以使用ORACLE數(shù)據(jù)工具管理存儲(chǔ)在服務(wù)器中的PL/SQL程序的安全性,。可以授權(quán)或撤銷數(shù)據(jù)庫(kù)其他用戶訪問(wèn)PL/SQL程序的能力,。
. PL/SQL代碼可以使用任何ASCII文本編輯器編寫,，所以對(duì)任何ORACLE能夠運(yùn)行的操作系統(tǒng)都是非常便利的
. 對(duì)于SQL，ORACLE必須在同一時(shí)間處理每一條SQL語(yǔ)句,，在網(wǎng)絡(luò)環(huán)境下這就意味作每一個(gè)獨(dú)立的調(diào)用都必須被oracle服務(wù)器處理,，這就占用大量的服務(wù)器時(shí)間,，同時(shí)導(dǎo)致網(wǎng)絡(luò)擁擠。而PL/SQL是以整個(gè)語(yǔ)句塊發(fā)給服務(wù)器,，這就降低了網(wǎng)絡(luò)擁擠,。
PL/SQL塊結(jié)構(gòu)
PL/SQL是一種塊結(jié)構(gòu)的語(yǔ)言，組成PL/SQL程序的單元是邏輯塊,，一個(gè)PL/SQL 程序包含了一個(gè)或多個(gè)邏輯塊,，每個(gè)塊都可以劃分為三個(gè)部分。與其他語(yǔ)言相同,，變量在使用之前必須聲明,，PL/SQL提供了獨(dú)立的專門用于處理異常的部分，下面描述了PL/SQL塊的不同部分：
聲明部分(Declaration section)
聲明部分包含了變量和常量的數(shù)據(jù)類型和初始值,。這個(gè)部分是由關(guān)鍵字DECLARE開始,，如果不需要聲明變量或常量，那么可以忽略這一部分;需要說(shuō)明的是游標(biāo)的聲明也在這一部分,。
執(zhí)行部分(Executable section)
執(zhí)行部分是PL/SQL塊中的指令部分,，由關(guān)鍵字BEGIN開始，所有的可執(zhí)行語(yǔ)句都放在這一部分,，其他的PL/SQL塊也可以放在這一部分,。
異常處理部分(Exception section)
這一部分是可選的，在這一部分中處理異?；蝈e(cuò)誤,，對(duì)異常處理的詳細(xì)討論我們?cè)诤竺孢M(jìn)行。
PL/SQL塊語(yǔ)法
|
[DECLARE]
---declaration statements
BEGIN
---executable statements
[EXCEPTION]
---exception statements
END
|
PL/SQL塊中的每一條語(yǔ)句都必須以分號(hào)結(jié)束,，SQL語(yǔ)句可以使多行的,，但分號(hào)表示該語(yǔ)句的結(jié)束。一行中可以有多條SQL語(yǔ)句,，他們之間以分號(hào)分隔,。每一個(gè)PL/SQL塊由BEGIN或DECLARE開始，以END結(jié)束,。注釋由--標(biāo)示,。
PL/SQL塊的命名和匿名
PL/SQL程序塊可以是一個(gè)命名的程序塊也可以是一個(gè)匿名程序塊。匿名程序塊可以用在服務(wù)器端也可以用在客戶端,。
命名程序塊可以出現(xiàn)在其他PL/SQL程序塊的聲明部分，這方面比較明顯的是子程序,，子程序可以在執(zhí)行部分引用,，也可以在異常處理部分引用。
PL/SQL程序塊可背獨(dú)立編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中,，任何與數(shù)據(jù)庫(kù)相連接的應(yīng)用程序都可以訪問(wèn)這些存儲(chǔ)的PL/SQL程序塊,。ORACLE提供了四種類型的可存儲(chǔ)的程序：
. 函數(shù) . 過(guò)程
. 包 . 觸發(fā)器
函數(shù)
函數(shù)是命名了的,、存儲(chǔ)在數(shù)據(jù)庫(kù)中的PL/SQL程序塊。函數(shù)接受零個(gè)或多個(gè)輸入?yún)?shù),，有一個(gè)返回值,，返回值的數(shù)據(jù)類型在創(chuàng)建函數(shù)時(shí)定義。定義函數(shù)的語(yǔ)法如下：
|
FUNCTION name [{parameter[,parameter,...])]
RETURN datatypes IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers]
END [name]
|
過(guò)程
存儲(chǔ)過(guò)程是一個(gè)PL/SQL程序塊,，接受零個(gè)或多個(gè)參數(shù)作為輸入(INPUT)或輸出(OUTPUT),、或既作輸入又作輸出(INOUT)，與函數(shù)不同,，存儲(chǔ)過(guò)程沒(méi)有返回值,，存儲(chǔ)過(guò)程不能由SQL語(yǔ)句直接使用，只能通過(guò)EXECUT命令或PL/SQL程序塊內(nèi)部調(diào)用,，定義存儲(chǔ)過(guò)程的語(yǔ)法如下：
|
PROCEDURE name [(parameter[,parameter,...])]
IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers ]
END [name]
|
包(package)
包其實(shí)就是被組合在一起的相關(guān)對(duì)象的集合,，當(dāng)包中任何函數(shù)或存儲(chǔ)過(guò)程被調(diào)用，包就被加載入內(nèi)存中,，包中的任何函數(shù)或存儲(chǔ)過(guò)程的子程序訪問(wèn)速度將大大加快,。
包由兩個(gè)部分組成：規(guī)范和包主體(body),規(guī)范描述變量、常量,、游標(biāo),、和子程序，包體完全定義子程序和游標(biāo),。
觸發(fā)器(trigger)
觸發(fā)器與一個(gè)表或數(shù)據(jù)庫(kù)事件聯(lián)系在一起的,，當(dāng)一個(gè)觸發(fā)器事件發(fā)生時(shí)，定義在表上的觸發(fā)器被觸發(fā),。
變量和常量
變量存放在內(nèi)存中以獲得值,，能被PL/SQL塊引用。你可以把變量想象成一個(gè)可儲(chǔ)藏東西的容器,，容器內(nèi)的東西是可以改變的,。
變量
變量一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強(qiáng)壯的類型語(yǔ)言,，這就是說(shuō)在引用變量前必須首先聲明,，要在執(zhí)行或異常處理部分使用變量，那么變量必須首先在聲明部分進(jìn)行聲明,。
聲明變量的語(yǔ)法如下：
|
Variable_name [CONSTANT] databyte [NOT
NULL][:=|DEFAULT expression]
|
注意:可以在聲明變量的同時(shí)給變量強(qiáng)制性的加上NOT NULL約束條件,，此時(shí)變量在初始化時(shí)必須賦值。
給變量賦值有兩種方式：
. 直接給變量賦值
X:=200;
Y=Y+(X*20);
. 通過(guò)SQL SELECT
INTO 或FETCH INTO給變量賦值
SELECT SUM(SALARY),SUM(SALARY*0.1)
INTO TOTAL_SALARY,TATAL_COMMISSION
FROM EMPLOYEE
WHERE DEPT=10;
常量
常量與變量相似,，但常量的值在程序內(nèi)部不能改變,，常量的值在定義時(shí)賦予，,，他的聲明方式與變量相似,，但必須包括關(guān)鍵字CONSTANT,。常量和變量都可被定義為SQL和用戶定義的數(shù)據(jù)類型。
|
ZERO_VALUE CONSTANT NUMBER:=0;
|
這個(gè)語(yǔ)句定了一個(gè)名叫ZERO_VALUE,、數(shù)據(jù)類型是NUMBER,、值為0的常量。
標(biāo)量(scalar)數(shù)據(jù)類型
標(biāo)量(scalar)數(shù)據(jù)類型沒(méi)有內(nèi)部組件,，他們大致可分為以下四類：
. number
. character
. date/time
. boolean
表1顯示了數(shù)字?jǐn)?shù)據(jù)類型;表2顯示了字符數(shù)據(jù)類型;表3顯示了日期和布爾數(shù)據(jù)類型,。
表1 Scalar Types:Numeric
|
Datatype
|
Range
|
Subtypes
|
description
|
|
BINARY_INTEGER
|
-214748-2147483647
|
NATURAL
NATURAL
NPOSITIVE
POSITIVEN
SIGNTYPE
|
用于存儲(chǔ)單字節(jié)整數(shù)。
要求存儲(chǔ)長(zhǎng)度低于NUMBER值,。
用于限制范圍的子類型(SUBTYPE):
NATURAL:用于非負(fù)數(shù)
POSITIVE:只用于正數(shù)
NATURALN:只用于非負(fù)數(shù)和非NULL值
POSITIVEN:只用于正數(shù),，不能用于NULL值
SIGNTYPE:只有值:-1,、0或1.
|
|
NUMBER
|
1.0E-130-9.99E125
|
DEC
DECIMAL
DOUBLE
PRECISION
FLOAT
INTEGERIC
INT
NUMERIC
REAL
SMALLINT
|
存儲(chǔ)數(shù)字值,，包括整數(shù)和浮點(diǎn)數(shù),?？梢赃x擇精度和刻度方式,，語(yǔ)法：
number[（[,]）],。
缺省的精度是38,scale是0.
|
|
PLS_INTEGER
|
-2147483647-2147483647
|
|
與BINARY_INTEGER基本相同,，但采用機(jī)器運(yùn)算時(shí),，PLS_INTEGER提供更好的性能 ,。
|
表2 字符數(shù)據(jù)類型
|
datatype
|
rang
|
subtype
|
description
|
|
CHAR
|
最大長(zhǎng)度32767字節(jié)
|
CHARACTER
|
存儲(chǔ)定長(zhǎng)字符串,，如果長(zhǎng)度沒(méi)有確定，缺省是1
|
|
LONG
|
最大長(zhǎng)度2147483647字節(jié)
|
|
存儲(chǔ)可變長(zhǎng)度字符串
|
|
RAW
|
最大長(zhǎng)度32767字節(jié)
|
|
用于存儲(chǔ)二進(jìn)制數(shù)據(jù)和字節(jié)字符串,，當(dāng)在兩個(gè)數(shù)據(jù)庫(kù)之間進(jìn)行傳遞時(shí),，RAW數(shù)據(jù)不在字符集之間進(jìn)行轉(zhuǎn)換。
|
|
LONGRAW
|
最大長(zhǎng)度2147483647
|
|
與LONG數(shù)據(jù)類型相似,，同樣他也不能在字符集之間進(jìn)行轉(zhuǎn)換,。
|
|
ROWID
|
18個(gè)字節(jié)
|
|
與數(shù)據(jù)庫(kù)ROWID偽列類型相同，能夠存儲(chǔ)一個(gè)行標(biāo)示符,，可以將行標(biāo)示符看作數(shù)據(jù)庫(kù)中每一行的唯一鍵值,。
|
|
VARCHAR2
|
最大長(zhǎng)度32767字節(jié)
|
STRINGVARCHAR
|
與VARCHAR數(shù)據(jù)類型相似，存儲(chǔ)可變長(zhǎng)度的字符串,。聲明方法與VARCHAR相同
|
表3 DATE和BOOLEAN
|
datatype
|
range
|
description
|
|
BOOLEAN
|
TRUE/FALSE
|
存儲(chǔ)邏輯值TRUE或FALSE,無(wú)參數(shù)
|
|
DATE
|
01/01/4712 BC
|
存儲(chǔ)固定長(zhǎng)的日期和時(shí)間值,，日期值中包含時(shí)間
|
LOB數(shù)據(jù)類型
LOB(大對(duì)象,Large
object) 數(shù)據(jù)類型用于存儲(chǔ)類似圖像，聲音這樣的大型數(shù)據(jù)對(duì)象,，LOB數(shù)據(jù)對(duì)象可以是二進(jìn)制數(shù)據(jù)也可以是字符數(shù)據(jù),，其最大長(zhǎng)度不超過(guò)4G。LOB數(shù)據(jù)類型支持任意訪問(wèn)方式,，LONG只支持順序訪問(wèn)方式,。LOB存儲(chǔ)在一個(gè)單獨(dú)的位置上，同時(shí)一個(gè)"LOB定位符"(LOB locator)存儲(chǔ)在原始的表中,，該定位符是一個(gè)指向?qū)嶋H數(shù)據(jù)的指針,。在PL/SQL中操作LOB數(shù)據(jù)對(duì)象使用ORACLE提供的包DBMS_LOB.LOB數(shù)據(jù)類型可分為以下四類：
. BFILE
. BLOB
. CLOB
. NCLOB
操作符
與其他程序設(shè)計(jì)語(yǔ)言相同，PL/SQL有一系列操作符,。操作符分為下面幾類：
. 算術(shù)操作符 . 關(guān)系操作符
. 比較操作符 . 邏輯操作符
算術(shù)操作符如表4所示
|
operator
|
operation
|
|
+
|
加
|
|
-
|
減
|
|
/
|
除
|
|
*
|
乘
|
|
**
|
乘方
|
關(guān)系操作符主要用于條件判斷語(yǔ)句或用于where子串中,，關(guān)系操作符檢查條件和結(jié)果是否為true或false,表5是PL/SQL中的關(guān)系操作符
|
operator
|
operation
|
|
<
|
小于操作符
|
|
<=
|
小于或等于操作符
|
|
>
|
大于操作符
|
|
>=
|
大于或等于操作符
|
|
=
|
等于操作符
|
|
!=
|
不等于操作符
|
|
<>
|
不等于操作符
|
|
:=
|
賦值操作符
|
表6 顯示的是比較操作符
|
operator
|
operation
|
|
IS NULL
|
如果操作數(shù)為NULL返回TRUE
|
|
LIKE
|
比較字符串值
|
|
BETWEEN
|
驗(yàn)證值是否在范圍之內(nèi)
|
|
IN
|
驗(yàn)證操作數(shù)在設(shè)定的一系列值中
|
表7.8顯示的是邏輯操作符
|
operator
|
operation
|
|
AND
|
兩個(gè)條件都必須滿足
|
|
OR
|
只要滿足兩個(gè)條件中的一個(gè)
|
|
NOT
|
取反
|
執(zhí)行部分
執(zhí)行部分包含了所有的語(yǔ)句和表達(dá)式,執(zhí)行部分以關(guān)鍵字BEGIN開始，以關(guān)鍵字EXCEPTION結(jié)束,，如果EXCEPTION不存在,，那么將以關(guān)鍵字END結(jié)束。分號(hào)分隔每一條語(yǔ)句,，使用賦值操作符:=或SELECT INTO或FETCH
INTO給每個(gè)變量賦值,，執(zhí)行部分的錯(cuò)誤將在異常處理部分解決，在執(zhí)行部分中可以使用另一個(gè)PL/SQL程序塊,，這種程序塊被稱為嵌套塊
所有的SQL數(shù)據(jù)操作語(yǔ)句都可以用于執(zhí)行部分,，PL/SQL塊不能再屏幕上顯示SELECT語(yǔ)句的輸出。SELECT語(yǔ)句必須包括一個(gè)INTO子串或者是游標(biāo)的一部分,，執(zhí)行部分使用的變量和常量必須首先在聲明部分聲明,，執(zhí)行部分必須至少包括一條可執(zhí)行語(yǔ)句，NULL是一條合法的可執(zhí)行語(yǔ)句,，事物控制語(yǔ)句COMMIT和ROLLBACK可以在執(zhí)行部分使用,，數(shù)據(jù)定義語(yǔ)言(Data Definition
language)不能在執(zhí)行部分中使用，DDL語(yǔ)句與EXECUTE
IMMEDIATE一起使用或者是DBMS_SQL調(diào)用,。
執(zhí)行一個(gè)PL/SQL塊
SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入/來(lái)執(zhí)行,，如下面的例子所示：
|
declare
v_comm_percent constant
number:=10;
begin
update emp
set comm=sal*v_comm_percent
where deptno=10;
end
SQL> /
PL/SQL procedure successfully completed.
SQL>
|
命名的程序與匿名程序的執(zhí)行不同，執(zhí)行命名的程序塊必須使用execute關(guān)鍵字：
|
create or replace procedure
update_commission
(v_dept in number,v_pervent
in number default 10) is
begin
update emp
set comm=sal*v_percent
where deptno=v_dept;
end
SQL>/
Procedure created
SQL>execute update_commission(10,15);
PL/SQL procedure successfully completed.
SQL>
|
如果在另一個(gè)命名程序塊或匿名程序塊中執(zhí)行這個(gè)程序,，那么就不需要EXECUTE關(guān)進(jìn)字,。
|
declare
v_dept number;
begin
select a.deptno
into v_dept
from emp a
where job='PRESIDENT'
update_commission(v_dept);
end
SQL>/
PL/SQL procedure successfully completed
SQL>
|
文中最后再次感謝FineReport報(bào)表軟件的整理與支持
|
