如何对此Oracle查询应用某种循环? [英] How to apply kind of loop for this Oracle query?

问题描述

我有一个使用Oracle开发的查询.我要更新同一列

I have a query that was developed using Oracle. I want update the same column

"5"次.在我开发的查询下面:

'5' times. below the query that i developed:

MERGE INTO product pr 
USING(
SELECT pr.uuid,
            xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint1"]/string/text()')  AS sellingpoint1,
            xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint2"]/string/text()')  AS sellingpoint2,
            xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint3"]/string/text()')  AS sellingpoint3,
            xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint4"]/string/text()')  AS sellingpoint4,
            xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint5"]/string/text()')  AS sellingpoint5
  FROM product pr WHERE pr.defaultproductvariationid ='1tap_vEBvuEAAAE89CgjnPbb' AND pr.typecode = '16'
) defaultproducts ON (pr.uuid = '8d2p_vEBCJgAAAE8ruYjnPba')
WHEN MATCHED THEN 
UPDATE SET pr.attributes_de_de = CASE WHEN sellingpoint1 IS NOT NULL THEN
                                  CASE WHEN (SELECT count(1) existscount FROM product pr 
                                              WHERE pr.uuid = '8d2p_vEBCJgAAAE8ruYjnPba' 
                                              AND existsNode(xmltype(pr.attributes_de_de), '/attrs/attr[@name="SellingPoint1"]') = 1) = 1 
                                        THEN 
                                  UPDATEXML(XMLTYPE.createXML(pr.attributes_de_de),'/attrs/attr[@name = "SellingPoint1"]/string/text()', 
                                                    sellingpoint1).getClobVal() 
                                        ELSE 
                                  APPENDCHILDXML(xmltype(pr.attributes_de_de), 'attrs/attr[@name="SellingPoint22"]',
                                                    XMLType('<string>test</string>')).getClobVal()
                                        END  
                                    ELSE 
                                  DELETEXML(xmltype(pr.attributes_de_de), '/attrs/attr[@name="SellingPoint1"]').getClobVal()  
                                END
DELETE where pr.uuid != '8d2p_vEBCJgAAAE8ruYjnPba' 

此查询中的难题是应针对Sellingpoint1，sellingpoint2，sellingpoint3，sellingpoint4，sellingpoint5更新列"pr.attribute_de_de".如何在oracle中完成此操作.非常感谢您的任何建议

the challenge in this query is the column 'pr.attribute_de_de' should update for sellingpoint1, sellingpoint2, sellingpoint3, sellingpoint4, sellingpoint5. How this can be done in oracle. Thank you very much for any suggestions

推荐答案

您不需要循环，因为可以使用Oracle updateXML函数在一个节点中的多个节点上用新值替换现有元素，属性和其他节点.单个SQL UPDATE语句.

You shouldn't need a loop because the Oracle updateXML function can be used to replace existing elements, attributes, and other nodes with new values at multiple nodes in a single SQL UPDATE statement.

...    
UPDATE SET pr.attributes_de_de = updateXML(pr.attributes_de_de, '/attrs/attr[@name = "SellingPoint1"]/string/text()', 'NewVal_SellingPoint1',  
                                                                '/attrs/attr[@name = "SellingPoint2"]/string/text()', 'NewVal_SellingPoint2',  
                                                                '/attrs/attr[@name = "SellingPoint3"]/string/text()', 'NewVal_SellingPoint3')  
...

查看 XMLtype操作.

这篇关于如何对此Oracle查询应用某种循环?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！