談談“&”替換標誌的一些特性

 

在Oracle客戶端，我們可以使用“&”來進行SQL語句指定內容的替換。當我們使用Sqlplus等基於Oracle客戶端的工具時，“&”標號可以幫助我們實現一些功能。在本篇中，筆者準備探討一下標號的基本特性和深層特點。

 

1、&標號基本使用

 

應該說，&標號的作用是在SQL語句執行過程中，如果Oracle發現中間使用了&標記，就終止執行，轉而要求使用者前段確認標記的變數取值，之後用輸入的值進行資料替換。下面是在Sqlplus上的一個基本應用，我們在Oracle 10g下進行試驗。

 

 

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

 

 

SQL> select empno, ename from emp where empno=&no;

輸入 no 的值:  7902

原值    1: select empno, ename from emp where empno=&no

新值    1: select empno, ename from emp where empno=7902

 

     EMPNO ENAME

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

      7902 FORD

 

 

當使用了&標號後，Oracle會提示使用者輸入這個取值，之後進行內容“全文替換”。注意，如果輸入變數是一個字串型別，要注意單引號’的使用特性如下：

 

 

SQL> select empno, ename from emp where job=&job;

輸入 job 的值:  CLERK

原值    1: select empno, ename from emp where job=&job

新值    1: select empno, ename from emp where job=CLERK

select empno, ename from emp where job=CLERK

                                       *

第 1 行出現錯誤:

ORA-00904: "CLERK": 識別符號無效

 

 

SQL> select empno, ename from emp where job='&job';

輸入 job 的值:  CLERK

原值    1: select empno, ename from emp where job='&job'

新值    1: select empno, ename from emp where job='CLERK'

 

     EMPNO ENAME

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

      7369 SMITH

      7876 ADAMS

 

SQL> select empno, ename from emp where job=&job;

輸入 job 的值:  'CLERK'

原值    1: select empno, ename from emp where job=&job

新值    1: select empno, ename from emp where job='CLERK'

 

     EMPNO ENAME

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

      7369 SMITH

      7876 ADAMS

 

 

在&與字串的結合應用中，我們從上面的例子就可以看出端倪要點，如果SQL語句中的字串列條件值沒有使用單引號進行包括，那麼輸入的變數就一定要求帶上’。如果字串列條件值已經包括了單引號，就不需要輸入了。

 

進一步想，我們可以認為，在&標號的情況下，就是一個簡單的“內容替換”。

 

2、&標號的更進一步特性

 

其實，&標號絕不僅僅可以替換where後面的條件句。從語法本質看，這個標記可以替換SQL語句中更多的內容。

 

 

--1、替換where字句

SQL> select empno, ename from emp &state job='CLERK';

輸入 state 的值:  where

原值    1: select empno, ename from emp &state job='CLERK'

新值    1: select empno, ename from emp where job='CLERK'

 

     EMPNO ENAME

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

      7369 SMITH

      7876 ADAMS

 

--2、替換select字句

SQL> &state empno, ename from emp where job='CLERK';

SP2-0734: 未知的命令開頭 "&state emp..." - 忽略了剩餘的行。

SQL> select empno, ename &state emp where job='CLERK';

輸入 state 的值:  from

原值    1: select empno, ename &state emp where job='CLERK'

新值    1: select empno, ename from emp where job='CLERK'

 

     EMPNO ENAME

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

      7369 SMITH

      7876 ADAMS

 

--3、替換列名

SQL> select empno, &colum from emp where job='CLERK';

輸入 colum 的值:  ename

原值    1: select empno, &colum from emp where job='CLERK'

新值    1: select empno, ename from emp where job='CLERK'

 

     EMPNO ENAME

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

      7369 SMITH

      7876 ADAMS

 

--4、替換排序列

SQL> select empno from emp where job='CLERK' order by &m;

輸入 m 的值:  empno

原值    1: select empno from emp where job='CLERK' order by &m

新值    1: select empno from emp where job='CLERK' order by empno

 

     EMPNO

----------

      7369

      7876

 

 

上面的四個語句試驗，告訴我們&替代符可以做更多的事情，包括替代SQL語句中的關鍵字和列名。注意，這裡除了SQL的第一個關鍵字不能做之外。

 

 

說到這裡，順便提一下“&&”標記。&&標記的作用就是記住變數的取值，在下一次使用的時候，就不需要強制進行替換了。

 

 

 

SQL> select empno, ename from emp where job='&&job';

輸入 job 的值:  CLERK

原值    1: select empno, ename from emp where job='&&job'

新值    1: select empno, ename from emp where job='CLERK'

 

     EMPNO ENAME

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

      7369 SMITH

      7876 ADAMS

      7900 JAMES

      7934 MILLER

 

SQL> select empno, ename from emp where job='&&job';

原值    1: select empno, ename from emp where job='&&job'

新值    1: select empno, ename from emp where job='CLERK'

 

     EMPNO ENAME

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

      7369 SMITH

      7876 ADAMS

 

 

3、關於&標號和繫結變數

 

&標號和我們在實際開發中使用的繫結變數有一些相似之處，但是具有更大的靈活性。繫結變數（bind variable）只能進行where條件單值的替換，而且不能進行SQL關鍵字的替換。那麼，我們怎麼看待這兩個特性呢？

 

筆者認為，&標號的特性具有更加強的靈活性，多種的替換功能是建立在Oracle客戶端功能的基礎之上。也就是說，我們替換的全文內容，是Oracle客戶端給我們進行替換的。Oracle客戶端進行替換之後，將替換完的SQL語句傳送給Oracle服務端，Oracle最佳化器optimizer接受和處理解析的語句，都是替換完成的SQL語句。下面我們透過實驗去證明：

 

 

SQL> alter system flush shared_pool;

System altered

 

 

SQL> select /*+ demo */count(*) from emp where deptno=&no;

輸入 no 的值:  10

原值    1: select /*+ demo */count(*) from emp where deptno=&no

新值    1: select /*+ demo */count(*) from emp where deptno=10

 

  COUNT(*)

----------

         3

 

SQL> select /*+ demo */count(*) from emp where deptno=&no;

輸入 no 的值:  40

原值    1: select /*+ demo */count(*) from emp where deptno=&no

新值    1: select /*+ demo */count(*) from emp where deptno=40

 

  COUNT(*)

----------

         0

 

此時，我們檢查Shared Pool中的遊標，如下：

 

 

SQL> col sql_text for a52;

SQL> select sql_text, sql_id from v$sqlarea where sql_text like 'select /*+ demo */count(*)%';

 

SQL_TEXT                                             SQL_ID

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

select /*+ demo */count(*) from emp where deptno=40  0rtgwj49d4yu2

select /*+ demo */count(*) from emp where deptno=10  19a1vdf75kucg

 

 

可見，我們在shared pool中找到兩條的父遊標記錄，說明在服務端接受的時候，就是已經替換完的取值。進一步說，&號在服務端是不能夠起到bind variable的共享父子游標的作用。

 

而bind variable是可以起到這個作用的。

 

 

SQL> var x number;

SQL> exec :x := 10;

 

PL/SQL 過程已成功完成。

 

SQL> select /*+ demo */count(*) from emp where deptno=:x;

 

  COUNT(*)

----------

         3

 

SQL> exec :x := 20;

 

PL/SQL 過程已成功完成。

 

SQL> select /*+ demo */count(*) from emp where deptno=:x;

 

  COUNT(*)

----------

         5

 

SQL> select sql_text, sql_id from v$sqlarea where sql_text like 'select /*+ demo */count(*)%';

 

SQL_TEXT                                             SQL_ID

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

select /*+ demo */count(*) from emp where deptno=:x  888j35kfajnuj

 

 

 

4、結論

 

&是我們在Oracle客戶端可以支援的一種工具型別。在我們進行除錯程式的時候，可以提供一定程度的幫助。但是筆者認為，在實際開發工作中，意義不是很大。&標記符的出現，可能是作為某種歷史時期的產品方向。