Posted By

jacktan on 10/30/14

Tagged

Versions (?)

Last Edited at 10/30/14 02:16am

Statistics

Viewed 475 times

Favorited by 0 user(s)

Related snippets

A procedure to init testing data

/ Published in: SQL

Init testing data for the table [`goods_order`](http://www.snipplr.com/view/80413/table-creating-sql-for-a-typical-shopping-cart/)

Highlighted TextExpand

DELIMITER $$

DROP PROCEDURE IF EXISTS `usp_make_data` $$
CREATE PROCEDURE `usp_make_data`()
BEGIN
    DECLARE iv_goods_id INT UNSIGNED DEFAULT 0;
    DECLARE iv_depot_id INT UNSIGNED DEFAULT 0;
    DECLARE iv_packet_id INT UNSIGNED DEFAULT 0;

SET iv_goods_id=5000;
    SET iv_depot_id=10;
    SET iv_packet_id=20;

WHILE iv_goods_id>0
    DO
      
      START TRANSACTION;
        WHILE iv_depot_id>0
        DO
          WHILE iv_packet_id>0
          DO
            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));
            SET iv_packet_id=iv_packet_id-1;
          END WHILE;

SET iv_packet_id=20;
          SET iv_depot_id=iv_depot_id-1;
        END WHILE ;

COMMIT;
      SET iv_depot_id=10;
      SET iv_goods_id=iv_goods_id-1;
    END WHILE ;

END $$

DELIMITER ;

Report this snippet Tweet