Java-Oracle預存程序知識小題

Java-Oracle預存程序知識小題

預存程序是儲存可以接受或返回使用者提供參數的SQL語句集合。在日常的使用中，經常會遇到複雜的商務邏輯和對資料庫的操作，使用預存程序可以進行封裝。可以在資料庫中定義子程式，然後把子程式儲存在資料庫伺服器，之後通過名稱調用。 一、特點： 1. 提高效能

預存程序是預先編譯過，進行最佳化後，儲存在SQL的記憶體中，使用的時候不需要重新編譯，提高工作效率。 2. 減少網路流量

預存程序的代碼直接儲存在資料庫中，使用者通過名稱進行調用，減小網路流量，加快執行速度。如：百萬以上的資料查詢，預存程序分頁要比其他方式的分頁快得多 3. 提高安全性

預存程序可以減少SQL注入攻擊，提高系統的安全性。執行的過程也受到使用者的身份許可權控制，因此沒有資料操作許可權的使用者只能在許可權控制下間接的儲存資料。 4. 交易處理機制

在同時進行主從表以及奪標的資料維護和有效性驗證時，預存程序比較方便，可以有效地利用SQL的交易處理機制。 5. 分離設計編碼與使用

使用預存程序，可以實現預存程序設計和編碼工作分開進行，只要預存程序名、參數、及返回資訊告訴編碼人員即可。 6. （缺點）不易移植和修改

使用預存程序封裝商務邏輯將限制應用程式的可移植性；另外，如果更改參數或者返回的資料以及類型，需要修改相關的代碼，比較繁瑣。 二、文法結構

完整的過程結構如下：

create to replace procedure 過程名 as

聲明語句段;begin

執行語句段;

exception

異常處理語句段;end;

舉例子：

-- 學生表studentcreate table student(

    sno number(6),

    sname varchar2(25),

    pno number(6) primary key

);

-- 預存程序create or replace procedure stu_proc as

p_name varchar2(25);begin

    select sname into p_name from student where sno=1;

    dbms_output.put_line(p_name);end;

-- 調用預存程序call stu_proc(); 三、關於參數的類型，預存程序大致提供下面幾種

1. 無參數的預存程序

定義

create or replace procedure stu_proc as pname varchar2(25);begin

    select sname into pname from student where sno=1;

    dbms_output.put_line(pname);end;

使用方法為：call stu_proc();

2. 僅有輸入參數的過程

create or replace procedure stu_proc1(pno in student.sno%type) as pname varchar2(25);begin

    select sname into pname from student where sno=pno;

    dbms_output.put_line(pname);end;

使用方法為:call stu_proc1('001')

3. 僅有輸出參數的過程

create or replace procedure stu_proc2(pname out student.sname%type) as begin

    select sname into pname from student where sno=1;

    dbms_output.put_line(pname);end;

這種預存程序不能直接用call調用，需要在oracle函數調用。使用方法為：call stu_proc2(name)

4. 有輸入、輸出的預存程序

create or replace procedure stu_proc3(pname out student.sname%type,pname out student.sname%type) as begin

    select sname into pname from student where sno=pno;

    dbms_output.put_line(pname);end;

使用方法為:call stu_proc3(name,'001') 四、預存程序的異常處理

為了提高預存程序的健壯性，避免運行錯誤，建立預存程序時，應該包含異常處理的部分。異常包括預定義異常、非預定義異常和自訂異常。

·預定義異常：PL\SQL提供的系統異常

·費預定義異常：用於處理與預定義異常無關的Oracle錯誤

·自訂異常：處理Oracle錯誤之外的一些異常

使用方法：

create or replace procedure stu_proc6(pno in student.sno%type,pname out student.sname%type)

    is

    begin

        select sname into pname from student where sno=pno;

    EXCEPTION

        when NO_DATA_FOUND then

        RAISE_APPLICATION_ERROR

            (-20011,'ERROR:不存在!');

end;

常用的異常處理：

命名的系統異常	產生原因
ACCESS_INTO_NULL	定義對象
CASE_NOT_FOUND	CASE中未包含相應的WHEN,並且沒有設定集合元素的初始化
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	遊標已經開啟
DUP_VAL_ON_INDEX	唯一索引對應的列上有重複的值
INVALID_CURSOR	在不合法的遊標上進行操作
INVALID_NUMBER	內嵌的SQL語句不能講字元穿換成數字
NO_DATA_FOUND	使用select into 未返回行，或者應用索引表未初始化的
TOO_MANY_ROWS	執行select into，結果集超過一行
ZERO_DIVIDE	除數為0
SUBSCRIPT_BEYOND_COUNT	元素下表超過巢狀表格或VARRAY的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套類或VARRAY時，將下表指定為負數
VALUE_ERROR	賦值時，變數長度不足以容納實際資料
LOGIN_DENIED	PL\SQL應用串連到oracle時，提供了 不正確的使用者名稱密碼
NOT_LOGGED_ON	PL\SQL應用程式在沒有串連oracle資料的情況下訪問資料
PROGRAM_ERROR	PL\SQL內部問題，可能需要重裝資料字典
ROWTYPE_MISMATCH	主遊標變數與PLSQL遊標變數的傳回型別不相容
SELF_IS_NULL	使用物件類型時，在null對象上調用對象方法
STORAGE_ERROR	運行PL\SQL時，超出記憶體空間
SYS_INVALIDE_ID	無效的ROWID字串
TIMEOUT_ON_RESOURCE	Oracle在等待資源連線逾時

五、預存程序與函數的區別 1. 在定義上

定義的名稱這個就不說了，一個是FUNCTION，一個是PROCEDURE；

· 預存程序的參數列表有輸入參數、輸出參數、輸入輸出參數

· 函數的參數只有輸入參數，最後會加上一個return傳回值。 2. 在傳回值上

· 預存程序的傳回值，可以有多個

· 函數的傳回值只有一個 3. 調用方式上

· 預存程序的調用方式有：exec、execute、語句塊調用

· 函數的調用方式有：可以在函數塊中、也可以直接在sql中使用，比如：

create or replace function add_three_numbers

(

    a NUMBER:=0,b NUMBER:=0,c NUMBER:=0

)

return number is

begin

return a+b+c;

end;

select add_three_numbers(1,2,3) from dual; 六、交易處理

1. 事務用於確保資料的一致性，要麼全部確認，要不全部取消。

2. 檔執行事務操作的時候，Oracle會作用在表上加鎖，防止其他的使用者改變表。同時也會在被作用的行上加行鎖，以防止其他事務在相應行上執行DML操作。

3. 執行事務提交或者交易回復時，Oracle會確認事務變化或復原事務、結束事務、刪除儲存點、釋放鎖。 七、參考

Oracle預存程序

http://wenku.baidu.com/view/e56d8071be1e650e52ea99a4.html?from=search