ORACLE逸出字元的那點事

標籤：

在oracle中，某些字元表示的有特殊的含義的，比如說&符號，表示變數替換；%表示在查詢過程中的萬用字元

下面列出一些在ORACLE中有特殊含義的字元作以測試：

符號	功能	類型
%	在where子句中表示0到任意個字元	萬用字元
_	在where子句中表示確切的未知字元	萬用字元
?	在where子句中表示確切的位置字元	萬用字元
#	在where子句中確切的阿拉伯數字	萬用字元
[a-d]	表示字元範圍	萬用字元
‘	表示文本、字元和日期	定界符
"	將包含特定字元或者空格的列別名括起來	定界符
&	用來指出一個變數	特殊符號
||	字串串連	特殊符號
*	在select語句中表示0個或者多個列	特殊符號
/	終止SQL語句	特殊符號
	注釋	注釋符

（註：經過測試，上面的3、4、5條通配，不靠譜，誤信）

這裡列出了常用的一些特殊符號，在這裡我們對其進行insert（這裡直接寫那些能夠insert成功的語句了），一下語句安裝表中出現的先後順序出現：

SQL> create table test (name varchar2(20));

SQL> insert into test values(‘abc3‘);

SQL> insert into test values(‘abc_123‘);

SQL> insert into test values(‘abc?123‘);

SQL> insert into test values(‘abc#123‘);

SQL> insert into test values(‘abc[a-d]123‘);

SQL> insert into test values(‘abc‘‘123‘);

SQL> insert into test values(‘abc"123‘);

SQL> insert into test values(‘abc‘ || chr(38) || ‘123‘);

SQL> insert into test values(‘abc||123‘);

SQL> insert into test values(‘abc*123‘);

SQL> insert into test values(‘abc/123‘);

SQL> insert into test values(‘abc123‘);

SQL> select * from test;

NAME

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

abc3

abc_123

abc?123

abc#123

abc[a-d]123

abc‘123

abc"123

abc&123

abc||123

abc*123

abc/123

abc123

可以看到在上面的insert操作中，除了單引號(‘)在字串中出現需要兩個重複之外，還有一個特例就是and符號(&)，這裡是用了ascii轉碼的方式來做的，關於ascii碼可以用這個方式來查詢：

SQL> select ascii(‘&‘) from dual;

ASCII(‘&‘)

----------

        38

既然將這個&拿出來了，我們就單獨對它說說，

上面的那種方式適用於SQL指令碼中，大量SQL中有&符號的話，可以用轉碼方式實現，如果你是在SQL*Plus中，還可以這麼做：

SQL> insert into test values(‘abc&test123‘);

Enter value for test123: &123

old   1: insert into test values(‘abc&test123‘)

new   1: insert into test values(‘abc&123‘)

雖然這麼做也可以實現，但是違背了我們的意圖

另外一種（使用逸出字元）：

SQL> show escape

escape OFF

SQL> set escape on

SQL> insert into test values(‘abc\&123‘);

1 row created.

或者（直接把define功能關閉）：

SQL> show define

define "&" (hex 26)

SQL> set define off

SQL> show define

define OFF

SQL> insert into test values(‘abc&123‘);

1 row created.

 

現在表中的資料如下：

SQL> select * from test;

NAME

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

abc3

abc_123

abc?123

abc[a-d]123

abc‘123

abc"123

abc&123

abc||123

abc*123

abc/123

abc123

abc#123

abc&123

abc&123

abc&123

abc3

 

接下來，說下select那些事：

由於前幾個字元是出現在where子句中才生效的，所以前面幾條用where子句來進行測試（和上面一樣，這裡還是直接寫測試成功的句子）：

SQL> select * from test where name like ‘abc\3‘ escape ‘\‘;

NAME

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

abc3

abc3

可以看到在這裡，我們指定了escape逸出字元標識，當然這裡的正斜杠可以是其他的字元，例如：

SQL> select * from test where name like ‘abc=3‘ escape ‘=‘;

NAME

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

abc3

abc3

OK，道理說明白了，其他的字元就不一一的列舉，在此只列出兩個比較特殊的例子，分別是單引號和and符號

單引號：

這裡你可以使用連寫兩個的方式進行轉換：

SQL> select * from test where name like ‘abc‘‘123‘;

NAME

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

abc‘123

但是，當你試圖對其進行轉義的時候，你會發現，報錯了（引用字串為正常結束，哦，好吧，看來單引號是需要特殊對待的）：

SQL> select * from test where name like ‘abc\‘123‘ escape ‘\‘;

ERROR:

ORA-01756: quoted string not properly terminated

 

&and字元：

SQL> select * from test where name like ‘abc\&123‘ escape ‘\‘;

Enter value for 123: &

可以看到，如果這個轉義的方式使用到&，是起不到作用的，不過如果我們把define關閉呢？

SQL> show define

define "&" (hex 26)

SQL> set define off

SQL> show define

define OFF

SQL> select * from test where name like ‘abc\&123‘ escape ‘\‘;

select * from test where name like ‘abc\&123‘ escape ‘\‘

                                   *

ERROR at line 1:

ORA-01424: missing or illegal character following the escape character

可以看到，這裡會報錯的，這個提示是在說，跟在逸出字元後面的錯誤或者缺失的字元，貌似不可以哦，其實在我們關閉define之後，完全可以這樣寫的：

SQL> select * from test where name like ‘abc&123‘;

NAME

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

abc&123

abc&123

abc&123

abc&123

如果你開啟了escape，預設逸出字元為‘\’，可以如下寫：

SQL> set escape on

SQL> show escape

escape "\" (hex 5c)

SQL> select * from test where name like ‘abc\&123‘;

NAME

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

abc&123

abc&123

abc&123

abc&123

當然啦，還有這麼一種方式：

SQL> select * from test where name like ‘abc‘ || chr(38) || ‘123‘;

NAME

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

abc&123

abc&123

abc&123

abc&123

 

好了，差不多就這麼多了，其餘的歡迎補充！

 

註：測試平台：

OS Version：Redhat Enterprise Linux 5 Update 4 x86

DB Version：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

ORACLE逸出字元的那點事