mysql存储过程实例

DELIMITER //
DROP PROCEDURE IF EXISTS mingRenTangJiangLi //
CREATE PROCEDURE mingRenTangJiangLi()
BEGIN
DECLARE 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;

REPEAT
set 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 = 1
END REPEAT;

CLOSE  my_cursor;

END //
DELIMITER ;


调用：call mingRenTangJiangLi()

spring的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;
}