sqlserver 指令碼和批處理指示小結

一.指令碼基礎
1.USE語句
設定當前資料庫。
2.聲明變數
文法：DECLARE @變數名 變數類型
在聲明變數後，給變數賦值之前，變數的值為NULL。
將系統函數賦給聲明的變數，這個方法可以使我們能更安全地使用值，該值只有人為地改變時它才變動。如果直接使用系統函數本身，那麼當它發生變動時，有
時會無法確定其究竟為何，因為大多數系統函數值是由系統確定的。這容易在你不預期的情況下導致系統改變了值，引起不可預期的後果。
(1).給變數賦值
SET：當進行變數賦值是，該值已經知道是確切值或者是其他變數時，使用SET。
SELECT：當變數賦值基於一個查詢時，使用SELECT。
(2).系統函數
SQL Server 2005中有30多個無參的系統函數，其中一些最重要的如下：
@@ERROR： 返回當前串連下，最後執行的T-SQL語句的錯誤碼，如無錯誤返回0。
@@FETCH_STATUS： 和FETCH語句配合使用。
@@IDENTITY： 返回最後一句運行語句的、自動產生的標識值，作為最後INSERT或者SELECT INTO語句的結果。
@@ROWCOUNT: 返回最後一個語句影響的行數。
@@SERVERNAME： 返回指令碼正在其上啟動並執行本地服務的名字。
@@TRANCOUNT： 返回活動事務的數量，特別是針對當前串連的事務的瓶頸程度。

二.批處理
(1).GO單獨佔一行。在同一行上，T-SQL語句不能在GO語句之前。
(2).所有語句從指令碼開始處或者上一個GO語句開始編譯，直到下一個GO語句或者指令碼結束，將這段代碼編譯到一個執行計畫中並相互獨立地送往伺服器。前一
個執行計畫中發生錯誤，不會影響後一個執行計畫。
(3).GO不是一個T-SQL命令，只是被編輯工具識別的命令。當編輯工具碰到GO，它把GO看做一個結束批處理的標記，將其打包，然後作為一個獨立單元發送到
伺服器——不包括GO，伺服器對於GO沒有任何概念。
1.批處理中的錯誤
語法錯誤，執行階段錯誤。
2.何時使用批處理
(1).獨自成批處理的語句
有幾個命令必須獨自成批處理，它們包括：
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TRIGGER
CREATE VIEW
如果想將這些語句中的任何一條和其他語句組成單獨的一個指令碼，那麼需要採用一個GO語句將它們分別斷開，歸入各自的批處理中。
(2).使用批處理建立優先順序
使用批處理最可靠的例子是，當需要考慮語句執行的優先順序時，也就是說，需要一個任務在另一個任務開始前執行。
例如：
CREATE DATABASE Test

複製代碼 代碼如下:CREATE TABLE TestTable
(
col1 INT,
col2 INT
)

執行語句，會發現產生的表沒有在Test資料庫中，而是在master資料庫中（如果當前使用的資料庫是系統資料庫）。因為在執行指令碼的時候，使用的資料
庫是系統資料庫，該資料庫是當前的，所以產生的表在系統資料庫中。看起來，應該在建立表之前指定資料庫Test。然而，這樣仍然存在問題。解析器試圖校
驗代碼，發現我們用USE命令引用的資料庫並不存在。原因在於建立資料庫的語句和建立表的語句寫在一個批處理中，在執行該指令碼之前，當然資料庫還沒有
建立。根據批處理的要求，我們將建立資料庫和建立表的指令碼用GO語句分為兩個獨立的批處理。正確代碼如下：複製代碼 代碼如下:CREATE DATABASE Test
GO

USE Test
CREATE TABLE TestTable
(
col1 INT,
col2 INT
)

三.動態SQL：使用EXE命令產生代碼
文法：EXEC/EXECUTE ({<字串變數> | '<字面值命令字串>'})
1.EXEC的範圍
真正的調用EXEC語句的行，擁有同該EXEC語句正在啟動並執行批或過程中的其他代碼相同的範圍。但是作為EXEC語句結果而被執行的代碼，被認為是在它自
己的批中。
例如：
DECLARE @OutVar VARCHAR(50)
EXEC ('SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1')
這裡系統會報錯，指出必須聲明變數@OutVar。因為EXEC的語句獨自成為一個批處理，其中的變數不能和其外的範圍相溝通，只在這個批處理中有效。此
時，@OutVar的值為NULL。正確的寫法如下：
EXEC ('DECLARE @OutVar VARCHAR(50)
SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1')
這裡，我們看到兩中不同的範圍，這兩種範圍間不能相互溝通。如果不採用外部機制，比如一個暫存資料表，我們就沒有辦法實現在內部範圍和外部範圍
之間傳遞資訊。有一個例外的事情是可以在EXEC的地區內部出現，並且也能在EXEC執行後被看到，這就是系統函數。因此，像@@ROWCOUNT這樣的變數仍然
能夠被使用。
2.安全上下文和EXEC
當賦予某人權利運行一個預存程序，意味著他也能獲得權利去執行預存程序內部的動作。比如，有一個預存程序用來列出去年內所有的僱傭員工。其中有許可權
執行該預存程序的人，才能夠執行並返回結果——即使他沒有許可權直接存取人力資源的員工表。
這樣隱含許可權對於EXEC語句是無效的。在預設情況下，任何在一個EXEC語句內部建立的參照，都將在目前使用者的安全上下文中運行。因此，我們有權利去訪
問一個叫spNewEmployee的預存程序，但是卻沒有權利去訪問員工表。如果spNewEmployee通過一個簡單的SELECT語句獲得值，那麼一切正常。但是如果
pNewEmployee使用EXEC語句去執行一個SELECT語句，這個EXEC語句將失敗——因為沒有權利訪問員工表。
3.使用者自訂函數和EXEC關聯
不能在同一個語句中同時運行一個函數和EXEC語句。例如：
DECLARE @Num INT
SET @Num = 3
EXEC ('SELECT LEFT(LastName, ' + CAST(@Num AS VARCHAR) + ') AS FilingName FROM Contact')
這個語句會返回一個錯誤訊息，因為CAST函數需要在EXEC所在行之前被解析。正確代碼如下：
DECLARE @Num INT
DECLARE @str VARCHAR(255)
SET @Num = 3
SET @str = 'SELECT LEFT(LastName, ' + CAST(@Num AS VARCHAR) + ') AS FilingName FROM Contact'
EXEC (@str)
這個例子工作正常，因為EXEC的輸入值已經是一個完整的字串。
4.EXEC和使用者自訂函數
一般來說，不允許使用者自訂函數內部使用EXEC去運行動態SQL，但是，使用EXEC運行一個預存程序，少數情況是合法的。