Transact-SQL程式設計

Transact-SQL程式設計

Transact-SQL是Microsoft® SQL Server™ 的程式設計語言，而Transact-SQL的本質就是「結構化查詢語言」(Structures Query Language：SQL，念做’sequel’)。

所謂「程式設計」即是學習如何以合理的方式將一系列的命令結合起來，而「合理的方式」也就是要以電腦看得懂的語法來編寫，這便涉及到Transact-SQL之程式結構與語法。

舉凡是Microsoft® SQL Server™ 的各項程式設計工具、Transact-SQL的組成元素、程式設計的基礎知識(包括：常數、記憶體變數、運算式、運算子…等等)。

在SQL Server中，所謂的 ’查詢’ 係泛指以Transact-SQL所撰寫的程式，它所執行的作業可能是新增、修改、刪除、或查詢資料記錄，亦或是任何其他作業，絕不是僅僅侷限於查詢資料記錄。

※準備工作

1.      先將主資料檔NorthwindSQL.MDF與記錄檔NorthwindSQL.LDF複製到磁碟目錄C:\Program Files\Microsoft SQL Server\MSSQL\Data (假設資料庫存放目錄) 中，然後執行下列程式以便將其依附於SQL Server內：

2.      於Query Analyzer公用程式中執行如下命令：

   /* 檔案名稱: BuildNorthwindSQL.sql */

   EXEC sp_attach_db @dbname = N'NorthwindSQL',

   @filename1 = N'C:\Program Files\…\MSSQL\Data\NorthwindSQL.mdf',

   @filename2 = N' C:\Program Files\…\MSSQL\Data \NorthwindSQL.ldf'

※善用SQL Query Analyzer

SQL Server擁有許多程式設計工具，其中最常使用的是SQL Query Analyzer與osql公用程式。

SQL Query Analyzer是一個極具親和力的圖形介面工具，主要特性與功能如下：

1.      提供一種交談式的環境來供撰寫、編輯、與執行Transact-SQL命令敘述，並檢視執行結果。

2.      能夠以不同的顏色與字型來顯示程式碼中不同的組成部分，使程式變得更加易讀與維護。

3.      能夠檢查所撰寫之程式的語法是否正確。

4.      可以使用T-SQL偵錯工具來偵測預存程序的錯誤。

5.      能夠從事先定義的查詢檔(即範本)中快速建立經常使用的資料庫物件。

6.      「物件瀏覽器」指令碼功能能夠快速複製現有的資料庫物件(即使不知道參數也能夠執行預存程序)。

7.      可以使用顯示執行計畫、顯示伺服器追蹤、顯示用戶端統計資訊、索引微調精靈等功能找出在執行效能方面的問題，以便透過一個明確的途徑來最佳化查詢的執行效能。

8.      Transact-SQL命令敘述與執行結果會同時顯示於不同的窗格中，以方便相互對照與偵錯，亦可以將查詢結果顯示於一個可捲動的多筆編輯方格中，或以純文字來顯示。

9.      可以將撰寫並測試無誤的Transact-SQL命令敘述儲存至一個SQL查詢檔(.sql)中，並可於SQL Query Analyzer中來執行。

10.  能夠以圖形方式來顯示執行計劃。

11.  能夠檢視統計資訊。

12.  可以同時執行多個Transact-SQL查詢檔或預存程式。

13.  快速檢視Transact-SQL語法的能力。

※開啟SQL Query Analyzer

1.      執行下列兩項操作之一：

(1)   從Windows「開始」功能表－＞「程式集」指令－＞Microsoft SQL Server指令－＞Query Analyzer指令。

或

(2)   如果已開啟SQL Server Enterprise Manager，可從其「工具」功能表中選取SQL Query Analyzer指令。

2.      必須先連線至SQL Server才能在SQL Query Analyzer中開啟一個查詢視窗。

3.      進行帳戶驗證選項，並按「確認」按鈕。

於SQL Query Analyzer的查詢視窗標題列中皆會以SqlServerName.Database.Login的格式來標示此資訊。

可以隨時使用「檔案」功能表中的「連線」指令來連線至特定的SQL Server。

※建立新的查詢

1.      於SQL Query Analyzer之「檔案」功能表中的「連線」指令，便會自動開啟一個新的查詢視窗；或在工具列中的「新增查詢」按鈕來開啟一個新的查詢視窗。

2.      決定作用的資料庫，可於「資料庫」下拉式清單方塊中選取，另一種方法是直接於程式碼中使用USE命令來設定。

3.      於查詢視窗的編輯器窗格中鍵入Transact-SQL程式碼。

4.      執行查詢並檢驗執行結果。

5.      存檔。

※重要操作技巧

1.      快速取得Transact-SQL語法說明 (按SHIFT - F1) ，先Mark關鍵字再按SHIFT-F1即可。

2.      清除編輯器窗格中的所有內容，只需從「編輯」功能表中選取「清除視窗」指令即可。

3.      快速移至特定列號的程式，只需從「編輯」功能表中選取「到命令列」指令即可。

※執行查詢

1.      先檢查Transact-SQL程式碼的語法是否正確：可在實際執行查詢前先去檢查Transact-SQL程式碼的語法是否正確。

◆    於實際執行查詢前，先檢查語法是否有誤，可使用工具列中的「剖析查詢」按鈕。

◆    可決定如何顯示查詢結果，從「查詢」功能表中選取「以文字顯示結果」指令；或「以方格顯示結果」指令。

  

2.      僅僅執行查詢中某一段Transact-SQL程式碼：基於偵錯與測試等目的，有時會希望只去執行某一段程式。

◆    如果要執行一整個查詢，則用工具列中的「執行查詢」按鈕(或按F5鍵)執行。

◆    如果只想執行查詢中某一段Transact-SQL程式碼，則先將所要執行的那一段Transact-SQL程式碼選取起來，在用工具列中的「執行查詢」按鈕(或按F5鍵)執行。

※使用範本

為了能夠快速以程式碼於資料庫中建立所需的物件，Microsoft® SQL Server™ 2000特別提供一系列的範本來協助完成此項工作。

SQL Query Analyzer所提供的範本程式碼中內含各種參數，透過重訂這些參數的值，便可以自訂程式碼而使其完全符合設計需求。

範本的參數定義使用下列格式：

此格式說明如下：

1.      parameter_name是程式碼中之參數的名稱。

2.      data_type是程式碼中之參數的資料型態。

3.      value是用以取代程式碼中之參數的值。

程式設計師可以使用「取代範本參數」對話方塊將替代值插入程式碼中。

也就是說，範本最主要的目的就是幫助程式設計師快速撰寫出執行特定作業的Transact-SQL命令敘述。

範例：

假如想要利用範本來協助快速撰寫出建立索引的CREATE INDEX命令敘述，則依下列步驟進行：

1.      按一下物件瀏覽器底端的「範本」標籤來切換至其頁面。

2.      由於要建立索引，因此應展開Create Index項目，再使用滑鼠將Create Index Basic範本拖放至編輯窗格中欲插入CREATE INDEX命令的位置上，此舉會立即將此範本之.sql檔的內容插入其中。

接下來，要利用「取代範本參數」對話方塊來變更CREATE INDEX命令中各個參數的值，以便使其符合需求。

3.      將所插入之範本的Transact-SQL命令敘述全部選取後，從「編輯」功能表中選取「取代範本參數」指令。

4.      於「取代範本參數」對話方塊開啟後，修改各個參數的設定值，然後按下「全部取代」按鈕。

※善用物件瀏覽器

物件瀏覽器採用可逐層展開的樹狀圖來列出所連線之SQL Server中所有的資料庫與通用物件，使能輕易導覽與存取各個資料庫中的物件，取用所有的內建函數，執行預存程序，甚至還能夠自動產生相關資料庫物件的建立、刪除、與存取命令敘述。善用之可大幅提昇開發效率。

其各項功能如下：

1.      將物件加至程式碼中

為了提昇程式撰寫的便利性，可以直接從物件瀏覽器將某個或某類物件拖放至編輯器窗格中，可省卻自行輸入的麻煩。

2.      檢視、新增、修改、刪除資料表的資料記錄

按右鍵於欲作用之資料表，並從快顯功能表中選取「開啟舊檔」指令，此舉會使得該資料表的所有資料記錄顯示於「開啟資料表」視窗中，以便於檢視、新增、修改、刪除資料表的資料記錄。

3.      編輯資料庫物件