虽然mysql的存储过程，一般情况下，是不会使用到的，但是在一些特殊场景中，还是有需求的。最近遇到一个sql server向mysql迁移的项目，有一些sql server的存储过程需要向mysql迁移。所以进行复习了一下。下面是一些存储过程的例子。
1. 例子1
delimiter //drop procedure if exists loginandreg //create procedure loginandreg(out userid bigint,in user_pwd varchar(32),in user_mobilecode varchar(16),in user_regip varchar(16))begindeclare cnt bigint default 0;declare cnt2 bigint default 0;declare outid bigint default -1;select count(*) into cnt from users u where u.user_mobilecode=user_mobilecode;if cnt > 0 then select count(*) into cnt2 from users u where u.user_mobilecode=user_mobilecode and u.user_pwd=user_pwd; if cnt2 > 0 then select u.userid into outid from users u where u.user_mobilecode=user_mobilecode and u.user_pwd=user_pwd limit 1; else select -1 into outid; end if; select outid into userid;else insert into users(user_pwd,user_mobilecode,user_visibility,user_level,user_regtime, user_regip,user_collecter,user_collected) values (user_pwd,user_mobilecode,6,6,now(),user_regip,0,0); set userid=last_insert_id(); select userid;end if;end //delimiter ;
知识点：
1）参数分为 in, out 类型，即输入类型和输出类型；
2）select xx into varible from table where ... 句式：
select count(*) into cnt from users u where u.user_mobilecode=user_mobilecode;
3）if cnt > 0 then ... elseif cnt =0 then ... else ... end if;
if 语句注意带有 then 关键字和 end if 结束关键字。
4）获取 insert 语句的主键：set userid=last_insert_id(); select userid;
select last_insert_id() into userid; 也是可以的。
5）如何调用该存储过程：
call loginandreg(@userid,'112358','18357xxx7','127.0.0.1');
select @userid;
最后的 select @userid 就是存储过程的 out 类型参数返回的结果。
2. 例子2
delimiter //drop procedure if exists mingrentangjiangli //create procedure mingrentangjiangli()begindeclare total_level,role_id,ming_ren_level,ming_ren_type, fuben_times,tiaozhan_times,duobei_shijian,no_more_data int default 0;declare my_cursor cursor for select playerroleid,`level`,type from mingrentang;declare continue handler for not found set no_more_data = 1;open my_cursor;fetch my_cursor into role_id,ming_ren_level,ming_ren_type;repeatset total_level = ming_ren_level + 10 * (ming_ren_type-1);set fuben_times = total_level / 2;set tiaozhan_times = total_level /3;set duobei_shijian = 10 * total_level;select total_level,fuben_times,tiaozhan_times,duobei_shijian;update player_role set hufu=hufu+1000,paihangbangnumber=paihangbangnumber+tiaozhan_times, duobeishijian=duobeishijian+duobei_shijian,fubentimes=fubentimes+fuben_times;fetch my_cursor into role_id,ming_ren_level,ming_ren_type;until no_more_data = 1end repeat;close my_cursor;end //delimiter ;
知识点：
1）该例子演示了游标的用法：
declare my_cursor cursor for select playerroleid,`level`,type from mingrentang;
declare continue handler for not found set no_more_data = 1;
定义了游标语句，也说明了游标循环结束时设置的标志：set no_more_data = 1;
open my_cursor;
fetch my_cursor into role_id,ming_ren_level,ming_ren_type;
打开游标，从游标中获取值。
repeat
......
fetch my_cursor into role_id,ming_ren_level,ming_ren_type;
until no_more_data = 1
end repeat;
repeat 循环 直到 no_more_data = 1： until no_more_data = 1，然后结束循环 end repeat;
最后关闭游标 close my_cursor;
因为上面在定义游标时，指明了，没有数据时设置了 no_more_data = 1，所以这里使用 until no_more_data = 1 来退出repeat
2）判断相等是使用 = ，而不是 == ，赋值操作是使用 set var=xxx; ：set fuben_times = total_level / 2;
3. java 如何调用存储过程
1）hibernate调用存储过程：
/* * 调用无参数的存储过程，传入存储过程名字 */ public int callprocedure(final string procedurename) { int count = (integer)this.gethibernatetemplate().execute( new hibernatecallback(){ public object doinhibernate(session session) throws hibernateexception, sqlexception { string proceduresql = {call + procedurename +()}; query query = session.createsqlquery(proceduresql); integer num = query.executeupdate(); return num; } }); return count; }
2）ibatis 调用mysql 存储过程：
@override public long loginandregbyprocedure(string user_pwd, string user_mobilecode, string user_regip){ long userid = null; hashmap parammap = new hashmap(); parammap.put(userid, userid); parammap.put(user_pwd, user_pwd); parammap.put(user_mobilecode, user_mobilecode); parammap.put(user_regip, user_regip); this.getsqlmapclienttemplate().queryforobject(users.loginandregbyprocedure, parammap); return (long)parammap.get(userid); }
对应的xml 文件配置：
{call loginandreg(?, ?, ?, ?)}
存储过程的参数的类型，是在xml文件中说明的。
3) jdbc 调用mysql 存储过程：
public long loginandregbyprocedure2(string user_pwd, string user_mobilecode, string user_regip){ connection conn = dbutil.getconnection(); callablestatement cstmt = conn.preparecall({call loginandreg(?, ?, ?, ?)}); cstmt.setstring(2, user_pwd); cstmt.setstring(3, user_mobilecode); cstmt.setstring(4, user_regip); cstmt.registeroutparameter(1, java.sql.types.bigint); cstmt.execute(); return cstmt.getlong(1); }
输入参数：cstmt.setstring(2, user_pwd);
输出参数：cstmt.registeroutparameter(1, java.sql.types.bigint);