Posted By

jacktan on 10/30/14


Tagged

procedure testing-data


Versions (?)

A procedure to init testing data


 / Published in: SQL
 

Init testing data for the table goods_order

  1. DELIMITER $$
  2.  
  3. DROP PROCEDURE IF EXISTS `usp_make_data` $$
  4. CREATE PROCEDURE `usp_make_data`()
  5. BEGIN
  6. DECLARE iv_goods_id INT UNSIGNED DEFAULT 0;
  7. DECLARE iv_depot_id INT UNSIGNED DEFAULT 0;
  8. DECLARE iv_packet_id INT UNSIGNED DEFAULT 0;
  9.  
  10. SET iv_goods_id=5000;
  11. SET iv_depot_id=10;
  12. SET iv_packet_id=20;
  13.  
  14. WHILE iv_goods_id>0
  15. DO
  16.  
  17. START TRANSACTION;
  18. WHILE iv_depot_id>0
  19. DO
  20. WHILE iv_packet_id>0
  21. DO
  22. INSERT INTO goods_order(order_id,goods_id,order_type,order_status,color_id,size_id,goods_number,depot_id,packet_id,gmt_create,gmt_modify)VALUES(SUBSTRING(RAND(),3,8),iv_goods_id,SUBSTRING(RAND(),3,1),SUBSTRING(RAND(),5,1)%2,SUBSTRING(RAND(),3,3),SUBSTRING(RAND(),4,3),SUBSTRING(RAND(),5,2),iv_depot_id,SUBSTRING(RAND(),4,2)*iv_packet_id,DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),2,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY));
  23. SET iv_packet_id=iv_packet_id-1;
  24. END WHILE;
  25.  
  26. SET iv_packet_id=20;
  27. SET iv_depot_id=iv_depot_id-1;
  28. END WHILE ;
  29.  
  30. COMMIT;
  31. SET iv_depot_id=10;
  32. SET iv_goods_id=iv_goods_id-1;
  33. END WHILE ;
  34.  
  35. END $$
  36.  
  37. DELIMITER ;

Report this snippet  

You need to login to post a comment.