MySQL stored procedure instance _ MySQL

Although MySQL stored procedures are generally not used, they are still required in some special scenarios. Recently, I encountered a project for SQL Server Migration to mysql. some SQL Server stored procedures need to be migrated to mysql. So I reviewed it. Although MySQL stored procedures are generally not used, they are still required in some special scenarios. Recently, I encountered a project for SQL server Migration to mysql. some SQL server stored procedures need to be migrated to mysql. So I reviewed it. The following are examples of stored procedures.

1. Example 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 ;

Knowledge Point:

1) parameters are divided into in and out types, that is, input and output types;

2) select xx into varible from table where... sentence:

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;

Note that the if statement contains the then keyword and the end if end keyword.

4) obtain the primary key of the insert statement: set userId = last_insert_id (); select userId;

Select last_insert_id () into userId; is also acceptable.

5) How to call the stored procedure:

CALL loginandreg (@ userId, '000000', '18357xxx7 ', '192. 0.0.1 ');

SELECT @ userId;

The final select @ userId is the result returned by the out type parameter of the stored procedure.

2. Example 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 ;

Knowledge Point:

1) This example demonstrates the usage of the cursor:

DECLARE my_cursor cursor for select playerRoleId, 'level', type from mingrentang;

Declare continue handler for not found set no_more_data = 1;

The cursor statement is defined, and the flag SET when the cursor loop ends: SET no_more_data = 1;

OPEN my_cursor;

FETCH my_cursor INTO role_id, ming_ren_level, ming_ren_type;

Open the cursor and obtain the value from the cursor.

REPEAT

......

FETCH my_cursor INTO role_id, ming_ren_level, ming_ren_type;

UNTIL no_more_data = 1

End repeat;

Repeat loop UNTIL no_more_data = 1: UNTIL no_more_data = 1, and then END the loop end repeat;

Close the cursor close my_cursor;

Because the cursor is defined above, and no_more_data = 1 is set when no data exists, here we use UNTIL no_more_data = 1 to exit repeat

2) use = instead of = to determine equality, and set var = xxx for the value assignment operation;: set fuben_times = total_level/2;

3. how does Java call stored procedures?

1) hibernate calls the stored procedure:

/** Call a stored procedure without parameters and pass in the stored procedure name */public int callProcedure (final String procedureName) {int count = (integer=this.gethibernatetemplate(cmd.exe cute (new HibernateCallback () {public Object doInHibernate (Session session) throws HibernateException, SQLException {String procedureSql = "{call" + procedureName + "()}"; Query query Query = session. createSQLQuery (procedureSql); Integer num = query.exe cuteUpdate (); return num ;}}); return count ;}

2) ibatis calls the mysql stored procedure:

    @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");     }
  
 

Corresponding xml file configuration:

  
       
        
        
        
    
   
 
        {call loginandreg(?, ?, ?, ?)}  
 

The type of stored procedure parameters is described in the xml file.

3) JDBC calls the mysql stored procedure:

    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);    }

Input parameter: cstmt. setString (2, user_Pwd );

Output parameter: cstmt. registerOutParameter (1, java. SQL. Types. BIGINT );