有机肥

绿色

mysql 存储过程

DELIMITER //
CREATE PROCEDURE proc_tmp()
BEGIN
DECLARE done INT DEFAULT 0;  
DECLARE product_Id VARCHAR(255);
DECLARE yuanliao VARCHAR(255);
DECLARE miaoshu VARCHAR(255);
DECLARE shazhi VARCHAR(255);
DECLARE midu VARCHAR(255);
DECLARE mf VARCHAR(255);
DECLARE zuzhi VARCHAR(255);
DECLARE quality VARCHAR(255);
DECLARE shuliang VARCHAR(255);
DECLARE jiage VARCHAR(255);
DECLARE price_date VARCHAR(255);

DECLARE idCur CURSOR FOR SELECT productId,yl,ylms,sz,md,fk,zz,qa,amount,price,pricedate FROM sheet1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN idCur;  

REPEAT
FETCH idCur INTO product_Id,yuanliao,miaoshu,shazhi,midu,mf,zuzhi,quality,shuliang,jiage,price_date;
IF NOT done THEN  
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'yl',yuanliao,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'ylms',miaoshu,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'sz',shazhi,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'md',midu,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'fk',mf,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'zz',zuzhi,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'qa',quality,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'amount',shuliang,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'price',jiage,product_Id);
INSERT INTO static_data(model_id,dataKey,dataVal,product_id) VALUES(3,'pricedate',price_date,product_Id);
END IF;
UNTIL done END REPEAT;

CLOSE idCur;
END//
DELIMITER ;

CALL proc_tmp();
DROP PROCEDURE proc_tmp;

DELETE FROM sheet1;
定义的变量不能和select里的同名

posted on 2018-06-08 11:04 有机肥 阅读(153) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。


网站导航:
 
<2018年6月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿

随笔档案

搜索

最新评论

阅读排行榜

评论排行榜