背景
数据库中有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;