Shulou(Shulou.com)05/31 Report--

This article is about the case of oracle merging multiple sys_refcursor. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. 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. To develop a PROC_B after a while, use the same logic as PROC_A, and in the process, call the PROC_A procedure in a loop. You have two choices ahead of you.

Open PL/SQL, read the PROC_A process carefully until you understand all the logic, and then rewrite the logic in your own process.

Directly copy the PROC_A code over here, write more extreme. It is better to be a standard law in the industry.

For circular invocation, create a temporary table and loop insert data into the temporary table (but there is another problem: the cursor columns returned may be different each time, and it is complicated to set up a temporary table)

Well, the new process is complete, but it seems to be more complex and more code-intensive. It is totally unacceptable and must be changed!

At this time, the ORACLE official help document https://docs.oracle.com/cd/B19306_01/index.htm has been silently opened to find a feasible way. The ultimate goal is to parse, integrate, and merge cursor sys_refcursor.

Second, train of thought

After searching and querying, the following feasible solutions are found.

Serialize sys_refcursor to xml document. ORACLE supports xml well. 12C already has JSON format.

Add, delete and modify serialized xml documents by using ORACLE xml parsing method

Convert to a memory table and return the results of the query through cursors

The knowledge you need to master for this is

Use Dbms_Lob package to manipulate clob type data, because the parsed cursor may not be loaded with varchar2, which helps address https://docs.oracle.com/cd/E11882_01/timesten.112/e21645/d_lob.htm#TTPLP600.

Focus on how to use https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_xml.htm#BABHCHHJ in Oracle type xmltype

III. Realization

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

.

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 and save it in the defined clob field.

Extract fragments from dom, using standard xpath syntax, / ROWSET/ROW, extract ROW information here

Declarex 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,'); x: = xmltype (ref_cur); Dbms_Output.put_line ('= complete REFCURSOR structure ='); Dbms_Output.put_line (x.getClobVal ()) Dbms_Output.put_line ('= extract 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,''); Dbms_Output.put_line ('= merged information ='); Dbms_Output.put_line (mergeXml); end

The output from executing this code looks like this

= complete REFCURSOR structure = system administrator admin1= only extracts line information = system administrator admin1= merged information = system administrator admin1 Huang Yan HUANGYAN1000

From the results printed above, we have successfully merged the column information we need in the two cursors ref_cur and ref_cur2 into one xml document. So next, we need to parse the xml and return a new sys_refcursor. Here you need to know the usage of the following oracle xmltable (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm) followed by the above code)

Dbms_Output.put_line (mergeXml); open ref_cur3 for select * from xmltable ('/ ROWSET/ROW' Passing xmltype (mergeXml) Columns F_USERNAME varchar2) path 'Flying USERNAME, F_USERCODE varchar2 (100) path' Flying USERCODE')

Briefly explain the xmltable constructor

Declare xpath, indicating where the dom you need to parse is, such as finding ROW / ROWSET/ROW from the root

Specify the xmltype you want to query

Define the transformation column, such as mapping the node value of F_USERNAME under ROW to the column of cursor column F_USERNAME

Attachment: comparison of advantages and disadvantages between sys_refcursor and cursor

Advantage comparison

Point 1: sys_refcursor, you can return a structure set in table format as a parameter in a stored procedure (I think it is a table type, easy to understand, but it is actually a cursor set). Cursor can only be used in the implementation of stored procedures, functions, packages, etc., not as parameters.

Point 2: sys_refcursor can be used as a parameter in the package to open the database object-oriented. Ha ha. I like it. Cursor can't.

Comparison of disadvantages:

Cons: sys_refcursor cannot be operated with open,close or fetch. Difficult to learn, difficult to understand.

Cursor can be operated with open,close and fetch, which is easy to learn and understand.

Thank you for reading! This is the end of this article on "the case of oracle merging multiple sys_refcursor". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.