PL/SQL:如何循环 sql extract() 结果

发布于 2024-12-02 13:26:42 字数 2624 浏览 1 评论 0原文

这是一个任意的例子。这是一个真正的问题,但我无法分享真实的代码。

我有一个没有标准化节点名称的 xml 字符串。示例:

<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>

一些元素节点有东西子节点,有些则没有。

该 xml 包含在数据库表中(出于参数考虑:table1、column1)。

我需要使用 pl/sql 循环这段代码来获取: 1.日期字段的name属性 2.日期字段的id属性 3.日期后第一个节点的name属性 4.日期后第一个节点的id属性 5.日期之后的第二个节点的name属性 6. 日期之后的第二个节点的 id 属性

我需要对前 4 个(任意)行执行此操作(sql 查询的 rownum < 5)

到目前为止,我一直在尝试使用

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;

当我运行此命令时获取数据,我明白了:

anonymous block completed
LOOPS: 1
 DATE: date1
 PKDATE: 101
 FLDNAME: ele1ele2ele3ele4...ele15
 PKFLD: 111121131141...1151
....

所以它本质上是从该数据库记录中的其余节点中吐出所有名称属性,这些属性混合在一起(而不是像我希望的那样在列表中)。

它对 id 执行相同的操作。

注意事项: - 所有元素节点都有广泛不同的名称属性。它们不仅仅是三个字符串的列表,末尾添加了数字 (ele1); - 每个节点的所有 id 属性都截然不同。它们是一串混乱的数字(例如 10212),不按 asc/dsc 顺序排列,不连续,并且不以任何模式相关。

显然,我不能循环遍历所有元素节点,因为它们都是唯一的。 我不知道如何编写 xpath 来获取“此节点之后的所有节点”。

我是 pl/sql 的新手,并且在短短几天内就学会了您在这里看到的所有内容,因此显然该语言的更复杂/微妙的点仍然让我困惑。

您能提供的任何帮助将不胜感激。如果我有任何拼写错误或有任何不清楚之处,请告诉我,以便我进行澄清。

谢谢 问

This is an arbitrary example. It is a real problem, but I can't share real code.

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.

This xml is contained within a database table (for argument sake: table1, column1).

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

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).

It does the same for the id's.

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.

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".

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.

Thanks
Q

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

他夏了夏天 2024-12-09 13:26:42

您正在寻找 XMLTABLE

create table so10t(
  id number,
  data xmltype
);

insert into so10t values (1, xmltype(
'<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>'));
insert into so10t values (2, xmltype(
'<row>
  <date name="date2" id ="102"/>
  <elem23 name="ele23" id="201">
    <whatever/>
  </elem23>
  <elem56 name="ele56" id="402"/>
  <elem112 name="ele112" id="804"/>
</row>'));

declare
  type rec_t is record(
    date_name varchar2(10),
    date_id number,
    first_elem_name varchar2(10),
    first_elem_id number,
    second_elem_name varchar2(10),
    second_elem_id number
  );
  rec rec_t;
  cur sys_refcursor;
begin
  open cur for
    select x.*
      from so10t,
      xmltable('row' passing so10t.data
               columns
               /* 1. the name attribute of the date field */
               date_name varchar2(10) path 'date/@name',
               /* 2. the id attribute of the date field */
               date_id number path 'date/@id',
               /* 3. the name attribute of the first node after the date */
               first_elem_name varchar2(10) path 'date/(following::*)[1]/@name',
               /* 4. the id attribute of the first node after the date */
               first_elem_id number path 'date/(following::*)[1]/@id',
               /* 5. the name attribute of the second node after the date */
               second_elem_name varchar2(10) path 'date/(following::*)[1]/(following::*)[1]/@name',
               /* 6. the id attribute of the second node after the date */
               second_elem_id number path 'date/(following::*)[1]/(following::*)[1]/@id'
               ) x 
      where rownum < 5 ;

  fetch cur into rec;
  while cur%found loop
    dbms_output.put_line(rec.date_name || ';' || rec.date_id || ';' ||
                         rec.first_elem_name || ';' || rec.first_elem_id || ';' ||
                         rec.second_elem_name || ';' || rec.second_elem_id);
    fetch cur into rec;
  end loop;

  close cur;
end;
/

You're looking for XMLTABLE.

create table so10t(
  id number,
  data xmltype
);

insert into so10t values (1, xmltype(
'<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>'));
insert into so10t values (2, xmltype(
'<row>
  <date name="date2" id ="102"/>
  <elem23 name="ele23" id="201">
    <whatever/>
  </elem23>
  <elem56 name="ele56" id="402"/>
  <elem112 name="ele112" id="804"/>
</row>'));

declare
  type rec_t is record(
    date_name varchar2(10),
    date_id number,
    first_elem_name varchar2(10),
    first_elem_id number,
    second_elem_name varchar2(10),
    second_elem_id number
  );
  rec rec_t;
  cur sys_refcursor;
begin
  open cur for
    select x.*
      from so10t,
      xmltable('row' passing so10t.data
               columns
               /* 1. the name attribute of the date field */
               date_name varchar2(10) path 'date/@name',
               /* 2. the id attribute of the date field */
               date_id number path 'date/@id',
               /* 3. the name attribute of the first node after the date */
               first_elem_name varchar2(10) path 'date/(following::*)[1]/@name',
               /* 4. the id attribute of the first node after the date */
               first_elem_id number path 'date/(following::*)[1]/@id',
               /* 5. the name attribute of the second node after the date */
               second_elem_name varchar2(10) path 'date/(following::*)[1]/(following::*)[1]/@name',
               /* 6. the id attribute of the second node after the date */
               second_elem_id number path 'date/(following::*)[1]/(following::*)[1]/@id'
               ) x 
      where rownum < 5 ;

  fetch cur into rec;
  while cur%found loop
    dbms_output.put_line(rec.date_name || ';' || rec.date_id || ';' ||
                         rec.first_elem_name || ';' || rec.first_elem_id || ';' ||
                         rec.second_elem_name || ';' || rec.second_elem_id);
    fetch cur into rec;
  end loop;

  close cur;
end;
/
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文