存储过程实例
来源:互联网 发布:后街女孩知乎 编辑:程序博客网 时间:2024/05/20 20:01
函数:
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
- 存储过程几个实例
- Mysql存储过程实例
- 存储过程经典实例
- 存储过程实例(一)
- 存储过程实例(二)
- ORcale 存储过程实例
- 存储过程 实例
- 存储过程实例
- Oracle 存储过程实例
- 存储过程知识点(实例)
- MySQL存储过程实例
- Mysql存储过程实例
- Oracle存储过程实例
- 存储过程实例
- MySQL存储过程实例
- Mysql存储过程实例
- 存储过程实例
- oracle 存储过程实例
- 【题】【状压&搜索】NKOJ 3751 扫雷游戏
- 使用Jenkins搭建Android自动化构建--安装篇
- Linux配置Selenium+Chrome+Python实现自动化测试
- mac上更新ruby
- CNTK学习笔记 -- Computational Network Toolkit part1 -- Network Builder and Learner
- 存储过程实例
- runtime从入门到精通(一)—— 初识runtime
- PHP获取函数的调用者名称
- 防止按钮在短时间内被多次点击
- flask传送post参数和token
- [转]【坐在马桶上看算法】算法12:堆——神奇的优先队列(下)--作者:ahalei
- queue.h usage
- 机房收费系统—上下机
- C#之二维数组学习案例