mysql存储过程且mybatis调用

　　首先，需要执行符DELIMITER ，建议用//,即在存储过程开始前定义delimiter //，在结束后加上//,最后加上DELIMITER ; 具体原因@参考文章1写的很清楚，不再赘述。

参考文章1中的示例：

delimiter //;     -- 改变 MySQL delimiter 为：“//”   
  
drop procedure if exists pr_stat_agent //   
  
-- call pr_stat_agent ('2008-07-17', '2008-07-18')   
  
create procedure pr_stat_agent   
(   
   pi_date_from  date   
  ,pi_date_to    date   
)   
begin   
   -- check input   
   if (pi_date_from is null) then   
      set pi_date_from = current_date();   
   end if;   
  
   if (pi_date_to is null) then   
      set pi_date_to = pi_date_from;   
   end if;   
  
   set pi_date_to = date_add(pi_date_from, interval 1 day);   
  
   -- stat   
   select agent, count(*) as cnt   
     from apache_log   
    where request_time >= pi_date_from   
      and request_time <  pi_date_to   
    group by agent   
    order by cnt desc;   
end; //   
  
delimiter ; //   -- 改回默认的 MySQL delimiter：“;”   

 删除存过程

drop procedure p_name;

完整示例：

 

mybatis调用：

<!-- 根据用户id获取姓名Zhangyn，以下两种方法等效 -->
  <select id="getUserName" resultType="java.lang.String">
      <!-- select name from user where id=#{0} -->
      {call yanan.get_user_name(#{0,jdbcType=INTEGER,mode=IN})}  
  </select>

 更新：

mysql> use zyt;
Database changed
mysql> delimiter //
mysql> create procedure updateordercountbyid(in cid int,in ordercount int,in personinfoid int)
    -> begin
    -> update t_cartgoods set orderCount=ordercount where cartGoodsId=cid and personInfoId=personinfoid;
    -> end //
Query OK, 0 rows affected

mysql> 

<!--  更新购物车购买数量,以下两种方法等效 -->
    <update id="updateOrderCountByCartId" >
        <!-- update t_cartgoods set orderCount=#{1} where cartGoodsId=#{0} and personInfoId=#{2} -->
        {call yanan.updateordercountbyid(#{0,jdbcType=INTEGER,mode=IN},#{1,jdbcType=INTEGER,mode=IN},#{2,jdbcType=INTEGER,mode=IN})}
    </update>

 条件：

mysql> use yanan;
Database changed
mysql> delimiter //
mysql> create procedure test(in i int) begin declare t int;if(i<=2) then set t=3;else set t=1;end if;select * from user where id=t;end //
Query OK, 0 rows affected
mysql> delimiter ;
mysql> call test(0);
+----+------+
| id | name |
+----+------+
|  3 | 1234 |
+----+------+
1 row in set

Query OK, 0 rows affected

mysql> call test(1);
+----+------+
| id | name |
+----+------+
|  3 | 1234 |
+----+------+
1 row in set

Query OK, 0 rows affected

mysql> call test(2);
+----+------+
| id | name |
+----+------+
|  3 | 1234 |
+----+------+
1 row in set

Query OK, 0 rows affected

mysql> call test(3);
+----+-------+
| id | name  |
+----+-------+
|  1 | yanan |
+----+-------+
1 row in set

Query OK, 0 rows affected

mysql> call test(4);
+----+-------+
| id | name  |
+----+-------+
|  1 | yanan |
+----+-------+
1 row in set

Query OK, 0 rows affected

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | yanan |
|  2 | zhang |
|  3 | 1234  |
|  4 | 7890  |
+----+-------+
4 rows in set

mysql> 

 

 循环

mysql> drop procedure test;
Query OK, 0 rows affected

mysql> delimiter //
mysql> create procedure test(in i int) begin declare t int;
    -> while i<4 do 
    -> set i=i+1;end while; set t=i;select * from user where id=t;end //
Query OK, 0 rows affected
mysql> delimiter ;
mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | yanan |
|  2 | zhang |
|  3 | 1234  |
|  4 | 7890  |
+----+-------+
4 rows in set

mysql> call test(-1);
+----+------+
| id | name |
+----+------+
|  4 | 7890 |
+----+------+
1 row in set

Query OK, 0 rows affected

mysql> call test(4);
+----+------+
| id | name |
+----+------+
|  4 | 7890 |
+----+------+
1 row in set

Query OK, 0 rows affected

mysql> call test(5);
Empty set

Query OK, 0 rows affected

mysql>