The background,

In data development, sometimes you need to merge two dynamic cursors, SYS_refCURSOR.

Develop a stored procedure PROC_A, which has complex business logic and long code. After some time, a PROC_B is developed, using the same logic as PROC_A, and in the process, PROC_A is called in a loop. You have two choices before you.

  • Open PL/SQL and read PROC_A carefully until you understand all the logic, then rewrite the logic in your own process.
  • Copy the code from PROC_A directly, write more extreme. It’s better to stick to industry standards
  • Create a temporary table for loop calls, and insert data into the temporary table through loop calls.

Ok, so this new process is done, but it looks like it’s more complicated and has more code. Totally unacceptable and must be changed! At this moment, has been quietly opened the ORACLE official documentation, https://docs.oracle.com/cd/B19306_01/index.htm, to find a feasible way to ultimate goal is to parse, consolidation, merger cursor sys_refcursor

Second, the train of thought

After a search query, the following feasible solutions were found

  1. Serialize SYS_refCURSOR to XML documents. ORACLE has good XML support. 12C already has JSON format
  2. Add, delete, and modify serialized XML documents using ORACLE XML parsing methods
  3. Converts to an in-memory table that returns the results of the query through a cursor

Here’s what you need to know

  • Use Dbms_Lob packages to manipulate cloB data, since parsed cursors may not fit with Varchar2, To help address https://docs.oracle.com/cd/E11882_01/timesten.112/e21645/d_lob.htm#TTPLP600.
  • Key to master the Oracle type xmltype how to use https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_xml.htm#BABHCHHJ

Three, implementation,

The constructor of an XMLType can be used to create an XMLType by passing the cursor xmlType (refCursor) directly to the cursor. The getClobVal method of the xmlType is called to get the serialized result

<?xml version="1.0"? >
<ROWSET>
  <ROW>
    <COLUMNNAME1></COLUMNNAME1>
    <COLUMNNAME2></COLUMNNAME2>
    <.>.</.>
  </ROW>.</ROWSET>
Copy the code

So, if you want to merge two columns of the same cursor, just extract the ROW node data from the DOM and save it into the defined CLOB field.

Extract fragments from the DOM using standard xpath syntax, /*/ROW where extract ROW information

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 (' = = = = = = = = = = complete REFCURSOR structure '); Dbms_Output.put_line(x.getClobVal()); Dbms_output.put_line ('===== extracts only line information ====='); 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>'); Put_line ('===== merged information ====='); dbms_output.put_line ('===== merged information ====='); Dbms_Output.put_line(mergeXml); end;Copy the code

The output from executing this code looks like this

===== Complete REFCURSOR structure =====<?xml version="1.0"? >
<ROWSET>
 <ROW>
  <F_USERNAME>System administrator</F_USERNAME>
  <F_USERCODE>admin</F_USERCODE>
  <F_USERID>1</F_USERID>
 </ROW>
</ROWSET>===== extracts only row information =====<ROW>
<F_USERNAME>System administrator</F_USERNAME>
<F_USERCODE>admin</F_USERCODE>
<F_USERID>1</F_USERID>
</ROW>===== Combined information =====<ROWSET><ROW>
<F_USERNAME>System administrator</F_USERNAME>
<F_USERCODE>admin</F_USERCODE>
<F_USERID>1</F_USERID>
</ROW>
<ROW>
<F_USERNAME>Yellow nest</F_USERNAME>
<F_USERCODE>HUANGYAN</F_USERCODE>
<F_USERID>1000</F_USERID>
</ROW>
</ROWSET>

Copy the code

From the results printed above, we have successfully merged the column information we need from the two cursors ref_CUR and ref_cur2 into one XML document. So next, We need to parse this XML and return a new SYS_REFCURSOR. Here’s what you need to know about Oracle The xmltable usage (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm), the code above

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');
Copy the code

A brief description of the XMLTable constructor

  • Declare xpath to indicate where the DOM you need to parse is, such as finding ROW from root/ROWSET/ROW
  • Specify the XMLType you want to query
  • Define a transformation column, such as mapping the F_USERNAME node value under ROW to the cursor column F_USERNAME

Four,

XML is also well supported in Oracle as an early file format for data transfer, serialization, and deserialization. Therefore, for language-based knowledge, each language implementation is basically familiar. The basics are important after all.