1.创建测试表:
create table test (
id int,
time dateTime,
data float
);
2.创建指定存储过程:
delimiter // #定义标识符为双斜杠
drop procedure if exists test; #如果存在test存储过程则删除
create procedure test() #创建无参存储过程,名称为test
begin
declare i int; #申明变量
declare beginDate datetime;
declare factor float;
declare length int;
set beginDate = STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s');
set i = 0; #变量赋值
set factor = 0.7;
set length = 30;
while i <= length do #结束循环的条件: 当i大于10时跳出while循环
if(i<3) then set factor = 0.7;
elseif(i<6) then set factor = 0.5;
elseif(i<10) then set factor = 0.3;
elseif(i>length-3) then set factor = 0.7;
elseif(i>length-6) then set factor = 0.5;
elseif(i>length-10) then set factor =0.3;
else set factor = 0.3;
end if;
insert into test values (i,date_add(beginDate, interval i day),round(RAND(),2)/10+factor); #往test表添加数据
set i = i + 1; #循环一次,i加一
end while; #结束while循环
select * from test; #查看test表数据
end
// #结束定义语句
call test();
3.查看数据: