mysql 存储过程生成随机数据

2019独角兽企业重金招聘Python工程师标准>>>

drop procedure TestVoucher;

delimiter $$
 create procedure TestVoucher() 
 begin 
    DECLARE max int; 
        DECLARE rc int; 
        declare tempVoucherId VARCHAR(60); -- 随机数  
        declare TEMP_VOUCHER_NO VARCHAR(60); -- 随机数  
    declare TEMP_GOODS_ID varCHAR(60);
    declare TEMP_GOODS_NAME varCHAR(60);
      declare time TIMESTAMP ;
    set max =100; 
    set rc =1; 
loopl: while rc<max do 
   
     SELECT CONCAT('99S000620160607',round(round(rand(),18)*1000000000000000)) into tempVoucherId;
   SELECT CONCAT('99',round(round(rand(),10)*1000000000000000)) into TEMP_VOUCHER_NO;  
   select CONCAT('2016-06-16 ',LPAD(FLOOR(7 + (RAND() * 16)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) into time ;
        
   SELECT CONCAT(FLOOR(1 + (RAND() * 6))) into TEMP_GOODS_ID;
   SELECT CONCAT('商品名称',TEMP_GOODS_ID) into TEMP_GOODS_NAME;
   insert into S0007_S_COMMON_VOUCHER(VOUCHER_ID,VOUCHER_NO,GOODS_ID,GOODS_NAME,CREATE_TIME) values(tempVoucherId,TEMP_VOUCHER_NO,TEMP_GOODS_ID,TEMP_GOODS_NAME,time); 
   set rc=rc+1; 
end while loopl; 
 end$$ 
 delimiter ;

call TestVoucher;
 

转载于:https://my.oschina.net/stonezing/blog/787550