存储过程

--提现订单
DELIMITER //
-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_withdraw_order`;
CREATE PROCEDURE `add_withdraw_order`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE userId INT DEFAULT 0;
    DECLARE serialNum VARCHAR(255) DEFAULT 1;
    WHILE i < n DO
        SET userId = FLOOR(ROUND(RAND()*1000000));
        SET serialNum = concat('0b',ROUND(RAND()*170814000000000000));
        INSERT INTO lcfarm_funds.`withdraw_order` VALUES (NULL, userId, serialNum, '10.00', '10.00', '0.00', '0.00', 'YFS', 'nfd_396683',
       '周芝芝', 'NO', 'H5', '0', null, null, null, null, '143463', 'Oi/obgToqeiZ6tGZ5UEZCOR3sYH1LpIyc2qPF4ym1xE=', '招商银行', null, '认证支付',
       'EbGWpttEboCVDnSnF9SVDQ==', '4GHDF5JF5zJ2a4q/HD7iLZRagJ+i0cQ6VxyE/Sz09rw=', '2017-11-01 19:04:11', '2017-11-01 19:04:11', null, null);
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;


CALL add_withdraw_order(1000000);


SELECT * from lcfarm_funds.`withdraw_order`;


DELETE from lcfarm_funds.`withdraw_order`;




--提现订单
DELIMITER //
-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_recharge_order`;
CREATE PROCEDURE `add_recharge_order`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE userId INT DEFAULT 0;
    DECLARE serialNum VARCHAR(255) DEFAULT 1;
    WHILE i < n DO
        SET userId = FLOOR(ROUND(RAND()*1000000));
        SET serialNum = concat('0B',ROUND(RAND()*270814000000000000));
        INSERT INTO lcfarm_funds.`recharge_order` VALUES (NULL,userId , '100.00', null, null, 'CITIC', 'iQ/u7MfonrYBUsiyVf14EOR3sYH1LpIyc2qPF4ym1xE=', '张三',
        'ANrb0WXVKxsmBvwvRlDHJfNsPUlsAVIN7IMM4jf4SYw=', '13800772619', serialNum, null, null, 'CG', 'DK', 'IOS', 'WDZ', '2017-11-01 20:01:43', '2017-11-01 20:01:44',
'2017-11-01 20:01:49', '2017-08-14 20:01:49');
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;
CALL add_recharge_order(1000000);






--还款兑付
DELIMITER //
-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_t_sh_repay_file_record`;
CREATE PROCEDURE `add_t_sh_repay_file_record`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE userId INT DEFAULT 0;
    DECLARE item_serial_no VARCHAR(255) DEFAULT 1;
    WHILE i < n DO
        SET userId = FLOOR(ROUND(RAND()*1000000));
        SET item_serial_no = concat('0L',ROUND(RAND()*171021000031200000));
        INSERT INTO `t_sh_repay_file_record` VALUES (NULL, 'srepayclebatch_1_20171021.dat', userId, '1', '901507', item_serial_no, '11', 
'1004.00', '1000.00', '-996.00', '0.00', '0', '2017-11-01', '2017-11-01 19:49:05', '2017-11-01 19:49:05', 'ywcg', '0', '2', null);
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;
CALL add_recharge_order(1000000);








--理财计划解冻与冻结
DELIMITER //
-- 创建插入数据的存储过程
DROP PROCEDURE IF EXISTS `add_t_sh_oder_flow_record`;
CREATE PROCEDURE `add_t_sh_oder_flow_record`(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE item_serial_no VARCHAR(255) DEFAULT 1;
    WHILE i < n DO
        SET item_serial_no = concat('0K',ROUND(RAND()*1000000000000000002));
        INSERT INTO s62.`t_sh_oder_flow_record` VALUES (i, '46737', i, 'YWCLCG', 'query|retry', '0', '0', null, '2017-10-30 19:41:36', '2017-10-25 19:41:36', 'DUBBO', 'com.njq.nongfadai.service.newproduct.INewProductPurchaseService#successCallBack',
'com.njq.nongfadai.service.newproduct.INewProductPurchaseService#failureCallBack', null);
INSERT INTO s62.`t_sh_oder_flow_record_detail` VALUES (NULL, i, item_serial_no, '{\"businessType\":\"46737\",\"ccy\":0,\"mgeAcct\":{\"id\":\"969522\"},\"msgHdr\":{\"date\":\"20171025\",\"instrCd\":46737,
\"rqRef\":{\"ref\":\"0K171025000041700002\"},\"svInst\":{\"instId\":\"P0007000\"},\"sysType\":4,\"time\":\"194135\",\"tradSrc\":\"0\",\"ver\":\"1.0\"},\"opFlag\":1,\"reqFrom\":\"P2P\",\"reqTo\":\"SHANGHAIBANK\",
\"serialNo\":\"0K171025000041700002\",\"trfAmt\":10}', 'com.njq.lcfarm.dto.frontend.req.CustomerBalanceFrozenReq', '0', 'com.njq.lcfarm.dto.frontend.res.CustomerBalanceFrozenRes', 'YHCLCG', '2017-10-30 19:41:36', '2017-10-30 19:41:36');
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;
CALL add_t_sh_oder_flow_record(1000000);




DELETE from s62.t_sh_oder_flow_record;


ALTER TABLE s62.t_sh_oder_flow_record add COLUMN `sync_result` enum('true','false') DEFAULT NULL COMMENT '同步回调处理是否完成 true-完成 false-未完成';