分表添加字段，添加注释，存储过程及游标
CREATE PROCEDURE addFollowAlarmField()
BEGIN
-- 需要定义接收游标数据的变量
DECLARE corpId CHAR(16);
DECLARE flag INT DEFAULT FALSE;
-- 游标
DECLARE cur1 CURSOR FOR SELECT id FROM gpsbuzdb.gps_corp
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE; -- 若没有数据返回,程序继续,并将变量flag设为true
OPEN cur1;
read_loop: LOOP -- LOOP循环
FETCH cur1 INTO corpId; -- 从游标拿数据
IF flag THEN -- 如果flag=true
LEAVE read_loop; -- 退出循环
END IF;
BEGIN
SET @sqlNew=CONCAT('ALTER TABLE ',CONCAT('gps_attent','_',corpId),
' MODIFY COLUMN `displacement_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'位移报警 0:不报警 1:报警'"' AFTER `stop_opt`,
MODIFY COLUMN `create_time` datetime NULL DEFAULT NULL COMMENT '"'创建时间'"' AFTER `displacement_opt`,
MODIFY COLUMN `imei` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'设备号'"' AFTER `create_time`,
MODIFY COLUMN `opt_type` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'报警类型：6-超速报警；1-离线报警；3-行驶报警；5-停车报警；9-离省报警；17-离市报警；7-驶入报警；\r\n 8-驶出报警；16-二押点报警；10-断电报警；13-震动报警；12-位移报警；11-光感报警；14-拆除报警；4-上线报警'"' AFTER `sms_alarm_opt`,
MODIFY COLUMN `opt_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'报警名称'"' AFTER `opt_type`;');
PREPARE pstmt FROM @sqlNew; -- 预处理
EXECUTE pstmt; -- 执行SQL
END;
BEGIN
SET @sqlNew=CONCAT('ALTER TABLE ',CONCAT('gps_attent','_',corpId),' ADD COLUMN `offline_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'离线报警 0:不报警 1:报警'"' AFTER `stop_opt`,
ADD COLUMN `overspeed_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'超速报警 0:不报警 1:报警'"' AFTER `offline_opt`,
ADD COLUMN `leavecity_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'离市报警 0:不报警 1:报警'"' AFTER `overspeed_opt`,
ADD COLUMN `leaveprovince_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'离省报警 0:不报警 1:报警'"' AFTER `leavecity_opt`,
ADD COLUMN `driverout_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'驶出报警 0:不报警 1:报警'"' AFTER `leaveprovince_opt`,
ADD COLUMN `driverin_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'驶入报警 0:不报警 1:报警'"' AFTER `driverout_opt`,
ADD COLUMN `pledgeStop` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'二押点停车报警 0:不报警 1:报警'"' AFTER `driverin_opt`,
ADD COLUMN `wireoutage_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'断电报警 0:不报警 1:报警'"' AFTER `pledgeStop`,
ADD COLUMN `vibration_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'震动报警 0:不报警 1:报警'"' AFTER `displacement_opt`,
ADD COLUMN `wirelessfalloff_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'光感报警 0:不报警 1:报警'"' AFTER `vibration_opt`,
ADD COLUMN `wirelesstamper_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '"'防拆报警 0:不报警 1:报警'"' AFTER `wirelessfallOff_opt`;');
PREPARE pstmt FROM @sqlNew;
EXECUTE pstmt;
END;
END LOOP;
CLOSE cur1;
commit;
END
-- 调用存储过程 call addFollowAlarmField();
-- gps_attent 表
ALTER TABLE `gps_attent`
MODIFY COLUMN `displacement_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '位移报警 0:不报警 1:报警' AFTER `stop_opt`,
MODIFY COLUMN `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间' AFTER `displacement_opt`,
MODIFY COLUMN `imei` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '设备号' AFTER `create_time`;
ALTER TABLE `gps_attent`
ADD COLUMN `offline_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '离线报警 0:不报警 1:报警' AFTER `stop_opt`,
ADD COLUMN `overspeed_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '超速报警 0:不报警 1:报警' AFTER `offline_opt`,
ADD COLUMN `leavecity_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '离市报警 0:不报警 1:报警' AFTER `overspeed_opt`,
ADD COLUMN `leaveprovince_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '离省报警 0:不报警 1:报警' AFTER `leavecity_opt`,
ADD COLUMN `driverout_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '驶出报警 0:不报警 1:报警' AFTER `leaveprovince_opt`,
ADD COLUMN `driverin_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '驶入报警 0:不报警 1:报警' AFTER `driverout_opt`,
ADD COLUMN `pledgeStop` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二押点停车报警 0:不报警 1:报警' AFTER `driverin_opt`,
ADD COLUMN `wireoutage_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '断电报警 0:不报警 1:报警' AFTER `pledgeStop`,
ADD COLUMN `vibration_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '震动报警 0:不报警 1:报警' AFTER `displacement_opt`,
ADD COLUMN `wirelessfalloff_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '光感报警 0:不报警 1:报警' AFTER `vibration_opt`,
ADD COLUMN `wirelesstamper_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '防拆报警 0:不报警 1:报警' AFTER `wirelessfallOff_opt`;
-- gps_attent_template 表
ALTER TABLE `gps_attent_template`
MODIFY COLUMN `displacement_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '位移报警 0:不报警 1:报警' AFTER `stop_opt`,
MODIFY COLUMN `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间' AFTER `displacement_opt`,
MODIFY COLUMN `imei` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '设备号' AFTER `create_time`;
MODIFY COLUMN `opt_type` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报警类型：6-超速报警；1-离线报警；3-行驶报警；5-停车报警；9-离省报警；17-离市报警；7-驶入报警；\r\n 8-驶出报警；16-二押点报警；10-断电报警；13-震动报警；12-位移报警；11-光感报警；14-拆除报警；4-上线报警',
MODIFY COLUMN `opt_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报警名称';
ALTER TABLE `gps_attent_template`
ADD COLUMN `offline_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '离线报警 0:不报警 1:报警' AFTER `stop_opt`,
ADD COLUMN `overspeed_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '超速报警 0:不报警 1:报警' AFTER `offline_opt`,
ADD COLUMN `leavecity_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '离市报警 0:不报警 1:报警' AFTER `overspeed_opt`,
ADD COLUMN `leaveprovince_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '离省报警 0:不报警 1:报警' AFTER `leavecity_opt`,
ADD COLUMN `driverout_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '驶出报警 0:不报警 1:报警' AFTER `leaveprovince_opt`,
ADD COLUMN `driverin_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '驶入报警 0:不报警 1:报警' AFTER `driverout_opt`,
ADD COLUMN `pledgeStop` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '二押点停车报警 0:不报警 1:报警' AFTER `driverin_opt`,
ADD COLUMN `wireoutage_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '断电报警 0:不报警 1:报警' AFTER `pledgeStop`,
ADD COLUMN `vibration_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '震动报警 0:不报警 1:报警' AFTER `displacement_opt`,
ADD COLUMN `wirelessfalloff_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '光感报警 0:不报警 1:报警' AFTER `vibration_opt`,
ADD COLUMN `wirelesstamper_opt` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '防拆报警 0:不报警 1:报警' AFTER `wirelessfallOff_opt`;
