1.1
PL/SQL
簡(jiǎn)介
??
PL/SQL是ORACLE的過程化語(yǔ)言，包括一整套的數(shù)據(jù)類型、條件結(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ù)類型
|
名稱
|
類型
|
說明
|
|
NUMBER
|
數(shù)字型
|
能存放整數(shù)值和實(shí)數(shù)值，并且可以定義精度和取值范圍
|
|
BINARY_INTEGER
|
數(shù)字型
|
可存儲(chǔ)帶符號(hào)整數(shù)，為整數(shù)計(jì)算優(yōu)化性能
|
|
DEC
|
數(shù)字型
|
NUMBER
的子類型，小數(shù)
|
|
DOUBLE PRECISION
|
數(shù)字型
|
NUMBER
的子類型，高精度實(shí)數(shù)
|
|
INTEGER
|
數(shù)字型
|
NUMBER
的子類型，整數(shù)
|
|
INT
|
數(shù)字型
|
NUMBER
的子類型，整數(shù)
|
|
NUMERIC
|
數(shù)字型
|
NUMBER
的子類型，與
NUMBER
等價(jià)
|
|
REAL
|
數(shù)字型
|
NUMBER
的子類型，與
NUMBER
等價(jià)
|
|
SMALLINT
|
數(shù)字型
|
NUMBER
的子類型，取值范圍比
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異常處理器必須排在最后，它處理所有沒有明確列出的異常。
*/
…
END;
1.4.2 預(yù)定義異常
|
異常名稱
|
ORACLE
錯(cuò)誤
|
說明
|
|
CURSOR_ALREADY_OPEN
|
ORA-6511
|
試圖打開一個(gè)已打開的光標(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ú)效的用戶名或者口令
|
|
NO_DATA_FOUND
|
ORA-1403
|
查詢未找到數(shù)據(jù)
|
|
NOT_LOGGED_ON
|
ORA-1012
|
還未連接就試圖數(shù)據(jù)庫(kù)操作
|
|
PROGRAM_ERROR
|
ORA-6501
|
內(nèi)部錯(cuò)誤
|
|
ROWTYPE_MISMATCH
|
ORA-6504
|
主變量和光標(biāo)的類型不兼容
|
|
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-006
|
由于死鎖提交被退回
|
|
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)?/font>
PRAGMA 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ò)誤，用戶可以在自己的應(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
中單條記錄的查詢
??? 在PL/SQL內(nèi)，有時(shí)在沒有定義顯式光標(biāo)的情況下需要查詢單條記錄，并把記錄的數(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)查詢多條記錄
????? 光標(biāo)（CURSOR）是指向一個(gè)稱為上下文相關(guān)區(qū)的區(qū)域的指針，這個(gè)區(qū)域在服務(wù)器的處理過程全局區(qū)（PGA）內(nèi)，當(dāng)服務(wù)器上執(zhí)行了一個(gè)查詢后，查詢返回的記錄集存放在上下文相關(guān)區(qū)，通過光標(biāo)上的操作可以把這些記錄檢索到客戶端的應(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í)成功返回，則值為TRUE
|
|
%NOTFOUND
|
布爾型屬性，它的值總與%FOUND屬性的值相反
|
|
%ISOPEN
|
布爾型屬性，當(dāng)光標(biāo)是打開時(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;
說明：
??? 當(dāng)用戶要?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ù)類型改變了時(shí)，用戶的PL/SQL代碼仍可正常工作。
?
1.8
怎樣用PL/SQL表實(shí)現(xiàn)數(shù)組功能
??? PL/SQL表與其他過程化語(yǔ)言(如C語(yǔ)言)的一維數(shù)組類似。實(shí)現(xiàn)PL/SQL表需要?jiǎng)?chuàng)建一個(gè)數(shù)據(jù)類型并另外進(jìn)行變量說明。
??? Type <類型名> Is
??? Table Of <數(shù)據(jù)類型>
??? 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;