存储过程批量添加数据

-- 批量创建member表用户数据
DROP PROCEDURE IF　EXISTS CREATE_USER;
CREATE PROCEDURE `CREATE_USER`()
BEGIN 
DECLARE i INT DEFAULT 10000;
DECLARE v_belong VARCHAR(32);
WHILE i >0 DO
        set v_belong=i;
        SET @insert_tmp_table=CONCAT('INSERT INTO sas_member (`BELONG`, `PARENT_ID`, `MEMBER_NAME`, `MEMBER_TYPE`, ',
                                '`MEMBER_STATUS`, `PERSON`, `TEL`, `CREATE_BY`, `CREATE_TIME`, `LAST_UPDATE_BY`, `LAST_UPDATE_TIME`,',
                                '`ENABLE_STATUS`, `com_type`) values('',v_belong,'', '', '测试企业一', '0', '1', '',',
                                ''18501749712', '', '2017-08-03 15:36:54', '', '2017-07-19 01:00:01', '1', '3')');
        PREPARE insert_tmp_table_stmt FROM @insert_tmp_table;
        EXECUTE insert_tmp_table_stmt;
        DEALLOCATE PREPARE insert_tmp_table_stmt;
SET i=i-1;
END WHILE;
END;

-- 批量创建用户清算因子信息数据
DROP PROCEDURE IF EXISTS CREATE_USER_INFO;
CREATE PROCEDURE `CREATE_USER_INFO`()
BEGIN 
DECLARE i INT DEFAULT 10000;
DECLARE v_belong VARCHAR(32);
WHILE i >0 DO
        set v_belong=i;
        SET @insert_tmp_table=CONCAT('INSERT INTO sas_member_rule_info (`SYS_CODE`, `USER_ID`, `CARRIER_CODE`, `BALANCE_CLEAR`,',
                                    '`ORDER_NO`, `ORDER_DATE`, `INVOICE_STATUS_SON`, `INVOICE_TYPE_SON`, `INVOICE_STATUS_PARENT`,',
                                    '`INVOICE_TYPE_PARENT`, `TITLE_TYPE`, `TITLE`, `VIP_TYPE`, `ACCOUNT_TYPE`, `REMARK`, `CREATE_BY`,',
                                    '`CREATE_TIME`, `LAST_UPDATE_BY`, `LAST_UPDATE_TIME`, `ENABLE_STATUS`, `BALANCE_CLEAR_TYPE`,',
                                    '`IS_BALANCE`, `DISPATCH_NUMBER`, `PAY_DAY`) values(NULL, '',v_belong,'', '',v_belong,'', '3',',
                                    ''2.00', '2.00', '0.00', '0.00', '0.00', '0.00', '20', '3', '0', NULL, NULL,',
                                    ''sysadmin', '2017-08-03', 'sysadmin', '2017-08-03 15:03:40', '1', '0', '1', '0', '1')');
        PREPARE insert_tmp_table_stmt FROM @insert_tmp_table;
        EXECUTE insert_tmp_table_stmt;
        DEALLOCATE PREPARE insert_tmp_table_stmt;
SET i=i-1;
END WHILE;
END;

-- 批量插入母订单数据
DROP PROCEDURE IF EXISTS CREATE_ORDER_INFO;
CREATE PROCEDURE `CREATE_ORDER_INFO`()
BEGIN
DECLARE i INT DEFAULT 50000;
DECLARE v_belong VARCHAR(32);
DECLARE v_ORDER_NO VARCHAR(32);
DECLARE V_ORDER_TYPE VARCHAR(2);
WHILE i>0 DO 
        set v_belong=MOD(i,10000)+1;
        set v_ORDER_NO=CONCAT('PO20170803',LPAD(i,5,0));
        set V_ORDER_TYPE=LPAD(mod(i,2)+1,2,0);
        SET @insert_tmp_table=CONCAT('INSERT INTO sas_order_2017 (`ORDER_NO`, `SYS_CODE`, `YEAR`, `MONTH`, `BELONG`, `SUPPLY_CODE`, `SUPPLY_NAME`, ',
                            '`CARRIER_NAME`, `CARRIER_CODE`, `ORDER_TYPE`, `IS_SENDORDER`, `ORDER_TIME`, `PAY_TIME`, `INSURANCE_FEE`, `ORDER_MONEY`, `PAY_TYPE`, ',
                            '`PAY_WAY`, `BANK_TYPE`, `PAY_STATUS`, `ORDER_SET_STATUS`, `START_ADDR`, `FINAL_ADDR`, `REMARK`, `CREATE_BY`, `CREATE_TIME`, ',
                            ' `ENABLE_STATUS`, `FLOAT_DISCOUNT_RATE`, `FLOAT_DISCOUNT_FREIGHT`, `ACTUAL_ORDER_MONEY`,',
                             '`FLOAT_FREIGHT_EXCHANGE`, `FREIGHT_EXCHANGE`)',
                                '    values('',v_ORDER_NO,'',NULL,'2017','08',',v_belong,v_belong,''测试企业一',',
                            ''测试企业一',',v_belong,V_ORDER_TYPE,''02',',NOW(),NOW(),''0.00', '100', '02', '01',', 
                            ''1','0.00', '0.00', '0.00', '0.00', '0.00')');
        PREPARE insert_tmp_table_stmt FROM @insert_tmp_table;
        EXECUTE insert_tmp_table_stmt;
        DEALLOCATE PREPARE insert_tmp_table_stmt;
SET i=i-1;
END WHILE;
END;

-- 批量插入子订单数据
DROP PROCEDURE IF EXISTS CREATE_SUB_ORDER_INFO;
CREATE PROCEDURE `CREATE_SUB_ORDER_INFO`()
BEGIN
DECLARE i INT DEFAULT 50000;
DECLARE v_belong VARCHAR(32);
DECLARE v_ORDER_NO VARCHAR(32);
DECLARE V_ORDER_TYPE VARCHAR(2);
DECLARE v_waybill_no VARCHAR(32); 
DECLARE V_ARBITRAGE_CODE VARCHAR(32);
WHILE i>0 DO 
        set v_belong=MOD(i,10000)+1;
        set v_ORDER_NO=CONCAT('PO20170803',LPAD(i,5,0));
        set V_ORDER_TYPE=LPAD(mod(i,2)+1,2,0);
      set v_waybill_no=CONCAT('ZDYD20170803',LPAD(i,5,0));
    set V_ARBITRAGE_CODE=CONCAT('ZC20170803',LPAD(i,5,0));
        SET @insert_tmp_table=CONCAT('INSERT INTO sas_sub_order (`waybill_no`, `ORDER_NO`, `P_ORDER_NO`, `SYS_CODE`, `YEAR`, `MONTH`, `BELONG`,',
                 '`SUB_ACCOUNT`, `SUPPLY_CODE`, `SUPPLY_NAME`, `CARRIER_CODE`, `CARRIER_NAME`, `IS_RAILWAY`, `IS_SENDORDER`, `ORDER_TYPE`,',
                 '`ORDER_TIME`, `PAY_TIME`, `ORDER_MONEY`, `CLAIMING_VALUE`, `TOTAL_INSURANCE_FEE`, `INSURANCE_FEE`, `PREMIUM`, `PREMIUM_BANLANCE`,',
                 '`BASIC_PRICE`, `RISE_PRICE`, `OVER_RATE`, `ARBITRAGE_CODE`, `SUPPLY_PAY`, `CARRIER_PAY`, `PLATFORM_PAY`, `VERIFICATION_FREIGHT`, ',
                '`PAY_TYPE`, `PAY_WAY`, `BANK_TYPE`, `PAY_STATUS`, `ORDER_SET_STATUS`, `REMARK`, `CREATE_BY`, `CREATE_TIME`,',
                 '`ENABLE_STATUS`, `CARRIER_ORG_LEVEL_CODE`, `SUPPLY_ORG_LEVEL_CODE`, `WAYBILL_NUMBER`, `PLATFORM_DISCOUNT_POINT`,',
                 '`FLOAT_DISCOUNT_RATE`, `FLOAT_DISCOUNT_FREIGHT`, `ACTUAL_ORDER_MONEY`, `PLATFORM_DISCOUNT_RATE`, `FLOAT_FREIGHT_EXCHANGE`, ',
                '`FREIGHT_EXCHANGE`, `BALANCE_TYPE`) ',
                'VALUES ('',v_waybill_no, '','',v_ORDER_NO,'','',v_ORDER_NO,'','10','2017','07','', v_belong, '',',
                                    'NULL,'',v_belong,'','', v_belong, '','',v_belong,'','', ''物流公司',',V_ORDER_TYPE,'', '02', '02',',
                                    NOW(),',NULL, '100.00', '0.00', '0.00', '0.00', '0.00', '0.00',',
                                     ''100.00', '0.00', '0.0','',V_ARBITRAGE_CODE,'','0.00', '0.00', '0.00', '0.00',',
                                ''02', '01', '', '01', '10', '', 'sysadmin',',NOW(),',',
                                 ''1', '0001', '0001', '', '0.00', ',
                                ''1.00', '0.00', '100.00', '1.00', '0.00', '0.00', '1')');
        PREPARE insert_tmp_table_stmt FROM @insert_tmp_table;
        EXECUTE insert_tmp_table_stmt;
        DEALLOCATE PREPARE insert_tmp_table_stmt;
SET i=i-1;
END WHILE;
END;

函数运用：

select MOD(50000,10000);
select LPAD(1,5,0)

SELECT LPAD(mod(2,2)+1,2,0);