在PL/SQL块中使用单引号和双“单引号"

问题描述：

这是需要执行的代码块:

Here is a block of code which needs to be executed:

DECLARE
STR CLOB;
BEGIN

STR := ' CREATE TABLE TNAME AS

SELECT ... FROM INPUT_TABLE IP
WHERE ((IP.DATE_FIELD = TO_DATE('12.08.2013', 'DD.MM.sYYYY'))) ' ;

EXECUTE IMMEDIATE (STR);

END;

此块是用Java代码形成的.在执行时，这将引发异常 org.springframework.jdbc.BadSqlGrammarException .但是，当我将TO_DATE('12.08.2013', 'DD.MM.sYYYY')更改为TO_DATE(''12.08.2013'', ''DD.MM.sYYYY'')时，它将成功执行.

This block is formed in java code. On execution, this throws exception org.springframework.jdbc.BadSqlGrammarException. But when i change TO_DATE('12.08.2013', 'DD.MM.sYYYY') to TO_DATE(''12.08.2013'', ''DD.MM.sYYYY'') it executes successfully.

这是我的问题:

1)为什么在我使用单引号时会引发异常?

1) Why does it throw exception when i am using single quote?

2)单引号和双单引号"之间有什么区别?

2) What is difference between single quote and double 'single quotes'?

3)如果我始终使用双单引号"，会不会有任何后果?

3) If i always use double 'single quotes', will there be any consequences?

答

摘自

在语法的顶部分支中:

In the top branch of the syntax:

c 是用户字符集的任何成员.文字中的单引号(')必须在前面加上转义字符. 要在文字中表示一个单引号，请输入两个单引号.

c is any member of the user's character set. A single quotation mark (') within the literal must be preceded by an escape character. To represent one single quotation mark within a literal, enter two single quotation marks.

在您的原始版本中，紧接12.08.2013之前的单引号被视为该字符串的结尾-如何知道以其他方式对其进行处理? 12.08然后成为一个数字，但是它不在有用的位置，因此解析器不知道该如何处理，因此会出现错误.

In your original version, the single quote immediately before the 12.08.2013 is treated as the end of that string - how would it know to treat it any other way? The 12.08 then becomes a number, but it's not in a useful place, so the parser doesn't know what to do with it, so you get an error.

在第二个版本中，您已将引号转义为实际文本值的一部分，因此Oracle知道它们是文本的一部分，而不是标记文本的结尾.当它在分号前到达单引号时，会看到那个作为字符串的结尾，这就是您想要的.

In the second version you've escaped the quotes that are part of the actual text value, so Oracle knows they are part of the text, not marking the end of it. When it reaches the lone single quote before the semi-colon it sees that as the end of the string, which is what you want.

就像@Parado所说的那样，尝试显示用转义引号引起来的版本，您会看到它以可以直接运行的形式出现，并且转义的单引号以字符串的形式单独显示为声明.

As @Parado says, try displaying the escape-quoted version and you'll see that it appears in a form that you could run directly, with the escaped single quotes appearing as strings in their own right as part of your create statement.

您确实需要转义所有单引号，但是您可能会发现替代引号语法更容易，如本文档第二个分支中所述.在您的情况下，将是:

You do need to escape all the single quotes, but you might find the alternative quoting syntax easier, as described in the second branch in the documentation. In your case that would be:

STR := q'[ CREATE TABLE TNAME AS
SELECT ... FROM INPUT_TABLE IP
WHERE ((IP.DATE_FIELD = TO_DATE('12.08.2013', 'DD.MM.sYYYY'))) ]';

这使加引号的文字文字更易于阅读，您不必担心捕获和转义其中的所有单引号.您只需要确保选择的引号定界符没有出现在文本中即可.显示的内容将与转义引用的版本完全相同.

This makes the quoted text literal easier to read and you don't have to worry about catching and escaping all the single quotes within it. You just need to make sure you pick a quote delimiter that doesn't appear in the text. Displaying that will look exactly the same as your escape-quoted version.

在PL/SQL块中使用单引号和双“单引号"

相关推荐