首先，需要执行符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>