关闭

存储过程实例

标签: 存储函数
201人阅读 评论(0) 收藏 举报
分类:

函数:

BEGIN
DECLARE status_p int DEFAULT 0 ;
DECLARE status_e int DEFAULT 0 ;
select count(1) into status_p from tms_member where name = names and `password` = passwords limit 1;
if status_p > 0 THEN
return 114;
END if;
select count(1) into status_e from tms_member where  `email` = emails limit 1;
if status_e > 0 THEN
return 118;
END if;
return 0;
END


存储过程:

BEGIN

 DECLARE num int;
 DECLARE sqlstatus int;
 DECLARE returnCode int;
 DECLARE showStatus varchar(20) DEFAULT '';
 DECLARE showName varchar(20) DEFAULT '';
 DECLARE deviceMac varchar(20) DEFAULT '';
 DECLARE var_status varchar(20) DEFAULT '';
 DECLARE var_username varchar(20) DEFAULT '';
 DECLARE var_mac varchar(20) DEFAULT '';
 DECLARE done INT DEFAULT 0;
 DECLARE sqlstr cursor for 
 select p1.status,p3.serialNo as mac from tms_member p1
 LEFT JOIN tms_member_terminal_bind p2 on p1.id =p2.member_id
 LEFT JOIN tms_terminal_infos p3 on p2.terminal_id = p3.id
 where p1.`name`=username and p1.`password`=password and p3.mac_address=in_mac;
 DECLARE sqlstr2 cursor for 
 select p4.name  from tms_member p4
 where p4.`name`=username and p4.`password`=password;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN sqlstr2;
 REPEAT
 FETCH sqlstr2 INTO showName;
 IF NOT done THEN
 set var_username = showName;
 end if;
 UNTIL done end repeat;
 close sqlstr2;
OPEN sqlstr;
 REPEAT
 FETCH sqlstr INTO showStatus,deviceMac;
 IF NOT done THEN
 set var_status = showStatus;
 set var_mac = deviceMac;
 end if;
 UNTIL done end repeat;
 close sqlstr;

 if  var_username = "" THEN
     set returnCode = 103;
 ELSEIF var_mac = "" THEN
set returnCode = 900;
 ELSEIF  var_status = "1" THEN
     set returnCode = 119;
 ELSE
select count(*) into num from tms_member 
where  name=username  and `password`=password
and now() between token_create_time and token_expire_time limit 1;
 
if num = 0 THEN
update tms_member 
set token = token ,`token_create_time` = now(),token_expire_time = date_add(now(),INTERVAL 3 MONTH),login_count=login_count+1,
          last_login_time = now(),last_ip = ip
where  name=username  and `password`=password;
END IF;  
set sqlstatus = ROW_COUNT();
        if sqlstatus > 0 then
if num > 0 THEN
set returnCode = 2;
ELSE
set returnCode = 1;
end if;
else
set returnCode = 103;
end if;
 END IF;
select returnCode;

END


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:21943次
    • 积分:403
    • 等级:
    • 排名:千里之外
    • 原创:21篇
    • 转载:4篇
    • 译文:0篇
    • 评论:1条
    文章分类
    最新评论