Advanced Search

A procedure to init testing data

/ Published in: SQL
Save to your folder(s)
Init testing data for the table [`goods_order`](http://www.snipplr.com/view/80413/table-creating-sql-for-a-typical-shopping-cart/)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.