从oracle的blob字段取数据的一个例子

从oracle的blob字段取数据的一个例子

这边有一个需求，blob存放一个xml文件的内容，然后需要读取xml某一个节点的值。

今天看了下，原来用Dbms_Lob.Substr和Utl_Raw.Cast_To_Varchar2以及Utl_Raw.Cast_To_Raw配合可以实现上边的需求,请各位指正！

笔记，sql如下：

Select Zxt1.Skgkdm,
    Zxt1.Pzhm,
    Case
     When Zxt1.Cot = 1 Then
     Zxt1.Tipsrcvfisccode
     Else
     Zxt1.Tipsrcvfisccode1
    End,
    Case
     When Zxt1.Cot = 1 Then
     Zxt1.Tipsrcvfiscname
     Else
     Zxt1.Tipsrcvfiscname1
    End,
    Zxt1.Zgrcvfisccode
From (Select Zxt.Skgkdm,
       Zxt.Pzhm,
       Zxt.Tipsrcvfisccode,
       Zxt.Tipsrcvfiscname,
       Zxt.Zgrcvfisccode,
       Zxt.Cot,
       Substr(Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(t.Dealcontent, 100,
                               Dbms_Lob.Instr(t.Dealcontent, Utl_Raw.Cast_To_Raw('')) +
                               Length(''))), 0,
          Instr(Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(t.Dealcontent, 100,
                                  Dbms_Lob.Instr(t.Dealcontent,
                                          Utl_Raw.Cast_To_Raw('')) +
                                   Length(''))), '             --从xml中找到skgkMc节点的位置，然后用Dbms_Lob.Substr来截取，然后再用Utl_Raw.Cast_To_Varchar2转换成varchar2
       Substr(Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(t.Dealcontent, 100,
                               Dbms_Lob.Instr(t.Dealcontent, Utl_Raw.Cast_To_Raw('')) +
                               Length(''))), 0,
          Instr(Utl_Raw.Cast_To_Varchar2(Dbms_Lob.Substr(t.Dealcontent, 100,
                                  Dbms_Lob.Instr(t.Dealcontent,
                                          Utl_Raw.Cast_To_Raw('')) +
                                   Length(''))), '   
   From (Select a.Skgkdm,
          a.Pzhm,
          b.Tipsrcvfisccode,
          b.Tipsrcvfiscname,
          b.Zgrcvfisccode,
          Count(1) Over(Partition By a.Pzhm) Cot
      From Tb_Sky_Recdetailid    a,
         Tc_Sky_Rcvfisccodecom b
      Where a.Skgkdm = b.Tipsrcvfisccode
      And a.Pzhm In ('012008100000006744', '012008100000006900', '012008100000006929', '012008100000006939',
          '012008100000007026', '012008100000007027', '012008100000007049', '012008100000007051',
          '012008100000007054', '012008100000007197', '012008100000007198', '012008100000007199',
          '012008100000007256', '012008100000007266', '012008100000007277', '012008100000007284',
          '012008100000007363', '012008100000007423', '012008100000007454', '012008100000007456',
          '012008100000007475', '012008100000007477', '012008100000007485', '012008100000007598',
          '012008100000007837', '012008100000007880', '012008100000007934', '012008100000008014',
          '012008100000008133', '012008100000008174', '012008100000008707', '012008100000008879',
          '012008100000008951', '012008100000009040', '012008100000009266', '012008100000009307',
          '012008100000009330', '012008100000009334', '012008100000009686', '012008100000009701',
          '012008100000009705', '012008100000009715', '012008100000009748', '012008100000009758',
          '012008100000009765', '012008100000009935', '012008100000010007', '012008100000010125',
          '012008100000010129', '012008100000010168', '012008100000010194', '012008100000010520',
          '012008100000010544', '012008100000010545', '012008100000010660', '012008100000010732',
          '012008100000010735', '012008100000010746', '012008100000010771', '012008100000010773',
          '012008100000010800', '012008100000010801', '012008100000010962', '012008100000011125',
          '012008100000011133', '012008100000011154', '012008100000011235', '012008100000011242',
          '012008100000011253', '012008100000011256', '012008100000011289', '012008100000011328',
          '012008100000011474', '012008100000011585', '012008100000011626', '012008100000011807',
          '012008100000011833', '012008100000011859', '012008100000011860', '012008100000011861',
          '012008100000011862')) Zxt,
      Tb_Sky_Tipslvy t
   Where Zxt.Pzhm = t.Billno) Zxt1
Where Case When Zxt1.Cot = 1 Then 1 Else Instr(Zxt1.Tipsrcvfiscname, Zxt1.Tipsrcvfiscname1) End > 0
Order By Zxt1.Pzhm

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-490690/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-490690/