Oracle使用PL/SQL脚本给表结构相同的动态表添加字段

背景

数据库中有400多个表结构相同的动态表， 这些表只有表名称稍微不一样， 现在需要给每个表添加一个相同的字段newField。 手工为每个表添加不太现实， 于是通过SQL脚本来实现是比较快捷的方法。 

以后涉及到动态表更新字段，删除字段都可以参考这个实现，稍微修改下，就可以了。

思路

思路超简单啦，

1.查询出所有需要添加字段的动态表

2.循环遍历这些动态表

3.检查数据表是否已经存在该字段，若不存在，添加字段

示例

第一次使用PL/SQL语法， 感觉和MS SQL Server的t-sql语法差别挺大的。

关键语法：for循环, if/else条件判断的使用

/**
 *
 * Function：动态表添加字段newField
 * author: codemouse
 * date: Jun.5th, 2012
 *  
 */
 /**
 
 代码逻辑： 
         1.所有动态表的表名以prefix_作为前缀, 后面接递增的后缀字符串（如00001,00002, ..）, 再接分钟值(如1,60，1440) ，备份数据表以_bak结尾
         如：prefix_00001，prefix_00001_bak, prefix_00001_1, prefix_00001_1_bak, prefix_00001_60, prefix_00001_60_bak, ...
             prefix_00002, prefix_00002_bak, prefix_00002_1, prefix_00002_1_bak, prefix_00002_60, prefix_00002_60_bak, ..
             prefix_00003, ....
             ... ...
         2.递增的后缀字符串存放在table_mt表中的counter字段中(如： 00001， 00002， 00003， ...)
         3.前缀prefix_和后缀字符串拼接在一起就是一个动态表名的前缀了， 所以先要遍历table_mt， 得到动态表的名称的前缀， 如prefix_00001，prefix_00002, ...
         4.在当前用户下查找到符合前缀的表：如前缀为prefix_00001的表：prefix_00001，prefix_00001prefix_00001_bak, prefix_00001_1 
         5.判断字段是否存在
         6.不存在则添加
 **/
declare 
        --动态表表名前缀
        tbl_name_prefix nvarchar2(255);
        --表名
        tbl_name nvarchar2(255);
        --字段
        add_column_name nvarchar2(255):='newField';
        --存放sql语句的字符串
        sql_str nvarchar2(500);
        --字段是否已存在
        is_exist number:=0;
        
        c_name number;
        ret number;
        --记录动态表的的数量
        mt_counter number:=0;
        
begin
        --for循环
        for counter_record in (select counter from table_mt where nodetype = 'water' order by counter) loop
            begin 
                       tbl_name_prefix := 'prefix_'||counter_record.counter;
                       --dbms_output.put_line('tbl_name_prefix: '|| tbl_name_prefix);
                       mt_counter := mt_counter + 1;
                       for tbl_name_record in (select table_name from USER_TABLES where table_name like tbl_name_prefix||'%') loop
                           dbms_output.put_line('tbl_name: '|| tbl_name_record.table_name);
                           
                           select count(column_name) into is_exist 
                           from cols
                           where table_name = upper(tbl_name_record.table_name) 
                           and column_name = upper(add_column_name);
                           
                           if(is_exist = 0) then
                                  dbms_output.put_line('字段 ['||add_column_name||'] 在表'||tbl_name_record.table_name||'中不存在');
                                  sql_str:= 'alter table '|| tbl_name_record.table_name|| ' add ' || add_column_name || ' float null';
                                  dbms_output.put_line('添加列, 动态执行DDL语句： '||sql_str);
                                  
                                  ---动态执行DDL语句
                                  --execute immediate sql_str;
                                  c_name:=dbms_sql.open_cursor;
                                  dbms_sql.parse(c_name, sql_str, dbms_sql.native);
                                  ret:= dbms_sql.execute(c_name);
                                  dbms_sql.close_cursor(c_name);
                                  
                           else
                                  dbms_output.put_line('字段 ['||add_column_name||'] 已经存在于表'||tbl_name_record.table_name||'中');
                           end if;
                       end loop;
            end;
        end loop;
        dbms_output.put_line('共有： [' || mt_counter || '] 个动态表。');
end;