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