Oracle pl-sql 转义字符（用于“'”）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11717159/
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
Oracle pl-sql escape character (for a " ' ")
提问by Bishan
When I am trying to execute INSERTstatement in oracle, I got SQL Error: ORA-00917: missing commaerror because there is a value as Alex's Tea Factoryin my INSERTstatement.
当我尝试INSERT在 oracle 中执行语句时，出现SQL Error: ORA-00917: missing comma错误，因为Alex's Tea Factory我的INSERT语句中有一个值。
How could I escape '?
我怎么能逃'？
回答by Codo
To escape it, double the quotes:
要逃避它，请将引号加倍：
INSERT INTO TABLE_A VALUES ( 'Alex''s Tea Factory' );
回答by Thilo
In SQL, you escape a quote by another quote:
在 SQL 中，你用另一个引号转义一个引号：
SELECT 'Alex''s Tea Factory' FROM DUAL
回答by ganapathydselva
you can use ESCAPE like given example below
你可以像下面给出的例子一样使用 ESCAPE
The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL.
'_' 通配符用于精确匹配一个字符，而 '%' 用于匹配零次或多次出现的任何字符。这些字符可以在 SQL 中转义。
SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/';
The same works inside PL/SQL:
在 PL/SQL 中同样有效：
if( id like '%/_%' ESCAPE '/' )
This applies only to like patterns, for example in an insert there is no need to escape _ or %, they are used as plain characters anyhow. In arbitrary strings only ' needs to be escaped by ''.
这仅适用于类似的模式，例如在插入中不需要转义 _ 或 %，它们无论如何都用作普通字符。在任意字符串中，只有 ' 需要被 '' 转义。
回答by Siva
SELECT q'[Alex's Tea Factory]' FROM DUAL
回答by Wael Assaf
Instead of worrying about every single apostrophe in your statement.
You can easily use the q' Notation.
而不是担心你的陈述中的每一个撇号。您可以轻松地使用q' Notation.
Example
例子
SELECT q'(Alex's Tea Factory)' FROM DUAL;
Key Components in this notation are
此符号中的关键组件是
q'which denotes the starting of the notation(an optional symbol denoting the starting of the statement to be fully escaped.- Alex's Tea Factory(Which is the statement itself)
)'A closing parenthesis with a apostrophe denoting the end of the notation.
q'这表示符号的开始(一个可选符号，表示要完全转义的语句的开头。- 亚历克斯的茶厂（这就是声明本身）
)'带有撇号的右括号表示符号的结尾。
And such that, you can stuff how many apostrophes in the notation without worrying about each single one of them, they're all going to be handled safely.
这样，您可以在符号中填充多少个撇号，而不必担心每个撇号，它们都将得到安全处理。
IMPORTANT NOTE
重要的提示
Since you used (you must close it with )', and remember it's optional to use any other symbol, for instance, the following code will run exactly as the previous one
既然你用过，(你必须用 关闭它)'，并记住使用任何其他符号是可选的，例如，下面的代码将与上一个完全一样运行
SELECT q'[Alex's Tea Factory]' FROM DUAL;
回答by Bob Jarvis - Reinstate Monica
Your question implies that you're building the INSERT statement up by concatenating strings together. I suggest that this is a poor choice as it leaves you open to SQL injection attacks if the strings are derived from user input. A better choice is to use parameter markers and to bind the values to the markers. If you search for Oracle parameter markersyou'll probably find some information for your specific implementation technology (e.g. C# and ADO, Java and JDBC, Ruby and RubyDBI, etc).
您的问题意味着您正在通过将字符串连接在一起来构建 INSERT 语句。我建议这是一个糟糕的选择，因为如果字符串来自用户输入，它会让您容易受到 SQL 注入攻击。更好的选择是使用参数标记并将值绑定到标记。如果您进行搜索，Oracle parameter markers您可能会找到有关您的特定实现技术（例如 C# 和 ADO、Java 和 JDBC、Ruby 和 RubyDBI 等）的一些信息。
Share and enjoy.
分享和享受。
