跳脫字元的運用

在一些查詢中，為了查詢的結果能夠達到高可讀性的效果，類似：

Hartstein's salary is:13000 這樣的出巡結果，我們往往在拼接
“'”的時候遇到一些麻煩，以下就講述三種字元轉義方法解決這個問題。

我們就拿例項使用者hr的employees表作為查詢物件。

1、使用跳脫字元 '：

hr@PROD>select last_name ||'''s salary is:' || salary as income

  2  from employees

  3   where salary >10000;

INCOME

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

Hartstein's salary is:13000

Higgins's salary is:12008

King's salary is:24000

Kochhar's salary is:17000

De Haan's salary is:17000

Greenberg's salary is:12008

Raphaely's salary is:11000

Russell's salary is:14000

Partners's salary is:13500

Errazuriz's salary is:12000

Cambrault's salary is:11000

INCOME

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

Zlotkey's salary is:10500

Vishney's salary is:10500

Ozer's salary is:11500

Abel's salary is:11000

15 rows selected.

 
2、使用q'/ /',或者q'[]',或者q'()'等轉義：

hr@PROD>select last_name ||q'/'s salary is:/'  || salary as income

  2  from employees

  3  where salary >10000;

INCOME

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

Hartstein's salary is:13000

Higgins's salary is:12008

King's salary is:24000

Kochhar's salary is:17000

De Haan's salary is:17000

Greenberg's salary is:12008

Raphaely's salary is:11000

Russell's salary is:14000

Partners's salary is:13500

Errazuriz's salary is:12000

Cambrault's salary is:11000

 

INCOME

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

Zlotkey's salary is:10500

Vishney's salary is:10500

Ozer's salary is:11500

Abel's salary is:11000

15 rows selected.

 
3、使用“'” 對應的ASCII數字代替：
--在上述的轉義方法的基礎上檢視“'” 對應的ASCII數字：

hr@PROD>select ascii('''')  from dual;

ASCII('''')

-----------

         39

 

hr@PROD>select last_name || chr(39) ||'s salary is:' || salary as income

  2  from employees

  3  where salary >10000;

INCOME

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

Hartstein's salary is:13000

Higgins's salary is:12008

King's salary is:24000

Kochhar's salary is:17000

De Haan's salary is:17000

Greenberg's salary is:12008

Raphaely's salary is:11000

Russell's salary is:14000

Partners's salary is:13500

Errazuriz's salary is:12000

Cambrault's salary is:11000

INCOME

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

Zlotkey's salary is:10500

Vishney's salary is:10500

Ozer's salary is:11500

Abel's salary is:11000

15 rows selected.

解決了轉義問題之後，高可讀性的查詢結果就不難建立。