Oracle Blob转成字符串

发布时间:2016-12-8 2:32:34 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"Oracle Blob转成字符串",主要涉及到Oracle Blob转成字符串方面的内容,对于Oracle Blob转成字符串感兴趣的同学可以参考一下。

Oracle数据库中的Blob字段转成字符串的函数: 1、Utl_Raw.Cast_To_Varchar2(blob_var)只支持Blob长度小于2000的字段: --Mysql Select Count(*) From Score_News_Online A Where A.Onlineflag=1 And Trim(Unhex(Hex(A.Onetitle)))=Trim(Unhex('D5AEC8A8')) And A.Newstype='news_sc_newstype_yxdt' And (A.Asstitle Between '10000000' And '50000000') --Oracle Select Count(*) From Bp_Winner_Ticketphoto A Where Convert(Utl_Raw.Cast_To_Varchar2(A.Photodata), 'utf8', 'zhs16gbk')='债权' And A.Newstype='news_hub_newstype_yxdt' And (A.Asstitle Between '10000000' And '50000000') 2、Blob_To_Varchar 自定义函数支持Blob长度大于2000的字段: --Only for Oracle 注意字段数据类型转换 Create Or Replace Function Blob_To_Varchar (Blob_In In Blob) Return Varchar2 Is     V_Varchar Varchar2(4000);     V_Start Pls_Integer := 1;     V_Buffer Pls_Integer := 4000; Begin     If Dbms_Lob.Getlength(Blob_In) Is Null Then         Return '';     End If;     For I In 1..Ceil(Dbms_Lob.Getlength(Blob_In) / V_Buffer) Loop         --当转换出来的字符串乱码时,可尝试用注释掉的函数         --V_Varchar := Utl_Raw.Cast_To_Varchar2(Utl_Raw.Convert(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'));         V_Varchar := Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start));         V_Start := V_Start + V_Buffer;     End Loop;     Return V_Varchar; End Blob_To_Varchar; --示例: Select Xh, Lwtm, Lwztc, Lwzs, Blob_To_Varchar(Yqzlwzy) Zwzy, Blob_To_Varchar(Yqzywzy) Ywzy From Xw_Bsxwlwb

上一篇:类之间的关系
下一篇:JSTL标签库学习笔记 二、核心标签库的表达式控制标签

相关文章

相关评论