对于此服务器版本，"DECLARE"在此位置无效，期望：错误

Question

我尽一切可能将"DECLARE"放在我的过程SQL查询中(使用MySQL工作台(并显示：

"DECLARE"在此位置对此服务器版本无效，应为：ERROR。

现在我需要帮助。

DELIMITER //
CREATE PROCEDURE getdetails()
BEGIN 
DECLARE 
vin table.vin%type; 
responsetime table.responseTimeStamp%type;
odometer table.odometer%type;
chargePercentage table.soc%type;
CURSOR sequential_vehicle_status is
SELECT vin, responseTimeStamp, odometer, soc FROM table ORDER BY vin, responseTimeStamp;
OPEN sequential_vehicle_status;
LOOP
FETCH sequential_vehicle_status into vin, responseTimeStamp, odometer, chargePercentage;
EXIT WHEN sequential_vehicle_status%notfound;
dbms_output.put_line(vin || " * " || responseTimeStamp || " * " || odometer || " * " || chargePercentage || "% " ||);
END LOOP;
CLOSE sequential_vehicle_status;
END //
DELIMITER ;

Answer 1

此代码将正常工作。这是一个演示。请根据表列类型调整声明的变量。

CREATE PROCEDURE getdetails()
BEGIN
DECLARE finished INTEGER DEFAULT 0; --this is added so the exit from loop can be made
DECLARE vin int; 
DECLARE responseTimeStamp int;
DECLARE odometer int;
DECLARE chargePercentage int;
DECLARE sequential_vehicle_status 
cursor for
SELECT vin
, responseTimeStamp
, odometer
, soc 
FROM `table` 
ORDER BY vin, responseTimeStamp;
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;--this is added so the exit from loop can be made
OPEN sequential_vehicle_status;
start_loop: LOOP
IF finished = 1 THEN 
LEAVE start_loop;
END IF;
FETCH sequential_vehicle_status into vin
, responseTimeStamp
, odometer
, chargePercentage;
select concat(vin, '*', responseTimeStamp, '*', odometer, '*', chargePercentage, '% ');                                         
END LOOP;
CLOSE sequential_vehicle_status;

结束；

请阅读对您问题的评论DBMS_OUTPUT是oracle包您可以添加以下代码来代替dbms_output行：

select concat(vin, '*', responseTimeStamp, '*', odometer, '*', chargePercentage, '% ');      

Answer 2

您将mysql和oracle语法结合在一起。Dbms_output是oracle中的包，在oracle中使用is子句来创建游标。

CREATE PROCEDURE getdetails()
BEGIN 
DECLARE vin table.vin%type; 
DECLARE responsetime table.responseTimeStamp%type;
DECLARE odometer table.odometer%type;
DECLARE chargePercentage table.soc%type;
DECLARE CURSOR sequential_vehicle_status for 
SELECT vin, responseTimeStamp, odometer, soc FROM table ORDER BY vin, responseTimeStamp;

此外，如果您想将某些内容打印到控制台，请选择dbms_output，它是oracle包。

即。SELECT concat('VIN is ', vin);