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;