一、什么是PL/SQL?
PL/SQL(PRocedural Language/SQL)是對SQL的擴充,它吸收了近年來編程語言的許多最高設(shè)計特點:如數(shù)據(jù)封裝性、信息隱蔽性、重載和例外處理等。 它答應(yīng)SQL的數(shù)據(jù)操縱語言和查詢語句包含在塊結(jié)構(gòu)(block_strUCtured)和代碼過程語言中,使PL/SQL成為一個功能強大的事務(wù)處理語言。
PL/SQL的優(yōu)點如下:
1.塊結(jié)構(gòu)(Block Structure)
PL/SQL是塊結(jié)構(gòu)語言,意味著程序可以分成若干邏輯塊,各自包含那個單元里要求的邏輯語言資源。可以對塊公布本地變量，在塊中使用這些變量,可在它們應(yīng)用的塊中非凡地處理錯誤條件(叫做Exceptions)
2. 流程控制
條件語句、循環(huán)和分支可用來控制程序的過程流，以決定是否或何時執(zhí)行SQL或其它行動。這些特點答應(yīng)Oracle工具(諸如SQL*Forms)去分組聯(lián)系在一起的命令組和通過PL/SQL控制它們的執(zhí)行。這樣可以避免置許多命令為單獨的觸發(fā)器步驟或者嵌套外部編程語言中的SQL語句的要求。
3. 可移植性
因為PL/SQL是ORACLE的主語言,故程序可移植至支持ORACLE和PL/SQL的任何操作系統(tǒng)平臺上。
4.集成性
PL/SQL在RDBMS(存貯過程、觸發(fā)器、包)和ORACLE工具中扮演了日益增長的中心角色。PL/SQL的變量和型與SQL的變量和型兼容(與自己用在數(shù)據(jù)庫列中的型也兼容)。因此，PL/SQL是連接數(shù)據(jù)庫技術(shù)和過程編程能力之間間隙的方便的橋梁。
5. 改進了性能
PL/SQL的使用可以幫助改進應(yīng)用程序的性能。在沒有PL/SQL時,ORACLE每次只處理一個SQL語句,而在具有PL/SQL時,一個完整的語句塊一次發(fā)送到ORACLE,可明顯地減少與ORACLE之間的通信和調(diào)用。提高了效益。效益的差異取決于PL/SQL使用的什么環(huán)境。這些效益稍后討論
二、PL/SQL的結(jié)構(gòu)
PL/SQL的每個單元由一個或多個塊(blocks)組成。這些塊可以是完全獨立的,或者一個塊嵌套在另一個塊之中。這樣,一個塊可表示其它塊的一小部分,反過來,它恰好也是整個程序碼單元的一部分。
通常,一個塊可以是無名塊或者一個子程序。
1. 無名塊(anonymous)
無名塊是一種沒有名字的塊。這些塊在運行它們的應(yīng)用程序中說明，并且為了執(zhí)行在運行時由PL/SQL引擎通過。無名塊可以嵌入預(yù)編譯程序(或OCI程序)、以及SQL*PLUS或SQL*DBA中。SQL*Forms中的觸發(fā)器也由這些塊組成,無名塊用得較多。
無名塊的定義形式為:
DECLARE
--說明
BEGIN
--語句序列
[EXCEPTION
--例外處理程序]
END；
一個PL/SQL塊由三部分組成:說明部分,可執(zhí)行部分和例外處理部分。
在說明部分中答應(yīng)說明變量和常量等PL/SQL對象,這些對象在塊中引用,或在嵌套的子塊中引用。說明部分是可選的。在執(zhí)行部分可使用SQL的DML語句,事務(wù)控制語句,還可使用控制結(jié)構(gòu)如條件控制、迭代控制和順序控制，這是PL/SQL對SQL的最重要的擴展。在PL/SQL程序中可以方便地發(fā)現(xiàn)和處理預(yù)定義的或用戶定義的稱為例外的出錯條件(警告或出錯條件),當(dāng)發(fā)生錯誤時,引起(raise)一個例外,正常的執(zhí)行被停止,控制轉(zhuǎn)移到例外處理程序。例外處理程序也是可選的。
例2.47: PL/SQL無名塊結(jié)構(gòu)的例子
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product=’TENNIS RACKET’;
IF qty_on_hand>0 THEN
UPDATE inventory SET quantity=quantity-l
WHERE product=’TENNIS RACKET’;
INSERT INTO purchase_record
VALUES (‘out of Tennis Rackets’,SYSDATE);
ENDIF;
COMMIT;
EXCEPTION
WHEN no_data_found THEN
INSERT TNTO error_table
VALUES (‘Product TENNIS RACKET not found’)
END;
注重要害詞DECLARE、BEGIN和EXCEPTION后面不用分號跟隨,但是END和全部其它PL/SQL語句要求分號。
上面的例子說明了一個PL/SQL變量’qtyon_hand’,然后使用了一個SELECT語句。假如此變量的值為正，則修改’inventory’表。假如SELECT尋找失敗,則引起叫’no-data found’的例外,控制立即轉(zhuǎn)移到EXCEPTION部分,將所出問題記錄到一個表中。
2. 子程序(Subprogram)
子程序是命名的PL/SQL塊,分為過程(Procedures)和函數(shù)(Functions)兩類。后者調(diào)用時,由RETURN返回一個值。因此一般可使用一個過程執(zhí)行一個動作，使用一個函數(shù)計算一個值。
SQL* Forms答應(yīng)將過程和函數(shù)說明為Form的一部分,并且可從Forms的其它作用點調(diào)用。
(1)過程
過程是執(zhí)行一種特定動作的子程序,是命名的PL/SQL塊。過程有兩部分:過程說明和過程體。過程的結(jié)構(gòu)如下:
PROCEDURE 過程名 (參數(shù))
--說明
BEGIN
--語句序列
[EXCEPTION
--例外處理程序]
END[過程名];
其中參數(shù) 格式為:
參數(shù)名 IN 類型名
OUT :=值。
IN OUT
過程說明指定過程名或參數(shù)表,參數(shù)說明為選擇項。過程體由三部分組成：申明部分、可執(zhí)行部分和可選項例外處理部分。申明部分包含類型、光標、常量、變量、例外和子程序的說明，這些對象是局部的，當(dāng)退出過程時它們不再存在。可執(zhí)行部分由賦值語句、控制語句和操縱ORACLE數(shù)據(jù)語句組成。例外處理部分包括例外處理程序。
參數(shù)方式(IN,OUT,INOUT)定義了形式參數(shù)的行為。IN參數(shù)可將值傳送給被調(diào)用的子程序。OUT參數(shù)將值返回給子程序的調(diào)用者。INOUT參數(shù)可將初始值傳送給被調(diào)用的子程序,并將修改的值返回調(diào)用者。在過程內(nèi),IN參數(shù)起像常量一樣的作用,它不能被賦值;IN OUT 參數(shù),它起像初始化的變量的作用,可被賦值,它的值可以賦給其它變量。OUT參數(shù)起像一個未初始化的變量的作用,它的值不可賦給其它變量或重新賦給自己。在退出過程之前，要顯式地將值賦給全部OUT形式參數(shù)。IN參數(shù)可初始化為缺省值。
例 2.48: OUT參數(shù)的例子:
PROCEDURE reconcile(acctno out INTEGER) IS
BEGIN
...
END reconcile;
例 2.49: 增加職工工資的過程:
PROCEDURE RAISE_SALARY(emp_id INTEGER,INCREASE REAL)IS
CURRENT_SALARY REAL;
SALARY_MISSING EXCEPTION;
BEGIN
SELECT SAL INTO CURRENT_SALARY FROM EMP
WHERE EPNO=emp_id;
IF CORRENT_SALARY IS NULL THEN
RAISE SALARY_MISSING;
ELSE
UPDATE EMP SET SAL=SAL+INCREASE
WHERE empno=emp_id;
ENDIF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO EMP_AUDIT VALUES(emp_id,’NO such number’);
WHEN salary_missing THEN
INSERT INTO EMP_AUDIT VALUES(emp_id,’SALARY IS NULL’);
END RAISE_SALARY;
當(dāng)調(diào)用該過程時,該過程接收一個職工號和一個工資增加數(shù),使用該職工號選擇EMP表中的當(dāng)前工資。假如該EMP無此職工號或當(dāng)前工資為NULL,則引起例外。否則修改工資。
過程調(diào)用為-PL/SQL語句,針對上述例子其調(diào)用語句形式為:
RAISE_SALARY (1002,600)
也可為:
RAISE_SALARY(INCREASE=>600,EMP_ID=>1002)
(2) 函數(shù)
函數(shù)(function)為一命名的程序單位,可帶參數(shù),并返回一個計算值。函數(shù)和過程其結(jié)構(gòu)是同樣的,除了函數(shù)有一個RETURN子句外。函數(shù)的結(jié)構(gòu)如下:
FUNCTION 函數(shù)名(變元)
RETURN 類型名 IS
--說明
BEGIN
--語句序列
[EXCEPTION
--例外處理程序]
END [函數(shù)名];
其中變元格式為:
變元名 IN 類型名
OUT :=值。
IN OUT
說明:函數(shù)中要害字和參數(shù)的含義同過程。RETURN 子句用于指定結(jié)果值的數(shù)據(jù)類型。
例 2.50: 決定一個職工工資是否超出范圍的函數(shù):
FUNCTION SAL_OK(SALARY REAL,TITLE CHAR)
RETURN BOOEAN IS
MAX_SAL REAL;
MIN_SAL REAL;
BEGIN
SELECT LOSAL,HISAL INTO MIN_SAL,AMX_SAL
FROM SALS WHERE JOB=TITLE;
RETURN(SALARY>=MIN_SAL)AND (SALARY<=MAX_SAL);
END SAL_OK;
當(dāng)該函數(shù)調(diào)用時,它接收一職工的工資以及工作名稱,它利用工作名稱查找SALS表中的范圍限制,該函數(shù)標識符SAL_OK由RETURN語句置成一個布爾值,假如工資超出范圍為FALSE,否則為TRUE。
函數(shù)調(diào)用可以是表達式的成分。
例 2.51:
IF SAL_OK(NEW_SAL,NEW_TITLE)THEN
...
END IF;
...
