我的mysql存储查询过程有什么问题?

l0oc07j2  于 2021-06-24  发布在  Mysql
关注(0)|答案(2)|浏览(435)

我是mysql存储过程的新手。我希望我的存储过程检查字段date中的日期值并与当前日期进行比较。如果当前日期大于表中的日期值,我想将字段status\u number中的值更新为“0”。
我的问题是:

DELIMITER $$
DROP PROCEDURE IF EXISTS check_status$$
CREATE PROCEDURE check_status()

 BEGIN
     DECLARE dd DATE;
     DECLARE bDone INT;
     DECLARE Count INT;
     DEClARE my_status CURSOR FOR SELECT dates FROM t_date;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

 OPEN my_status;
    SET bDone = 0;

    REPEAT
        FETCH my_status INTO dd;

        IF (dd < DATE(NOW()))

                UPDATE t_date SET (status_number) VALUES (1);

        END IF;

    UNTIL bDone END REPEAT;

 CLOSE my_status;

 END$$

DELIMITER ;

错误消息:

谢谢你的回答。

mu0hgdu0

mu0hgdu01#

DELIMITER $$
    DROP PROCEDURE IF EXISTS check_status$$
    CREATE PROCEDURE check_status()

     BEGIN
         DECLARE dd DATE;
         DECLARE bDone INT;
         DECLARE Count INT;
         DEClARE my_status CURSOR FOR SELECT dates FROM t_date;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

     OPEN my_status;
        SET bDone = 0;

        REPEAT
            FETCH my_status INTO dd;

            IF (dd < DATE(NOW()))
--                  UPDATE t_date SET (status_number) VALUES (1);
                    UPDATE t_date SET status_number =  1;

            END IF;

        UNTIL bDone END REPEAT;

     CLOSE my_status;

     END$$

    DELIMITER ;
rkkpypqq

rkkpypqq2#

检查一下。它在mssql上进行了测试。我尝试将其转换为mysql,但没有在mysql上测试,但我希望它能工作。

DELIMITER //

CREATE PROCEDURE check_status()

BEGIN
DECLARE NOT_FOUND INT DEFAULT 0;

DECLARE v_dd DATE;
-- DECLARE @bDone INT;
-- DECLARE @Count INT;

DECLARE v_name VARCHAR(50); -- database name 
DECLARE v_path VARCHAR(256); -- path for backup files 
DECLARE v_fileName VARCHAR(256); -- filename for backup 
DECLARE v_fileDate VARCHAR(20); -- used for file name 

DECLARE db_cursor CURSOR FOR 

SELECTdates INTO v_dd 
FROM t_date

OPEN 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_FOUND = 1;db_cursor  
 -- SET @bDone = 0;
FETCH NEXT FROM; db_cursor INTO v_dd  

WHILE NOT_FOUND = 0  
DO  

        IF (v_dd < cast (Now() as date) )
        Then
                    UPDATE t_date SET status_number   =  1;
        END IF; 

      FETCH NEXT FROM; db_cursor INTO v_dd  
END WHILE; 

CLOSE db_cursor;  
END;
//

DELIMITER ;

相关问题