存储过程批量添加数据

-- 批量创建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);