存儲過程可以一次執行多條語句,處理復雜的業務邏輯,完成一些計算。
這篇博客總結一下mysql中存儲過程基本的用法——mysql存儲過程開荒。我們從怎麼寫存儲過程和怎麼調用兩方面來探討下:
注意下面的示例可以在mysql管理工具中(我用的navicat)直接運行,如果要在mysql客戶端(dos窗口)需要加 delimiter$$ 分隔符。
首先來看第一個例子:
這個存儲過程有兩個int類型的輸入參數,一個varchar類型的輸出參數
在begin和end之前執行數據庫操作或是計算,
用declare聲明了一個int類型的變量,
後面是一個if 判斷,注意後面需要有then 和end if,這才是完整的if判斷
select語句進行輸出,可以直接用select ‘*’輸出,或是用as 添加一個列名
存儲過程寫好編譯無誤後,用call調用,這裡需要一個輸出參數,所以我們定義了一個@p_in變量
use etoak;
drop procedure if exists t1;
create procedure t1(in a int,in b int,out d varchar(30))
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
/* select c as sum;*/
select 's' into d;
select d as '哈哈'; -- 輸出一列
end;
/*調用存儲過程*/
set @p_in = 1;
call t1(10,1,@p_in);
上面我們使用if then條件判斷,下面來看使用case when來完成更多的條件:
drop procedure if exists t1;
create procedure t1(in a int,in b int,out c varchar(30))
begin
declare d int;
set d = a+1;
case d
when 1 then insert into student values(null,'dx',11,now());
when 2 then insert into student values(null,'aa',11,now());
else insert into student values(null,'bb',11,now());
end case;
select * from student;
end;
再來看兩個循環,一個是while do循環,一個是loop循環:
/*使用while do循環*/
create procedure t1()
begin
declare i int DEFAULT 0;
while i<5 DO
insert into student(name) values(i);
set i=i+1;
end while;
select * from student;
end;
/*使用loop循環*/
drop procedure if exists t1;
create procedure t1()
begin
declare i int DEFAULT 0;
loop_label:LOOP
if i = 3 THEN
set i = i + 1;
ITERATE loop_label; -- iterate相當於java循環裡的continue
end if;
insert into student values(null,i,i,now());
set i = i + 1;
if i >= 5 THEN
leave loop_label;
end if;
end loop;
select * from student;
end;
還有比較常用的模糊查詢:
/*模糊查詢*/
drop procedure if exists t1;
create procedure t1(in a varchar(30),out c varchar(30))
begin
declare d int;
select * from student where name like concat('%',a,'%');
end;
這個例子中要注意的是使用了concat拼接字符串函數。
二、在java代碼中如何調用存儲過程
通過上面我們知道可以在mysql客戶端裡面通過call調用存儲過程,那在java代碼裡面又是如何調用的呢
我們來看下下面的例子,使用jdbc的方式調用帶輸入輸出參數的存儲過程:
存儲過程為如下,實現簡單的加法:
create procedure t1(in a int,in b int,out d int)
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c into d;
end;
java中通過jdbc調用:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
public class TestProc {
public static void main(String[] args) throws SQLException {
TestProc tp = new TestProc();
int a = tp.testPro(5, 6);
System.out.println(a); //打印輸出值
}
//獲取數據庫連接
private static DBConnection dbConnection=null;
static {
if (null == dbConnection) {
dbConnection = new DBConnection();
}
}
//執行存儲過程的方法
public int testPro(int a,int b) throws SQLException{
Connection conn = null;
CallableStatement stmt = null;
int out = 0;
String sql="";
try {
conn = dbConnection.getConnection();
stmt = conn.prepareCall("{call t1(?,?,?) }");
stmt.setInt(1, a);
stmt.setInt(2, b);
stmt.registerOutParameter(3, Types.INTEGER);
stmt.execute();
out = stmt.getInt(3); //這裡獲取下輸出參數
}finally {
dbConnection.close(conn);
dbConnection.close(stmt);
}
return out;
}
}
mybatis中存儲過程的調用:
聲明接口:
public Map proc(Map map);
xml:
<select id="proc" parameterType="map" statementType="CALLABLE"> {call t1( #{firstParam,jdbcType=INTEGER,mode=IN}, #{secondParam,jdbcType=INTEGER,mode=IN}, #{outParam,jdbcType=INTEGER,mode=OUT} )} </select>
測試:
Map map = new HashMap();
map.put("firstParam",1);
map.put("second", 2);
bi.proc(map);
System.out.println(map.toString());
這裡注意一下:
mybatis的入參map裡面不需要put輸出參數,執行完存儲過程之後,會自動把輸出參數放到map裡面。所以我們的打印結果如下:
{second=2, firstParam=1, outParam=1}