declare vsql varchar2(500); begin for vrec in (select TABLENAME from tabs where LENGTH(TABLENAME)=6 AND substr(TABLENAME,1,2)='T_') loop vsql := 'ALTER TABLE '||vrec.TABLENAME||' ADD YOUFIELD .....'; execute immediate vsql; end loop; end;
select TABLENAME from tabs where LENGTH(TABLENAME)=6 AND substr(TABLENAME,1,2)='T_' TABLENAME ->TABLE_NAME 这个是得到你需要增加列的表名,你可以看下结果是不是正确的
sql>spool c:\add_field.txtsql>select 'alter table ' || table_name ' add *****(here is your add field)' from dba_tables where table_name like 'T_%';sql>spool off然后到c:\add_field.txt,把脚本copy出来再执行
cursor tab_name is select OWNER,table_name from dba_tab_comments where table_type='TABLE'and owner in ('CANP','ZHHH','BM','PM','QM','PQ','RM','EM','CM','PM','SM','TM') and table_name not in ('PBCATCOL','PBCATEDT','PBCATFMT','PBCATTBL','PBCATVLD','BIN$8Vd3yT8DSaKKQ5FBwLpDyg==$0'); begin for t in tab_name loop s_tname:=t.owner||'.'||t.table_name; s_sql:= 'alter table '|| s_tname|| ' add (projcode varchar2(30))'; -- s_sql:='alter table '|| s_tname|| ' drop column projcode'; execute immediate s_sql;
s_comment:='comment on column '|| s_tname||'.PROJCODE is ''项目代码'''; execute immediate s_comment;
declare
vsql varchar2(500);
begin
for vrec in (select TABLENAME from tabs where LENGTH(TABLENAME)=6 AND substr(TABLENAME,1,2)='T_') loop
vsql := 'ALTER TABLE '||vrec.TABLENAME||' ADD YOUFIELD .....';
execute immediate vsql;
end loop;
end;
TABLENAME ->TABLE_NAME
这个是得到你需要增加列的表名,你可以看下结果是不是正确的
s_tname varchar2(2000);
s_sql varchar2(2000);
s_comment varchar2(1000);
cursor tab_name is select OWNER,table_name from dba_tab_comments where table_type='TABLE'and owner in ('CANP','ZHHH','BM','PM','QM','PQ','RM','EM','CM','PM','SM','TM') and table_name not in ('PBCATCOL','PBCATEDT','PBCATFMT','PBCATTBL','PBCATVLD','BIN$8Vd3yT8DSaKKQ5FBwLpDyg==$0');
begin
for t in tab_name loop
s_tname:=t.owner||'.'||t.table_name;
s_sql:= 'alter table '|| s_tname|| ' add (projcode varchar2(30))';
-- s_sql:='alter table '|| s_tname|| ' drop column projcode';
execute immediate s_sql;
s_comment:='comment on column '|| s_tname||'.PROJCODE is ''项目代码''';
execute immediate s_comment;
dbms_output.put_line(s_comment);
end loop;
end;