mysql cursor

最新推荐文章于 2026-01-01 22:00:33 发布

原创最新推荐文章于 2026-01-01 22:00:33 发布 · 551 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #insert #date

use pcdata;
DELIMITER $$

DROP PROCEDURE IF EXISTS `curdemo1` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `curdemo1`()
BEGIN
DECLARE a int(11);
DECLARE b varchar(45);
declare CreateStr varchar(2000);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT distinct curcekey FROM pcdata.curcevalue;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
   FETCH cur1 INTO a;
     IF NOT done THEN
       set b=CONCAT('curcevalue_',a);
       INSERT INTO pcdata.t2 VALUES (a,b);
       set CreateStr=concat('insert into ', b,' select * from curcevalue where curcekey=',a);
       set @sqlcounts = CreateStr;
       prepare stmt from @sqlcounts;
       execute stmt;
     end if;
UNTIL done END REPEAT;
CLOSE cur1;
END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `AddRandData` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddRandData`()
BEGIN
declare StartDate datetime;
DECLARE v1 INT DEFAULT 5;
declare temp1sk float;
declare temp1rf float;
declare speed1 float;
declare press1 float;
set StartDate=Date_Add(now(),Interval -30 day);
WHILE v1 < 43200 DO
   set StartDate=Date_Add(StartDate,Interval 1 minute);
   select round(round(rand(),4)*100,1) into temp1sk;
   if temp1sk>50 or temp1sk<40 then
    set temp1sk=45;
   end if;
insert into curcevalue(CurceKey,CurceValues,CurceDate,Ptype,OperatorName,roomid)
values(1657,temp1sk,StartDate,'admin1',3);

select round(round(rand(),4)*100,1) into temp1rf;
   if temp1rf>60 or temp1rf<40 then
    set temp1rf=50;
   end if;
insert into curcevalue(CurceKey,CurceValues,CurceDate,Ptype,OperatorName,roomid)
values(1663,temp1rf,StartDate,'admin1',7);

select round(round(rand(),4)*10000,0) into speed1;
   if speed1>3500 or speed1<2000 then
    set speed1=3000;
   end if;
insert into curcevalue(CurceKey,CurceValues,CurceDate,Ptype,OperatorName,roomid)
values(1669,speed1,StartDate,'admin1',1);
select round(round(rand(),4)*10,1) into press1;
   if press1>3.5 or press1<1 then
    set press1=2.0;
   end if;
insert into curcevalue(CurceKey,CurceValues,CurceDate,Ptype,OperatorName,roomid)
values(1643,press1,StartDate,'admin1',3);
   SET v1 = v1 + 1;

END WHILE;
END $$

DELIMITER ;