PL/SQL:如何循环遍历sql extract()结果 [英] PL/SQL: how to loop through sql extract() results
问题描述
这是一个任意示例.这是一个实际的问题，但是我不能共享真实的代码.
This is an arbitrary example. It is a real problem, but I can't share real code.
我有一个没有标准节点名称的xml字符串.例如:
I have an xml string that does not have standardized node names. example:
<row>
<date name="date1" id="101"></date>
<element1 name="ele1" id="111">
<stuff></stuff>
<stuff></stuff>
<stuff></stuff>
</element1>
<element2 name="ele2" id="121">
</element2>
...
<element15 name="ele15" id="1151></element15>
</row>
有些元素节点有填充子元素，有些则没有.
some of the element nodes have stuff children, some do not.
此xml包含在数据库表中(出于参数考虑:table1，column1).
This xml is contained within a database table (for argument sake: table1, column1).
我需要使用pl/sql遍历此代码以获取: 1.日期字段的名称属性 2.日期字段的id属性 3.日期之后的第一个节点的名称属性 4.日期之后的第一个节点的id属性 5.日期之后的第二个节点的名称属性 6.日期后第二个节点的id属性
I need to loop through this code using pl/sql to get: 1. the name attribute of the date field 2. the id attribute of the date field 3. the name attribute of the first node after the date 4. the id attribute of the first node after the date 5. the name attribute of the second node after the date 6. the id attribute of the second node after the date
我需要对前4个(任意)行执行此操作(sql查询的rownum< 5)
I need to do this for the first 4 (arbitrary) rows (sql queries have rownum < 5)
到目前为止，我一直在尝试获取数据
So far I've been trying to get data with
set serveroutput on format word_wrapped;
DECLARE
x_att_name varchar2(4000);
x_id varchar2(4000);
x_oth_name varchar2(4000);
x_oth_id varchar2(4000);
aCount number := 1;
xpath1 varchar2(4000);
xpath2 varchar2(4000);
BEGIN
FOR i IN (
SELECT
EXTRACT(column1, '/row/date/@name') as att_name,
EXTRACT(column1, '/row/date/@id') as id,
EXTRACT(column1, '/row/date/following::*/@name') as other_name,
EXTRACT(column1, '/row/date/following::*/@id') as other_id
FROM table1
WHERE column1is not null and rownum < 5
)
LOOP
x_att_name := i.att_name.getStringVal();
x_id := i.id.getStringVal();
x_oth_name := i.other_name.getStringVal();
x_oth_id := i.other_id.getStringVal();
dbms_output.put_line('LOOPS: ' || aCount);
dbms_output.put_line(' DATE: ' || x_att_name);
dbms_output.put_line(' PKDATE: ' || x_id);
dbms_output.put_line(' FLDNAME: ' || x_oth_name);
dbms_output.put_line(' PKFLD: ' || x_oth_id);
aCount := aCount+1;
END LOOP;
END;
运行此命令时，我得到:
When I run this, I get:
anonymous block completed
LOOPS: 1
DATE: date1
PKDATE: 101
FLDNAME: ele1ele2ele3ele4...ele15
PKFLD: 111121131141...1151
....
因此，它本质上是从该数据库记录中混在一起的其余节点中吐出所有名称属性(而不是我希望的列表中).
So it essentially spits back all the name attributes from the rest of the nodes in that database record mashed together (rather than in a list as I'd hoped).
它对ID的作用相同.
注意事项: -所有元素节点的名称属性各不相同.它们不只是三个字符串的列表，在每个字符串的末尾都添加了数字(ele1). -每个节点的所有id属性都大不相同.它们是混杂的数字字符串(例如10212)，它们不按asc/dsc顺序排列，不连续，并且不以任何模式关联.
Things to note: - all of the element nodes have widely varying name attributes. They are not simply a list of three character strings with numbers added to the ends (ele1); - all of the id attributes for every node are vastly different. They are a jumbled string of numbers (10212 for example), the do not go in asc/dsc order, are not consecutive, and are not related by any pattern.
很显然，我不能仅遍历所有元素节点，因为它们都是唯一的. 我不知道如何编写xpath来获取此之后的所有节点".
Obviously I can't just loop through all of the element nodes as they're all unique. I can't figure out how to write an xpath to get "all the nodes after this one".
我是pl/sql的新手，几天之内就了解了您在此处看到的所有内容，因此显然，语言的更复杂/微妙之处仍在我耳边.
I'm brand new to pl/sql and have learned everything you see here in just a matter of days, so obviously the more complex / subtle points of the language still elude me.
您能提供的任何帮助将不胜感激.如果我有任何错别字或不清楚的地方，请告诉我，以便澄清.
Any help you can offer would be greatly appreciated. If I've made any typos or have been unclear in any way, please let me know so I can clarify.
谢谢 问
推荐答案
您正在寻找
这篇关于PL/SQL:如何循环遍历sql extract()结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
