CREATE PROCEDURE test()
BEGIN
DECLARE fid int;
DECLARE days int DEFAULT 1;
DECLARE s int default 0;
DECLARE company_code VARCHAR;
DECLARE cursor_company CURSOR FOR SELECT company FROM test WHERE status = 3;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET a=1;
OPEN cursor_company;
FETCH cursor_company INTO company_code;
WHILE a<>1 DO
IF company_code = 'DD' THEN
SET days = 2;
END IF;
ELSEIF company_code ='ZD' THEN
SET days = 5;
END ELSEIF;
ELSEIF company_code ='GG' THEN
SET days = '8';
END ELSEIF;
FETCH cursor_company INTO company_code;
DECLARE cursor_name CURSOR FOR SELECT id FROM test WHERE UNIX_TIMESTAMP(check_time) < UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL -days DAY));
#设置一个终止标记
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
#打开游标
OPEN cursor_name;
#获取游标当前指针的记录,读取一行数据并传给变量stu
FETCH cursor_name INTO fid;
#开始循环,判断是否游标已经到达了最后作为循环条件
WHILE s <> 1 do
UPDATE test SET status = 8 where id=fid;
#读取下一行的数据
FETCH cursor_name INTO fid;
END WHILE;
#关闭游标
CLOSE cursor_name ;
#语句执行结束
END WHILE;
CLOSE cursor_company;
END
表结构
id int
check_time datetime
status int
company varchar
错误信息:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
DECLARE cursor_company CURSOR FOR SELECT company FROM test WHERE status =' at line 10
看了好久,可是感觉没有错误,不知道毛病出在哪里,求大神解答
CREATE PROCEDURE test ()
BEGIN
DECLARE
fid INT;
DECLARE
days INT DEFAULT 1;
DECLARE
s INT DEFAULT 0;
DECLARE
a INT DEFAULT 0;
DECLARE
company_code VARCHAR (50);
DECLARE
cursor_company CURSOR FOR SELECT
company
FROM
test
WHERE
STATUS = 3;
DECLARE
cursor_name CURSOR FOR SELECT
id
FROM
test
WHERE
UNIX_TIMESTAMP(check_time) < UNIX_TIMESTAMP(
DATE_ADD(NOW(), INTERVAL - days DAY)
);
#设置一个终止标记
DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000'
SET s = 1;
OPEN cursor_company;
FETCH cursor_company INTO company_code;
WHILE s <> 1 DO
IF company_code = 'DD' THEN
SET days = 2;
ELSEIF company_code = 'ZD' THEN
SET days = 5;
ELSEIF company_code = 'GG' THEN
SET days = '8';
END IF;
FETCH cursor_company INTO company_code;
#打开游标
OPEN cursor_name;
#获取游标当前指针的记录,读取一行数据并传给变量stu
FETCH cursor_name INTO fid;
#开始循环,判断是否游标已经到达了最后作为循环条件
WHILE s <> 1 DO
UPDATE test
SET STATUS = 8
WHERE
id = fid;
set s = 0;
#读取下一行的数据
FETCH cursor_name INTO fid;
END
WHILE;
#关闭游标
CLOSE cursor_name;
#语句执行结束
END
WHILE;
CLOSE cursor_company;
END
给你格式化了一下
不然有点乱乱
DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000'
这个只定义一个
想实现的功能是:
根据表中company 的字段去判断,不同的company, 赋予days变量不同天数,然后让当前时间-days跟checktime去做比较,最后执行update语句。。。
其实就是更改status状态,来实现 链接失效 这个功能,求大神们指导,刚入行的新人。轻喷。。
可以啦,谢谢 = =