oracle Clob数据生成SQl

demo:

例子：

DECLARE
 REALLYBIGTEXTSTRING CLOB := '待插入的海量字符串';
BEGIN

   INSERT INTO test_table VALUES('test', REALLYBIGTEXTSTRING, '0');
end ;

commit;

--生成定义

select 'RT_1_'||rownum||' CLOB :=#'||dbms_lob.substr(a.award,4000,1) || '#;RT_2_'||rownum|| 'CLOB :=#'||dbms_lob.substr(a.content_area,4000,1) || '#;'    from achivement_bak a  order by a.remarks desc 
--生成插入语句

 select ID, TITLE, OFFICE_ID, PROFESSION, CONTACT_NAME, CONTACT_ADDR, CONTACT_EMAIL, FINISHED_YEAR, PARTNER, INVOCATION, ECONOMIC_BENEFITS, STATE, PUBLISH_TIME, VIEW_COUNT, DOWN_COUNT, ATTENTION_COUNT, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE, REMARKS, DEL_FLAG, CONTACT_NUMBER, COMMENTS ,  'RT_1_'||rownum as AWARD,'RT_2_'||rownum as CONTENT_AREA
  from achivement_bak order by remarks desc 

--处理特殊 

commit;
update achivement_bak SET award = replace(award,'chr(38)','&'),content_area = replace(content_area,'chr(38)','&');
commit;
end;

 

转载于:https://my.oschina.net/u/2277392/blog/1545790