DROP PROCEDURE IF EXISTS insert_historytable;
DELIMITER //
CREATE PROCEDURE insert_historytable()
BEGIN
##定义判断变量
DECLARE 1_id varchar(50);
DECLARE 1_dissolved_oxygen FLOAT;
DECLARE 1_conductivity FLOAT;
DECLARE 1_turbidity FLOAT;
DECLARE 1_ph FLOAT;
DECLARE 1_water_temperature FLOAT;
DECLARE 1_report_date TIMESTAMP;
DECLARE 1_name varchar(500);
## 定义查询变量
DECLARE _cur CURSOR FOR
SELECT id,dissolved_oxygen,conductivity,turbidity,ph,water_temperature,report_date FROM sys_salvage_point_data1;
### 循环赋初始值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET 1_id=NULL;
### 打开
OPEN _cur;
## 赋值
FETCH _cur INTO 1_id,1_dissolved_oxygen,1_conductivity,1_turbidity,1_ph,1_water_temperature,1_report_date;
###循环判断
WHILE (1_id is not null ) DO
SET 1_name=CONCAT('<ph>',1_ph,'<ph/>','<conductivity>',1_conductivity,'<conductivity/>');
select 1_id,1_dissolved_oxygen,1_conductivity,1_turbidity,1_ph,1_water_temperature,1_report_date,1_name;
## 赋值下一个游标
FETCH _cur INTO 1_id,1_dissolved_oxygen,1_conductivity,1_turbidity,1_ph,1_water_temperature,1_report_date;
END WHILE;
## 关闭
CLOSE _cur;
END//
DELIMITER ;
CALL insert_historytable(); #调用存储过程
转载于:https://www.cnblogs.com/zhouheblog/p/9798419.html
