MySQL Stored procedure Example 1

Delimiter//drop procedure if exists p_prelogin//create procedure P_prelogin (in In_username varchar (+), in In_password varchar (+), in In_asshkey varchar (on in_usshkey varchar), in In_gameid tinyint, In_logintype tinyint) Proc_la Bel:begindeclare result int default 0;declare res_aid int default 0;declare res_uid int default 0;declare res_srvid int D Efault 0;declare res_status tinyint default 0;declare res_cnt int default 0;declare res_sshkey varchar (all) default Null;de Clare Res_ip varchar (+) default null;declare res_port int default 0;declare res_out int default 0;--according to username and Passwo Rd query out UID, aid, cannot find return error. Select Uid,aid into Res_uid,res_aid from tb_user where username = in_username and password = In_ Password;if COALESCE (res_uid, 0) = 0 Thenset res_out = -1;select res_out as result; LEAVE Proc_label; END if;--Platform side judgment: Find tb_access based on aid, verify information status Sshkeyselect status,sshkey into Res_status,res_sshkey from tb_access wh Ere aid = res_aid;if Res_status <> 1 tHenset res_out = -2;select res_out as result; LEAVE Proc_label; END if;--Compare Server sshkey IF in_asshkey <> res_sshkey thenset res_out = -3;select res_out as result; LEAVE Proc_label; END if;--User side judgment: Determine whether the user has permission to participate in the game. Select status into Res_status from tb_usergame where aid = res_aid;if COALESCE (res_status, 0) = 0 Thenset res_out = -4;s Elect res_out as result; LEAVE Proc_label; END if;--srv_id, IP, Portselect Srv_id,ip,port into res_srvid,res_ip,res_port based on GameID and Logintype Tb_allserve r where GameID = In_gameid and Logintype = in_logintype;if coalesce (res_srvid, 0) = 0 Thenset res_out = -5;select Res_ou T as result; LEAVE Proc_label; END if;--update tb_prelogin includes status = 1. Login Lasttime = last time, Sshkey = MD5 (random number +lasttime+rid), Srv_id,logintype;select count (*) into res_cnt from Tb_prelogin where UI D = res_uid;if Coalesce (res_cnt, 0) = 0 theninsert into Tb_prelogin (UID, status, Lasttime, Sshkey, srv_id, Logintype) v Alues (Res_uid, 1, now (), In_usshkey, Res_srvid, In_logintype);Elseupdate tb_prelogin SET status = 1, Lasttime = Now (), Sshkey = In_usshkey, srv_id = res_srvid, Logintype = In_logintype where uid = Res_uid; END If;select res_out as err, res_uid as UserID, Res_port as Port, res_ip as Ip;end//delimiter;

　　

MySQL Stored procedure Example 1