Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1585740
  • 博文数量: 413
  • 博客积分: 8399
  • 博客等级: 中将
  • 技术积分: 4324
  • 用 户 组: 普通用户
  • 注册时间: 2011-06-09 10:44
文章分类

全部博文(413)

文章存档

2015年(1)

2014年(18)

2013年(39)

2012年(163)

2011年(192)

分类: Mysql/postgreSQL

2014-04-08 00:55:10

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模块调用:

点击(此处)折叠或打开

  1. /*
  2.      * 调用无参数的存储过程,传入存储过程名字
  3.      */
  4. public int callProcedure(final String procedureName)
  5. {
  6.         int count =
  7.     (Integer)this.getHibernateTemplate().execute(
  8. new HibernateCallback(){
  9.             public Object doInHibernate(Session session)
  10. throws HibernateException, SQLException {
  11.                 String procedureSql = "{call "+ procedureName +"()}";
  12.                 Query query = session.createSQLQuery(procedureSql);
  13.                 Integer num = query.executeUpdate();
  14.                 return num;
  15.             }
  16.         });
  17.         return count;
  18. }

阅读(1023) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~