带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;