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

2019-12-29 18:36:20

在使用SQL生成SQL技术完成维护任务的过程中，会遇到类似单引号这样的字符需要转义，给脚本编写带来了些许的麻烦。
一般处理单引号转义是通过“两个单引号表示一个单引号”的规则完成转义。这种方法在在文章《【转义】使用SQL生成SQL语句时单引号的转义处理》（http://space.itpub.net/519536/viewspace-628186）中已经给出描述和应用。

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

我们可以使用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'{}'方法来实现转义。

Good luck.

secooler
11.05.16

-- The End --