PL/SQL入門教程

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資料類型

名稱	類型	說明
NUMBER	數字型	能存放整數值和實數值，並且可以定義精度和取值範圍
BINARY_INTEGER	數字型	可儲存帶正負號的整數，為整數計算最佳化效能
DEC	數字型	NUMBER 的子類型，小數
DOUBLE PRECISION	數字型	NUMBER 的子類型，高精度實數
INTEGER	數字型	NUMBER 的子類型，整數
INT	數字型	NUMBER 的子類型，整數
NUMERIC	數字型	NUMBER 的子類型，與NUMBER等價
REAL	數字型	NUMBER 的子類型，與NUMBER等價
SMALLINT	數字型	NUMBER 的子類型，取值範圍比INTEGER小
VARCHAR2	字元型	存放可變長字串，有最大長度
CHAR	字元型	定長字串
LONG	字元型	變長字串，最大長度可達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;

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

1.4.2 預定義異常

異常名稱	ORACLE 錯誤	說明
CURSOR_ALREADY_OPEN	ORA-6511	試圖開啟一個已開啟的游標
DUP_VAL_ON_INDEX	ORA-0001	試圖破壞一個唯一性限制
INVALID_CURSOR	ORA-1001	試圖使用一個無效的游標
INVALID_NUMBER	ORA-1722	試圖對非數字值進行數字操作
LOGIN_DENIED	ORA-1017	無效的使用者名稱或者口令
NO_DATA_FOUND	ORA-1403	查詢未找到資料
NOT_LOGGED_ON	ORA-1012	還未串連就試圖資料庫操作
PROGRAM_ERROR	ORA-6501	內部錯誤
ROWTYPE_MISMATCH	ORA-6504	主變數和游標的類型不相容
STORAGE_ERROR	ORA-6500	內部錯誤
TIMEOUT_ON_RESOURCE	ORA-0051	發生逾時
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命令把這個變數(-01455)串連到
這個ORACLE錯誤，該語句的文法如下：
    PRAGMA EXCEPTION_INIT(exception_name, error_number);
其中error_number是負數，因為錯誤號碼被認為負數，當定義錯誤時記住使用負號

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;

屬性	含量
%FOUND	布爾型屬性，當最近一次該記錄時成功返回，則值為TRUE
%NOTFOUND	布爾型屬性，它的值總與%FOUND屬性的值相反
%ISOPEN	布爾型屬性，當游標是開啟時返回TRUE
%ROWCOUNT	數字型屬性，返回已從游標中讀取的記錄數

　

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;

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

1.9 怎樣用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