1 PL/SQL簡介

1 PL/SQL簡介
　　PL/SQL是ORACLE的過程化語言，包括一整套的資料類型、條件結構、迴圈結構和異常處理結構，PL/SQL可以執行SQL語句，SQL語句中也可以使用PL/SQL函數。
　　1.2 建立PL/SQL程式塊
　　DECLARE
　　…
　　BEGIN
　　…
　　EXCEPTION
　　END;
　　1.3 PL/SQL資料類型
　　　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 預定義異常
　　　SQL入門教程（圖二）" />
　　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;
　　SQL入門教程（圖三）" />
　　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;  
 Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1806588