mysql发布更新sql时修改表结构使用储存过程增加容错判断

1.使用 if not exists判断增加字段是否存在

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='dept_name') THEN
alter table t_trans_surgery_info 
        add column dept_name varchar(255) DEFAULT NULL COMMENT '科室名称';
end if;

贴上一个版本的更新sql（储存过程、触发器）

drop table if EXISTS t_cst_use_recorde;
CREATE TABLE `t_cst_use_recorde` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `his_patient_id` varchar(32) DEFAULT NULL COMMENT '患者医院Id',
  `patient_name` varchar(32) DEFAULT NULL COMMENT '患者名字',
  `case_no` varchar(32) DEFAULT NULL COMMENT '病案号',
  `gender` varchar(2) DEFAULT NULL COMMENT '性别',
  `dept_id` varchar(32) DEFAULT NULL COMMENT '科室编码',
  `dept_name` varchar(32) DEFAULT NULL COMMENT '科室名称',
  `his_schedule_id` varchar(32) DEFAULT NULL COMMENT '医院手术Id',
  `his_doctor_id` varchar(32) DEFAULT NULL COMMENT '手术医生Id',
  `doctor_name` varchar(32) DEFAULT NULL COMMENT '手术医生姓名',
  `surgery_name` varchar(32) DEFAULT NULL COMMENT '手术名称',
  `epc` varchar(32) DEFAULT NULL COMMENT '耗材epc编码',
  `inventory_id` varchar(32) DEFAULT NULL COMMENT '库存表主键id',
  `operator_name` varchar(32) DEFAULT NULL COMMENT '操作人名称',
  `operation_time` datetime DEFAULT NULL COMMENT '操作时间',
  `cst_code` varchar(255) DEFAULT NULL COMMENT '耗材编码',
  `cst_model` varchar(64) DEFAULT NULL COMMENT '耗材型号',
  `cst_name` varchar(255) DEFAULT NULL COMMENT '耗材名称',
  `cst_spec` varchar(255) DEFAULT NULL COMMENT '耗材规格',
  `manu_name` varchar(64) DEFAULT NULL COMMENT '生产厂家名称',
  `sth_id` varchar(64) DEFAULT NULL COMMENT '库房id',
  `sth_name` varchar(64) DEFAULT NULL COMMENT '库房名称',
  `vendor_name` varchar(64) DEFAULT NULL COMMENT '供应商名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='患者使用记录表';


drop table if EXISTS t_sys_log_configure;
CREATE TABLE `t_sys_log_configure` (
  `log_id` char(32) NOT NULL,
  `log_title` varchar(30) DEFAULT NULL,
  `log_method` varchar(50) DEFAULT NULL,
  `log_args` varchar(300) DEFAULT NULL,
  `log_type` varchar(10) DEFAULT NULL COMMENT '日志类型，1新增/修改，3删除',
  `trans_code` char(4) DEFAULT NULL,
  `tags` varchar(100) DEFAULT NULL,
  `log_insert_date` date DEFAULT NULL,
  `log_update_date` date DEFAULT NULL,
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入手术和临时患者表字段
DROP PROCEDURE IF EXISTS insert_columns;
CREATE PROCEDURE insert_columns()
BEGIN

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='dept_name') THEN
alter table t_trans_surgery_info 
        add column dept_name varchar(255) DEFAULT NULL COMMENT '科室名称';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='dept_pym') THEN
alter table t_trans_surgery_info 
        add column dept_pym varchar(255) DEFAULT NULL COMMENT '科室拼音码';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='his_patient_id') THEN
alter table t_trans_surgery_info 
        add column his_patient_id varchar(255) DEFAULT NULL COMMENT 'HIS患者ID';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='opt_room_name') THEN
alter table t_trans_surgery_info 
        add column opt_room_name varchar(255) DEFAULT NULL COMMENT '手术间名称';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='opt_room_no') THEN
alter table t_trans_surgery_info 
        add column opt_room_no varchar(255) DEFAULT NULL COMMENT '手术间编码';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='opt_room_pym') THEN
alter table t_trans_surgery_info 
        add column opt_room_pym varchar(255) DEFAULT NULL COMMENT '手术间拼音码';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='patient_id') THEN
alter table t_trans_surgery_info 
        add column patient_id varchar(255) DEFAULT NULL COMMENT '患者ID';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='patient_name') THEN
alter table t_trans_surgery_info 
        add column patient_name varchar(255) DEFAULT NULL COMMENT '患者名称';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='patient_pym') THEN
alter table t_trans_surgery_info 
        add column patient_pym varchar(255) DEFAULT NULL COMMENT '患者拼音码';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='patient_type') THEN
alter table t_trans_surgery_info 
        add column patient_type varchar(255) DEFAULT NULL COMMENT '患者类型';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='ward_code') THEN
alter table t_trans_surgery_info 
        add column ward_code varchar(32) DEFAULT NULL COMMENT '所在病区代码';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_surgery_info' AND COLUMN_NAME='ward_name') THEN
alter table t_trans_surgery_info 
        add column ward_name varchar(32) DEFAULT NULL COMMENT '所在病区名称';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_temp_patient' AND COLUMN_NAME='dept_name') THEN
alter table t_trans_temp_patient 
        add column dept_name varchar(255) DEFAULT NULL COMMENT '科室名称';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_temp_patient' AND COLUMN_NAME='dept_pym') THEN
alter table t_trans_temp_patient 
        add column dept_pym varchar(255) DEFAULT NULL COMMENT '科室拼音码';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_temp_patient' AND COLUMN_NAME='opt_room_name') THEN
alter table t_trans_temp_patient 
        add column opt_room_name varchar(255) DEFAULT NULL COMMENT '手术间名称';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_temp_patient' AND COLUMN_NAME='opt_room_no') THEN
alter table t_trans_temp_patient 
        add column opt_room_no varchar(255) DEFAULT NULL COMMENT '手术间编码';
end if;

IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_trans_temp_patient' AND COLUMN_NAME='opt_room_pym') THEN
alter table t_trans_temp_patient 
        add column opt_room_pym varchar(255) DEFAULT NULL COMMENT '手术间拼音码';
end if;

end ;
call insert_columns;


DROP TRIGGER
IF EXISTS auto_set_value;

CREATE TRIGGER auto_set_value BEFORE INSERT ON t_trans_surgery_info FOR EACH ROW
BEGIN

SET new.patient_id = (
    SELECT
        p.patient_id
    FROM
        t_trans_patient p
    LEFT JOIN t_trans_seek_medical m ON p.patient_id = m.patient_id
    WHERE
        m.medical_id = new.medical_id
);


SET new.his_patient_id = (
    SELECT
        p.his_patient_id
    FROM
        t_trans_patient p
    LEFT JOIN t_trans_seek_medical m ON p.patient_id = m.patient_id
    WHERE
        m.medical_id = new.medical_id
);


SET new.patient_name = (
    SELECT
        p.patient_name
    FROM
        t_trans_patient p
    LEFT JOIN t_trans_seek_medical m ON p.patient_id = m.patient_id
    WHERE
        m.medical_id = new.medical_id
);


SET new.patient_pym = (
    SELECT
        p.pym
    FROM
        t_trans_patient p
    LEFT JOIN t_trans_seek_medical m ON p.patient_id = m.patient_id
    WHERE
        m.medical_id = new.medical_id
);

SET new.patient_type = (
    SELECT
        m.patient_type
    FROM
        t_trans_seek_medical m
    WHERE
        m.medical_id = new.medical_id
);


SET new.opt_room_name = (
    SELECT
        r.room_name
    FROM
        t_base_operation_room r
    WHERE
        r.opt_room_id = new.opt_room_id
);


SET new.opt_room_pym = (
    SELECT
        r.pym
    FROM
        t_base_operation_room r
    WHERE
        r.opt_room_id = new.opt_room_id
);


SET new.opt_room_no = (
    SELECT
        r.room_no
    FROM
        t_base_operation_room r
    WHERE
        r.opt_room_id = new.opt_room_id
);


SET new.dept_pym = (
    SELECT
        d.pym
    FROM
        t_base_dept d
    WHERE
        d.dept_id = new.dept_id
);


SET new.dept_name = (
    SELECT
        d.dept_name
    FROM
        t_base_dept d
    WHERE
        d.dept_id = new.dept_id
);

END;


DROP TRIGGER
IF EXISTS auto_set_temp_patient_value;

CREATE TRIGGER auto_set_temp_patient_value BEFORE INSERT ON t_trans_temp_patient FOR EACH ROW
BEGIN

SET new.opt_room_name = (
    SELECT
        r.room_name
    FROM
        t_base_operation_room r
    WHERE
        r.opt_room_id = new.room_id
);


SET new.opt_room_pym = (
    SELECT
        r.pym
    FROM
        t_base_operation_room r
    WHERE
        r.opt_room_id = new.room_id
);


SET new.opt_room_no = (
    SELECT
        r.room_no
    FROM
        t_base_operation_room r
    WHERE
        r.opt_room_id = new.room_id
);


SET new.dept_pym = (
    SELECT
        d.pym
    FROM
        t_base_dept d
    WHERE
        d.dept_id = new.dept_id
);


SET new.dept_name = (
    SELECT
        d.dept_name
    FROM
        t_base_dept d
    WHERE
        d.dept_id = new.dept_id
);

END;

drop TRIGGER if EXISTS auto_inventory_value;

CREATE TRIGGER auto_inventory_value BEFORE update ON t_cst_inventory FOR EACH ROW
begin
SET new.dept_id = (
    SELECT
        s.dept_id
    FROM
        t_base_dept_storehouse s
    WHERE
        s.sth_id = new.sth_id limit 1
);
end;




DROP PROCEDURE IF EXISTS insert_columns;
CREATE PROCEDURE insert_columns()
BEGIN
IF NOT EXISTS(SELECT 1 FROM  information_schema.COLUMNS WHERE TABLE_SCHEMA=(SELECT database()) AND table_name='t_cst_inventory_journal' AND COLUMN_NAME='thing_id') THEN
alter table t_cst_inventory_journal 
        add column thing_id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '设备ID';
end if;
end ;
call insert_columns;

 附上兼容navicat12版本的储存过程示例

-- 储存过程version_update_ten开始
DROP PROCEDURE IF EXISTS version_update_ten;
-- 非12版本navicat请去掉该行下面的分号
;
delimiter ;;
CREATE PROCEDURE version_update_ten() BEGIN



-- 2019/10/28 新增指纹仪部件字典
IF NOT EXISTS (SELECT * from t_base_device_dict where `name` = '指纹仪')
THEN
INSERT INTO `t_base_device_dict` (`dict_id`, `name`, `code`, `model`, `brand`, `device_type`, `status`, `remark`, `update_time`, `is_parts`, `is_ruihua`) VALUES ('4028728164f3373c0164f34402a80019', '指纹仪', '002', '指纹仪', '指纹仪', '3', '1', '', '2019-10-28 11:18:43', '1', '1');
END IF;

-- 2019/10/28 新增IC卡部件字典
IF NOT EXISTS (SELECT * from t_base_device_dict where `name` = 'IC卡')
THEN
INSERT INTO `t_base_device_dict` (`dict_id`, `name`, `code`, `model`, `brand`, `device_type`, `status`, `remark`, `update_time`, `is_parts`, `is_ruihua`) VALUES ('4028728164f3373c0164f34402a80020', 'IC卡', '003', 'IC卡', 'IC卡', '4', '1', '', '2019-10-28 11:20:30', '1', '1');
END IF;


-- 新增pad  移除权限
IF NOT EXISTS (SELECT * from t_base_func where func_name = '移除' and system_type = 'HCT') THEN
INSERT INTO `t_base_func` (`func_id`, `system_type`, `func_name`, `is_leaf`, `is_intf`, `seq`, `parent_seq`, `menu_seq`, `url`, `img_url`, `security_url`, `version`, `use_state`, `create_time`, `update_time`, `btn_permission`, `func_level`) VALUES ('4028efc46e3fca1f016e3fed690d0000', 'HCT', '移除', NULL, NULL, '20', '2', NULL, NULL, NULL, NULL, '2.6.10', '1', '2019-11-06 16:55:54', '2019-11-06 16:55:54', '0', NULL);
END IF;


-- 新增主辅柜配置项
IF NOT EXISTS (SELECT * from t_base_config_dict where code = '057') THEN
INSERT INTO `t_base_config_dict`(`dict_id`, `name`, `code`, `grade`, `val_type`, `status`, `remark`, `create_time`, `update_time`) VALUES ('ff80818165c787c00165c7ac89145700', '是否主辅柜之间耗材可任意退回', '057', '3', '0', b'1', '此配置项只对主辅柜有效，单主柜即使开启配置项也需要退回到对应柜子', '2018-09-11 00:00:00', '2019-11-18 13:59:16');
END IF;


-- 新增cabinetType柜子类型：1：上柜 -1：下柜 0：单柜
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=(SELECT database())  AND table_name = 't_base_device' AND COLUMN_NAME = 'cabinet_type') THEN
ALTER TABLE `t_base_device`
ADD COLUMN `cabinet_type`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '柜子类型：1：上柜 -1：下柜 0：单柜';
END IF;

-- 柜子编号：同个编号表示同一个柜子
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=(SELECT database())  AND table_name = 't_base_device' AND COLUMN_NAME = 'cabinet_num') THEN
ALTER TABLE `t_base_device`
ADD COLUMN `cabinet_num`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '柜子编号：同个编号表示同一个柜子';
END IF;

-- 手书表新增申请科室名
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=(SELECT database())  AND table_name = 't_trans_surgery_info' AND COLUMN_NAME = 'order_dept_name') THEN
ALTER TABLE `t_trans_surgery_info`
ADD COLUMN `order_dept_name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '申请科室名';
END IF;

-- 修改过滤epc相关配置项名称
UPDATE t_base_config_dict set name = '只显示多少位的EPC（输入位数）' where `code` = '043';
UPDATE t_base_config_dict set name = '过滤掉特定字符开头的EPC（输入字符串）' where `code` = '044';
UPDATE t_base_config_dict set name = '过滤掉特定字符结尾的EPC（输入字符串）' where `code` = '045';

-- bug:14345问题，patient_type为空时诊间计费选择患者会报错
UPDATE t_trans_surgery_info SET patient_type = '2' where patient_type is null;
UPDATE t_trans_seek_medical set patient_type = '2' where patient_type is null;

ALTER TABLE `t_trans_seek_medical`
MODIFY COLUMN `patient_type`  char(1) DEFAULT 2 COMMENT '患者类别(1:急诊患者;2:住院患者;3:门诊患者)';

ALTER TABLE `t_trans_surgery_info`
MODIFY COLUMN `patient_type`  char(1) DEFAULT 2 COMMENT '患者类别(1:急诊患者;2:住院患者;3:门诊患者)';

-- 新增hrp盘点需要对应表

DROP TABLE IF EXISTS `t_trans_hrp_check_order`;
CREATE TABLE `t_trans_hrp_check_order` (
  `order_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '盘点单主键',
  `order_no` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '盘点单编号',
  `sth_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '库房主键id',
  `sth_name` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '库房名称',
  `operator_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人id',
  `operator_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '操作人名称',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '盘点单状态：{0：执行中,1:已完成}',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `t_trans_hrp_check_order_detail`;
CREATE TABLE `t_trans_hrp_check_order_detail` (
  `detail_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '盘点单详情主键',
  `order_no` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '盘点单',
  `order_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '盘点单主键id',
  `cst_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '耗材id',
  `epc` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'epc',
  `status` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '状态',
  `device_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '耗材柜id',
  `device_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '耗材柜名称',
  `thing_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备id',
  `thing_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备名称',
  `batch_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '批号',
  `expiry_date` datetime DEFAULT NULL COMMENT '效期',
  `epc_status` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '盘盈：1，正常：0，盘亏：-1',
  `hrp_not_in` int(1) DEFAULT '0' COMMENT 'hrp未入库数',
  `hrp_not_out` int(1) DEFAULT '0' COMMENT 'hrp未出库数',
  PRIMARY KEY (`detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


DROP TABLE IF EXISTS `t_trans_hrp_inventory`;
CREATE TABLE `t_trans_hrp_inventory` (
  `hrp_inventory_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `order_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '查询标识（每次查询标识相同）',
  `sth_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '库房id',
  `cst_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '耗材id',
  `epc` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'epc编码',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- 新增菜单
IF NOT EXISTS (SELECT * from t_base_func where func_id = '4028efda6ed499ce016ed4a4fb880000') THEN
INSERT INTO `t_base_func` VALUES ('4028efda6ed499ce016ed4a4fb880000', 'HVC', 'HRP耗材盘点', null, null, '82', '24', null, null, null, 'hvcs-inventory/hrp-check', '2.6.9', '1', '2019-12-05 14:00:12', '2019-12-05 14:00:12', '0', null);
END IF;

-- 新增状态
IF NOT EXISTS (SELECT * from t_dict where dict_id in ('208','209','210','211','212','213')) THEN
INSERT INTO `t_dict` VALUES ('208', '数据传输中', '0', 'checkOrderType', '盘点状态', '0', '1', null, null);
INSERT INTO `t_dict` VALUES ('209', '已完成', '1', 'checkOrderType', '盘点状态', '0', '1', null, null);
INSERT INTO `t_dict` VALUES ('210', '数据传输失败', '2', 'checkOrderType', '盘点状态', '0', '1', null, null);
INSERT INTO `t_dict` VALUES ('211', '盘亏', '-1', 'cstCheckType', '耗材盘点状态', '0', '1', null, null);
INSERT INTO `t_dict` VALUES ('212', '正常', '0', 'cstCheckType', '耗材盘点状态', '0', '1', null, null);
INSERT INTO `t_dict` VALUES ('213', '盘盈', '1', 'cstCheckType', '耗材盘点状态', '0', '1', null, null);
END IF;

-- 增加导出模板
IF NOT EXISTS (SELECT * from t_base_excel_template where id = '402881fa6ed54577016ed54cef4e0000') THEN
INSERT INTO `t_base_excel_template` VALUES ('402881fa6ed54577016ed54cef4e0000', '2019-12-05 00:00:00', 'cn.rivamed.hvc.vo.order.HrpCheckOrderDetailVo', 'hrp盘点详情导出模板', '0', 'hrp盘点', '1', '2019-12-06 09:57:28');
END IF;

IF NOT EXISTS (SELECT * from t_base_excel_config where template_id = '402881fa6ed54577016ed54cef4e0000') THEN
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b730009', '耗材编码', 'cstCode', '0', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b74000a', '耗材名称', 'cstName', '1', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b74000b', '规格', 'cstSpec', '2', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b74000c', '生产厂商', 'manuName', '3', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b74000d', '单位', 'unit', '4', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b74000e', '柜内数量', 'invotoryInNum', '5', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b74000f', 'hrp未入库数', 'hrpNotInNum', '6', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b740010', 'hrp未出库数', 'hrpNotOutNum', '7', null, '402881fa6ed54577016ed54cef4e0000');
INSERT INTO `t_base_excel_config` VALUES ('4028efda6ed8e32e016ed8ed1b740011', 'hrp数', 'hrpNum', '8', null, '402881fa6ed54577016ed54cef4e0000');
END IF;


IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema=(SELECT database())  AND table_name = 't_trans_hrp_inventory' AND COLUMN_NAME = 'status') THEN
alter table t_trans_hrp_inventory  add column `status` char(1) COLLATE utf8mb4_general_ci DEFAULT '2';
END IF;

-- 删除盘点详情表里HRP出库的数据
DELETE from t_trans_hrp_check_order_detail where epc in (select epc from t_cst_inventory where status = '3' and is_confirm_used = '1');



-- 储存过程version_update_ten结束
END
;;
delimiter ;
CALL version_update_ten();