Although MySQL stored procedures, in general, will not be used, but in some special scenarios, there is still demand. Recently encountered a project that SQL Server migrated to MySQL, there are some SQL Server stored procedures that need to be migrated to MySQL.
Example 1
DELIMITER//DROP PROCEDURE IF EXISTSLoginandreg//CREATE PROCEDURELoginandreg (out UserIdBIGINT,inchUser_pwdVARCHAR( +),inchUser_mobilecodeVARCHAR( -),inchUser_regipVARCHAR( -))BEGINDECLARECntBIGINT DEFAULT 0;DECLARECnt2BIGINT DEFAULT 0;DECLAREOutidBIGINT DEFAULT -1;SELECT COUNT(*) intoCnt fromUsers uWHEREU.user_mobilecode=User_mobilecode;IFCnt> 0 Then SELECT COUNT(*) intoCnt2 fromUsers uWHEREU.user_mobilecode=User_mobilecode andU.user_pwd=user_pwd; IFCnt2> 0 Then SELECTU.userid intoOutid fromUsers uWHEREU.user_mobilecode=User_mobilecode andU.user_pwd=User_pwd LIMIT1; ELSE SELECT -1 intoOutid; END IF; SELECTOutid intouserId;ELSE INSERT intoUsers (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); SETUserid=last_insert_id (); SELECTuserId;END IF;END //DELIMITER;
MySQL Stored Procedures