oracle 合併多個sys_refcursor

_楊瀚博發表於2018-05-28
Oracle

一、背景

在資料開發中，有時你需要合併兩個動態遊標sys_refcursor。

開發一個儲存過程PROC_A,這個過程業務邏輯相當複雜，程式碼篇幅較長。一段時間後要開發一個PROC_B,要用PROC_A同樣的邏輯,而且在這個過程中，還要迴圈呼叫PROC_A這個過程。擺在你面前的有兩個選擇。

  • 開啟PL/SQL,仔細的讀PROC_A這個過程，一直到明白了所有的邏輯，然後在自己的過程中重寫這個邏輯 。
  • 直接複製PROC_A這個過的程式碼過來，多寫極端。還是業界標準大法好
  • 針對迴圈呼叫的，建立一個臨時表，迴圈插入資料到臨時表（但這裡還有一個問題，每次返回的遊標可能列都不相同，建立臨時表就顯得複雜了）

好吧，這個新的過程是完成了，可是看上去，它更復雜了，程式碼量更大了。完全不能接受，必須改改！ 這時，已經默默開啟了ORACLE官方幫助文件 https://docs.oracle.com/cd/B19306_01/index.htm，尋找一個可行的辦法，最終目標標是要解析，整合，合併 遊標 sys_refcursor

二、思路

經過搜尋查詢，找到以下可行的方案

  1. 序列化sys_refcursor為xml文件,ORACLE對xml支援還不錯，12C已經有JSON格式了
  2. 使用ORACLE xml解析的方法，對序列化的xml文件，新增、刪除、修改
  3. 轉換為記憶體表，通過遊標返回查詢的結果

為此你需要掌握的知識有

  • 使用 Dbms_Lob 個package操作clob型別資料，因為解析後的遊標可能用varchar2是裝不下的，幫助地址 https://docs.oracle.com/cd/E11882_01/timesten.112/e21645/d_lob.htm#TTPLP600。
  • 重點掌握Oracle型別xmltype如何使用 https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_xml.htm#BABHCHHJ

三、實現

從上邊的幫助文件中，知道xmltype的建構函式中可以直接傳入遊標xmltype(refcursor)從而得到一個xmltype，呼叫xmltype的getClobVal方法，可得到序列化的結果，所以它的結構是這樣的

<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <COLUMNNAME1></COLUMNNAME1>
    <COLUMNNAME2></COLUMNNAME2>
    <...>...</...>
  </ROW>
  ....
</ROWSET>
複製程式碼

所以，如果需要合併兩個資料列相同遊標，只需要提取DOM中的ROW節點資料儲存到定義的clob欄位中去。

提取dom中片段，採用標準的xpath語法，/*/ROW這裡提取ROW資訊

Declare
  x        xmltype;
  rowxml   clob;
  mergeXml clob;
  ref_cur  Sys_Refcursor;
  ref_cur2 Sys_Refcursor;
  ref_cur3 Sys_Refcursor;
begin
  open ref_cur for
    select F_USERNAME, F_USERCODE, F_USERID
      from Tb_System_User
     where F_userid = 1;
  Dbms_Lob.createtemporary(mergeXml, true);
  Dbms_Lob.writeappend(mergeXml, 8, '<ROWSET>');
  x := xmltype(ref_cur);
  Dbms_Output.put_line('=====完整的REFCURSOR結構=====');
  Dbms_Output.put_line(x.getClobVal());
  Dbms_Output.put_line('=====只提取行資訊=====');
  rowxml := x.extract('/ROWSET/ROW').getClobVal(0, 0);
  Dbms_Output.put_line(rowxml);
  Dbms_Lob.append(mergeXml, rowxml);ROWSET
  open ref_cur2 for
    select F_USERNAME, F_USERCODE, F_USERID
      from Tb_System_User
     where F_userid = 1000;
  x      := xmltype(ref_cur2);
  rowxml := x.extract('/ROWSET/ROW').getClobVal(0, 0);
  Dbms_Lob.append(mergeXml, rowxml);
  Dbms_Lob.writeappend(mergeXml, 9, '</ROWSET>');
  Dbms_Output.put_line('=====合併後的資訊=====');
  Dbms_Output.put_line(mergeXml);
end;
複製程式碼

執行這段程式碼輸出的結果是這樣的

=====完整的REFCURSOR結構=====
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <F_USERNAME>系統管理員</F_USERNAME>
  <F_USERCODE>admin</F_USERCODE>
  <F_USERID>1</F_USERID>
 </ROW>
</ROWSET>

=====只提取行資訊=====
<ROW>
<F_USERNAME>系統管理員</F_USERNAME>
<F_USERCODE>admin</F_USERCODE>
<F_USERID>1</F_USERID>
</ROW>

=====合併後的資訊=====
<ROWSET><ROW>
<F_USERNAME>系統管理員</F_USERNAME>
<F_USERCODE>admin</F_USERCODE>
<F_USERID>1</F_USERID>
</ROW>
<ROW>
<F_USERNAME>黃燕</F_USERNAME>
<F_USERCODE>HUANGYAN</F_USERCODE>
<F_USERID>1000</F_USERID>
</ROW>
</ROWSET>

複製程式碼

從上邊列印的結果看，我們已經成功的將兩個遊標 ref_cur和ref_cur2中我們需要的列資訊合併到了一個xml文件中。那麼接下了，我們就需要通過解析這個xml並返回一個新的sys_refcursor,這裡你有必要了解以下oracle xmltable的用法(https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm)接上邊程式碼

Dbms_Output.put_line(mergeXml);
open ref_cur3 for
    select *
      from xmltable('/ROWSET/ROW' Passing xmltype(mergeXml) Columns
                    F_USERNAME varchar2(100) path 'F_USERNAME',
                    F_USERCODE varchar2(100) path 'F_USERCODE');
複製程式碼

簡單說明下xmltable建構函式

  • 宣告xpath，指明你需要解析的dom在哪裡，比如從根找到ROW /ROWSET/ROW
  • 指明你要查詢的xmltype
  • 定義轉換列，比如把ROW下邊的F_USERNAME這個節點值，對映到遊標列F_USERNAME 這個列中

四、總結

xml作為早期資料傳輸，序列化和反序列化的檔案格式，在oracle中也有良好的支援。所以，對於基於語言之上的知識，各個語言實現方式基本相識。基礎終究是重要的。

相關文章