mysql数据存储过程添加数据_Mysql 存储过程 自动插入数据

1. 表结构

CREATE TABLE `zc2_ticket_orderticket` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`acid` int(11) NOT NULL COMMENT '商户ID',

`order_no` varchar(64) NOT NULL DEFAULT '' COMMENT '订单号',

`ticket_no` varchar(64) NOT NULL DEFAULT '' COMMENT '车票号',

`ticket_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1全票；2半票；3儿童票；9网上成人单程;10网上成人双程;13网上小童双程;14网上小童单程；17网上长者 65 岁单程；18网上长者 65 岁双程',

`passenger_name` varchar(64) NOT NULL DEFAULT '' COMMENT '乘客姓名',

`passenger_id` varchar(64) DEFAULT NULL COMMENT '乘客证件号',

`passenger_id_type` tinyint(4) DEFAULT NULL COMMENT '1身份证2港澳回乡证3台湾通行证4护照5军官证6居住证',

`passenger_tel` varchar(13) NOT NULL DEFAULT '' COMMENT '手机号码',

`status` tinyint(11) NOT NULL COMMENT '0已锁票；1已购票；2已退票；3已检票；4已过期；5已撤销',

`fee` decimal(10,2) DEFAULT '0.00' COMMENT '票价',

`discount_fee` decimal(10,2) DEFAULT '0.00' COMMENT '中港通接口返回的折扣价',

`order_time` int(10) NOT NULL COMMENT '购票时间，unix时间戳',

`refund_time` int(10) DEFAULT NULL COMMENT '退票时间，unix时间戳',

`refund_time2` int(11) NOT NULL DEFAULT '0' COMMENT '退款到账时间',

`refund_fee` decimal(10,2) DEFAULT NULL COMMENT '退款金额',

`refund_rate` varchar(10) NOT NULL DEFAULT '' COMMENT '退票费率',

`insurance_refund_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '保险退款金额',

`check_in_channel` char(1) NOT NULL DEFAULT '' COMMENT '检票渠道:1车载机 2后台 3乘客端',

`check_in_user` varchar(32) NOT NULL DEFAULT '' COMMENT '检票渠道 车载机:车牌号 后台:管理员姓名 乘客端:乘客姓名',

`check_in_time` int(11) NOT NULL DEFAULT '0' COMMENT '检票时间',

`start_station_id` varchar(32) DEFAULT NULL COMMENT '出发站编号',

`start_station` varchar(32) DEFAULT NULL COMMENT '出发站名称',

`end_station_id` varchar(32) DEFAULT NULL COMMENT '目的站编号',

`end_station` varchar(32) DEFAULT NULL COMMENT '目的站名称',

`sch_date` varchar(10) DEFAULT NULL COMMENT '班次日期',

`sch_time` varchar(8) DEFAULT NULL COMMENT '班次时间',

`sch_code` varchar(64) DEFAULT NULL COMMENT '班次编号',

`trip` int(11) DEFAULT NULL COMMENT '0.去程 1.返程',

`seat` int(10) DEFAULT '0' COMMENT '座位号',

`gate` varchar(64) DEFAULT '' COMMENT '检票口',

`insurance_number` varchar(50) DEFAULT '' COMMENT '保险单号',

`insurance_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '保险金额',

`refund_insurance_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '退保金额',

`insurance_status` tinyint(4) DEFAULT '0' COMMENT '保险状态 0：未投保 1：已投保 2：已退保',

`havechilden` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '是否携带免票儿童：0 未携带 1：携带',

`children_name` varchar(50) DEFAULT '' COMMENT '儿童姓名',

`children_card_no` varchar(32) DEFAULT '' COMMENT '儿童证件号码',

`children_card_type` tinyint(4) DEFAULT '2' COMMENT '儿童证件类型 身份证:2、港澳回乡证:3、台湾通行证:4、护照:5、军官证:6、居住证:7',

PRIMARY KEY (`id`),

KEY `acid_ticketno` (`acid`,`ticket_no`) USING BTREE,

KEY `order_no` (`order_no`)

) ENGINE=InnoDB AUTO_INCREMENT=2847 DEFAULT CHARSET=utf8;

2.  创建存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS `autoinsert_orderticket`$$

CREATE PROCEDURE `autoinsert_orderticket`(IN insertCount INT)

BEGIN

DECLARE count INT DEFAULT 0;

WHILE(count < insertCount)

DO

INSERT INTO `zc2-dev`.`zc2_ticket_orderticket` (`acid`, `order_no`, `ticket_no`, `ticket_type`, `passenger_name`, `passenger_id`, `passenger_id_type`, `passenger_tel`, `status`, `fee`, `discount_fee`, `order_time`, `refund_time`, `refund_time2`, `refund_fee`, `refund_rate`, `insurance_refund_fee`, `check_in_channel`, `check_in_user`, `check_in_time`, `start_station_id`, `start_station`, `end_station_id`, `end_station`, `sch_date`, `sch_time`, `sch_code`, `trip`, `seat`, `gate`, `insurance_number`, `insurance_price`, `refund_insurance_price`, `insurance_status`, `havechilden`, `children_name`, `children_card_no`, `children_card_type`) VALUES ('6', '201902131040404214yy', '0007068748yt', '1', '1ytutyu', '3463496794654', '5', '18252072', '1', '42.00', '0.00', '1025640', NULL, '0', NULL, '', '0.00', '', '', '0', '102114', '南汽车站', '10284', '北汽车总站', '2019-02-16', '18:10', '1102504', NULL, '31', '3号检票口', '', '0.00', '0.00', '0', '0', '', '', '0');

SET count = count+1;

END WHILE;

END$$

DELIMITER;

3.调用

call autoinsert_orderticket(10000);

4.查看存储过程或者函数信息

SELECT* FROM information_schema.Routines

5.删除存储过程

DROP PROCEDURE IF EXISTS `autoinsert_orderticket`