Oracle XMLQuery传递参数值 [英] Oracle XMLQuery passing parameter value
问题描述
我的XMLQuery需要帮助.
Need help in my XMLQuery.
获取循环序列
SELECT extractvalue(update_xmldoc, 'count(/invoice/AR_ITEMS/ITEMS[NAME="Voice" and ITEM_TOTAL!=0])')
INTO item_check
FROM xml_billrun_files
WHERE seq_id = seq
AND docname = REPLACE(fn,'.','_HULK.')
ORDER BY TIMESTAMP DESC;
然后item_check值将传递到循环
then item_check value will pass to the loop
FOR lp IN 1..item_check LOOP
SELECT tst, bill_info_id, bill_no, dur_net_amt, dur_vat_amt, dur_pro_net_amt, dur_pro_vat_amt, sys_descr
INTO xmlU, bp, bill, net_amt1, vat_amt1, net_amt2, vat_amt2, disc_name
FROM
(SELECT row_number() over (partition BY t.docname order by t.timestamp DESC) rn,
t.docname,
XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS['||lp||']/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
where $i = "Plan499 Corp Disc"
or $i = "Plan899 Corp Disc"
or $i = "Plan1099 Corp Disc"
or $i = "Plan1599 Corp Disc"
return $i' passing original_xmldoc returning content ).getStringVal() sys_descr
我只是张贴了一部分脚本.
i just post a part of my script.
脚本很好，但是当我在XMLQuery中传递lp的值时，我得到了错误
the script is good but when i pass the value of lp inside the XMLQuery i get the error
错误(528,66):PL/SQL:ORA-19109:期望使用RETURNING关键字
Error(528,66): PL/SQL: ORA-19109: RETURNING keyword expected
好的，所以下面是我的脚本示例.
Okay, so below is an example of my script.
DECLARE
item_check NUMBER;
fn VARCHAR2(100) := 'test.xml';
seq NUMBER := 1;
BEGIN
SELECT extractvalue(update_xmldoc, 'count(/invoice/AR_ITEMS/ITEMS[NAME="Voice" and ITEM_TOTAL!=0])')
INTO item_check
FROM xml_billrun_files
WHERE seq_id = seq
AND docname = REPLACE(fn,'.','_HULK.')
ORDER BY TIMESTAMP DESC;
IF item_check <> 0 THEN
FOR lp IN 1..item_check LOOP
SELECT tst
INTO xmlU
FROM
(SELECT row_number() over (partition BY t.docname order by t.timestamp DESC) rn,
t.docname,
XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS['||lp||']/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
where $i = "Plan499 Corp Disc"
or $i = "Plan899 Corp Disc"
or $i = "Plan1099 Corp Disc"
or $i = "Plan1599 Corp Disc"
return $i' passing original_xmldoc returning content ).getStringVal() sys_descr,
SUM(SUBSTR(x.chrg_duration,1,instr(x.chrg_duration,':',1)-1)) over (partition BY t.docname) chrg_duration,
CASE
WHEN extractvalue(xmltype('<DISCOUNT_INFO>'||XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
where $i = "Plan499 Corp Disc"
or $i = "Plan899 Corp Disc"
or $i = "Plan1099 Corp Disc"
or $i = "Plan1599 Corp Disc"
return $i' passing original_xmldoc returning content )||'</DISCOUNT_INFO>'),'DISCOUNT_INFO') = 'Plan499 Corp Disc' AND SUM(SUBSTR(x.chrg_duration,1,instr(x.chrg_duration,':',1)-1)) over (partition BY t.docname) <= prorate_mins
THEN insertchildxml(
insertchildxml(
updatexml(
updatexml(
updatexml(t.update_xmldoc,'/invoice/BILLINFO/TOTAL_DUE/text()',
extractvalue(t.update_xmldoc,'/invoice/BILLINFO/TOTAL_DUE') - SUM(x.amount) over (partition BY t.docname)),'/invoice/BILLINFO/CURRENT_TOTAL/text()',
extractvalue(t.update_xmldoc,'/invoice/BILLINFO/CURRENT_TOTAL') - (SUM(x.amount) over (partition BY t.docname))),'/invoice/BILLINFO/DISCOUNT_VALUE',
xmltype('<DISCOUNT_VALUE>'||(NVL(extractvalue(t.update_xmldoc,'/invoice/BILLINFO/DISCOUNT_VALUE'),0) - ROUND((SUM(x.amount) over (partition BY t.docname))/1.12,2))||'</DISCOUNT_VALUE>')), '/invoice/AR_ITEMS/ITEMS[NAME="Voice"]/USAGE_RECORDS[STREAM_NAME="NDD"]', 'DURATION_NET_DISCOUNT',
xmlelement("DURATION_NET_DISCOUNT", ROUND((SUM(x.amount) over (partition BY t.docname)*-1)/1.12,2))),'/invoice/AR_ITEMS/ITEMS[NAME="Voice"]/USAGE_RECORDS[STREAM_NAME="NDD"]','DURATION_VAT_DISCOUNT',
xmlelement("DURATION_VAT_DISCOUNT",ROUND(((SUM(x.amount) over (partition BY t.docname)*-1)/1.12)*.12,2)))
END tst
FROM xml_billrun_files t ,
xmltable('/invoice/AR_ITEMS/ITEMS/USAGE_RECORDS/SESSION_INFO'
passing t.update_xmldoc
columns chrg_duration VARCHAR2(20) path 'DURATION',
amount NUMBER path 'AMOUNT') x
WHERE seq_id = seq
AND t.docname = REPLACE(fn,'.','_HULK.')
)
WHERE rn = 1;
UPDATE xml_billrun_files a
SET update_xmldoc = xmlU
WHERE seq_id = seq
AND docname = REPLACE(fn,'.','_HULK.');
COMMIT;
END LOOP;
END IF;
END;
推荐答案
问题是XPath中的字符串串联.这不像你在做... AR_ITEMS['||lp||']/ITEMS ....
The problem is the string concatenation in the XPath. It doesn't like you doing ... AR_ITEMS['||lp||']/ITEMS ....
您可以通过passing子句传入PL/SQL lp变量的值，该子句允许使用多个逗号分隔的参数.给它一个标识符，然后直接在XPath中引用它.我已经使用过"lp"和$lp了；它们不必与PL/SQL变量名匹配，但是如果匹配，则可能会更清楚.
You can pass the value of your PL/SQL lp variable in through the passing clause, which allows multiple comma-separated arguments; give it an identifier and then refer to that directly in the XPath. I've stuck used "lp" and $lp; they don't have to match the PL/SQL variable name, but it might be clearer if they do.
XMLQuery('for $i in distinct-values(/invoice/AR_ITEMS[$lp]/ITEMS/EVENTS/BAL_IMPACTS/DISCOUNT_INFO)
where $i = "Plan499 Corp Disc"
or $i = "Plan899 Corp Disc"
or $i = "Plan1099 Corp Disc"
or $i = "Plan1599 Corp Disc"
return $i' passing original_xmldoc, cast(lp as number) as "lp" returning content ).getStringVal() sys_descr,
PL/SQL循环索引变量lp是通行子句不喜欢的pls_integer.如果直接将其传递给您，则会得到ORA-00932，因此需要将其强制转换为数字数据类型.
The PL/SQL loop index variable lp is a pls_integer which the passing clause doesn't like; if you pass that directly you get ORA-00932, so you need to cast it to the number data type.
这篇关于Oracle XMLQuery传递参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
