如何对此Oracle查询应用某种循环？

Question

我有一个使用Oracle开发的查询。我想更新相同的专栏

“5”次。下面是我开发的查询：

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中做到这一点。非常感谢您的建议

Answer 1

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

...    
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 operations的Oracle文档。

Answer 2

我认为在"USING“查询中需要5行。工会能行得通吗？比如说，就像这样：

MERGE INTO product pr 
USING(
  SELECT pr.uuid, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint1"]/string/text()') as sellingpoint,
  UNION ALL SELECT pr.uuid, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint2"]/string/text()'),
  UNION ALL SELECT pr.uuid, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint3"]/string/text()'),
  UNION ALL SELECT pr.uuid, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint4"]/string/text()'),
  UNION ALL SELECT pr.uuid, xmltype(pr.attributes_de_de).extract('//attr[@name = "SellingPoint5"]/string/text()')
) defaultproducts ...

..。然后是查询的其余部分，但使用"sellingpoint“而不是"sellingpoint1”、"sellingpoint2“等。

注意，UNION ALL而不是UNION:普通UNION (没有ALL)将消除重复的行。我假设您每次都想要5行，而不考虑重复项。

希望这至少是在正确的方向上的一个推动。我在使用XML查询时总是睡眼欲睡:)