一 、存儲過程說明

1）說明：

1.存儲過程是用于特定操作的pl/sql語句塊

2.存儲過程是預(yù)編譯過的，經(jīng)優(yōu)化后存儲在sql內(nèi)存中，使用時無需再次編譯，提高了使用效率；

3.存儲過程的代碼直接存放在數(shù)據(jù)庫中，一般直接通過存儲過程的名稱調(diào)用，減少了網(wǎng)絡(luò)流量，加快了系統(tǒng)執(zhí)行效率；

2）存儲過程與函數(shù)的區(qū)別：

1.一般來說，存儲過程實現(xiàn)的功能要復(fù)雜一點，而函數(shù)的實現(xiàn)的功能針對性比較強(qiáng)。

2.對于存儲過程來說可以返回參數(shù)(output)，而函數(shù)只能返回值或者表對象。

3.存儲過程一般是作為一個獨(dú)立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一個部分來調(diào)用，由于函數(shù)可以返回一個表對象，因此它可以在查詢語句中位于FROM關(guān)鍵字的后面。

3）存儲過程的優(yōu)點：

1.執(zhí)行速度更快 – 在數(shù)據(jù)庫中保存的存儲過程語句都是編譯過的

2.允許模塊化程序設(shè)計 ，程序的可移植性更強(qiáng)– 類似方法的復(fù)用（使用存儲過程可以實現(xiàn)存儲過程設(shè)計和編碼工作的分開進(jìn)行，只要將存儲過程名、參數(shù)、返回信息等告訴編程人員即可）；

3.提高系統(tǒng)安全性 – 防止SQL注入 （執(zhí)行存儲過程的用戶要具有一定的權(quán)限才能使用存儲過程）

4.減少網(wǎng)絡(luò)流通量 – 只要傳輸存儲過程的名稱（在大批數(shù)據(jù)查詢時使用存儲過程分頁查詢比其他方式的分頁要快很多）

5.在同時進(jìn)行逐主、從表間的數(shù)據(jù)維護(hù)及有效性驗證時，使用存儲過程更加方便，可以有效的利用SQL中的事務(wù)處理機(jī)制

二、語法

1）創(chuàng)建存儲過程

CREATE[OR REPLACE]PROCEDURE procedure_name 

[(parameter1[model] datatype1, parameter2[model] datatype2..)]

IS[AS]

BEGIN

    PL/SQL;

END    [procedure_name];

說明：

1. parameter用于指定參數(shù)，model用于指定參數(shù)模式，datatype用于指定參數(shù)類型

2.定義存儲過程的參數(shù)時，只能指定數(shù)據(jù)類型，不能指定數(shù)據(jù)長度

3. IS/AS用于開始PL/SQL代碼塊

4. 創(chuàng)建存儲過程時，既可以指定參數(shù)也可以不指定任何參數(shù)；

5. 存儲過程參數(shù)：1）輸入?yún)?shù) IN 　IN用于接收調(diào)用環(huán)境的輸入?yún)?shù)（創(chuàng)建存儲過程時，輸入?yún)?shù)的IN可以省略）

2) 輸出參數(shù) OUT 　OUT用于將輸出數(shù)據(jù)傳遞到調(diào)用環(huán)境

3) 輸入輸出參數(shù)（IN OUT）其中IN用于接收調(diào)用環(huán)境的輸入?yún)?shù)，OUT用于將輸出數(shù)據(jù)傳遞到調(diào)用環(huán)境

2）刪除存儲過程

DROPPROCEDURE procedure_name;

3)編譯存儲過程

ALTER PROCEDURE procedure_name COMPILE

三、存儲過程調(diào)用

1）說明：

1.在PL/SQL中可以直接引用存儲過程(在SQL*PLUS中調(diào)用存儲過程時需要使用call或者execute命令);

2.當(dāng)調(diào)用存儲過程時，如果無參數(shù)，那么直接引用存儲過程名；如果有輸入?yún)?shù)，則需提供輸入?yún)?shù)數(shù)值；如果有輸出參數(shù)，需要使用變量接收輸出結(jié)果；

3.參數(shù)傳遞時有位置傳遞，名稱傳遞和組合傳遞三種方法，三種參數(shù)傳遞方式如下：

DECLARE
v_para1            varchar2(10);  
v_para2            varchar2(10);
v_para3            varchar2(30);    
v_para4            varchar2(30);
BEGIN
v_para1 := '123';
v_para2 := '456';
v_para4 := '789';
USP_Learing(v_para1,v_para2,v_para3,v_para4);--位置傳遞
USP_Learing(p_para1=>v_para1,p_para2=>v_para2,p_para3=>v_para3,p_para4=>v_para4); --值傳遞
USP_Learing(v_para1,v_para2,p_para3=>v_para3,p_para4=>v_para4);--組合傳遞
dbms_output.put_line(v_para3);    dbms_output.put_line(v_para4);
END; 

2）存儲過程調(diào)用例子

CREATE OR REPLACE PROCUDURE print_Time 
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(SYSDATE);
END print_time;

1.pl/sql中直接在pl/sql代碼塊中調(diào)用 print_time()即可

2.sql*plus中 EXEC print_time();

四、存儲過程中常用數(shù)據(jù)類型

1）記錄（RECORD）（單行多列）

2. 表（TABLE）（多行多列）

3. 嵌套表（table）（多行多列）

4）變長數(shù)組（VARRY）（多行單列）

5）Common Table Expression (CTE)

五、存儲過程中事務(wù)處理

1）事務(wù)說明：

1.事務(wù)用于確保數(shù)據(jù)的一致性，有一組相關(guān)的DML語句組成，改組DML語句所執(zhí)行的操作要么全部確認(rèn)，要么全部取消。

2.當(dāng)執(zhí)行事務(wù)操作DML時，oracle會在被作用的表上加鎖，以防止其他用戶改變表結(jié)構(gòu)，同時也會在被作用的行上加鎖，以防止其他事務(wù)在該行上執(zhí)行DML操作

3.當(dāng)執(zhí)行事務(wù)提交或者事務(wù)回滾時，oracle會確認(rèn)事務(wù)變化或者回滾事務(wù)、結(jié)束事務(wù)、山粗保存點、釋放鎖。

4. 提交事務(wù)（commit）確認(rèn)事務(wù)變化，結(jié)束當(dāng)前事務(wù)、刪除保存點，釋放鎖，使得當(dāng)前事務(wù)中所有未決的數(shù)據(jù)永久改變

5.保存點（savepoint）在當(dāng)前事務(wù)中，標(biāo)記事務(wù)的保存點

6. 回滾操作（rollback）回滾整個事務(wù)，刪除該事務(wù)中所有保存點，釋放鎖，丟棄所有未決的數(shù)據(jù)改變
　　7. ROLLBACK TO SAVEPOINT 回滾到指定的保存點

2）存儲過程中事務(wù)說明：

1.盡可能的讓事務(wù)持續(xù)的越短越好

2.在事務(wù)中盡可能的存取最少的數(shù)據(jù)量
3）實例

CREATE OR REPLACE PROCEDURE trancPro
IS 
BEGIN
    INSERT INTO tab1 VALUES('AA','1212','1313');
    COMMIT;
    SAVEPOINT s1;
    INSERT INTO tab1  VALUES('BB','1414','1515');
    DBMS_TRANSACTION.SAVEPOINT('s2');
    UPDATE tab1 SET SNO='1515' WHERE ID='BB';
    COMMIT;
    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO SAVEPOINT s1;
    RAISE_APPLICATION_ERROR(-20010,'ERROR:違反唯一索引約束');
    WHEN OTHERS THEN ROLLBACK;
END trancPro;

六、存儲過程例子
1)簡單例子--利用存儲過程打印日期

CREATE OR REPLACE PROCUDURE print_Time 
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(SYSDATE);
END print_time;

2)例2--包含輸入輸出參數(shù)

CREATE OR REPLACE PROCEDURE para_Procedure
(
        para1　　　　　　　　varchar2 :='paraString1',
        para2　　　　　　　　varchar2 default 'paraString2',
        para3  out      　 varchar2,
        para4  in out　　　 varchar2
)
IS
BEGIN
DECLARE
       para5               varchar2(20);
BEGIN
       para5 := '輸入輸出參數(shù)：'|| para4;
       para3 := '輸出參數(shù)：' || para1 || para2;
       para4 :=para5;
       dbms_output.put_line(para5);
　　　　dbms_output.put_line('para4 is'||para4);
END;
END para_Procedure;

七、java程序調(diào)用
在本節(jié)中，我們使用java語言調(diào)用存儲過程。其中，關(guān)鍵是使用CallableStatement這個對象，代碼如下：

String oracleDriverName = "oracle.jdbc.driver.OracleDriver";
 
        // 以下使用的Test就是Oracle里的表空間
        String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        Connection myConnection = null;
        try {
            Class.forName(oracleDriverName);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        try {
            myConnection = DriverManager.getConnection(oracleUrlToConnect,
                    "xxxx", "xxxx");//此處為數(shù)據(jù)庫用戶名與密碼
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
             
            CallableStatement proc=null;
            proc=myConnection.prepareCall("{call xs_proc(?,?)}");
            proc.setString(1, "zhangsan");
            proc.registerOutParameter(2, Types.NUMERIC);
            proc.execute();
            String teststring=proc.getString(2);
            System.out.println(teststring);
 
        } catch (Exception ex) {
            ex.printStackTrace();
        }