Oracle中单引号与双引号的使用及动态拼接_oracle 拼接单引号

1.单引号

   单引号在Oracle中主要有两种作用，一是引用字符串,二是转义。

   栗子：

• 引用字符串：

SELECT 'string' FROM DUAL 

• 转义单引号本身：

SELECT '''' FROM DUAL

此语句结果为单引号本身。如果写作

select ''' from dual 

则会报错:

注意单引号转义时，两个单引号是相连的，中间没有空格间隔，在

select '''' from dual
 

语句中，两端口两个单引号是正常的字符串引用，中间两个紧密相连的单引号，第一个为转义字符，第二个为要转义的单引号本身，再比如

select '''''' from dual
 

语句，同样，两端是引用字符串的单引号，内部四个单引号，第二个单引号转义第三个单引号本身，第三个单引号转义第四个单引号本身，查询结果即为两个单引号：

进行转义时单引号之间有空格报错：

select '' '' from dual

如果觉得转义会出错，也可以直接使用单引号的ASSII码 CHR(39)来表示。比如查询两个单引号：

SELECT CHR(39)||CHR(39) FROM DUAL;

当然，在动态语句中想要使用单引号本身，也可以直接以CHR(39)代替。比如有以下拼接语句：

' AND A.COLUMN1 = 1 AND A.COLUMN2 = ' ||''''|| VARCHAR2_VALUE||'''';

以上语句中，假设变量VARCHAR2_VALUE是一个字符串变量，此时这个动态拼接语句中就不能直接使用下列语句：

' AND A.COLUMN1 = 1 AND A.COLUMN2 = ' ||VARCHAR2_VALUE;

上面语句中直接拼接字符串变量时，执行动态语句时，实际上varchar2_value变量值并没有被加上单引号，执行会报错，即执行语句类似：

AND A.COLUMN1 = 1 AND A.COLUMN2 = 字符变量值

因此，在动态变量拼接时，如果连接的一个变量或参数是字符型（尤其是含有非数值字符，比如字母）的值时，必须添加单引号，这时就需要使用转义或者直接使用CHR(39)代替想要转义的单引号：

' AND A.COLUMN1 = 1 AND A.COLUMN2 = ' ||CHR(39)|| VARCHAR2_VALUE||CHR(39);

当要拼接的字符类型变量全是数值时，字符会自动转型为数值，此时即时拼接时没有添加转义单引号，也不会报错，但严谨起见，这种情况还是最好都添加单引号。

总之，动态拼接时遵循以下要点:

   (1)最外边上的两个单引号是引用字符串的固定写法;

   (2)中间两个紧密相连的单引号表示单引号本身，例如：

''

第一个单引号表是转义字符,用来转义后面的字符;第二个单引号是被转义的字符。

   (3)中间不想转义的话直接用CHR(39)代替。

一个有点绕的拼接栗子：

想要获得执行的最终SQL

   SELECT T1.column1,
    T2.column2 ,
    T2.column3 ||
    '提示语句拼接前段'
    || T1.column1 ||' 提示语句拼接后段!'
    
    
   FROM 
   
   TABLE_T1 T1,
   TABLE_T2 T2
   ;
 

要动态拼接这种有多个明确分隔点的，可以按其分隔点单段拼接：

'
  
  SELECT T1.column1,
    T2.column2,
    T2.column3 ||
    '
    
    ||
    
    
    '''提示语句拼接前段'''
    
    ||
    
    '|| T1.column1 ||'
    
    ||
    
    '''提示语句拼接后段!'''
    
    || 'AS DESCRIPTION'
    
    ||
    '
     FROM 
   TABLE_T1 T1,
   TABLE_T2 T2
   '
;
 

以上拼接语句中，划分段为：

(1) ||连接符为目标语句中正常部分 ，因此划分到第一段，直接两内部不含单引号，直接两端单引号引用即可;
(2) 目标语句中含有单引号，需要转义：语句最两端单引号起引用作用，内部两端口的两个单引号第一个转义，第二个为被转义单引号本身；
(3) ||连接符为目标语句中正常部分,直接整体引用，注意拼接语句时只要不是非变量（输入或输出变量，或块中定义的变量），而是某个表的字段，则直接引用，不必再使用单独连接符；
(4) 同(2);
(5) 同(1);
(6) 同(1).

再来一个有点绕的拼接栗子：

目标语句：

 SELECT * FROM TEST_TABLE A  WHERE 
    INSTR(','||V_STRING||',' , ','||A.COLUMN1||',') > 0;
 

这里用到instr使用分隔符后连接后就有较多的单引号。实际拼接语句如下：

'SELECT * FROM TEST_TABLE A  WHERE
   INSTR('',''||'''
    ||  
    V_STRING
    ||
    '''||'','','',''||A.COLUMN1||'','') > 0';
 

以上语句中我们以V_STRING 变量为节点分段拼接：

(1) 第一段语句：两段单引号引用，注意||连接符是目标语句中应该包含的，注意到字符串变量在拼接时变量（表字段不需要处理）两端需要有单引号，比如：

SELECT INSTR(','||'1,2,5'||',' , ','||'1,2'||',') FROM DUAL;
 

因此实际拼接时注意V_STRING变量两边应有单引号，

INSTR(','||'

此内部语句中中含有三个单引号，要分别使用一个单引号转义（目标语句含单引号在拼接时在目标语句内部单引号基础上加一个单引号即可）；

(2) V_STRING为字符串变量，两边使用||拼接；

(3) 第三段中同理，V_STRING右边也需要一个单引号：

'||',' , ','||A.COLUMN1||',') > 0

然后按拼接原则 ，两端加单引号，内部所有单引号再加一个单引号转义即可。

2. 双引号

   双引号在oracle中的使用作用比较单一，主要是按目标输出，并不触发oracle报错。

   (1) to_char输出日期时，包装非法字符，按预期正常输出：

select to_char(sysdate,'hh24"小时"mi"分"ss"秒"')  from dual;
 

   (2)其他查询输出目标形式：

SELECT 1 AS "税费（津贴+佣金）" FROM DUAL;
 

日常查询中，可能有时返回字段名称为中文，且包含一些ORACLE可以识别的特殊字符（+，- ，*，/ 括号（oracle对中英文括号同等识别）等），此时需要使用双引号才可以正常输出目标形式,否则会报错

SELECT 1 AS 税费（津贴+佣金） FROM DUAL;

另外，如果查询中出现使用中包含特殊字符的字段名称，比如临时查询字段名称为 税费（津贴+佣金），那么最好在查询时先行将这些特殊字符处理掉，(一般配合使用replace与translate函数去除)否则ORACLE也会对这些特殊字符进行识别，最终可能判别语法出现问题。