Oracle PL/SQL 中的格式化回车、换行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14398036/
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
Formatting carriage return, line feed in Oracle PL/SQL
提问by BenoitParis
I have problems regarding a mail body formatting I'm sending:
我在发送的邮件正文格式方面遇到问题：
Here is the KO version (no new lines):
这是 KO 版本（没有新行）：
declare
crlf VARCHAR2(2) := chr(13)||chr(10);
msg_body VARCHAR2(2000);
begin
msg_body := msg_body || ('Blablah : '|| SYSTIMESTAMP ) || crlf;
msg_body := msg_body || ('Blablah : '|| SYSTIMESTAMP ) || crlf;
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
UTL_MAIL.send(sender => '[email protected]', recipients => '[email protected]', subject => 'Blah', MESSAGE => msg_body);
end;
Here is the OK version (nice new lines):
这是 OK 版本（漂亮的新行）：
declare
crlf VARCHAR2(2) := chr(13)||chr(10);
msg_body VARCHAR2(2000);
begin
msg_body := msg_body || ('Blablah : ') || crlf;
msg_body := msg_body || ('Blablah : ') || crlf;
EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
UTL_MAIL.send(sender => '[email protected]', recipients => '[email protected]', subject => 'Blah', MESSAGE => msg_body);
end;
Best regards
此致
回答by Ben
The only difference between your two versions is the exclusion of SYSTIMESTAMP in the one that works.
您的两个版本之间的唯一区别是在一个有效的版本中排除了 SYSTIMESTAMP。
You're not explicitly converting your SYSTIMESTAMP to a character using TO_CHAR(). It will be being implicitly converted according to your NLS_DATE_FORMATinstead.
您没有使用 将 SYSTIMESTAMP 显式转换为字符TO_CHAR()。它将根据您的NLS_DATE_FORMAT进行隐式转换。
Convert it to a character correctly, using whatever format modelyou wish; for instance
使用您希望的任何格式模型将其正确转换为字符；例如
to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss:ff3')
ffis fractional seconds.
ff是小数秒。
Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:
SQL statements are easier to understand when you use explicit data type conversion functions.
Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.
Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.
Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.
Oracle 建议您指定显式转换，而不是依赖隐式或自动转换，原因如下：
使用显式数据类型转换函数时，SQL 语句更容易理解。
隐式数据类型转换会对性能产生负面影响，尤其是在将列值的数据类型转换为常量而不是相反时。
隐式转换取决于它发生的上下文，并且可能不会在每种情况下都以相同的方式工作。例如，从日期时间值到 VARCHAR2 值的隐式转换可能会返回意外的年份，具体取决于 NLS_DATE_FORMAT 参数的值。
隐式转换算法可能会因软件版本和 Oracle 产品而异。显式转换的行为更可预测。
I would recommend investigating using UTL_SMTPinstead of UTL_MAIL. You don't need to alter the session. A really simple send procedure might look like this:
我建议使用UTL_SMTP而不是 UTL_MAIL进行调查。您不需要更改会话。一个非常简单的发送过程可能如下所示：
declare
l_to_list long;
l_crlf varchar2(2) := chr(13) || chr(10);
l_conn utl_smtp.connection;
l_date varchar2(255) default to_char(sysdate, 'dd Mon yyyy hh24:mi:ss');
begin
l_conn := utl_smtp.open_connection(<mailhost>, 25);
utl_smtp.helo(l_conn, <mailhost>);
utl_smtp.mail(l_conn, <sender>);
l_to_list := address_email('To: ', <recipients>);
utl_smtp.open_data(l_conn);
utl_smtp.write_data('Date: ' || l_date);
utl_smtp.write_data('From: ' || <sender>);
utl_smtp.write_data('Subject: ' || nvl(<subject>, '(No Subject)'));
utl_smtp.write_data('X-Mailer: ' || <mailer_id>);
utl_smtp.write_data(l_to_list);
utl_smtp.write_data(l_conn, '' || l_crlf);
utl_smtp.write_data(l_conn, <msg>);
utl_smtp.close_data(l_conn);
utl_smtp.quit(l_conn);
end;
