用存储过程批量给表添加字段

最新推荐文章于 2021-01-21 17:29:44 发布

weixin_34044273

最新推荐文章于 2021-01-21 17:29:44 发布

阅读量642

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/AmosWang/blog/2244718

版权

2019独角兽企业重金招聘Python工程师标准>>>

把表都建完了，忽然发现少个创建时间，一个一个加字段又比较麻烦，索性来个procedure......

一、创建存储过程

CREATE PROCEDURE `BATCH_TABLE_ADD_COLUMN`();

二、具体内容

BEGIN

-- 临时变量 TABLE NAME
DECLARE T_NAME VARCHAR(256) DEFAULT NULL;
DECLARE T_SQL VARCHAR(256) DEFAULT NULL;

-- 游标相关 CURSOR TABLE_NAMES
DECLARE C_ALL_TABLE CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = 'wmmm_confidence' AND table_name REGEXP 'magic_wand'; 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET T_NAME = NULL; 

OPEN C_ALL_TABLE;
    FETCH C_ALL_TABLE INTO T_NAME;
    WHILE(T_NAME IS NOT NULL) DO
        SET @T_SQL = CONCAT('ALTER TABLE ', T_NAME, ' ADD COLUMN CREATE_TIME datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT \'创建时间\'');
        PREPARE statm FROM @T_SQL;
        EXECUTE statm;
        FETCH C_ALL_TABLE INTO T_NAME;
    END WHILE;
CLOSE C_ALL_TABLE;

END

三、题外话---游标多个参数

-- 游标相关 APPLY_INFO
DECLARE C_APPLY_INFO CURSOR FOR SELECT ID, USER_ID, MOBILE from apply_info WHERE EXTERNAL_STATUS = 'SEND' and TIMEDIFF(NOW(), CREATE_TIME) > 1000;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET T_USER_ID = NULL, T_PHONE_NO = NULL; 

OPEN C_APPLY_INFO ;
    FETCH C_APPLY_INFO INTO T_APPLY_INFO_ID, T_USER_ID, T_PHONE_NO;
    WHILE(T_APPLY_INFO_ID IS NOT NULL AND T_USER_ID IS NOT NULL AND T_PHONE_NO IS NOT NULL) DO
        -- 省略业务逻辑...
        FETCH C_APPLY_INFO INTO T_APPLY_INFO_ID, T_USER_ID, T_PHONE_NO;
    END WHILE;
CLOSE C_APPLY_INFO ;

END

四、题外话---两张遍历方式

BEGIN

-- 临时变量
DECLARE T_ID BIGINT(32) DEFAULT NULL;
DECLARE T_NAME VARCHAR(64) DEFAULT NULL;

-- 游标相关 APPL_INFO
DECLARE C_ORDER_DETAIL CURSOR FOR SELECT ID, PRODUCT_NAME from order_detail;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET T_ID = NULL, T_NAME = NULL;

OPEN C_ORDER_DETAIL;

    FETCH C_ORDER_DETAIL INTO T_ID, T_NAME;

    -- 方法 1
    -- AAA: LOOP
    --     SELECT T_ID, T_NAME;
    --     FETCH C_ORDER_DETAIL INTO T_ID, T_NAME;
    --     IF T_ID IS NULL AND T_NAME IS NULL THEN
    --         LEAVE AAA;
    --     END IF;
    -- END LOOP;

    -- 方法 2
    WHILE(T_ID IS NOT NULL AND T_NAME IS NOT NULL) DO
        SELECT T_ID, T_NAME;
        FETCH C_ORDER_DETAIL INTO T_ID, T_NAME;
    END WHILE;

CLOSE C_ORDER_DETAIL;

END

转载于:https://my.oschina.net/AmosWang/blog/2244718