下午自己写的,公司产品用到,第一次写存储过程。发上来代码。不做详细解释,有疑问可以问我。
DELIMITER $$DROP PROCEDURE IF EXISTS `tsts_user`.`proc_login`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_login`(id varchar(50), pwd varchar(255), pTypeUser int, ServerIP varchar(20), ip varchar(50), out result int)begin#返回记录#declare ploginid varchar(50);set @ploginid = null;set @ppwd = null;set @pUserType = null;set @pEndTime = null;set result = null;select @ploginid:=`loginid` ,@ppwd:=`password`,@pUserType:=ptype,@pEndTime:=endtime,name,groupname,area,notlogin,validdate,viewnews,viewgp,viewqh,viewwp,viewwh,viewgg,userlevel, memo,viewgp_max,viewqh_max,viewwp_max,viewwh_max,viewgg_max,usercfg,begintime,usedtotaltime, isonline,serverip from product p where loginid=id;#如果帐户不存在if(@ploginid is null) thenset result = 1;else#如果帐户存在,但是密码不正确if(@ppwd is null or @ppwd != pwd) thenset result = 2;else#如果帐户存在,并且密码正确,但是过期了if(@pEndTime is null or now() > @pEndTime) thenset result = 3;else#如果帐户存在、密码正确、没过期,但是登录类型不正确if(@pUserType is null or pTypeUser not in (0, 16, 18, @pUserType)) thenset result = 4;else##全部正确,进行更新update product set last_access_time=now(),login_times=login_times+1,isOnline=1,serverip=ServerIP,last_access_ip=ip where loginid=id;end if;end if;end if;end if;#如果result为空则改变成为0if(result is null) thenset result = 0;end if;end$$DELIMITER ;