设为首页收藏本站
查看: 46|回复: 0

[PHP] MySQL存储过程实例_MySQL-mysql教程

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2017-5-12 16:11:00 | 显示全部楼层 |阅读模式
    虽然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) ) BEGIN DECLARE 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() 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 ;   

    知识点:
      
    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);
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表