oracle存储过程批量创建索引,用存储过程批量给表添加字段

把表都建完了，忽然发现少个创建时间，一个一个加字段又比较麻烦，索性来个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