mysql預存程序講解

標籤：

1.資料庫預存程序：簡單滴說，預存程序就是儲存在資料庫中的一個程式。

2..資料庫預存程序作用： 

 第一：預存程序因為SQL語句已經預編繹過了，因此啟動並執行速度比較快。   

Html代碼  

1. 第二：預存程序可以接受參數、輸出參數、返回單個或多個結果集以及傳回值。可以向程式返回錯誤原因。       
2. 第三：預存程序運行比較穩定，不會有太多的錯誤。只要一次成功，以後都會按這個程式運行。       
3. 第四：預存程序主要是在伺服器上運行，減少對客戶機的壓力。     
4. 第五：預存程序可以包含程式流、邏輯以及對資料庫的查詢。同時可以實體封裝和隱藏了資料邏輯。     
5. 第六：預存程序可以在單個預存程序中執行一系列   SQL   語句。     
6. 第七：預存程序可以從自己的預存程序內引用其它預存程序，這可以簡化一系列複雜語句。  
7. 此外，如果多條SQL語句執行過程中，過程環節返回了資料作為後面環節的輸入資料，如果直接通過  
8. SQL語句執行，勢必導致大量的資料通過網路返回到客戶機，並在客戶機運算；如果封裝在預存程序中，  
9. 則將運算放在伺服器進行，不但減少了客戶機的壓力，同時也減少了網路流量，提高了執行的效率。  

 

3.mysql預存程序：

MySQL5預存程序，用得人好像不多。按照資料庫設計原理來講，預存程序是在db server上先行編譯的，所以查詢速度會比較起純SQL語句快很多。可能是現在流行OO，導至預存程序使用的餘地大打折扣。但如果從效果上來講，用預存程序來實現商務規則所帶得DB SERVER壓力，比用JAVA類實現商務規則所帶來的WEB SERVER壓力要小。當然預存程序也不應濫用，象普通的insert、update之類的語句就不需要使用預存程序了。

   好了，言歸正傳。MySQL建立procudure的文法與sql server/Oracle差別較大。
例1：傳入參數的procedure

 

create procedure usp_test(param varchar(20)   select * from talbeName where column=param

例2：更新表的procedure

create procedure usp_test2 (t varchar(20))   begin     set xname = ‘test‘;     update table set column = xname where column1=t;  end

    MySQL建立預存程序時不帶as，而且()不能省略，即便是沒有傳入參數。這一點象sql server或是oracle那樣直觀，而且聲明變數時直接用declare，不用加的@或@@(in out變數除外)。還有一點是很怪的文法，如果是以“select”為開頭的預存程序，是不能加"begin end"的。"begin end"表示多條SQL語句的複合體。

   當然，事實上幾乎沒有哪款資料庫是完全符合SQL3標準的，多少都含有自己的一些成份裡面，這也造成使用預存程序會使程式的可移性降低。

   調用procedure
   MySQL使用call關鍵字。例：call usp_test(‘test‘)；而不是execute，同樣()是不能省略的。
  建立完procedure後，再看看java是如何調用procedure的。
    Connection conn = null;

Java代碼  

1. CallableStatement cstmt = null;  
2. ResultSet rs = null;  
3. try{  
4.     conn = DbConn.getDbConn();//get pool conn  
5.     CallableStatement cstmt = conn.prepareCall("{call usp_test(?)}");  
6.         call.setString(1, "test");  
7.         rs = call.executeQuery();  
8.         while(rs.next()){  
9.             String te = rs.getString(1);  
10.             System.out.println("te:"+te);  
11.         }  
12.     }catch(Exception e){  
13.         System.out.println("e: "+e);  
14.     }finally{  
15.         try{  
16.             rs.close();  
17.             cstmt.close();  
18.             conn.close();  
19.         }catch(Exception ex){  
20.             System.out.println("ex:"+ex);  
21.         }  
22.     }  

 
   這裡使用的是"{call usp_test()}"來調用預存程序。同時也可以編程傳入參數，進行查詢。
    上述方法有個致命的缺點，就是傳入的參數是由編寫預存程序決定的。也就是說不支援象PreparedStatement那樣的多參數動態查詢。

    下面先講解MySQL預存程序的輸入輸出參數應用。例子可以直接copy運行。
    1.輸入參數(也是預設參數)
     CREATE PROCEDURE usp1(IN p INT)
     BEGIN
         SET @x = p；
     END;
     call usp1(123456);
     select @x
     返回結果為 123456
    2.輸出參數(out)
     CREATE PROCEDURE usp2 (OUT p int, IN p2 int)
     BEGIN
        SET p = -5 + p2;
     END
     call p6(@y,10)
     select @y
     返回結果為 5
    3.輸入輸出參數(inout)

CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT) BEGIN     SET inOutParam = 1000;     SELECT inOutParam;     SELECT CONCAT(‘zyxw ‘, inputParam); END

     call demoSP(‘ test‘,@q)
     返回結果"zyxw  test"。

     MySQL調用預存程序的文法講完了，總結一下就是三個參數關索引值IN、OUT、INOUT，分別代表輸入參數、輸出參數、輸入輸出參數。下面就講解是JAVA是如何調用帶有輸出參數的預存程序。
    先建立一個帶輸出參數的Procedure

CREATE PROCEDURE demoSp(OUT inOutParam varchar(50)) BEGIN set inOutParam = "hello procedure"; END

    然後在java中調用。

CallableStatement cstmt =  conn.prepareCall("{call demoSp(?)}");             cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);             boolean i = cstmt.execute();            String x = cstmt.getString(1);             System.out.println("call result:"+i+x);

    很明顯與沒有輸出參數的預存程序相比，多一個registerOutParameter。
也就是說要先註冊輸出參數的類型，再執行execute(),最後取斷行符號出參數的值。
這個過程是不能顛倒的。

mysql預存程序講解