使用SQL生成SQL语句时单引号的转义处理之q'{}'方法

当一句话中出现多个单引号，频繁转义将非常麻烦，而且会极大的降低语句的可读性，有没有更简便的方法实现单引号的转义？答案是肯定的！

  我们可以使用q'{}'将需要转义的字符串放到两个大括号之间的方法达到目的。演示如下。

1.创建四张测试表
sec@ora10g> create table t1 (x int);

Table created.

sec@ora10g> create table t2 (x int);

Table created.

sec@ora10g> create table t3 (x int);

Table created.

sec@ora10g> create table t4 (x int);

Table created.

2.使用普通单引号转义方法实现生成表分析语句的方法如下
sec@ora10g> SET LIN 300
sec@ora10g> SET HEAD OFF
sec@ora10g> select    'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'
  2        || ''''
  3        || OWNER
  4        || ''''
  5        || ',TABNAME=>'
  6        || ''''
  7        || TABLE_NAME
  8        || ''''
  9        || ',METHOD_OPT=>'
 10        || ''''
 11        || 'FOR ALL COLUMNS SIZE 254'
 12        || ''''
 13        || ',DEGREE=>4,CASCADE=>TRUE);'
 14   from DBA_TABLES
 15  where WNER = 'SEC';

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T1',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T2',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T3',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T4',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);

3.使用q'{}'方法实现单引号转义
sec@ora10g> SET LIN 300
sec@ora10g> SET HEAD OFF
sec@ora10g> select    'EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'
  2        || q'{'}'
  3        || OWNER
  4        || q'{'}'
  5        || ',TABNAME=>'
  6        || q'{'}'
  7        || TABLE_NAME
  8        || q'{'}'
  9        || ',METHOD_OPT=>'
 10        || q'{'}'
 11        || 'FOR ALL COLUMNS SIZE 254'
 12        || q'{'}'
 13        || ',DEGREE=>4,CASCADE=>TRUE);'
 14   from DBA_TABLES
 15  where WNER = 'SEC';

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T1',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T2',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T3',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SEC',TABNAME=>'T4',METHOD_OPT=>'FOR ALL COLUMNS SIZE 254',DEGREE=>4,CASCADE=>TRUE);

单引号转义成功。针对上面的例子使用q'{}'方法略显笨拙！相比普通转义方法没有太大的优势。
再看一例。

4.使用q'{}'方法简化多引号场景
以在SQL*Plus中显示字符串“I'm Secooler. I'm Andy. I'm Shengwen.”为例。
1）使用普通单引号转义方法
sec@ora10g> select 'I''m Secooler. I''m Andy. I''m Shengwen.' from dual;

I'm Secooler. I'm Andy. I'm Shengwen.

2）使用q'{}'方法转义单引号
sec@ora10g> select q'{I'm Secooler. I'm Andy. I'm Shengwen.}' from dual;

I'm Secooler. I'm Andy. I'm Shengwen.

此时q'{}'对单引号的转义方法可以保留语句的全部内容，不会对阅读字符串造成影响。这便是这种方法的真正的优势。

5.小结
  在字符串中出现少量单引号时，我们可以使用普通的转义方法处理，这样会更加高效；当字符串中出现大量的单引号时，便可以使用这种q'{}'方法来实现转义。