mysql存储过程之实战篇

俗话说工欲善其事，必先利其器

通过对mysql存储过程基本语法，控制语句，异常处理，光标等的学习，下面是在实际项目中，写了两个mysql的存储过程

 

代码1：

/*====================================================================*/
/* 说明:  用户充值的存储过程，如果充值成功返回用户的账户余额，失败返回0
/* AUTHOR: 
/* CREATE: 
/* UDPATE：
/*====================================================================*/
CREATE DEFINER=`xxxx`@`%` PROCEDURE `pro_recharge`(in userid varchar(100),in nowx date,in cardpsw varchar(100))
begin
declare idx int;
declare snvaluex int;
declare result int DEFAULT 0;

select ID,SNValue into idx,snvaluex  from Recharge_Log where ValidTime>nowx and  Flag=1 and SNPsw=cardpsw;

if idx is null or  snvaluex is null then
	select result;
else
	SET autocommit=0;
		insert into Recharge_UserID(SNID,UserID) values (idx,userid);
		update User_Info set FeeValue=FeeValue + snvaluex,Points=Points +snvaluex*0.1 where UserID=userid;
		update Recharge_Log set Flag=0 where SNPsw=cardpsw;
	COMMIT;
	select FeeValue into result from User_Info where UserID=userid limit 1;
end if;
select result;
end

 

代码2：

/*====================================================================*/
/* 说明:  用户注册的存储过程，完成用户的账户注册,成功返回用户名和密码，失败返回0
/* AUTHOR: 
/* CREATE: 
/* UDPATE：
/*====================================================================*/
CREATE DEFINER=`xxxx`@`%` PROCEDURE `pro_register`(
in cldc char(20),in midp char(20),in phone char(30),in name char(10),in mobile char(11),in city int,
in psw char(32),in usertype char(10),in recommended char(10),in logintime date)

begin
declare useridx int;
declare result int DEFAULT 0;

if mobile='13911113xxxx' and psw='xxxxxx' then
	set useridx = 10008888;
else
	select UserID into useridx from User_ID order by rand() limit 1;
end if;

if useridx is null then
	select result;
else
	SET autocommit = 0;
		delete from User_ID where UserID=useridx;
		insert into User_Info (UserID, Mobile, Name, Psw, City, Cldc, Midp, Phone, Recommended,UserType, LoginTime) Values 
		(useridx, mobile, name, psw, city, cldc, midp, phone, recommended, usertype, logintime);
	COMMIT;
	select useridx,psw;
end if;
end;