如何在 PLSQL Developer 中测试包含 DML 的 Oracle 函数？

发布于 2024-08-14 17:39:33 字数 891 浏览 10 评论 0原文

选择不包含 DML 的 Oracle 存储函数的返回值可以通过简单地选择函数来完成：

select function_name() from dual;

如果函数包含 DML（在这种情况下进行一些插入来记录传递给函数的参数），则上述查询不是允许。 (ORA-14551)

如何选择/查看该函数的返回值？

如果我在 plsql Developer 中选择“test”，plsqldev 会生成类似以下内容的内容：

declare
  -- Non-scalar parameters require additional processing 
  result xmltype;
begin
  -- Call the function
  result := find_person(as_surname => :as_surname,
                       as_given => :as_given,
                       ad_birth_date_from => :ad_birth_date_from,
                       ad_birth_date_to => :ad_birth_date_to,
                       as_gender => :as_gender);
end;

如何查看“result”变量的值？

select result from dual;

在开始/结束块内产生

ORA-06550: PLS-00428: an INTO clause is expected in this SELECT statement

原文

Selecting the return value of an Oracle stored function that doesn't contain DML can be done by simply selecting the function:

select function_name() from dual;

If the function contains DML (in this case some inserts to log the arguments passed to the function), the above query is not allowed. (ORA-14551)

How can I select/view the return value of this function?

if I choose "test" in plsql developer, plsqldev produces something like:

declare
  -- Non-scalar parameters require additional processing 
  result xmltype;
begin
  -- Call the function
  result := find_person(as_surname => :as_surname,
                       as_given => :as_given,
                       ad_birth_date_from => :ad_birth_date_from,
                       ad_birth_date_to => :ad_birth_date_to,
                       as_gender => :as_gender);
end;

How can I view the value of the "result" variable?

select result from dual;

inside the begin/end block produces

ORA-06550: PLS-00428: an INTO clause is expected in this SELECT statement

分享到QQ

分享到微博

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

发布评论

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

看春风乍起 2024-08-21 17:39:33

将“结果”更改为“：结果”，然后单击变量网格左上角的小箭头。
它应该添加“结果”作为绑定变量，并且您可以指定其类型。

根据您的情况，最好的选择是 clob 或 PL/SQL 字符串。

您的脚本可能如下所示：

declare
  result xmltype;
begin
  result := find_person(as_surname => :as_surname,
                        as_given => :as_given,
                        ad_birth_date_from => :ad_birth_date_from,
                        ad_birth_date_to => :ad_birth_date_to,
                        as_gender => :as_gender);
  if result is null then
    :result := null;
  else
    :result := result.GetClobVal();
  end if;
end;

如您所见，它基本上是 PL/SQL Dev 为您创建的内容，除了处理如何以 PL/SQL Dev 理解的方式返回 xmltype 之外。

如果你想返回结果集，你可以返回游标：

begin
  ...
  open :someCursor for 
    select 1 from ...;
  ...

你必须将变量网格中“someCursor”的类型更改为“cursor”，否则它将不起作用。

change "result" to ":result" and click on the little arrow thingy in the top left corner of the variables grid.
It should add "result" as a bind varibale and you can specify its type.

In your case the best options are clob or PL/SQL string.

And your script could look like so:

declare
  result xmltype;
begin
  result := find_person(as_surname => :as_surname,
                        as_given => :as_given,
                        ad_birth_date_from => :ad_birth_date_from,
                        ad_birth_date_to => :ad_birth_date_to,
                        as_gender => :as_gender);
  if result is null then
    :result := null;
  else
    :result := result.GetClobVal();
  end if;
end;

As you can see, it is basically what PL/SQL Dev has created for you, except for the handling of how to return the xmltype in a way that PL/SQL Dev understands.

if you want to return a resultset, you can return cursors:

begin
  ...
  open :someCursor for 
    select 1 from ...;
  ...

You have to change the type of "someCursor" in the variables grid to "cursor" or it is not going to work.

回复收藏 0 原文

二智少女猫性小仙女 2024-08-21 17:39:33

我没有使用过 xmltype，但文档提供了以下选项：

dbms_output.put_line(result.getStringVal());

I haven't worked with xmltype, but documentation gives the following option:

dbms_output.put_line(result.getStringVal());

回复收藏 0 原文

鹤舞 2024-08-21 17:39:33

添加

pragma autonomous_transaction

到declare块中的函数允许它从dual中选择

select find_person(arguments) from dual;

由于函数中的DML只是为了记录传入函数的参数，所以使用autonomous_transaction是可以接受的，但除此之外应该避免

adding

pragma autonomous_transaction

to the function in the declare block allows it to be selected from dual

select find_person(arguments) from dual;

Since the DML in the function is simply for logging the arguments passed in to the function, it is an acceptable use of autonomous_transaction, but otherwise should be avoided

回复收藏 0 原文