俗话说工欲善其事,必先利其器
通过对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;