oracle 使用sql语句向表中添加字段 博客分类： 数据库ORACLE oraclesql表中添加字段

DECLARE
tsql   varchar2(500);
countI number;
begin
  FOR I IN (select *
              from dossier_group t
             where exists
             (select *
                      from dossier_tableinfo a
                     where a.dossier_group_id = t.id
                       and (a.englishname = 'wh' or a.englishname = 'WH'))) LOOP
   select count(*) into countI from all_Tab_Columns where table_name=upper(I.Dossier_Table||'_BAK') and column_name='JSR';
   if countI > 0 then
    tsql:= 'ALTER TABLE '||I.Dossier_Table||'_bak drop column JSR ';   
    execute immediate tsql;  
   end if;       
    tsql:= 'ALTER TABLE '||I.Dossier_Table||'_bak add JSR VARCHAR2(50) '; 
    execute   immediate   tsql;  
  END LOOP;
  EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM); 
end;