Mysql 存储过程的一个例子

参考博客：http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

需求：向taskbill表压入几百万数据，其中taskbill_no列的格式 如   RZD000069455

drop procedure task_insert;                 -- 删除这个存储过程

DELIMITER //                                             -- 定义分隔符//
create PROCEDURE task_insert(count int unsigned)
begin
declare num int unsigned default 1;
declare str varchar(20) default 'RZD1';
while num <= count do
INSERT into t(s) values (CONCAT(str,LPAD(num,8,'0')));   -- 拼接字符串，并对num前补0
set num = num+1;
end while;
end;
//
DELIMITER ;                                              -- 分隔符变为默认的;

call task_insert(1000)    -- 受影响的行: 1  时间: 1.672ms

运行效果如图

保存一下写的sql  和上述一致。

-- drop procedure task_insert;

DELIMITER //
create PROCEDURE task_insert(count int unsigned)
begin
declare num int unsigned default 1;
declare str varchar(20) default 'RZD1';
while num <= count do
INSERT into taskbill(taskbill_no,task_type,main_taskbill_no,driver_no,driver_name,driver_phone,car_id,cartype_no,
cartype_name,car_no,car_batch_no,dist_service_id,dist_service_name,batch_id,delivery_time,origin_name) values (CONCAT(str,LPAD(num,8,'0')),10,
taskbill.taskbill_no,'1034201017',concat('新旧宫--司机',num),18212346990,302928374,2015012201,'车型test','京L09876','test1',
5948277494,'晨鲜送',29,1467302400 + 86400*floor(rand()*30),'北京仓库1');
set num = num+1;
end while;
end;
//
DELIMITER ;

call task_insert(1)