oracle - 我可以在 Xquery 中使用用户定义的函数来替换 PLSQL 中的节点值吗？

Question

我想通过我的函数 my_calculator 更改 oracle xmltype 节点值，但我不能。看那里:

set serveroutput on
declare
w_xml xmltype;
begin

SELECT
      XMLQUERY('copy $tmp := .
               modify
                (for $l3 in $tmp//Line3
                    return replace value of node $l3 with my_calculator($l3)
                )
                
                return $tmp'
               PASSING xmltype('<Response><Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>10</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
  <Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>12</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
</Response>') RETURNING CONTENT)
        into w_xml
        FROM dual;
        dbms_output.put_line( w_xml.extract('/*').getClobVal() );
end;

它返回:

Error report -
ORA-19237: XPST0017 - unable to resolve call to function - fn:my_calculator
ORA-06512: at line 5
19237. 00000 -  "XPST0017 - unable to resolve call to function - %s:%s"
*Cause:    The name and arity of the function call given could not be matched with any in-scope function in the static context.
*Action:   Fix the name of the function or the number of parameters to match the list of in-scope functions.

我可以在“return replace value of node $l3 with my_calculator($l3)”中使用用户函数吗？非常感谢。

Answer 1

您不能在 xpath 和 xquery 中调用 pl/sql 函数。它们有自己的函数和运算符:https://www.w3.org/TR/xpath-functions-31/但是您可以声明并使用自己的 xquery 函数:

SELECT
      XMLQUERY('declare function local:my_calculator($p) {xs:decimal($p + 100)}; (: eof :)
      copy $tmp := .
               modify
                (for $l3 in $tmp//Line3
                    return replace value of node $l3 with (local:my_calculator($l3))
                )
                return $tmp'
               PASSING xmltype('<Response><Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>10</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
  <Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>12</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
</Response>') RETURNING CONTENT) as xdata
FROM dual;

结果:

XDATA
----------------------------------------------------------------------------------------------------
<Response>
  <Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>110</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
  <Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>112</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
</Response>

但在这种简单的情况下，您甚至不需要 PL/SQL 函数，只需使用简单的运算符即可:

SELECT
      XMLQUERY('copy $tmp := .
               modify
                (for $l3 in $tmp//Line3
                    return replace value of node $l3 with (xs:integer($l3) + 100)
                )
                
                return $tmp'
               PASSING xmltype('<Response><Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>10</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
  <Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>12</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
</Response>') RETURNING CONTENT) as xdata
FROM dual;

结果:

XDATA
----------------------------------------------------------------------------------------------------
<Response>
  <Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>110</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
  <Card>
    <Address attr_cust="0">
      <Line2>def</Line2>
      <Line3>112</Line3>
      <Line4>jkl</Line4>
      <Line5>mno</Line5>
      <Country/>
    </Address>
  </Card>
</Response>