7.6 &符號(hào)替換

隨著SQL語句的發(fā)展和完善，可以保存它們以便將來使用。有時(shí)，查詢只是略有不同，因此，希望有一種通用的查詢形式，在運(yùn)行時(shí)可以替換變量或者占位符。SQL*Plus以&符號(hào)替換的形式提供這種功能。SELECT語句的每個(gè)元素都可以替換，將查詢減少為核心元素以方便重用，這樣可以節(jié)省大量單調(diào)的重復(fù)性工作。本節(jié)將討論替換變量以及DEFINE和VERIFY命令。

7.6.1 替換變量

可將替換變量看作是占位符。SQL查詢由兩個(gè)或者多個(gè)子句組成。每個(gè)子句又可劃分為子子句，這些子子句由字符文本組成。所有文本、子子句或者子句元素都是替換的對(duì)象。

1．單個(gè)&符號(hào)替換

SQL語句中最基本、最普遍的元素替換形式是單個(gè)&符號(hào)替換。&符號(hào)是用來在語句中指定替換變量的符號(hào)，它在變量名之前，并且它們之間沒有空格。當(dāng)執(zhí)行語句時(shí)，SQL*Plus客戶程序處理語句，發(fā)現(xiàn)替換變量，并嘗試用下面兩種方法之一來解析變量的值。首先，它檢查在用戶會(huì)話中是否定義了該變量(DEFINE命令將在本章稍后討論)。如果沒有定義變量，用戶進(jìn)程提示需要一個(gè)值，這個(gè)值會(huì)替換變量。提交值之后，語句就完成了，Oracle服務(wù)器就會(huì)執(zhí)行它。在執(zhí)行時(shí)解析&符號(hào)替換變量，有時(shí)稱之為運(yùn)行時(shí)綁定( runtime binding)或者運(yùn)行時(shí)替換(runtime substitution)。

給定LAST_NAME或者EMPLOYEE_ID值，要求查找聯(lián)系信息，例如PHONE_NUMBER數(shù)據(jù)。通用查詢形式如下所示：

        select employee_id, last_name, phone_number from employees
        where last_name = '&LASTNAME' or employee_id = &EMPNO;

當(dāng)運(yùn)行該查詢時(shí)，Oracle服務(wù)器提示給名為LASTNAME的變量輸入一個(gè)值。可以輸入員工的姓(如果知道的話)，如“King”。如果不知道員工的姓氏，但知道員工的ID號(hào)，也可以輸入ID號(hào)，然后按Enter鍵提交。接下來Oracle會(huì)提示為EMPNO變量輸入一個(gè)值。輸入值(例如0)之后，按Enter鍵。之后，Oracle就沒有剩余的替換變量需要解析了，因此執(zhí)行下面的語句：

        select employee_id, last_name, phone_number from employees
        where last_name = 'King' or employee_id = 0;

可以將變量指定為任何字面值，但必須是有效的標(biāo)識(shí)符名稱。提示變量時(shí)替換的字面值必須是與該上下文相適應(yīng)的數(shù)據(jù)類型；否則就會(huì)返回ORA-00904: invalid identifier錯(cuò)誤。如果變量要替換字符或者日期值，那么需要將字面值包含在單引號(hào)內(nèi)。一種有用方法是，當(dāng)處理字符和日期值時(shí)，將&替換變量包含在單引號(hào)內(nèi)。這樣，要求用戶提交字面值時(shí)，就不必輸入引號(hào)了。

2．雙&符號(hào)（&&）替換

有時(shí)在相同查詢中會(huì)多次引用替換變量。在這種情況下，Oracle服務(wù)器會(huì)提示輸入每個(gè)單&符號(hào)替換變量的值。對(duì)于復(fù)雜腳本而言，這種工作可能效率低下、令人乏味。下面的語句從EMPLOYEES表中檢索FIRST_NAME和LAST_NAME列，查找這兩列中包含相同字符串的行：

        select first_name, last_name from employees
        where last_name like '%&SEARCH%' and first_name like '%&SEARCH%';

這兩個(gè)條件相同，但應(yīng)用于不同列。執(zhí)行該語句時(shí)，首先提示輸入在與LAST_NAME列的比較中使用的SEARCH變量的替換值。之后，提示輸入在與FIRST_NAME列的比較中使用的SEARCH變量的替換值。這里有兩個(gè)問題。第一，輸入相同的值兩次，這樣做效率低下。第二，且更重要的是，查詢可能出現(xiàn)拼寫錯(cuò)誤，因?yàn)镺racle沒有驗(yàn)證每次都為相同名稱的替換變量輸入相同的字面值。在本示例中，邏輯假設(shè)是替換變量的內(nèi)容應(yīng)該相同，但變量有相同名稱這一點(diǎn)對(duì)于Oracle服務(wù)器沒有意義，它不做這種假設(shè)。圖7-11中的第一個(gè)示例顯示了運(yùn)行前面的查詢并給SEARCH替換變量提交兩個(gè)不同值之后的結(jié)果。在此特例中，結(jié)果不正確，因?yàn)橐髾z索包含相同字符串的FIRST_NAME和LAST_NAME對(duì)。

如果要在相同查詢中多次引用替換變量，而且語句中變量的值必須相同，在這種情況下，最好使用&&替換。這需要在查詢中多次出現(xiàn)的替換變量第一次出現(xiàn)時(shí)添加前綴，要使用兩個(gè)&符號(hào)而不是一個(gè)。當(dāng)Oracle 服務(wù)器遇到&&替換變量時(shí)，會(huì)為該變量定義會(huì)話值，并不再提示輸入后續(xù)引用中替換該變量的值。

圖7-11中的第二個(gè)示例顯示在有關(guān)LAST_NAME列的條件中的SEARCH變量之前添加兩個(gè)&符號(hào)，之后在有關(guān)FIRST_NAME列的條件中的SEARCH變量之前添加一個(gè)&符號(hào)。當(dāng)執(zhí)行時(shí)，對(duì)于LAST_NAME列而言只會(huì)提示一次輸入一個(gè)值取代SEARCH變量。在后續(xù)引用中，會(huì)自動(dòng)從該變量的會(huì)話值中解析這個(gè)值，和在有關(guān)LAST_NAME列的條件中一樣。要解除SEARCH變量的定義，需要使用本章稍后討論的UNDEFINE命令。

3．替換列名

到目前為止，WHERE子句的字面元素都是替換討論的焦點(diǎn)，但實(shí)際上SQL語句的任何元素都是可以替換的對(duì)象。在下面的語句中，F(xiàn)IRST_NAME和JOB_ID列是靜態(tài)的，總是會(huì)被檢索，但選中的第三列是變化的，被指定為名為COL的替換變量。在ORDER BY子句中按該替換變量列排序結(jié)果集：

        select first_name, job_id, &&col
        from employees
        where job_id in ('MK_MAN', 'SA_MAN')
        order by &col;

和字符和日期字面值不一樣，當(dāng)顯式指定或者通過&替換變量指定時(shí)，列名引用都不需要單引號(hào)。

4．替換表達(dá)式和文本

在運(yùn)行時(shí)幾乎可以替換SQL語句的任何元素。條件是Oracle要求至少第一個(gè)單詞是靜態(tài)的。在SELECT語句中，至少需要SELECT關(guān)鍵字不變，可以將語句的余下部分替換為如下所示：

        select &rest_of_statement;

當(dāng)執(zhí)行時(shí)，會(huì)提示提交名為REST_OF_STATEMENT變量的值，在追加到SELECT關(guān)鍵字后，它可以是任何合法查詢。適合&替換的是那些多次運(yùn)行只是略有不同的語句。

7.6.2 定義和驗(yàn)證

當(dāng)相同變量在語句中多次出現(xiàn)時(shí)，可以使用&&替換來避免重復(fù)輸入。當(dāng)出現(xiàn)&&替換時(shí)，變量被保存為會(huì)話變量。隨著語句的執(zhí)行，會(huì)使用保存的會(huì)話變量自動(dòng)解析所有后續(xù)出現(xiàn)的變量。相同會(huì)話內(nèi)語句的后續(xù)執(zhí)行自動(dòng)從保存的會(huì)話值中解析替換變量。這不一定是用戶想要的，也的確限制了替換變量的有用性。然而，Oracle提供了一種機(jī)制來解除定義(UNDEFINE)這些會(huì)話變量。VERIFY命令專用于SQL*Plus，它控制在執(zhí)行使用替換變量的SQL語句之前，是否將替換的元素回顯到用戶的屏幕上。

1．DEFINE和UNDEFINE命令

在使用&&替換的SQL語句中第一次引用替換變量時(shí)，就隱式創(chuàng)建了會(huì)話級(jí)別的變量。在會(huì)話期間或者在顯式解除定義它們之前，它們一直存在或者保持可用。當(dāng)用戶退出客戶工具(例如SQL*Plus)或者用戶進(jìn)程異常終止時(shí)，會(huì)話就會(huì)結(jié)束。

永久會(huì)話變量的存在會(huì)降低使用&替換變量語句的一般功能。幸運(yùn)的是，使用UNDEFINE命令可以刪除這些會(huì)話變量。在腳本內(nèi)或者在SQL*Plus或SQL Developer的命令行中，解除定義會(huì)話變量的語法如下所示：

        UNDEFINE variable;

考察一個(gè)簡單示例，它從EMPLOYEES表中選擇靜態(tài)和可變列，并依據(jù)可變列排序輸出。

        select last_name, &&COLNAME
        from employees where department_id=30 order by &COLNAME;

該語句第一次執(zhí)行時(shí)，會(huì)提示為名為COLNAME的變量輸入一個(gè)值。假設(shè)輸入SALARY。替換變量，執(zhí)行語句。相同會(huì)話內(nèi)語句的后續(xù)執(zhí)行不會(huì)提示任何COLNAME值，因?yàn)橐呀?jīng)在會(huì)話上下文中將它定義為SALARY，只能使用UNDEFINE COLNAME命令解除它的定義。解除變量定義之后，語句下面的執(zhí)行就會(huì)提示用戶輸入COLNAME變量的值。

DEFINE命令有兩個(gè)目的：它可用來檢索SQL會(huì)話中當(dāng)前定義的所有變量列表；還可用來顯式定義會(huì)話期間在一個(gè)或者多個(gè)語句中作為替換變量引用的變量的值。DEFINE命令的這兩個(gè)變體的語法如下所示：

        DEFINE;
        DEFINE variable=value;

支持永久會(huì)話變量的功能可以按要求使用SET DEFINE OFF命令啟動(dòng)或者關(guān)閉。SET命令不是SQL語言命令，而是SQL環(huán)境控制命令。通過指定SET DEFINE OFF，客戶工具(如SQL*Plus)就不保存會(huì)話變量或者給&符號(hào)添加特殊含義。如有必要，這允許將&用作普通的字面值字符。因此，SET DEFINE ON | OFF命令確定&替換在會(huì)話中是否可用。下面的查詢將&用作字面值。當(dāng)執(zhí)行時(shí)，會(huì)提示輸入綁定變量SID的值。

        select 'Coda & Sid' from dual;

關(guān)閉&替換的功能，執(zhí)行該查詢時(shí)就沒有提示：

        SET DEFINE OFF
        select 'Coda & Sid' from dual;
        SET DEFINE ON

一旦執(zhí)行語句，SET DEFINE ON命令可用來打開替換功能。如果DEFINE設(shè)置為OFF，并且不能從字面上解析在語句中使用了&的上下文，Oracle就會(huì)返回錯(cuò)誤。

2．VERIFY命令

操作Oracle服務(wù)器時(shí)可以使用兩類命令：SQL語言命令和SQL客戶控制命令。SELECT語句是語言命令，而SET命令控制SQL客戶環(huán)境。有許多不同的語言和控制命令可用，但與替換相關(guān)的控制命令是DEFINE和VERIFY。

VERIFY命令控制提交的替換變量是否顯示到屏幕上，以便驗(yàn)證替換是否正確。顯示的消息由舊子句和包含替換變量的輸入值的新子句組成。使用命令SET VERIFY ON|OFF可將VERIFY命令切換為ON或OFF。如果首先將VERIFY切換為OFF，執(zhí)行使用&替換的查詢，系統(tǒng)會(huì)提示輸入一個(gè)值。然后替換值，運(yùn)行語句，顯示結(jié)果。然后將VERIFY切換為ON，執(zhí)行相同的查詢，系統(tǒng)會(huì)提示輸入一個(gè)值。一旦輸入值，在語句開始執(zhí)行之前，Oracle會(huì)先顯示包含替換變量名的舊子句，并且有行號(hào)，然后，顯示包含替換值的新子句。

練習(xí)7-5 使用&替換

要求使用當(dāng)前稅率寫一個(gè)可重用的查詢，將EMPLOYEE_ID號(hào)作為輸入，返回EMPLOYEE_ID、FIRST_NAME、SALARY、ANNUAL SALARY (SALARY * 12)、TAX_RATE和TAX (TAX_RATE * ANNUAL SALARY)信息，供HR部門的職員使用。

(1) 啟動(dòng)SQL*Plus ，連接HR模式。

(2) 選擇的列表必須包含4個(gè)指定列以及兩個(gè)表達(dá)式。第一個(gè)表達(dá)式的別名為ANNUAL SALARY，它的計(jì)算很簡單，而第二個(gè)表達(dá)式的別名為TAX，它取決于TAX_RATE。因?yàn)門AX RATE可能變化，所以必須在運(yùn)行時(shí)提交這個(gè)值。

(3) 可能的解決方案如下所示：

        SELECT &&EMPLOYEE_ID, FIRST_NAME, SALARY, SALARY * 12 AS "ANNUAL SALARY",
        &&TAX_RATE, (&TAX_RATE * (SALARY * 12)) AS "TAX"
        FROM EMPLOYEES WHERE EMPLOYEE_ID = &EMPLOYEE_ID;

(4) SELECT子句中EMPLOYEE_ID和TAX_RATE前面的雙&符號(hào)規(guī)定，Oracle在執(zhí)行語句時(shí)，必須提示用戶給每個(gè)替換變量分別只提供一個(gè)值，在后續(xù)的&EMPLOYEE_ID和&TAX_RATE被引用時(shí)要使用它們。