mysql - phpmyadmin 存储过程语法错误 sum(convert (field,decimal(4,2)))

Question

以下存储过程显示#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 18 行的“SELECT SUM(CONVERT(hoursworked.starhours, DECIMAL(4,2))) FROM hoursworked WHERE h”附近使用的正确语法。(以 SET 开头的行) v小时=。

如果我用变量替换值并作为 SQL 查询运行，它就可以正常工作。

我找不到任何语法错误，如果有人能指出我正确的方向，我将非常感激。

DELIMITER $$
CREATE PROCEDURE set_monw()
BEGIN

DECLARE vmonw, vmona, vmonb, vtuew, vtuea, vtueb, vwedw, vweda, vwedb, vthuw, vthua, vthub, vfriw, vfria, vfrib, vsatw, vsata, vsatb, vsunw, vsuna,
vsunb, vowork, vwwt, vwat, vwbt, vrwt, vavg, smonb, stuew, stuea, stueb, swedw, sweda, swedb, sthuw, sthua, sthub, sfriw, sfria, sfrib, ssatw,
ssata, ssatb, ssunw, ssuna, ssunb, sowork, nwwt, nwat, nwbt, nrwt, navg, vhours, vmins, vrawmins DECIMAL(4,2);

DECLARE vempid, vwkno INT;

DECLARE vweekend DATE;  

DECLARE rtddata CURSOR FOR SELECT * FROM rtd;

OPEN rtddata;

mainloop:

LOOP

FETCH rtddata INTO vempid, vwkno, vweekend, vmonw, vmona, vmonb, vtuew, vtuea, vtueb, vwedw, vweda, vwedb, vthuw, vthua, vthub, vfriw, vfria, vfrib, vsatw, vsata, vsatb, vsunw, vsuna, vsunb, vowork, vwwt, vwat, vwbt, vrwt, vavg;

SET vhours = SELECT SUM(CONVERT(hoursworked.starthrs, DECIMAL(4,2))) FROM hoursworked WHERE hoursworked.empid = vempid
                AND hoursworked.wkno = vwkno AND hoursworked.company = 'Hrs Worked (inc driving)' AND hoursworked.day = 1 GROUP BY
                hoursworked.empid, hoursworked.wkno, hoursworked.company, hoursworked.day;

SET vrawmins = SELECT SUM(CONVERT(hoursworked.startmins, DECIMAL(4,2))) FROM hoursworked WHERE hoursworked.empid = vempid
                AND hoursworked.wkno = vwkno AND hoursworked.company = 'Hrs Worked (inc driving)' AND hoursworked.day = 1 GROUP BY
                hoursworked.empid, hoursworked.wkno, hoursworked.company, hoursworked.day;

SET vhours = vhours + (vrawmins/60);

UPDATE rtd SET rtd.monw = vhours WHERE rtd.empid = vempid AND rtd.wkno = vwkno;

END LOOP;

CLOSE rtddata;

END $$

DELIMITER ;

END $$
DELIMITER ;

Answer 1

解决方案:

抱歉，我自己找到了答案。它需要在 select 语句周围加上括号。

埃迪·L