ORACLE 动态SQL中的多个单引号

在ORACLE中，单引号有两个作用，一是字符串是由单引号引用，二是转义。单引号的使用是就近配对，即就近原则。而在单引号充当转义角色时相对不好理解。

今天在项目中遇到动态拼sql的语句，

语句如下：

v_sql := \'update Table_Test t \'

||\' set t.field1 = \'\'\' || 变量1 || \'\'\',\' -- v_BalRuleID || v_BalanceSeq || \'\'\',\'

||\' t.field2 = \'\'\' || 变量2 || \'\'\',\'

||\' t.field3 = \'\'\' || 变量3 || \'\'\',\'

||\' t.field4 = 1,\'

||\' t.field5 = \' || 变量4

||\' where t.field6 = \'\'\' || 变量5 || \'\'\'\'

||\' and INSTRb( \'\'\'|| 变量6 ||\'\'\',t.field7 ) >0 \';

execute immediate v_sql;

于是对其中多个连续的单引号感到奇怪，google后，终于恍然大悟，下面把总结贴出来：

在ORACLE中，单引号有两个作用，一是字符串是由单引号引用，二是转义。单引号的使用是就近配对，即就近原则。而在单引号充当转义角色时相对不好理解。

1、从第二个单引号开始被视为转义符，如果第二个单引号后面还有单引号（哪怕只有一个）。

SQL> SELECT \'\'\'\' FROM DUAL;

\'\'

--

\'

为了证实结果是被第二个单引号转义的第三个单引号（既：SELECT \'\'\'\' FROM DUAL;），我们做如下两个测试：

SQL> SELECT \'\'\' FROM DUAL;

ERROR：

ORA-01756: 括号内的字符串没有正确结束

相信大家对这样的错误不陌生吧

SQL> SELECT \'SDLF FROM DUAL;

ERROR：

ORA-01756: 括号内的字符串没有正确结束

也就是说，当第二个单引号充当转义角色，第三个单引号被转义，（既：select \'\' \' from dual;）自然就缺少与第一个单引号匹配的单引号了，出现了孤立的单引号

下面的两个实验就更加支持了上面的结论。

SQL> SELECT \' \'\' \' FROM DUAL;

\'\'\'\'

------

\'

SQL> SELECT \'\' \'\' FROM DUAL;

SELECT \'\' \'\' FROM DUAL

*

ERROR 位于第 1 行:

ORA-00923: 未找到预期 FROM 关键字

对于第一个：SELECT \' \'\' \' FROM DUAL;

对于第二个：不存在转义。

上面的这两个实验其实也是单引号转义与非转义的一个性质：转义是密集的，也就是说，如果单引号出现在转义的位置上，而该单引号后面紧跟（紧跟的定义为：两个单引号之间是零距离的）的不是单引号，这个时候单引号就不在充当转义的角色，而是与它前面的配对。

2、连接符‘||’导致了新一轮的转义：连接符号‘||’左右的单引号没有任何的关系，除非‘||’是作为字符串的一部分（这在动态SQL中很常见）。

SQL> SELECT \'ORACLE\'||\'\'\'\' FROM DUAL;

\'ORACLE\'||\'\'

----------

ORACLE\'

个人理解，\'ORACLE\'||\'\'\'\' 后面的“\'\'\'\'”应该认为是一个字符串（即前后单引号，中间是“\'\'”串，而中间又是密集单引号，因此第一个为转义功能）

SQL> SELECT \'ORACLE\'\'\'\'\' FROM DUAL;

\'ORACLE\'\'\'\'\'

------------

ORACLE\'\'

对于第一个，前两个单引号配对，后面四个单引号按照上面的第一条原则分配，既：SELECT \'ORACLE\'||\'\'\'\' FROM DUAL;

对于第二个，由于第二个单引号后面存在单引号，所以就不与第一个配对，而是充当了转义的角色。既：SELECT \'ORACLE\'\'\'\'\' FROM DUAL;

有了上面的两条原则，我们就可以从容的面对复杂的动态SQL了。

本文参考：http://www.cnblogs.com/mingal/archive/2008/06/04/967782.html