The MySql stored procedure loop insert data problem

DELIMITER $$
CREATE PROCEDURE wheelSta()
BEGIN
DECLARE inexp,income,taks,c_count,t_taks, e,c INT;
DECLARE b INT DEFAULT 0; /*Whether to achieve record end control variables*/
DECLARE day_1,day_2 DATE;
DECLARE cur_1 CURSOR FOR SELECT COUNT,creat_time,taks FROM `cn_members_log` WHERE taks = 12 AND DATE(creat_time) = DATE_SUB(CURDATE(),INTERVAL 1 DAY);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur_1;
FETCH cur_1 INTO c_count, day_1, t_taks; /*Gets the first record*/
WHILE b=0 DO
INSERT INTO cn_members_sta(inexp,TIME,taks) VALUES(c_count, day_1, t_taks);
FETCH cur_1 INTO c_count, day_1, t_taks; /*Take the next record*/
END WHILE;
CLOSE cur_1;
END$$
DELIMITER ;

By SELECT COUNT, creat_time, taks FROM `cn_members_log` WHERE taks = 12 AND DATE (creat_time) = DATE_SUB (CURDATE), INTERVAL (1 DAY); it can detect 4 records, but after the implementation of the cn_members_sta table is a data are not solved,~

Started by Jacob at November 26, 2016 - 7:50 PM

As a key commit! At the end of the loop, and have a try!

Posted by Alfred at December 01, 2016 - 8:43 PM

MySQL does not require commit.

Posted by Jacob at December 02, 2016 - 9:42 PM

Use repeat to traverse the cursor feeling better, WHILE performs simple bulk insert more convenient, suggest changes to become so, script:

CREATE PROCEDURE wheelSta()
BEGIN
    DECLARE inexp,income,taks,c_count,t_taks, e,c INT;
    DECLARE b INT DEFAULT 0;    /*Whether to achieve record end control variables*/
    DECLARE day_1,day_2 DATE;   
    DECLARE cur_1 CURSOR FOR SELECT COUNT,creat_time,taks FROM `cn_members_log` WHERE taks = 12 AND DATE(creat_time) = DATE_SUB(CURDATE(),INTERVAL 1 DAY);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    OPEN cur_1;
    FETCH cur_1 INTO c_count, day_1, t_taks; /*Gets the first record*/
repeat
FETCH cur1 INTO ps_lsjlid, ps_ywid, ps_fwsj, ps_fwjssj, ps_ckid;
if not b then
 INSERT INTO cn_members_sta(inexp,TIME,taks)  VALUES(c_count, day_1, t_taks);
 until b end
REPEAT;
  CLOSE cur1;
commit;
END$$

Posted by Alfred at December 09, 2016 - 9:59 PM

You automatically submit? You try it on! You have a look whether to open check automatically submit ?

Posted by Alfred at December 15, 2016 - 10:56 PM

Or not. DECLARE cur_1 CURSOR FOR SELECT COUNT,creat_time,taks FROM `cn_members_log` WHERE taks = 12 AND DATE(creat_time) = DATE_SUB(CURDATE(),INTERVAL 1 DAY); Is it right? Belt can not define the cursorwhereRemove the back. WHERE taks = 12 AND DATE(creat_time) = DATE_SUB(CURDATE(),INTERVAL 1 DAY); Can insert data. , But I want to insert data consistent with where behind the condition.~

Posted by Jacob at December 17, 2016 - 11:16 PM

Independent and where conditions, you can try my script, to print each from the cursor data using select c_count, day_1, t_taks to print? Recommend the use of dbForge Studio for MySQL tools for debugging.!

Posted by Alfred at December 27, 2016 - 11:22 PM

Tried... No way. Hey

Posted by Jacob at December 30, 2016 - 11:24 PM