从 PL/SQL 过程返回 CLOB 时是否需要使用 DBMS_LOB？

发布于 2024-08-08 21:57:30 字数 892 浏览 4 评论 0原文

我想创建一些将 XML 作为 CLOB 参数返回的 PL/SQL 过程。我只想这样做（这对于简单的测试来说效果很好）：

create or replace procedure p_xml_test_1(
  p_xml out nocopy clob
) is
begin
  p_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
    '<test><something>some value</something></test>';
end p_xml_test_1;

但是我可以访问一些其他的源代码，基本上可以做到这一点：

create or replace procedure p_xml_test_2(
  p_xml out nocopy clob
) is
  lv_xml clob;
begin
  dbms_lob.createtemporary(
    lob_loc => p_xml,
    cache   => true
  );

  lv_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
    '<test><something>some value</something></test>';

  dbms_lob.writeappend(
    lob_loc => p_xml,
    amount  => length(lv_xml),
    buffer  => lv_xml
  );
end p_xml_test_2;

我想知道第一种方法是否会给我带来任何问题。这样做可以吗？第二种方法有什么优点（如果有的话）？谢谢！

原文

I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests):

create or replace procedure p_xml_test_1(
  p_xml out nocopy clob
) is
begin
  p_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
    '<test><something>some value</something></test>';
end p_xml_test_1;

But I have access to some other source code that basically does this:

create or replace procedure p_xml_test_2(
  p_xml out nocopy clob
) is
  lv_xml clob;
begin
  dbms_lob.createtemporary(
    lob_loc => p_xml,
    cache   => true
  );

  lv_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
    '<test><something>some value</something></test>';

  dbms_lob.writeappend(
    lob_loc => p_xml,
    amount  => length(lv_xml),
    buffer  => lv_xml
  );
end p_xml_test_2;

I'm wondering if the first method will cause any problems for me down the road. Is it ok to do it that way? What is the advantage, if any, to the second method? Thanks!

分享到QQ

分享到微博

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

发布评论

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

酷遇一生 2024-08-15 21:57:30

我运行了以下程序来比较执行时间：

版本 1

create or replace procedure p_xml_test_1(
  p_xml out nocopy clob
) is
  lv_i number;
begin
  for lv_i in 1 .. 999999 loop
    p_xml := p_xml || 'a';
  end loop;
end p_xml_test_1;

版本 2

create or replace procedure p_xml_test_2(
  p_xml out nocopy clob
) is
  lv_xml clob;
  lv_i   number;
begin
  dbms_lob.createtemporary(
    lob_loc => p_xml,
    cache   => true
  );

  for lv_i in 1 .. 999999 loop
    lv_xml := 'a';

    dbms_lob.writeappend(
      lob_loc => p_xml,
      amount  => length(lv_xml),
      buffer  => lv_xml
    );
  end loop;
end p_xml_test_2;

差异可以忽略不计。两者均持续在 0.2 秒左右。

如果我将程序更改为循环到 999999 而不是 10000，则版本 1 的性能开始有所下降（大约 39 秒，而版本 2 为 32 秒）。

I ran the following procedures to compare execution time:

Version 1

create or replace procedure p_xml_test_1(
  p_xml out nocopy clob
) is
  lv_i number;
begin
  for lv_i in 1 .. 999999 loop
    p_xml := p_xml || 'a';
  end loop;
end p_xml_test_1;

Version 2

create or replace procedure p_xml_test_2(
  p_xml out nocopy clob
) is
  lv_xml clob;
  lv_i   number;
begin
  dbms_lob.createtemporary(
    lob_loc => p_xml,
    cache   => true
  );

  for lv_i in 1 .. 999999 loop
    lv_xml := 'a';

    dbms_lob.writeappend(
      lob_loc => p_xml,
      amount  => length(lv_xml),
      buffer  => lv_xml
    );
  end loop;
end p_xml_test_2;

The difference is neglible. Both come in at around .2 seconds consistently.

If I change the procedures to loop to 999999 instead of 10000, version 1's performance starts to decline somewhat (about 39 seconds versus 32 seconds for version2).

回复收藏 0 原文