带DDL话语的plsql块

带DDL语句的plsql块
--plsql块中不能定义create等表结构操作语句,
--虽然可以通过执行execute immediate 语句来实现create操作,但是不符合我的意愿
--所以要单独提出来写.
set serveroutput on

create table t_table1_bak as select * from t_table1 ;

truncate table t_table1;

alter table t_table1 drop column thFileIndex;
alter table t_table1 drop column thWebIconUrl;
alter table t_table1 drop column thWapIconUrl;
alter table t_table1 drop column thSoTerIconUrl;

alter table t_table1 add (thFileIndex sys.xmltype);
alter table t_table1 add (thWebIconUrl sys.xmltype);
alter table t_table1 add (thWapIconUrl sys.xmltype);
alter table t_table1 add (thSoTerIconUrl sys.xmltype);

declare

i_l_id                  number;             --t_table1中id字段值
i_l_thFileIndex            varchar2(1000);     --t_table1中thFileIndex字段值
i_l_thwebiconurl           varchar2(2048);     --t_table1中thWebIconUrl字段值
i_l_thwabiconurl           varchar2(2048);     --t_table1中themewabiconurl字段值
i_l_thsoteconurl       varchar2(2048);     --t_table1中themesofttericonurl字段值


cursor cur is select * from t_table1_bak;
   
begin


  for cur_result in cur loop
    begin

      i_l_id := cur_result.oid;

      i_l_thFileIndex := cur_result.thFileIndex;

      i_l_thwebiconurl := cur_result.thWebIconUrl;

      i_l_thwabiconurl := cur_result.thWapIconUrl;

      i_l_thsoteconurl := cur_result.thSoTerIconUrl;
     

      if (i_l_thFileIndex is  null) then
            i_l_thFileIndex := ' ';
      end if;
      if (i_l_thwebiconurl is  null) then
            i_l_thwebiconurl := ' ';
      end if;
      if (i_l_thwabiconurl is  null) then
            i_l_thwabiconurl := ' ';
      end if;
      if (i_l_thsoteconurl is  null) then
            i_l_thsoteconurl := ' ';
      end if;


      i_l_thFileIndex := '<vs><vau>' || i_l_thFileIndex || '</vau></vs>';
      i_l_thwebiconurl := '<vs><vau>' || i_l_thwebiconurl || '</vau></vs>';
      i_l_thwabiconurl := '<vs><vau>' || i_l_thwabiconurl || '</vau></vs>';
      i_l_thsoteconurl := '<vs><vau>' || i_l_thsoteconurl || '</vau></vs>';
     

      insert into t_table1 (ID,thFileIndex,thWebIconUrl,thWapIconUrl,thSoTerIconUrl)
      values(i_l_id,xmltype(i_l_thFileIndex),xmltype(i_l_thwebiconurl),xmltype(i_l_thwabiconurl),xmltype(i_l_thsoteconurl));
     
    end;
  end loop;
 
  commit;
end;
/

drop table t_table1_bak;