oracle PL/SQL，如何在字符串中转义单引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11315340/
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
PL/SQL, how to escape single quote in a string?
提问by user595234
In the Oracle PL/SQL, how to escape single quote in a string ? I tried this way, it doesn't work.
在 Oracle PL/SQL 中，如何转义字符串中的单引号？我试过这种方法，它不起作用。
declare
stmt varchar2(2000);
begin
for i in 1021 .. 6020
loop
stmt := 'insert into MY_TBL (Col) values(\'ER0002\')';
dbms_output.put_line(stmt);
execute immediate stmt;
commit;
end loop;
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end;
/
回答by DCookie
You can use literal quoting:
您可以使用文字引用：
stmt := q'[insert into MY_TBL (Col) values('ER0002')]';
Documentation for literals can be found here.
可以在此处找到文字文档。
Alternatively, you can use two quotes to denote a single quote:
或者，您可以使用两个引号来表示单引号：
stmt := 'insert into MY_TBL (Col) values(''ER0002'')';
The literal quoting mechanism with the Q syntax is more flexible and readable, IMO.
带有 Q 语法的文字引用机制更加灵活和可读，IMO。
回答by cboler
Here's a blog postthat should help with escaping ticks in strings.
这是一篇博客文章，应该有助于转义字符串中的刻度。
Here's the simplest method from said post:
这是上述帖子中最简单的方法：
The most simple and most used way is to use a single quotation mark with two single >quotation marks in both sides.
SELECT 'test single quote''' from dual;
The output of the above statement would be:
test single quote'
Simply stating you require an additional single quote character to print a single quote >character. That is if you put two single quote characters Oracle will print one. The first >one acts like an escape character.
This is the simplest way to print single quotation marks in Oracle. But it will get >complex when you have to print a set of quotation marks instead of just one. In this >situation the following method works fine. But it requires some more typing labour.
最简单也最常用的方法是用一个单引号，两边有两个单>引号。
SELECT 'test single quote''' from dual;
上述语句的输出将是：
测试单引号'
简单地说你需要一个额外的单引号字符来打印一个单引号>字符。也就是说，如果您输入两个单引号字符，Oracle 将打印一个。第一个 >one 就像一个转义字符。
这是在 Oracle 中打印单引号的最简单方法。但是当您必须打印一组引号而不是一个引号时，它会变得>复杂。在这种情况下，以下方法可以正常工作。但这需要更多的打字劳动。
回答by Corwin01
In addition to DCookie's answer above, you can also use chr(39) for a single quote.
除了上面 DCookie 的回答之外，您还可以使用 chr(39) 作为单引号。
I find this particularly useful when I have to create a number of insert/update statements based on a large amount of existing data.
当我必须基于大量现有数据创建许多插入/更新语句时，我发现这特别有用。
Here's a very quick example:
这是一个非常快速的示例：
Lets say we have a very simple table, Customers, that has 2 columns, FirstName and LastName. We need to move the data into Customers2, so we need to generate a bunch of INSERT statements.
假设我们有一个非常简单的表，客户，它有 2 列，名字和姓氏。我们需要将数据移动到Customers2 中，因此我们需要生成一堆INSERT 语句。
Select 'INSERT INTO Customers2 (FirstName, LastName) ' ||
'VALUES (' || chr(39) || FirstName || chr(39) ',' ||
chr(39) || LastName || chr(39) || ');' From Customers;
I've found this to be very useful when moving data from one environment to another, or when rebuilding an environment quickly.
我发现这在将数据从一个环境移动到另一个环境或快速重建环境时非常有用。
回答by Shravan Ramamurthy
EXECUTE IMMEDIATE 'insert into MY_TBL (Col) values(''ER0002'')'; worked for me.
closing the varchar/stringwith two pairs of single quotes did the trick. Other option could be to use usingkeyword, EXECUTE IMMEDIATE 'insert into MY_TBL (Col) values(:text_string)' using 'ER0002'; Remember usingkeyword will not work, if you are using EXECUTE IMMEDIATEto execute DDL's with parameters, however, using quotes will work for DDL's.
EXECUTE IMMEDIATE 'insert into MY_TBL (Col) values(''ER0002'')'; 对我来说有效。用两对单引号关闭varchar/string就可以了。其他选项可能是使用using关键字，EXECUTE IMMEDIATE 'insert into MY_TBL (Col) values(:text_string)' using 'ER0002'; 记住using关键字将不起作用，如果您使用EXECUTE IMMEDIATE带参数执行 DDL，但是，使用引号将适用于 DDL。
