吱吱z
2015-08-31 02:30
采纳率: 100%
浏览 1.8k

sql语句出错,求大神帮忙解答

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

看了好久,可是感觉没有错误,不知道毛病出在哪里,求大神解答
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • 微甜灬呼吸 2015-08-31 03:05
    已采纳

    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'
    这个只定义一个

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 吱吱z 2015-08-31 02:35

    想实现的功能是:

    根据表中company 的字段去判断,不同的company, 赋予days变量不同天数,然后让当前时间-days跟checktime去做比较,最后执行update语句。。。

    其实就是更改status状态,来实现 链接失效 这个功能,求大神们指导,刚入行的新人。轻喷。。

    评论
    解决 无用
    打赏 举报
  • 吱吱z 2015-08-31 03:29

    可以啦,谢谢 = =

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题