DELIMITER $$USE `sslf`$$DROP PROCEDURE IF EXISTS `pro_sslfdata`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_sslfdata`(IN date1 VARCHAR(50),IN tabname VARCHAR(50))
BEGIN
DECLARE stationum VARCHAR(50);
DECLARE lat FLOAT;
DECLARE lon FLOAT;
DECLARE done INT DEFAULT 0;
DECLARE v_sql VARCHAR(500);
DECLARE cursslf CURSOR FOR (SELECT StationNum,Latitude,Longitude FROM sslf_stationinfo);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
DROP TABLE IF EXISTS tmpdatasslf;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpdatasslf
(
StationNum VARCHAR(50),
DataTime DATETIME,
T FLOAT,
U FLOAT,
V FLOAT,
R_1h FLOAT,
WindS_2m FLOAT,
WindD_2m FLOAT,
TA FLOAT,
TB FLOAT,
Latitude FLOAT,
Longitude FLOAT
);
OPEN cursslf;
cursor_loop:LOOP
FETCH cursslf INTO stationum,lat,lon;
SET v_sql= CONCAT('select a.StationNum,DataTime,T,U,V,R_1h,WindS_2m,WindD_2m,TA,TB,b.Latitude,b.Longitude FROM', tabname,' a INNER JOIN sslf_stationinfo b ON a.StationNum=b.StationNum WHERE a.StationNum=',stationum,' AND DataTime<=', date1,' ORDER BY DataTime DESC LIMIT 0 ,1' );
IF done = 1 THEN
LEAVE cursor_loop;
END IF;
INSERT INTO tmpdatasslf (StationNum,DataTime,T,U,V,R_1h,WindS_2m,WindD_2m,TA,TB,Latitude,Longitude)
SELECT v_sql;
END LOOP cursor_loop;
CLOSE cursslf;
SELECT * FROM tmpdatasslf;
END$$DELIMITER ;CALL pro_sslfdata('2014-11-15 21:00:00','tab_sslf_201411');//调用存储过程
输出的结果:
错误码: 1136
Column count doesn't match value count at row 1Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
怎么会出错呢,求解!!!
BEGIN
DECLARE stationum VARCHAR(50);
DECLARE lat FLOAT;
DECLARE lon FLOAT;
DECLARE done INT DEFAULT 0;
DECLARE v_sql VARCHAR(500);
DECLARE cursslf CURSOR FOR (SELECT StationNum,Latitude,Longitude FROM sslf_stationinfo);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
DROP TABLE IF EXISTS tmpdatasslf;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpdatasslf
(
StationNum VARCHAR(50),
DataTime DATETIME,
T FLOAT,
U FLOAT,
V FLOAT,
R_1h FLOAT,
WindS_2m FLOAT,
WindD_2m FLOAT,
TA FLOAT,
TB FLOAT,
Latitude FLOAT,
Longitude FLOAT
);
OPEN cursslf;
cursor_loop:LOOP
FETCH cursslf INTO stationum,lat,lon;
SET v_sql= CONCAT('select a.StationNum,DataTime,T,U,V,R_1h,WindS_2m,WindD_2m,TA,TB,b.Latitude,b.Longitude FROM', tabname,' a INNER JOIN sslf_stationinfo b ON a.StationNum=b.StationNum WHERE a.StationNum=',stationum,' AND DataTime<=', date1,' ORDER BY DataTime DESC LIMIT 0 ,1' );
IF done = 1 THEN
LEAVE cursor_loop;
END IF;
INSERT INTO tmpdatasslf (StationNum,DataTime,T,U,V,R_1h,WindS_2m,WindD_2m,TA,TB,Latitude,Longitude)
SELECT v_sql;
END LOOP cursor_loop;
CLOSE cursslf;
SELECT * FROM tmpdatasslf;
END$$DELIMITER ;CALL pro_sslfdata('2014-11-15 21:00:00','tab_sslf_201411');//调用存储过程
输出的结果:
错误码: 1136
Column count doesn't match value count at row 1Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
怎么会出错呢,求解!!!
解决方案 »
- insert into ... on duplicate key 多条数据问题
- 有这种查询吗?
- FORCE INDEX 无效,没有使用索引求解!
- mysql数据库导数据很慢为什么?
- 高分求助:如何从JAVA的DatabaseMetadata类中得到某一列是否有unique key 的约束.
- 问个数据库查询的问题
- 高手们,100分相送
- mysql怎么设置数据存储的编码?java连接串带的参数什么意思?
- mysql中mysql_stmt_fetch返回值MYSQL_DATA_TRUNCATED的问题
- Linux 服务器,安装2个mysql 问题
- 创建触发器执行时说列名无效,急!~
- Mysql删除语句
SELECT v_sql;这句话里面的v_sql是变量这个语句是不能执行执行的需要使用动态语句执行先将整个insert语句组合成字符串,再按照以下方式执行prepare s from @sql;
execute s;
deallocate prepare s;
BEGIN
DECLARE stationum VARCHAR(50);
DECLARE lat FLOAT;
DECLARE lon FLOAT;
DECLARE done INT DEFAULT 0;
DECLARE v_sql VARCHAR(500);
DECLARE cursslf CURSOR FOR (SELECT StationNum,Latitude,Longitude FROM sslf_stationinfo);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
DROP TABLE IF EXISTS tmpdatasslf;
CREATE TEMPORARY TABLE IF NOT EXISTS tmpdatasslf
(
StationNum VARCHAR(50),
DataTime DATETIME,
T FLOAT,
U FLOAT,
V FLOAT,
R_1h FLOAT,
WindS_2m FLOAT,
WindD_2m FLOAT,
TA FLOAT,
TB FLOAT,
Latitude FLOAT,
Longitude FLOAT
);
OPEN cursslf;
cursor_loop:LOOP
FETCH cursslf INTO stationum,lat,lon;
SET v_sql= CONCAT('select a.StationNum,DataTime,T,U,V,R_1h,WindS_2m,WindD_2m,TA,TB,b.Latitude,b.Longitude FROM', tabname,' a INNER JOIN sslf_stationinfo b ON a.StationNum=b.StationNum WHERE a.StationNum=',stationum,' AND DataTime<=', date1,' ORDER BY DataTime DESC LIMIT 0 ,1' );
IF done = 1 THEN
LEAVE cursor_loop;
END IF;
set @vsql=concat(' INSERT INTO tmpdatasslf (StationNum,DataTime,T,U,V,R_1h,WindS_2m,WindD_2m,TA,TB,Latitude,Longitude) SELECT', v_sql);
PREPARE stmt1 FROM @vsql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
END LOOP cursor_loop;
CLOSE cursslf;
SELECT * FROM tmpdatasslf;
END$$DELIMITER ;
set @vsql=concat(' INSERT INTO tmpdatasslf (StationNum,DataTime,T,U,V,R_1h,WindS_2m,WindD_2m,TA,TB,Latitude,Longitude) SELECT', v_sql);
PREPARE stmt1 FROM @vsql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
存储过程执行到这里出错
错误码: 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 'a INNER JOIN sslf_stationinfo b ON a.StationNum=b.StationNum WHERE a.StationNum' at line 1Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
这是什么原因??