Oracle Merges multiple Sys_refcursor

Oracle Merges multiple Sys_refcursor I. BACKGROUND

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

Develop a stored procedure proc_a, the process business logic is quite complex, the code length is longer. After a period of time to develop a proc_b, to use proc_a the same logic, and in this process, but also loop calls proc_a this process. There are two choices in front of you.

• Open PL/SQL, carefully read the proc_a process, until you understand all the logic, and then rewrite the logic in your own process.
• Direct copy proc_a This over the code come over, write more extreme. or the industry standard DAFA good
• For a loop call, create a temporary table that loops through the data to the staging table (but there is also a problem where the cursor may be different each time it is returned, creating a temporary table becomes complex)

Well, the new process is done, but it looks more complicated and the code is much bigger. Completely unacceptable, must be changed!
At this time, has silently opened the Oracle official Help document Https://docs.oracle.com/cd/B19306_01/index.htm, to find a feasible way, the ultimate goal is to parse, consolidate, merge cursors Sys_refcursor

Second, the idea

After search query, find the following feasible scenarios

1. Serialized Sys_refcursor as an XML document, Oracle support for XML is good, 12C is already in JSON format
2. Use the Oracle XML parsing method to add, delete, and modify serialized XML documents
3. Convert to Memory table, returns the result of the query through a cursor

For this you need to master the knowledge there is

• Use the Dbms_lob package to manipulate CLOB type data because the parsed cursor may not fit with VARCHAR2, and the help address https://docs.oracle.com/cd/E11882_01/timesten.112/ e21645/d_lob.htm#ttplp600.
• Key to mastering Oracle type XmlType how to use Https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_xml.htm#BABHCHHJ

Third, the realization

From the help document above, know that the XmlType constructor can be passed directly to the cursor to xmltype(refcursor) get a xmltype, call XmlType getClobVal method, can get the result of serialization, so its structure is like this

1 <?XML version= "1.0"?>2 <ROWSET>3 <ROW>4 <COLUMNNAME1></COLUMNNAME1>5 <COLUMNNAME2></COLUMNNAME2>6 <...>...</...>7 </ROW>8 ....9 </ROWSET>

So, if you need to merge two data columns with the same cursor, you only need to extract the row node data from the DOM to the defined Clob field.

Extract the fragment from the DOM, using the standard XPath /ROWSET/ROW syntax, where row information is extracted

1 Declare2 x XmlType;3 Rowxml Clob;4 Mergexml Clob;5 ref_cur sys_refcursor;6 REF_CUR2 sys_refcursor;7 REF_CUR3 sys_refcursor;8 begin9 OpenRef_cur forTen SelectF_username, F_usercode, F_userid One     fromTb_system_user A    whereF_userid= 1; -  dbms_lob.createtemporary (Mergexml, true); -Dbms_lob.writeappend (Mergexml,8,'<ROWSET>'); theX:=XmlType (ref_cur); -Dbms_output.put_line ('===== Complete refcursor structure ====='); -  Dbms_output.put_line (X.getclobval ()); -Dbms_output.put_line ('===== only fetch line information ====='); +Rowxml:=X.extract ('/rowset/row'). Getclobval (0,0); -  Dbms_output.put_line (rowxml); +  dbms_lob.append (Mergexml, rowxml); ROWSET A  OpenRef_cur2 for at   SelectF_username, F_usercode, F_userid -    fromTb_system_user -   whereF_userid=  +; -X:=XmlType (REF_CUR2); -Rowxml:=X.extract ('/rowset/row'). Getclobval (0,0); -  dbms_lob.append (Mergexml, rowxml); inDbms_lob.writeappend (Mergexml,9,'</ROWSET>'); -Dbms_output.put_line ('===== Post-merger information ====='); to  Dbms_output.put_line (mergexml); + End;

The result of executing this code output is this

1 ===== Complete refcursor structure =====2 <?XML version= "1.0"?>3 <ROWSET>4 <ROW>5 <F_username>System administrator</F_username>6 <F_usercode>Admin</F_usercode>7 <F_userid>1</F_userid>8 </ROW>9 </ROWSET>Ten  One ===== only fetch line information ===== A <ROW> - <F_username>System administrator</F_username> - <F_usercode>Admin</F_usercode> the <F_userid>1</F_userid> - </ROW> -  - ===== Post-merger information ===== + <ROWSET><ROW> - <F_username>System administrator</F_username> + <F_usercode>Admin</F_usercode> A <F_userid>1</F_userid> at </ROW> - <ROW> - <F_username>Hoang Yen</F_username> - <F_usercode>Huangyan</F_usercode> - <F_userid>1000</F_userid> - </ROW> in </ROWSET>

From the results printed above, we have successfully combined two cursors ref_cur with the ref_cur2 column information we need in an XML document. So then, we need to parse this XML and return a new one sys_refcursor , here you need to understand the following Oracle xmltable usage (https://docs.oracle.com/cd/B19306_01/ server.102/b14200/functions228.htm) connect the top code

1 Dbms_output.put_line (mergexml);2 OpenRef_cur3 for3   Select *4    fromXMLTable'/rowset/row'passing XmlType (mergexml) Columns5F_usernamevarchar2( -) path'F_username',6F_usercodevarchar2( -) path'F_usercode');

Simple description under xmltable constructors

• Declare the XPath, indicating where you need to parse the DOM, such as finding the row from the root/ROWSET/ROW
• Indicate the xmltype you want to query
• Define the transformation columns, such as the F_username node value below the row, mapped to the cursor column f_username this column

Iv. Summary

XML is also well supported in Oracle as a file format for early data transfer, serialization, and deserialization. Therefore, for the knowledge based on the language, the implementation of each language basic acquaintance. The foundation is ultimately important.

Oracle Merges multiple Sys_refcursor