MySQL 存储过程实例_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<String,Object> paramMap = new HashMap<String,Object>();          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 文件配置：

  <parameterMap id="pro_pram_Map" class="java.util.Map">      <parameter property="userId" javaType="java.lang.Long" jdbcType="BIGINT" mode="OUT"/>      <parameter property="user_Pwd" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>      <parameter property="user_MobileCode" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>      <parameter property="user_RegIP" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>  </parameterMap>  <procedure id="loginAndRegByProcedure" parameterMap="pro_pram_Map">      {call loginandreg(?, ?, ?, ?)}  </procedure>

存储过程的参数的类型，是在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);