SQL 使用 sqlplus 在 Oracle 中插入多行字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9090072/
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
insert a multiline string in Oracle with sqlplus
提问by Louis Rhys
I have a SQL script that will insert a long string into a table. The string contains a new line (and this new line is absolutely necessary), so when it is written in a text file, the query is split to multiple lines. Something like:
我有一个 SQL 脚本，可以将一个长字符串插入到表中。该字符串包含一个新行（并且这个新行是绝对必要的），因此当它写入文本文件时，查询被拆分为多行。就像是：
insert into table(id, string) values (1, 'Line1goesHere
Line2GoesHere
blablablabla
');
This runs ok in Toad, but when I save this as a .sql file and run it using sqlplus, it considers each line a separate query, meaning that each line will fail (beacuse insert into table(id, string) values (1, 'Line1goesHere, Line2GoesHerearen't well-formated scripts.
这在 Toad 中运行正常，但是当我将其保存为 .sql 文件并使用 sqlplus 运行它时，它会将每一行视为一个单独的查询，这意味着每一行都会失败（因为insert into table(id, string) values (1, 'Line1goesHere，Line2GoesHere不是格式良好的脚本。
SP2-0734: unknown command beginning "Line2GoesHere" - rest of line ignored.
Is there a way to fix this?
有没有办法来解决这个问题？
回答by jim mcnamara
Enable SQLBLANKLINESto allow blank lines in SQL statements. For example:
启用SQLBLANKLINES以允许在 SQL 语句中使用空行。例如：
SET SQLBLANKLINES ON
insert into table(id, string) values (1, 'Line1goesHere
Line2GoesHere
blablablabla
');
The premise of this question is slightly wrong. SQL*Plus does allow multi-line strings by default. It is only blanklines that cause problems.
这个问题的前提稍有错误。默认情况下，SQL*Plus 确实允许多行字符串。只有空行才会导致问题。
回答by ibre5041
You can also use not-well-known feature of Oracle's SQL: Perl style quoted strings.
您还可以使用 Oracle 的 SQL 的不知名特性：Perl 样式的带引号的字符串。
SQL> select q'[f dfgdfklgdfkjgd
2 sdffdslkdflkgj dglk
3 glfdglkjdgkldj ]'
4 from dual;
Q'[FDFGDFKLGDFKJGDSDFFDSLKDFLKGJDGLKGLFDGLKJDGKLDJ]'
----------------------------------------------------
f dfgdfklgdfkjgd
sdffdslkdflkgj dglk
glfdglkjdgkldj
回答by Chad
SQL*Plus Manual
You can end a SQL command in one of three ways:
- with a semicolon (;)
- with a slash (/) on a line by itself
- with a blank line
A blank line in a SQL statement or script tells SQL*Plus that you have finished entering the command, but do not want to run it yet. Press Return at the end of the last line of the command.
SQL*Plus 手册
您可以通过以下三种方式之一结束 SQL 命令：
- 带分号 (;)
- 在一行上单独加一个斜杠 (/)
- 有一个空行
SQL 语句或脚本中的空行告诉 SQL*Plus 您已完成输入命令，但还不想运行它。在命令的最后一行末尾按回车键。
Turning SQLBLANKLINES on in this situation may be the answer, but even with it you still have to worry about the following SQL*Plus commands.
在这种情况下打开 SQLBLANKLINES 可能是答案，但即使使用它，您仍然需要担心以下 SQL*Plus 命令。
@ ("at" sign) (Start of line)
@@ (double "at" sign) (Start of line)
# SQLPREFIX (Start of line)
. BLOCKTERMINATOR (Start of line and by itself)
/ (slash) (Start of line and by itself)
; SQLT[ERMINATOR] (Start of line and by itself, or at the end)
SQLPREFIX is something that you cannot turn off; it's a feature of SQL*Plus. BLOCKTERMINATOR can be activated or disabled. Slash on the other hand if it appears at the start of a new line will cause it to execute the contents in the buffer. SQL[TERMINATOR] has a similar behavior.
SQLPREFIX 是您无法关闭的；这是 SQL*Plus 的一个特性。BLOCKTERMINATOR 可以被激活或禁用。另一方面，如果斜线出现在新行的开头，则会导致它执行缓冲区中的内容。SQL[TERMINATOR] 有类似的行为。
回答by A.B.Cade
Another way of inserting newlines to a string is concatenating:
另一种向字符串插入换行符的方法是连接：
chr(13)||chr(10)
(on windows)
（在窗户上）
or just:
要不就：
chr(10)
(otherwise)
（除此以外）
