1.1 PL/SQL簡(jiǎn)介1.2 創(chuàng )建PL/SQL程序塊 1.3 PL/SQL數據類(lèi)型 1.4 處理PL/SQL的異常1.4.1 PL/SQL的異常
1.4.2 自定義異常處理
1.4.3 自定義異常
1.5 在PL/SQL中單條記錄的查詢(xún) 1.6 用光標查詢(xún)多條記錄 1.6.1 使用光標的基本方法
1.6.2 使用光標FOR循環(huán)
1.6.3 帶參數的光標
1.7 創(chuàng )建代表數據庫記錄和列的變量
1.8 怎樣用PL/SQL表實(shí)現數組功能1.1 簡(jiǎn)介
PL/SQL是ORACLE的過(guò)程化語(yǔ)言，包括一整套的數據類(lèi)型、條件結構、循環(huán)結構和異常處理結構，PL/SQL可以執行SQL語(yǔ)句，SQL語(yǔ)句中也可以使用PL/SQL函數。
1.2
DECLARE
…
BEGIN
…
EXCEPTION
END;
1.3 數據類(lèi)型
名稱(chēng)
類(lèi)型
說(shuō)明
NUMBER
數字型
能存放整數值和實(shí)數值，并且可以定義精度和取值范圍
BINARY_INTEGER
數字型
可存儲帶符號整數，為整數計算優(yōu)化性能
DEC
數字型
NUMBER的子類(lèi)型，小數
DOUBLE PRECISION
數字型
NUMBER的子類(lèi)型，高精度實(shí)數
INTEGER
數字型
NUMBER的子類(lèi)型，整數
INT
數字型
NUMBER的子類(lèi)型，整數
NUMERIC
數字型
NUMBER的子類(lèi)型，與NUMBER等價(jià)
REAL
數字型
NUMBER的子類(lèi)型，與NUMBER等價(jià)
SMALLINT
數字型
NUMBER的子類(lèi)型，取值范圍比INTEGER小
VARCHAR2
字符型
存放可變長(cháng)字符串，有最大長(cháng)度
CHAR
字符型
定長(cháng)字符串
LONG
字符型
變長(cháng)字符串，最大長(cháng)度可達32,767
DATE
日期型
以數據庫相同的格式存放日期值
BOOLEAN
布爾型
TRUE OR FALSE
ROWID
ROWID
存放數據庫的行號
例子：
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í)現技術(shù)：
EXCEPTION WHEN first_exception THEN
…
WHEN second_exception THEN
…
WHEN OTHERS THEN
/*THERS異常處理器必須排在最后，它處理所有沒(méi)有明確列出的異常。*/
…
END;
1.4.2 預定義異常
異常名稱(chēng)
ORACLE錯誤
說(shuō)明
CURSOR_ALREADY_OPEN
ORA-6511
試圖打開(kāi)一個(gè)已打開(kāi)的光標
DUP_VAL_ON_INDEX
ORA-0001
試圖破壞一個(gè)唯一性限制
INVALID_CURSOR
ORA-1001
試圖使用一個(gè)無(wú)效的光標
INVALID_NUMBER
ORA-1722
試圖對非數字值進(jìn)行數字操作
LOGIN_DENIED
ORA-1017
無(wú)效的用戶(hù)名或者口令
NO_DATA_FOUND
ORA-1403
查詢(xún)未找到數據
NOT_LOGGED_ON
ORA-1012
還未連接就試圖數據庫操作
PROGRAM_ERROR
ORA-6501
內部錯誤
ROWTYPE_MISMATCH
ORA-6504
主變量和光標的類(lèi)型不兼容
STORAGE_ERROR
ORA-6500
內部錯誤
TIMEOUT_ON_RESOURCE
ORA-0051
發(fā)生超時(shí)
TOO_MANY_ROWS
ORA-1422
SELECT INTD命令返回的多行
TRANSACTION_BACKED_OUT
ORA-006
由于死鎖提交被退回
VALUE_ERROR
ORA-6502
轉換或者裁剪錯誤
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 因為PRAGMA EXCEPTION_INIT命令把這個(gè)變量(-01455)連接到
這個(gè)ORACLE錯誤，該語(yǔ)句的語(yǔ)法如下：
PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是負數，因為錯誤號被認為負數，當定義錯誤時(shí)記住使用負號
1.4.4
異常不一定必須是oracle返回的系統錯誤，用戶(hù)可以在自己的應用程序中創(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內，有時(shí)在沒(méi)有定義顯式光標的情況下需要查詢(xún)單條記錄，并把記錄的數據賦給變量。
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
光標（CURSOR）是指向一個(gè)稱(chēng)為上下文相關(guān)區的區域的指針，這個(gè)區域在服務(wù)器的處理過(guò)程全局區（PGA）內，當服務(wù)器上執行了一個(gè)查詢(xún)后，查詢(xún)返回的記錄集存放在上下文相關(guān)區，通過(guò)光標上的操作可以把這些記錄檢索到客戶(hù)端的應用程序。
1.6.1
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
布爾型屬性，當最近一次該記錄時(shí)成功返回，則值為T(mén)RUE
%NOTFOUND
布爾型屬性，它的值總與%FOUND屬性的值相反
%ISOPEN
布爾型屬性，當光標是打開(kāi)時(shí)返回TRUE
%ROWCOUNT
數字型屬性，返回已從光標中讀取的記錄數
1.6.2 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
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
變量名 基表名.列名%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ō)明：
當用戶(hù)要創(chuàng )建一個(gè)變量來(lái)表示一個(gè)基表列或者要創(chuàng )建多個(gè)變量來(lái)代表一整條記錄時(shí)，可以實(shí)際使用%TYPE屬性和%ROWTYPE屬性，使用%TYPE屬性和%ROWTYPE屬性可以保證當基表的結構或者其中某列的數據類(lèi)型改變了時(shí)，用戶(hù)的PL/SQL代碼仍可正常工作。
1.8
PL/SQL表與其他過(guò)程化語(yǔ)言(如C語(yǔ)言)的一維數組類(lèi)似。實(shí)現PL/SQL表需要創(chuàng )建一個(gè)數據類(lèi)型并另外進(jìn)行變量說(shuō)明。
Type <類(lèi)型名> Is
Table Of <數據類(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;
