oracle 在假脱机文件中添加换行符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19241913/
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
Adding a newline in a spool file
提问by Andreas
Is there a way to add a newline in an sql spool file?
I need the file to be structured in a specific way.
有没有办法在 sql 假脱机文件中添加换行符？
我需要以特定方式构建文件。
'\n' doesn't seem to work and gets printed literally
'\n' 似乎不起作用并按字面打印
thanks,
谢谢，
Update: my SQL file
更新：我的 SQL 文件
-- Prepare blank file
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 250
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL OFF
-- Create the Employees file
SPOOL employees.txt;
SELECT (case GENDER
when '1' then 'Mr.'
else 'Mrs.'
end ) ||' '||
LNAME ||', '||
FNAME ||' newline character '||
FUNCTION ||', '|| DEPARTMENT
from EMPLOYEES;
SPOOL OFF;
-- EXIT SQL and go back to the .bat
EXIT;
Result would be something like:
结果将是这样的：
Mr. Doe, John
Manager, Finance
Mr. Doe, John
财务经理
回答by Alex Poole
You can embed a newline character using its ASCII code (well, not strictly ASCII, but close enough here) using the CHRfunction, in this case with CHR(10)to represent \n:
您可以使用CHR函数使用其 ASCII 代码（好吧，不是严格的 ASCII，但在这里足够接近）嵌入换行符，在本例中用CHR(10)来表示\n：
SELECT (case GENDER
when '1' then 'Mr.'
else 'Mrs.'
end ) ||' '||
LNAME ||', '||
FNAME || CHR(10) ||
FUNCTION ||', '|| DEPARTMENT
from EMPLOYEES;
If you want a carriage return as well, append CHR(13)(\r) too.
如果您还需要回车，也可以附加CHR(13)( \r)。
As noted in comments, this introduces a blank line between rows as well. The only way to get rid of that, as far as I can tell, is to - somewhat unintuitively - SET RECSEP OFF. The new line seems to make SQL*Plus treat the output as wrapped, which you can also see with the SET WRAP OFFcommand or with the COLUMN ... TRUNCATEoption, both of which suppress the second line.
正如评论中所指出的，这也会在行之间引入一个空行。据我所知，摆脱这种情况的唯一方法是 - 有点不直观 - SET RECSEP OFF。新行似乎使 SQL*Plus 将输出视为已换行，您也可以使用SET WRAP OFF命令或COLUMN ... TRUNCATE选项查看，这两者都禁止显示第二行。
You might also want to SET TAB OFF, and SET TERMOUT OFFif you only want the data spooled to the file, not to the terminal.
您可能还想SET TAB OFF，SET TERMOUT OFF如果您只想将数据假脱机到文件，而不是终端。
(From before the question was edited with more detail)
（从更详细地编辑问题之前开始）
If you just want a blank line between the results from two queries, you can use the SQL*Plus PROMPTcommand, with no arguments:
如果您只想在两个查询的结果之间有一个空行，您可以使用不带参数的SQL*PlusPROMPT命令：
select dummy from dual;
prompt
select dummy from dual;
From the docs:
从文档：
PRO[MPT] [text]where textrepresents the text of the message you want to display.
Sends the specified message or a blank line to the user's screen. If you omit text,
PROMPTdisplays a blank line on the user's screen.
PRO[MPT] [text]其中text表示要显示的消息的文本。
将指定的消息或空行发送到用户屏幕。如果省略text，则
PROMPT在用户屏幕上显示一个空行。
You can also look at the section on formatting SQL*Plus reportsif you have other specific requirements for how the output looks.
如果您对输出的外观有其他特定要求，还可以查看有关格式化 SQL*Plus 报告的部分。
If you have a PL/SQL block that is producing output via dbms_outputcalls you can use NEW_LINEto add a blank line between existing output:
如果您有一个通过dbms_output调用生成输出的 PL/SQL 块，您可以使用NEW_LINE在现有输出之间添加一个空行：
dbms_output.put_line('some text');
dbms_output.new_line;
dbms_output.put_line('some more text');
