Error mysql syntax use 'declare pfee int default null;'，查看与您的 MySQL 服务器版本相对应的手册 [英] Error mysql syntax use 'declare pfee int default null;',check the manual that corresponds to your MySQL server version

问题描述

delimiter //
create procedure payFees(in deviceid int, in fee int)
begin
    declare pbalance int default null;
    declare ptype varchar(2) default "";
    declare total int;
    select balance, type into pbalance, ptype from device d where d.deviceid = deviceid;
    if pbalance is null
    then 
        select "cannot found the device" as result;
    else
/*      declare rate1 float;
        declare rate2 float;
        declare damage1 float;
        declare damage2 float;
        select rate1, rate2, damage1, damage2 into rate1, rate2, damage1, damage2 from charge_table ct where ct.type = ptype; */

        set total = pbalance + fee;
        update device d set balance = total where d.deviceid = deviceid;    

        declare pfee int default null;
        declare pdate varchar(6) default null;
        declare curl cursor for select basicfee, yearmonth from meter_record m, electricity_bill e where m.id = e.eid and m.deviceid = deviceid and flag = 0;
        declare continue handler for not found set pfee = null;
        open curl;

        fetch curl into pfee, pdate;
        while(pfee is not null) do
            declare overyear bool;
            if year(pdate) = year(now()) or (year(pdate) + 1 = year(now()) and month(pdate) = "12"):
            then
                set overyear = false;
            else
                set overyear = true;
            end if;
            select overyear;
            fetch curl into payfee, pdate;
        end while;
        close curl;
    end if;
end;
//

这是我的 sql 代码，我尝试使用游标.但是有个错误困扰了很久，试了很多方法都没有解决.

this is my sql code and I try to use cursor. but there is a mistake I have long troubled, I tried a lot of ways no solution.

错误信息是:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在declare pfee int default null"附近使用的正确语法；声明 pdate varchar(6) 默认为空；在第 20 行声明'

the error message is: 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 pfee int default null; declare pdate varchar(6) default null; declar' at line 20

请帮忙！

推荐答案

你不能把你的声明放在其他语句之后.

You can't put your declare after other statements.

https://dev.mysql.com/doc/refman/5.7/en/declare.html 说:

DECLARE 只允许在 BEGIN ... END 复合语句中使用，并且必须在其开头，在任何其他语句之前.

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

这篇关于Error mysql syntax use 'declare pfee int default null;'，查看与您的 MySQL 服务器版本相对应的手册的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！