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 'DECLARE done INT DEFAULT 0;
DECLARE temp_orderdate varchar(20);
DELCARE te' at line 14
SQL:
delimiter //
drop procedure if exists Proc_GetAveEffi//
create procedure Proc_GetAveEffi(IN startDate varchar(10),IN endDate varchar(10))
deterministic
BEGIN
/*建立用于循环的临时表*/
DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table(
orderdate varchar(20)
);
INSERT INTO tmp_table (orderdate) SELECT DISTINCT(ORDERDATE) FROM mc_sort_effirecord WHERE orderdate>=startDate AND orderdate<=endDate; /*数据定义*/
DECLARE done INT DEFAULT 0;
DECLARE temp_orderdate varchar(20);
DELCARE temp_aveEffi2 varchar(10);
DELCARE temp_aveEffi3 varchar(10);
DECLARE my_cursor CURSOR FOR
(SELECT orderdate FROM tmp_table);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; /*建立临时表*/
DROP TABLE IF EXISTS tmp_aveEffiTable;
CREATE TEMPORARY TABLE tmp_aveEffiTable(
orderdate varchar(20),
aveEffi1 varchar(10),
aveEffi2 varchar(10),
aveEffi3 varchar(10)
); /*打开游标*/
OPEN my_cursor;
emp_loop:LOOP
FETCH my_cursor INTO temp_orderdate;
IF done=1 THEN
LEAVE emp_loop;
END IF;
INSERT INTO tmp_aveEffiTable(orderdate,aveEffi1)
SELECT orderdate,aveEffi FROM mc_sort_effirecord
WHERE orderdate=@temp_orderdate AND picklinecode=1; SELECT @temp_aveEffi2 = aveEffi FROM mc_sort_effirecord WHERE orderdate=@temp_orderdate AND picklinecode=2; UPDATE tmp_aveEffiTable
SET orderdate=@temp_orderdate,aveEffi2=@temp_aveEffi2;
SELECT @temp_aveEffi3 = aveEffi FROM mc_sort_effirecord WHERE orderdate=@temp_orderdate AND picklinecode=3; UPDATE tmp_aveEffiTable
SET orderdate=@temp_orderdate,aveEffi3=@temp_aveEffi3;
END emp_loop;
CLOSE my_cursor;
select * from tmp_aveEffiTable;
TRUNCATE tmp_aveEffiTable;
TRUNCATE tmp_table;END//
DECLARE temp_orderdate varchar(20);
DELCARE temp_aveEffi2 varchar(10);
DELCARE temp_aveEffi3 varchar(10);目测你这个DELCARE 拼错了
deterministic
BEGIN
/*建立用于循环的临时表*/
DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table(
orderdate varchar(20)
);
INSERT INTO tmp_table (orderdate) SELECT DISTINCT(ORDERDATE) FROM mc_sort_effirecord WHERE orderdate>=startDate AND orderdate<=endDate; /*数据定义*/
DECLARE done INT DEFAULT 0;变量声明必须放在首部,不可以放在其它执行语句之后。
--------------------
my_cursor游标取自于临时表tmp_table,这种情况如何处理,是先声明别的变量,再建立tmp_table表,最后声明游标是不是?
1、修整了一下代码,第一个临时表不用建立,直接
DECLARE my_cursor CURSOR FOR (SELECT DISTINCT(ORDERDATE) FROM mc_sort_effirecord WHERE orderdate>=startDate AND orderdate<=endDate);
2、代码里面的错误确实很多,比如SELECT @temp_aveEffi3 = aveEffi FROM mc_sort_effirecord WHERE orderdate=@temp_orderdate AND picklinecode=3;在sql中能用,但是在mysql老是不成功,最后用aveEffi INTO temp_aveEffi3 才搞定;