Oracle批量给分表新增字段

场景

        Oracle数据库，给所有分表添加字段（FORMAT），我这里的分表是每月创建，后缀如“201906”。

 

思路

        根据表名规则获取该用户下的所有表名，生成批量SQL。

 

用到的函数

        wm_concat()、replace()、to_char()

 

最终SQL

        select to_char(replace(wm_concat('alter table '||table_name||' add FORMAT VARCHAR(18)'),',',';')) from user_tables where table_name like 'T_ORD_ORDER_2%';

SQL说明

        1、查询用户的所有相关分表，表名“T_ORD_ORDER_[年月]”
select table_name from user_tables where table_name like 'T_ORD_ORDER_2%';
        2、对查出的表名进行SQL拼接
（1）需要使用函数wm_concat()，该函数在10版本推出，可以把列值以英文逗号分隔起来并显示成一行，例子：select wm_concat(name) from test; 
实现的查询结果为“name1,name2,name3,……” 
（2）在表名前后添加字符串，如： 'alter table '||table_name||' add FORMAT VARCHAR(18)'
（3）SQL拼接时，需要将英文逗号替换为分号，使用函数replace()
REPLACE ( char, search_string [, replace_string]) 如果没有指定replace_string 变量的值，那么当发现search_string 变量的值时，就将其删除。输入可以为任何字符数据类型——CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB或NCLOB。
3、使用函数wm_concat()，查询出的结果类型为CLOB，可以使用函数to_char()转换输出格式

 

注

        1、确保使用like匹配出的所有分表完整且准确
        2、如果使用其他方式（比如写程序拼接SQL），需要确保分表存在
        3、本人数据库小菜，若有更好的解决方案望不吝分享，非常感谢