mysql - 程序错误进入条件

Question

当我尝试执行 mysql 时，我有一个程序显示错误，该错误出现在下一个条件中:

IF END_GAL > 0 THEN    
 SET DIFF = (END_GAL - INI_GAL);
 SET V_DIFF = CAST(DIFF AS VARCHAR(50));
 SET V_END_GAL = CAST(END_GAL AS VARCHAR(50));
ELSE
 SET V_DIFF = 'N/A';
 SET V_END_GAL = 'NOT UPDATED';
END IF;

我包含所有程序，mysql显示消息:

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 'VARCHAR(50)); SET V_END_GAL = CAST(END_GAL AS VARCHAR(50));' at line 46

DELIMITER $$
CREATE  PROCEDURE `hourly_gas_change`(IN dateReport varchar(50), IN tank INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE INI_DATE DATE;
DECLARE INI_GAL INT;
DECLARE END_GAL INT;
DECLARE DIFF INT;
DECLARE V_HOUR VARCHAR(50);
DECLARE V_INI_GAL VARCHAR(50);
DECLARE V_END_GAL VARCHAR(50);
DECLARE V_DIFF VARCHAR(50);
DECLARE INITIALDATA CURSOR FOR
SELECT
      DATE,
      GALLONS 
      FROM TLS_TEMP_DATA
      WHERE FK_TANK = tank
      AND DATE LIKE '%:01:%' 
      AND (DATE BETWEEN CONCAT(dateReport, ' 00:00:00') AND CONCAT(dateReport, ' 10:59:59'))
      ORDER BY ID;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DROP TABLE IF EXISTS HOURLYGASCHANGE;
CREATE TEMPORARY TABLE IF NOT EXISTS HOURLYGASCHANGE (
    `HOUR_DATA` VARCHAR(50) NOT NULL,
    `INI_GAL` VARCHAR(50) NOT NULL,
    `END_GAL` VARCHAR(50) NOT NULL,
    `DIFF` VARCHAR(50) NOT NULL)
     ENGINE=MEMORY; 
OPEN INITIALDATA;
READ_LOOP: LOOP
    FETCH INITIALDATA INTO INI_DATE, INI_GAL;
    IF done THEN
      LEAVE READ_LOOP;
    END IF;
    SET END_GAL = -1;
    SELECT
     GALLONS INTO END_GAL
    FROM TLS_TEMP_DATA
    WHERE FK_TANK = tank
     AND DATE LIKE CONCAT(DATE_FORMAT(DATE_ADD(DATE_FORMAT(INI_DATE, '%Y-%m-%d %H'), INTERVAL 1 HOUR), '%Y-%m-%d %H'),':%')
     AND (DATE LIKE '%:01:%' OR DATE LIKE '%:06:%') 
    ORDER BY DATE;

    IF END_GAL > 0 THEN    
     SET DIFF = (END_GAL - INI_GAL);
     SET V_DIFF = CAST(DIFF AS VARCHAR(50));
     SET V_END_GAL = CAST(END_GAL AS VARCHAR(50));
    ELSE
     SET V_DIFF = 'N/A';
     SET V_END_GAL = 'NOT UPDATED';
    END IF;
    SET V_INI_GAL = CAST(INI_GAL AS VARCHAR(50));
    SET V_HOUR = CAST(DATE_FORMAT(INI_DATE, '%H') AS VARCHAR(50));
    INSERT INTO HOURLYGASCHANGE VALUES(V_HOUR, V_INI_GAL, V_END_GAL, V_DIFF);
 END LOOP;
close INITIALDATA;
SELECT * FROM HOURLYGASCHANGE;
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS `hourly_gas_change`; 

Answer 1

documentation for CAST未将 VARCHAR 列为合法转换之一。尝试转换为固定宽度的 CHAR(50)。