PL/SQL入門

.1 PL/SQL簡介 轉

http://xsb.itpub.net/post/419/107621

[Ref: ]

1.1 簡介

PL/SQL是ORACLE的過程化語言，包括一整套的資料型別、條件結構、迴圈結構和異常處理結構，PL/SQL可以執行SQL語句，SQL語句中也可以使用PL/SQL函式。

DECLARE…BEGIN…EXCEPTION
END;1.3 資料型別

例子：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 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.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內，有時在沒有定義顯式游標的情況下需要查詢單條記錄，並把記錄的資料賦給變數。
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）內，當伺服器上執行了一個查詢後，查詢返回的記錄集存放在上下文相關區，透過游標上的操作可以把這些記錄檢索到客戶端的應用程式。

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

1.9

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;

PL/SQL with Array Collections

For example:

DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;


BEGIN
/* Retrieve employee record. */
SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7468;
...
END;