oracle dbms_output.put_line
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4919437/
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
dbms_output.put_line
提问by Orange
Does dbms_output.put_linedecrease the performance in plsqlcode?
是否会dbms_output.put_line降低plsql代码的性能？
回答by Rob van Wijk
Everyextra line of code decreases the performance of code. After all, it is an extra instruction to be executed, which at least consumes some CPU. So yes, dbms_output.put_line decreases the performance.
每多一行代码都会降低代码的性能。毕竟是额外要执行的指令，至少要消耗一些CPU。所以是的，dbms_output.put_line 会降低性能。
The real question is: does the benefit of this extra line of code outweigh the performance penalty? Only you can answer that question.
真正的问题是：这行额外代码的好处是否超过了性能损失？只有你可以回答那个问题。
Regards,
Rob.
问候，
罗伯。
回答by Hans Olsson
Yes, it's another piece of code that needs to be executed, but unless the output is actually turned on, I think the overhead is quite minimal.
是的，这是另一段需要执行的代码，但除非实际打开输出，否则我认为开销非常小。
Here's an AskTom question with more details: Is there a performance impact for dbms_output.put_line statements left in packages?
这是一个包含更多详细信息的 AskTom 问题：是否对保留在包中的 dbms_output.put_line 语句有性能影响？
回答by Gary Myers
You can look into conditional compilationso that the DBMS_OUTPUT.PUT_LINE are only in the pre-parsed code if the procedure is compiled with the appropriate option.
您可以查看条件编译，以便 DBMS_OUTPUT.PUT_LINE 仅在预先解析的代码中，如果该过程是使用适当的选项编译的。
One question is, has DBMS_OUTPUT.ENABLE been called. If so, any value in a DBMS_OUTPUT.PUT_LINE will be recorded in the session's memory structure. If you continue pushing stuff in there and never taking it out (which might be the case with some application server connections) you might find that after a few days you have a LOT of stuff in memory.
一个问题是，是否调用了 DBMS_OUTPUT.ENABLE。如果是这样，DBMS_OUTPUT.PUT_LINE 中的任何值都将记录在会话的内存结构中。如果您继续将内容推入其中并且从未将其取出（某些应用程序服务器连接可能就是这种情况），您可能会发现几天后您的内存中有很多内容。
回答by tbone
I use a log table instead of dbms_output. Make sure to setup as autonomous transaction, something like (modify for your needs of course):
我使用日志表而不是 dbms_output。确保设置为自治事务，例如（当然可以根据您的需要进行修改）：
create or replace package body somePackage as
...
procedure ins_log(
i_msg in varchar2,
i_msg_type in varchar2,
i_msg_code in number default 0,
i_msg_context in varchar2 default null
) IS PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into myLogTable
(
created_date,
msg,
msg_type,
msg_code,
msg_context
)
values
(
sysdate,
i_msg,
i_msg_type,
i_msg_code,
i_msg_context
);
commit;
end ins_log;
...
end;
Make sure you create your log table of course. In your code, if you're doing many operations in a loop, you may want to only log once per x num operations, something like:
当然，请确保您创建了日志表。在您的代码中，如果您在一个循环中执行许多操作，您可能希望每 x num 个操作只记录一次，例如：
create or replace myProcedure as
cursor some_cursor is
select * from someTable;
v_ctr pls_integer := 0;
begin
for rec in some_cursor
loop
v_ctr := v_ctr + 1;
-- do something interesting
if (mod(v_ctr, 1000) = 0) then
somePackage.ins_log('Inserted ' || v_ctr || ' records',
'Log',
i_msg_context=>'myProcedure');
end if;
end loop;
commit;
exception
when others then
somePackage.ins_log(SQLERRM, 'Err', i_msg_context=>'myProcedure');
rollback;
raise;
end;
Note that the autonomous transaction will ensure that your log stmt gets inserted, even if an error occurs and you rollback everything else (since its a separate transaction).
请注意，自治事务将确保您的日志 stmt 被插入，即使发生错误并且您回滚其他所有内容（因为它是一个单独的事务）。
Hope this helps...much better than dbms_output ;)
希望这会有所帮助...比 dbms_output 好得多；)
回答by René Nyffenegger
It depends on the ratio of how many times you call dbms_output.put_lineversus what else you do in PL/SQL.
这取决于您调用的次数dbms_output.put_line与您在 PL/SQL 中执行的其他操作的比率。
