如何在 PLSQL Developer 中测试包含 DML 的 Oracle 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1894939/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to test an Oracle function that contains DML in PLSQL Developer?
提问by James
Selecting the return value of an Oracle stored function that doesn't contain DML can be done by simply selecting the function:
选择不包含 DML 的 Oracle 存储函数的返回值可以通过简单地选择函数来完成：
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)
如果函数包含 DML（在这种情况下，一些插入以记录传递给函数的参数），则不允许上述查询。(ORA-14551)
How can I select/view the return value of this function?
如何选择/查看此函数的返回值？
if I choose "test" in plsql developer, plsqldev produces something like:
如果我在 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;
How can I view the value of the "result" variable?
如何查看“result”变量的值？
select result from dual;
inside the begin/end block produces
在开始/结束块内产生
ORA-06550: PLS-00428: an INTO clause is expected in this SELECT statement
回答by Robert Giesecke
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.
将“result”更改为“:result”，然后单击变量网格左上角的小箭头。它应该添加“结果”作为绑定变量，您可以指定其类型。
In your case the best options are clob or PL/SQL string.
在您的情况下，最佳选择是 clob 或 PL/SQL 字符串。
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.
如您所见，它基本上是 PL/SQL Dev 为您创建的，除了处理如何以 PL/SQL Dev 理解的方式返回 xmltype。
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.
您必须将变量网格中的“someCursor”类型更改为“cursor”，否则它将无法工作。
回答by jva
I haven't worked with xmltype, but documentation gives the following option:
我没有使用过 xmltype，但文档提供了以下选项：
dbms_output.put_line(result.getStringVal());
回答by David Aldridge
SQL Developer works pretty well with DBMS_OUTPUT .. it has a separate tab for it alongside the script output, results etc. just click the little "enable output" button and use DBMS_Output.Put_Line(result);in your code.
SQL Developer 与 DBMS_OUTPUT 配合得很好。它在脚本输出、结果等旁边有一个单独的选项卡。只需单击小“启用输出”按钮并DBMS_Output.Put_Line(result);在您的代码中使用。
回答by James
adding
添加
pragma autonomous_transaction
to the function in the declareblock allows it to be selected from dual
declare块中的功能允许它从双
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
由于函数中的 DML 只是用于记录传递给函数的参数，因此它是一种可以接受的自治事务使用，但应避免使用
回答by Venkataramesh Kommoju
pragma_autonomous_transaction is one way.
pragma_autonomous_transaction 是一种方式。
but to test with out effecting your original database, there are few open source tools to test your SQL / PLSQLs like DBUNIT, utPLSQL etc.
但是为了在不影响原始数据库的情况下进行测试，很少有开源工具可以测试您的 SQL / PLSQL，例如 DBUNIT、utPLSQL 等。
these are unit testing tools for SQL and plsql
这些是 SQL 和 plsql 的单元测试工具
回答by Rene
The test screen in PLSQL developer has two sections. In the upper part you will find the code you've shown in your question. The code that the test function has generated has replaced the variables of your function with bind variables: :as_surname, :as_given etc. In the lower part of the screen you can enter values for these parameters and view the value of the result.
PLSQL developer 中的测试屏幕有两个部分。在上半部分，您会找到您在问题中显示的代码。测试函数生成的代码已将函数的变量替换为绑定变量：:as_surname、:as_given 等。在屏幕的下部，您可以输入这些参数的值并查看结果的值。
