学步园

Oracle中对一些特殊字符处理的时候，需要进行转义。
特殊字符包括:
% 在Oracle中用来匹配0到多个字符。
_ 在Oracle中用来匹配1个字符。
&  在Oracle中用来作输入提示符。
\   默认的转义字符。
' 单引号用来括起字符串，所以如果字符串中有'，必须用两个连续的'来代替。

首先来看看如果SQL文中包括特殊字符&的时候怎么处理。
有4种办法：
1) 先执行set define off关闭&的输入提示作用，之后再执行set define on恢复。
2) 先执行set escape on打开'\'的转义作用(默认关闭)，然后在需要输入&的地方用\&代替。
   之后再执行set escape off恢复。
3) 使用字符串拼接。例如 'abc'||'&'||'def'，单个的&，Oracle不会把它当作输入提示符。
4) 使用set define 把输入提示符换掉。例如set define :
5) 使用chr函数转换&的ASCII码38为字符，例如 'abc'||chr(38)||'def'

再来看看\,前面说了执行set escape on打开'\'的转义作用，执行set escape off关闭。
所以如果转义打开的时候，需要输入\的地方需要用\\来代替，否则直接输入。

tony@ORA11GR2> create table t(txt varchar2(64));

表已创建。

tony@ORA11GR2> show define
define "&" (hex 26)
tony@ORA11GR2> show escape
escape "\" (hex 5c)
tony@ORA11GR2> insert into t values('abc%def');
已创建 1 行。
tony@ORA11GR2> insert into t values('abc_def');
已创建 1 行。
tony@ORA11GR2> set define off;
tony@ORA11GR2> insert into t values('abc&def');
已创建 1 行。
tony@ORA11GR2> set define on;
tony@ORA11GR2> set escape on;
tony@ORA11GR2> insert into t values('abc\&1def');
已创建 1 行。
tony@ORA11GR2> insert into t values('abc\\def');
已创建 1 行。
tony@ORA11GR2> set escape off
tony@ORA11GR2> insert into t values('abc' || '&' || '2def');
已创建 1 行。
tony@ORA11GR2> insert into t values('abc\1def');
已创建 1 行。
tony@ORA11GR2> insert into t values('abc123def');
已创建 1 行。
tony@ORA11GR2> insert into t values('abc''def');
已创建 1 行。
tony@ORA11GR2> select * from t;

TXT
--------------------
abc%def
abc_def
abc&def
abc&1def
abc\def
abc&2def
abc\1def
abc123def
abc'def

现在来看看查询时候的处理：

tony@ORA11GR2> select * from t where txt like 'abc\%def' escape '\';
TXT
--------------------
abc%def

tony@ORA11GR2> select * from t where txt like 'abc\_def' escape '\';
TXT
--------------------
abc_def

tony@ORA11GR2> --'&'不能通过转义字符查找
tony@ORA11GR2> set define off;
tony@ORA11GR2> select * from t where txt like 'abc&def';
TXT
--------------------
abc&def

tony@ORA11GR2> set define on;
tony@ORA11GR2> select * from t where txt like ('abc' || '&' || '1def');
TXT
--------------------
abc&1def

tony@ORA11GR2> select * from t where txt like ('abc''def');
TXT
--------------------
abc'def

替换输入提示符：

scott@ORA11GR2> select &a from dual;
Enter value for a: 1
old   1: select &a from dual
new   1: select 1 from dual

         1
----------
         1

scott@ORA11GR2> set define :
scott@ORA11GR2> select :a from dual;
Enter value for a: 1
old   1: select :a from dual
new   1: select 1 from dual

         1
----------
         1