循环给表加字段并update

Question

declare                                                                                             
  v_sql   varchar2(1000);  
  v_sql2  varchar2(1000);                                                                         
begin                                                                                               
  for vrec in (select TABLE_NAME from tabs where LENGTH(TABLE_NAME)=23                              
               AND upper(substr(TABLE_NAME,1,5))='XXXX_') loop                         
     v_sql := 'ALTER TABLE '||vrec.TABLE_NAME||' ADD (COL CHAR(1) default '||'''0'')';       
     execute immediate v_sql;                                                                       
                                                                                                  
     v_sql2:='update ' || vrec.TABLE_NAME || ' a set COL =''1'' where ......';               
                                   
     execute immediate v_sql2;                                                                       
                                                                                           
  end loop;
  commit;                                                                                         
exception                                                                                           
  when others then 
     dbms_output.put('error');                                                                                 
     rollback;                                                                                      
                                                                                                    
end;                                                                                                
/ 为啥只能ALTER一个表，其他XXXX_前缀的表都不添加COL字段？

Answer 1

declare
   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 ('xxxxsss' ) and table_name not in ('xx'); 
begin
   for t in tab_name loop
      s_tname:=t.owner||'.'||t.table_name;
      s_sql:= 'alter table '|| s_tname|| ' add (projcode varchar2(30))'; 
       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;
是你的循环中的问题吧 

Answer 2

v_sql2:='update '  ¦ ¦ vrec.TABLE_NAME  ¦ ¦ ' a set COL =''1'' where ......';        
把 where ......去掉就可以了。
v_sql2:='update '  ¦ ¦ vrec.TABLE_NAME  ¦ ¦ ' a set COL =''1''';  

Answer 3

可是你在的循环是在v_sql2:='update '  ¦ ¦ vrec.TABLE_NAME  ¦ ¦ ' a set COL =''1'' where ......';  
这句话执行的时候跳进异常处理的，说明你的UPDATE 写错了，你的更新条件是什么？