MYSQL存储过程处理表字段数据更新及索引

如题实现如下功能：

• 表字段修改
• 数据更新
• 索引创建

DROP PROCEDURE if EXISTS create_column_index_for_xh_track_path_tb;

CREATE PROCEDURE create_column_index_for_xh_track_path_tb()
BEGIN
# 定义变量名称
DECLARE dbname VARCHAR(200) DEFAULT 'xht_ywp';
DECLARE tbname VARCHAR(200);
## 定义查询变量
DECLARE cursor_ CURSOR FOR 
         # 注意这里的正则匹配结尾是手机号注册的分表
         SELECT TABLE_NAME from information_schema.`TABLES` WHERE TABLE_NAME REGEXP "^xh_track_path_tb_[1][35678][0-9]{9}$";
# 打开游标
OPEN cursor_;
# 游标赋值
FETCH cursor_ into tbname;

my_tables_loop:LOOP
    # 获取单位编码
    SET @dwcode='';
    SET @dwcode_select=CONCAT('SELECT RIGHT("',tbname,'",11) INTO @dwcode');
    PREPARE dwcode_select FROM 	@dwcode_select;
    EXECUTE dwcode_select;
    # 查询是否可以修改
    SET @enable_alter = 0;
    SET @selec = CONCAT('SELECT count(*) INTO @enable_alter	FROM	information_schema.COLUMNS	WHERE		table_schema = "',dbname,'"	AND table_name = "',tbname,'"	AND column_name in ("STARTDATE","STOPDATE","NSJGID")');
    PREPARE selec FROM 	@selec;
    EXECUTE selec;
   
    # 可执行时执行修改
    IF (@enable_alter = 0 AND LENGTH(@dwcode)=11 AND tbname <> '' ) THEN
          # 开启事务处理
          START TRANSACTION;
          # ---------------------------------------新增分表字段------------------------
					# 新增修改字段语句
					SET @mysql_alter_startdate_column=CONCAT('ALTER TABLE ',tbname,' ADD STARTDATE DATETIME');
					PREPARE mysql_alter_startdate_column FROM	@mysql_alter_startdate_column;
					SET @mysql_alter_stopdate_column=CONCAT('ALTER TABLE ',tbname,' ADD STOPDATE DATETIME');
					PREPARE mysql_alter_stopdate_column FROM	@mysql_alter_stopdate_column;
					SET @mysql_alter_nsjgid_column=CONCAT('ALTER TABLE ',tbname,' ADD NSJGID VARCHAR(200)');
					PREPARE mysql_alter_nsjgid_column FROM	@mysql_alter_nsjgid_column;
          # 更新字段
	        EXECUTE mysql_alter_startdate_column;
          EXECUTE mysql_alter_stopdate_column;
          EXECUTE mysql_alter_nsjgid_column;

					# ---------------------------------------更新缺省数据------------------------
					# 更新日期字段的值
					SET @mysql_update_date_data=CONCAT('UPDATE ',tbname,' SET STARTDATE=DATE_FORMAT(START_TIME,"%Y-%m-%d 00:00:00"),STOPDATE=DATE_FORMAT(STOP_TIME,"%Y-%m-%d 00:00:00") WHERE (STARTDATE IS NULL) OR (STOPDATE IS NULL)');
					PREPARE mysql_update_date_data FROM	@mysql_update_date_data;
					# 更新组织机构数据
					SET @mysql_update_nsjg_data=CONCAT('UPDATE ',tbname,' A SET A.NSJGID=(SELECT B.NSJGID FROM XH_HLY_TB_',@dwcode,' B WHERE A.HLY_ID=B.HLY_ID) WHERE A.NSJGID IS NULL');
					PREPARE mysql_update_nsjg_data FROM	@mysql_update_nsjg_data;
          # 更新数据
          EXECUTE mysql_update_date_data;
          EXECUTE mysql_update_nsjg_data;
					# ---------------------------------------新增分表索引------------------------
					# 创建表对应索引列
					SET @mysql_create_hly_index=CONCAT('ALTER TABLE ',tbname,' ADD INDEX HLY_ID(HLY_ID)');
					PREPARE mysql_create_hly_index FROM	@mysql_create_hly_index;
					SET @mysql_create_startdate_index=CONCAT('ALTER TABLE ',tbname,' ADD INDEX STARTDATE(STARTDATE)');
					PREPARE mysql_create_startdate_index FROM	@mysql_create_startdate_index;
					SET @mysql_create_stopdate_index=CONCAT('ALTER TABLE ',tbname,' ADD INDEX STOPDATE(STOPDATE)');
					PREPARE mysql_create_stopdate_index FROM	@mysql_create_stopdate_index;
          # 创建索引
          EXECUTE mysql_create_hly_index;
					EXECUTE mysql_create_startdate_index;
					EXECUTE mysql_create_stopdate_index;
          # 提交事务
          COMMIT;
          # 赋值下一个游标
	  FETCH cursor_ INTO tbname;
    ELSEIF (@enable_alter = 0 AND LENGTH(@dwcode)<>11 AND tbname <> '')  THEN
          # 赋值下一个游标
	  FETCH cursor_ INTO tbname;
          #继续迭代
          iterate my_tables_loop;
    ELSE
          # 离开循环
          leave my_tables_loop;
    END IF;

end LOOP my_tables_loop;

CLOSE cursor_;

END