數據庫管理員在寫SQL命令的時候總希望SQL語句能夠滿足不同環境的需要為此可以通過交互式命令來提高SQL語句的靈活性當在一個SQL語句中加入了交互式變量之後在語句運行之時數據庫就會提醒用戶輸入相關的變量如下圖
![利用替換變量提高Oracle數據庫系統的交互性]()
不過在實際工作中往往把這個SQL語句保存為腳本如此的話可以進一步提高腳本文件的可重用性有時會在數據庫管理的時候結合替換變量寫一些有用的腳本文件可以起到畫龍點睛的作用筆者在平時數據庫開發與管理中就喜歡采用這種方式來簡化工作在這篇文章中筆者就結合自己的工作經驗給大家談談如何在Oracle數據庫中利用可替換變量來提高SQL語句的靈活性以及在使用替換變量時的一些注意點
一 定義與使用替換變量
在Oracle提供的相關工具中如SQL PLUS管理平台中允許數據庫管理員采用替換變量來提高SQL語句的靈活性替換變量的定義跟普通變量的定義類似只需要在變量名前面加入&符號即可如上面例子中的&name_filed就表示一個替換變量當運行一段有替換變量的SQL語句時數據庫系統會自動提醒用戶要輸入哪些變量值
如在如下這條語句中&name_filed表示用戶想要顯示的字段值若用戶想要顯示某個表中的多個字段的話則可以在這裡定義多個替換變量而條件語句中的&NAME則表示一個限制條件或者說一個查詢參數這條語句的大意就是用戶輸入員工的名字然後讓系統查詢出用戶想了解的信息如用戶的身份證號碼等等
SQL> select &name_filed from dtmfgad_user t where tname like &NAME;
細心的讀者或許會發現在查詢條件中的替換變量為什麼要用單引號括起來呢?這是因為我們在寫SQL語句的時候若字段時字符型的話就需要用單引號括起來所以在定義替換變量的時候跟平時寫SQL語句類型若數據類型是字符或者日期型的則需要在定義替換變量的時候用單引行括起來否則的話在用戶執行這條語句輸入變量的時候每次都需要把括號添加進去那就非常的麻煩相比起來還不如在定義這條SQL語句的時候就加入單引號如此在輸入對應值的時候就不用用單引號括起來了可以直接輸入
另外替換變量不但在Select語句中可以使用在Update等語句中也可以普遍應用
二 定義默認值
有時候對於替換變量我們可能會有默認值如果數據庫管理員已經知道替換變量的具體值又想避免運行腳本文件時反復出現則可以給替換變量定義一個默認值在SQL Plus平台中數據庫管理員可以利用Define命令來定義替換變量的默認值
在定義替換變量默認值的時候需要注意幾個問題
一是要合理使用單引號該用單引號括起來的就要使用單引號;不需要用的時候堅決不用否則的話會造成SQL語句的運行錯誤那麼什麼時候該用單引號呢?這主要是根據SQL語句來的平時在寫SQL語句的過程中要用到單引號的則在定義默認值的時候也要用單引號括起來如果平時不用的話則就可以不用如在定義字段名稱的默認值時就不需要用單引號括起來畫蛇添足反而會導致SQL語句的執行錯誤
二是這只是定義了一個默認值當定義來一個默認值之後系統就不會提示用戶輸入相關的參數而直接采用默認值如在上面這個例子中若利用define NAME=SuperUser這條語句定義了NAME的默認值之後在執行這條Select語句的時候就不會再提示用戶輸入NAME值我們可以利用Define命令來查看當前環境中所有的可替換變量的值也可以利用Define NAME來查看可替換變量NAME的默認值
三是若用戶采用的是腳本文件的話則可以直接把這個定義默認值的語句放在腳本文件的一開始如此的話這個默認值的定義就會隨著腳本文件而走用戶不用每次使用腳本文件的時候都先去定義默認值這個這個默認值的作用域只是適用於當前的會話當數據庫管理員結束當前的會話後這個變量的默認值也就失效了為了在下次重新會話的時候仍然可以利用這個默認值故在腳本文件一開始定義可替換變量的默認值也是非常有必要的
四是利用Define命令來定義可替換變量的時候有一個限制條件即可替換變量必須是字符型的數據若用戶想定義的可替換變量是數字型的如員工的薪水此時就需要采用ACCEPT命令否則的話就是一個無效的默認值
若我們有時候不需要這個默認值需要取消他改如何處理呢?此時我們就可以利用undefine命令來取消某個替換變量的默認值當然我們也可以利用Define命令來給某個替換變量設置一個新的值在實際工作中可能還是直接采用Define命令來為其設置一個默認值來的方便
三 測試完畢後保存為腳本文件
為了增強這個SQL語句的可重用性我們往往會把寫好的SQL語句保存為腳本文件如此的話下次需要用到的時候直接執行這個腳本語句即可在保存的時候為了有更好的可讀性筆者有一些小小的建議
一是最好在腳本文件的開頭注釋一些這條語句的具體用途俗話說好記性不如爛筆頭在幾個月後很難確保你還記得這條語句的用途
二是給可替換變量取一些比較直觀的名字否則的話當使用數據庫管理員可能直接都不知道某個替換變量所代表的含義另外列的替換變量跟查詢語句中的替換變量最好進行區分如可以利用前綴進行區別如此做的話這個語句的可閱讀性就會增強
三是可利用Prompt命令來輸出一些提示信息在編寫替換變量的時候要像編寫其他程序一樣養成為每個可替換變量做好注釋的習慣這時作為數據庫管理員一個必備的技能之一
四 其他解決方案
針對Oracle數據庫SQL語句的交互性而言它還提供了一些其他的解決方案
如利用ACCEPTPAUSEPROMPT三個命令結合使用提供一個模擬的用戶輸入界面ACCEPT命令可以讓數據庫管理員指定替換變量的類型因為Define定義替換變量默認值的時候只能夠接受字符型數據的默認值若是其他數據類型的默認值的話就要用ACCEPT來定義而PAUSE顧名思義可以讓腳本語句暫時運行以提供一個更加友好的用戶界面如可以讓用戶通過輸入回車鍵來執行剩下的腳本語句等等如此就給用戶一個喘息核對數據的機會
如可以通過綁定變量的方式來實現替換變量的功能綁定變量只在當前會話中存在同名的綁定變量一最後一個定義為准綁定變量在賦值的時候比較麻煩需要采用Excute命令來給綁定變量進行賦值所以在實際應用中不是很廣泛
筆者在這集中解決方案中比較喜歡采用前面兩種方式即利用Define與Accept語句來定義替換變量同時采用Prompt與Pause命令來給用戶以及自己設計一個人性化的輸入界面
From:http://tw.wingwit.com/Article/program/Oracle/201311/17006.html
