Cool eye

1.1 PL/SQL 簡介

?? PL/SQL是ORACLE的過程化語言，包括一整套的數據類型、條件結構、循環結構和異常處理結構，PL/SQL可以執行SQL語句，SQL語句中也可以使用PL/SQL函數。

?

1.2 創建PL/SQL程序塊

DECLARE
…
BEGIN
…
EXCEPTION
END;

?

1.3 PL/SQL數據類型

例子：
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;

實現技術：
EXCEPTION WHEN first_exception THEN
…
WHEN second_exception THEN
…
WHEN OTHERS THEN
/*THERS異常處理器必須排在最后，它處理所有沒有明確列出的異常。 */
…
END;

1.4.2 預定義異常

?

1.4.4 自定義異常

??? 異常不一定必須是 oracle 返回的系統錯誤，用戶可以在自己的應用程序中創
建可觸發及可處理的自定義異常
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內，有時在沒有定義顯式光標的情況下需要查詢單條記錄，并把記錄的數據賦給變量。
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）是指向一個稱為上下文相關區的區域的指針，這個區域在服務器的處理過程全局區（PGA）內，當服務器上執行了一個查詢后，查詢返回的記錄集存放在上下文相關區，通過光標上的操作可以把這些記錄檢索到客戶端的應用程序。

?

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;

1.6.2 使用光標 FOR 循環

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;

?

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;
說明：
??? 當用戶要創建一個變量來表示一個基表列或者要創建多個變量來代表一整條記錄時，可以實際使用%TYPE屬性和%ROWTYPE屬性，使用%TYPE屬性和%ROWTYPE屬性可以保證當基表的結構或者其中某列的數據類型改變了時，用戶的PL/SQL代碼仍可正常工作。

?

??? PL/SQL表與其他過程化語言(如C語言)的一維數組類似。實現PL/SQL表需要創建一個數據類型并另外進行變量說明。
??? Type <類型名> Is
??? Table Of <數據類型>
??? 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;