首页 > 数据库技术 > 详细

mysql存储过程实例,查询多参数赋值

时间:2018-12-14 00:34:11      阅读:286      评论:0      收藏:0      [点我收藏+]

drop procedure if exists p_for_create_customer;

create procedure p_for_create_customer()
begin
declare ii int default 0;
declare i int default 1;
declare minss int default 0;
declare idd int;
declare start_d datetime;
declare channel_code_ VARCHAR(255);
declare end_d datetime;
declare minutes int;
select id,countt,start_date,end_date,channel_code into idd,ii,start_d,end_d,channel_code_ from schedu_create_info where is_effective=‘true‘ limit 1;
select timestampdiff(minute,start_d,end_d) into minss from dual;
select idd from dual;
loop_example : loop
update schedu_create_info set is_effective=‘true‘ where id = idd;
set i = i + 1;
insert into crm_t_customer (mobile ,name ,idcard,channel_code,create_date)
value(CONCAT(‘1*********‘,rand_num(0,9)),‘***‘,‘***‘,channel_code_,DATE_ADD(start_d,INTERVAL rand_num(0,minss) MINUTE) );
if i >ii then
leave loop_example;
end if;
end loop;
end;

call p_for_create_customer();

 

 

 

 

drop procedure if exists p_for_create_customer;

create procedure p_for_create_customer()
begin
declare ii int default 0;
declare i int default 1;
declare minss int default 0;
declare idd int;
declare start_d datetime;
declare channel_code_ VARCHAR(255);
declare end_d datetime;
declare minutes int;
select id,countt,start_date,end_date,channel_code into idd,ii,start_d,end_d,channel_code_ from schedu_create_info where is_effective=‘true‘ limit 1;
select timestampdiff(minute,start_d,end_d) into minss from dual;
select idd from dual;
loop_example : loop
update schedu_create_info set is_effective=‘false‘ where id = idd;
set i = i + 1;
insert into crm_t_customer (mobile ,name ,idcard,channel_code,create_date)
value(CONCAT(‘1*********‘,rand_num(0,9)),‘***‘,‘***‘,channel_code_,DATE_ADD(start_d,INTERVAL rand_num(0,minss) MINUTE) );
if i >ii then
leave loop_example;
end if;
end loop;
end;

call p_for_create_customer();

delete from crm_t_customer;
select * from schedu_create_info;
select * from crm_t_customer;
call p_for_create_customer();

select rand_num(2,54);


alter event event_task_customer ON COMPLETION PRESERVE ENABLE;
alter event event_task_customer ON COMPLETION PRESERVE DISABLE;
DROP EVENT event_task_customer;


create event event_task_customer
on schedule every 2 minute
on completion PRESERVE
do call p_for_create_customer();

 

mysql存储过程实例,查询多参数赋值

原文:https://www.cnblogs.com/adolfmc/p/10117216.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!