oracle Clob数据生成SQl

原创
2017/09/30 09:22
阅读数 208

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;

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部