1.1 PL/SQL簡(jiǎn)介

PL/SQL是ORACLE的過(guò)程化語(yǔ)言，包括一整套的數(shù)據(jù)類(lèi)型、條件結(jié)構(gòu)、循環(huán)結(jié)構(gòu)和異常處理結(jié)構(gòu)，PL/SQL可以執(zhí)行SQL語(yǔ)句，SQL語(yǔ)句中也可以使用PL/SQL函數(shù)。


1.2 創(chuàng)建PL/SQL程序塊

DECLARE
…
BEGIN
…
EXCEPTION
END;


1.3 PL/SQL數(shù)據(jù)類(lèi)型

名稱(chēng) 類(lèi)型 說(shuō)明
NUMBER 數(shù)字型 能存放整數(shù)值和實(shí)數(shù)值，并且可以定義精度和取值范圍
BINARY_INTEGER 數(shù)字型 可存儲(chǔ)帶符號(hào)整數(shù)，為整數(shù)計(jì)算優(yōu)化性能
DEC 數(shù)字型 NUMBER的子類(lèi)型，小數(shù)
DOUBLE PRECISION 數(shù)字型 NUMBER的子類(lèi)型，高精度實(shí)數(shù)
INTEGER 數(shù)字型 NUMBER的子類(lèi)型，整數(shù)
INT 數(shù)字型 NUMBER的子類(lèi)型，整數(shù)
NUMERIC 數(shù)字型 NUMBER的子類(lèi)型，與NUMBER等價(jià)
REAL 數(shù)字型 NUMBER的子類(lèi)型，與NUMBER等價(jià)
SMALLINT 數(shù)字型 NUMBER的子類(lèi)型，取值范圍比INTEGER小
VARCHAR2 字符型 存放可變長(zhǎng)字符串，有最大長(zhǎng)度
CHAR 字符型 定長(zhǎng)字符串
LONG 字符型 變長(zhǎng)字符串，最大長(zhǎng)度可達(dá)32,767
DATE 日期型 以數(shù)據(jù)庫(kù)相同的格式存放日期值
BOOLEAN 布爾型 TRUE OR FALSE
ROWID ROWID 存放數(shù)據(jù)庫(kù)的行號(hào)

例子：
DECLARE
ORDER_NO NUMBER(3);
CUST_NAME VARCHAR2(20);
ORDER_DATE DATE;
EMP_NO INTEGER:=25;
PI CONSTANT NUMBER:=3.1416;
BEGIN
NULL;
END;


1.4 處理PL/SQL的異常

1.4.1 PL/SQL的異常

例如：
DECLARE
X NUMBER;
BEGIN
X:= 'yyyy';--Error Here
EXCEPTION WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
END;

實(shí)現(xiàn)技術(shù)：
EXCEPTION WHEN first_exception THEN
…
WHEN second_exception THEN
…
WHEN OTHERS THEN
/*THERS異常處理器必須排在最后，它處理所有沒(méi)有明確列出的異常。*/
…
END;

1.4.2 預(yù)定義異常

異常名稱(chēng) ORACLE錯(cuò)誤 說(shuō)明
CURSOR_ALREADY_OPEN ORA-6511 試圖打開(kāi)一個(gè)已打開(kāi)的游標(biāo)
DUP_VAL_ON_INDEX ORA-0001 試圖破壞一個(gè)唯一性限制
INVALID_CURSOR ORA-1001 試圖使用一個(gè)無(wú)效的游標(biāo)
INVALID_NUMBER ORA-1722 試圖對(duì)非數(shù)字值進(jìn)行數(shù)字操作
LOGIN_DENIED ORA-1017 無(wú)效的用戶(hù)名或者口令
NO_DATA_FOUND ORA-1403 查詢(xún)未找到數(shù)據(jù)
NOT_LOGGED_ON ORA-1012 還未連接就試圖數(shù)據(jù)庫(kù)操作
PROGRAM_ERROR ORA-6501 內(nèi)部錯(cuò)誤
ROWTYPE_MISMATCH ORA-6504 主變量和游標(biāo)的類(lèi)型不兼容
STORAGE_ERROR ORA-6500 內(nèi)部錯(cuò)誤
TIMEOUT_ON_RESOURCE ORA-0051 發(fā)生超時(shí)
TOO_MANY_ROWS ORA-1422 SELECT INTD命令返回的多行
TRANSACTION_BACKED_OUT ORA-00 由于死鎖提交被退回
VALUE_ERROR ORA-6502 轉(zhuǎn)換或者裁剪錯(cuò)誤
ZERO_DIVIDE ORA-1476 試圖被零除

1.4.3 自定義異常處理

DECLARE
BAD_ROWID EXCEPTION;
X ROWID;
PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);
BEGIN
SELECT ROWID INTO X FROM TAB
WHERE ROWNUM=1;
EXCEPTION WHEN BAD_ROWID THEN
DBMS_OUTPUT.PUT_LINE('CANNOT QUERY ROWID FROM THIS VIEW');
END;

注意：-01445 因?yàn)镻RAGMA EXCEPTION_INIT命令把這個(gè)變量(-01455)連接到
這個(gè)ORACLE錯(cuò)誤，該語(yǔ)句的語(yǔ)法如下：
PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是負(fù)數(shù)，因?yàn)殄e(cuò)誤號(hào)被認(rèn)為負(fù)數(shù)，當(dāng)定義錯(cuò)誤時(shí)記住使用負(fù)號(hào)

1.4.4 自定義異常

異常不一定必須是oracle返回的系統(tǒng)錯(cuò)誤，用戶(hù)可以在自己的應(yīng)用程序中創(chuàng)
建可觸發(fā)及可處理的自定義異常
DECLARE
SALARY_CODE VARCHAR2(1);
INVALID_SALARY_CODE EXCEPTION;
BEGIN
SALARY_CODE:='X';
IF SALARY_CODE NOT IN('A', 'B', 'C') THEN
RAISE INVALID_SALARY_CODE;
END IF;
EXCEPTION WHEN INVALID_SALARY_CODE THEN
DBMS_OUTPUT.PUT_LINE('INVALID SALARY CODE');
END;


1.5 在PL/SQL中單條記錄的查詢(xún)

在PL/SQL內(nèi)，有時(shí)在沒(méi)有定義顯式游標(biāo)的情況下需要查詢(xún)單條記錄，并把記錄的數(shù)據(jù)賦給變量。
DECLARE
ln_dno NUMBER;
lvs_dname VARCHAR2(40);
BEGIN
SELECT DEPT_NO,DEPT_NAME
INTO ln_dno,lvs_dname
FROM dept
WHERE DEPT_NO=1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)||'.'||lvs_dname);
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA_FOUND');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;

1.6 用游標(biāo)查詢(xún)多條記錄

游標(biāo)（CURSOR）是指向一個(gè)稱(chēng)為上下文相關(guān)區(qū)的區(qū)域的指針，這個(gè)區(qū)域在服務(wù)器的處理過(guò)程全局區(qū)（PGA）內(nèi)，當(dāng)服務(wù)器上執(zhí)行了一個(gè)查詢(xún)后，查詢(xún)返回的記錄集存放在上下文相關(guān)區(qū)，通過(guò)游標(biāo)上的操作可以把這些記錄檢索到客戶(hù)端的應(yīng)用程序。

1.6.1 使用游標(biāo)的基本方法

DECLARE
CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS
WHERE ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
OPEN C1;
FETCH C1 INTO VNAME;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||' '||VNAME);
FETCH C1 INTO VNAME;
END LOOP;
END;

屬性 含量
%FOUND 布爾型屬性，當(dāng)最近一次該記錄時(shí)成功返回，則值為T(mén)RUE
%NOTFOUND 布爾型屬性，它的值總與%FOUND屬性的值相反
%ISOPEN 布爾型屬性，當(dāng)游標(biāo)是打開(kāi)時(shí)返回TRUE
%ROWCOUNT 數(shù)字型屬性，返回已從游標(biāo)中讀取的記錄數(shù)

1.6.2 使用游標(biāo)FOR循環(huán)

DECLARE
CURSOR C1 IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE ROWNUM<=10
ORDER BY VIEW_NAME;
BEGIN
FOR I IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
END LOOP;
EXCEPTION WHEN OTHERS THEN
NULL;
END;

1.6.3 帶參數(shù)的游標(biāo)

DECLARE
CURSOR C1(VIEW_PATTERN VARCHAR2) IS
SELECT VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE VIEW_PATTERN||'%' AND
ROWNUM<=10
ORDER BY VIEW_NAME;
VNAME VARCHAR2(40);
BEGIN
FOR I IN C1('USER_AR') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
DBMS_OUTPUT.PUT_LINE();
FOR I IN C1('USER') LOOP
DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('AAA');
END;

1.7 創(chuàng)建代表數(shù)據(jù)庫(kù)記錄和列的變量

變量名 基表名.列名%TYPE
DECLARE
D_NO DEPT.DEPT_NO%TYPE;
D_NAME DEPT.DEPT_NAME%TYPE;
BEGIN
SELECT DEPT_NO,DEPT_NAME INTO D_NO,D_NAME
FROM DEPT;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO));
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;

變量名 基表名%ROWTYPE
DECLARE
D VEQU12%ROWTYPE;
BEGIN
SELECT ASSET12ID,ASSET12NAME
INTO D.ASSET12ID, D.ASSET12NAME
FROM VEQU12;
DBMS_OUTPUT.PUT_LINE(D.ASSET12ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;
說(shuō)明：
當(dāng)用戶(hù)要?jiǎng)?chuàng)建一個(gè)變量來(lái)表示一個(gè)基表列或者要?jiǎng)?chuàng)建多個(gè)變量來(lái)代表一整條記錄時(shí)，可以實(shí)際使用%TYPE屬性和%ROWTYPE屬性，使用%TYPE屬性和%ROWTYPE屬性可以保證當(dāng)基表的結(jié)構(gòu)或者其中某列的數(shù)據(jù)類(lèi)型改變了時(shí)，用戶(hù)的PL/SQL代碼仍可正常工作。

1.8 怎樣用PL/SQL表實(shí)現(xiàn)數(shù)組功能

PL/SQL表與其他過(guò)程化語(yǔ)言(如C語(yǔ)言)的一維數(shù)組類(lèi)似。實(shí)現(xiàn)PL/SQL表需要?jiǎng)?chuàng)建一個(gè)數(shù)據(jù)類(lèi)型并另外進(jìn)行變量說(shuō)明。
Type <類(lèi)型名> Is
Table Of <數(shù)據(jù)類(lèi)型>
Index by Binary_Integer;
以下為一個(gè)例子：
Declare
Type Array_type is
Table Of Number
Index by Binary_Integer;
My_Array Array_type;
Begin
For I In 1..10 Loop
My_Array(I) := I*2;
End Loop;
For I In 1..10 Loop
Dbms_Output.Put_line(To_char(My_Array(I)));
End Loop;
End;