萬盛學電腦網 >> 數據庫 >> sql server教程 >> 編寫可在多種語言之間移植的 Transact-SQL 代碼
編寫可在多種語言之間移植的 Transact-SQL 代碼
簡介
在本月的專欄中，我將討論那些希望編寫可以在多種語言之間移植的 Transact-SQL 代碼的開發人員所面臨的一些問題。在 sql server 7.0 中引入了 Unicode 數據類型、排序規則以及其他各種國際化和本地化功能，從而與以前的版本相比，使得編寫可以在多種語言之間移植的 Transact-SQL 代碼變得更加容易。SQL Server 2000 通過添加列級別的排序規則等內容增強了 SQL Server 7.0 中的國際化功能，因此可供開發人員使用的工具已經隨著時間的推移而變得越來越好。www.come on babyiTbulo.comLk8q0HN
SQL Server 聯機圖書 (BOL) 包含一個簡短的部分，內容是關於編寫可以在多種語言之間移植的代碼的，您應該首先閱讀一下。如果您尚未閱讀 BOL 主題“Writing International Transact-SQL Statements”（編寫國際性 Transact-SQL 語句），我建議您首先閱讀一下，然後再閱讀本專欄。www.come on babyiTbulo.comLk8q0HN
日期
在開始討論編寫可移植代碼時，讓我們首先探討一下以可移植的方式處理日期時所涉及到的問題。盡管人們通常將編寫可移植的數據庫代碼與以不同方式處理常規字符串聯系起來，但一開始編寫可移植的日期處理代碼是一種很好的方法，因為它基本上只需知道一些簡單的規則和選項就可以了。 www.come on babyiTbulo.comLk8q0HN
有關以可移植的方式在 Transact-SQL 中處理日期方面的內容，我想談的第一點就是：避免就日期格式或一周的第一天進行假設。第一點似乎是很顯然的 — 大多數人都知道全世界的日期格式是不一樣的。然而，第二點可能不是那麼明顯。在不同的文化中，將哪一天視為一周的第一天是不同的。例如，請看以下代碼：www.come on babyiTbulo.comLk8q0HN
set language us_english select datepart (dw, '20060606') set language british select datepart (dw, '20060606')
它將返回：www.come on babyiTbulo.comLk8q0HN
Changed language setting to us_english. ----------- 3 Changed language setting to British. ----------- 2
這兩個查詢返回不同的結果，這是因為在美國和英國，將哪一天視為一周的第一天是不同的。您可以通過查詢系統函數 @@datefirst 來核實這一點：www.come on babyiTbulo.comLk8q0HN
Changed language setting to us_english. ---- 7 Changed language setting to British. ---- 1
您可以通過 SET DATEFIRST 命令更改將哪一天視為一周的第一天（從而更改 @@datefirst 返回的值）。默認設置隨文化的不同而異。www.come on babyiTbulo.comLk8q0HN
對於以日期不可知的方式編寫使用 datepart(dw,...) 的代碼，有兩種簡單方法。第一種方法是在任何過程的開始簡單地調用 SET DATEFIRST，以便就一周的第一天進行假設。這將在相應過程中覆蓋 DATEFIRST 的連接設置。第二種方法是規格化 @@datefirst 返回的值，以使其與語言無關。下面是一些說明如何做到這一點的代碼。www.come on babyiTbulo.comLk8q0HN
declare @ndf_dw int set language us_english select @ndf_dw = (@@datefirst + datepart(dw, '20060606')) % 7 select @ndf_dw, datepart(dw, '20060606') set language british select @ndf_dw = (@@datefirst + datepart(dw, '20060606')) % 7 select @ndf_dw, datepart(dw, '20060606')
如果您運行上述代碼，您將看到 datepart(dw,...) 返回的值隨當前語言設置的不同而不同，但是為 @ndf_dw 返回的值是相同的。www.come on babyiTbulo.comLk8q0HN
日期名稱
Transact-SQL 日期函數為月份和星期幾返回的名稱將隨語言設置的不同而不同。正如聯機圖書中所指出的，這意味著您應該使用數值日期部分而不是名稱字符串來進行月份和星期幾的比較。例如：www.come on babyiTbulo.comLk8q0HN
select DATENAME(dw,'20060606')
如果默認語言被設置為美國英語，則返回：www.come on babyiTbulo.comLk8q0HN
------------------------------ Tuesday
但如果默認語言被設置為法語，則返回：www.come on babyiTbulo.comLk8q0HN
------------------------------ Mardi
很明顯，如果您編寫的代碼依賴於返回的特定語言的日期字符串，則當默認語言被設置為其他某種語言時，返回的字符串將可能破壞您的代碼。使用數值日期部分可以緩解這一問題。www.come on babyiTbulo.comLk8q0HN
顯示日期值
當然，在提供數據以供用戶使用或顯示時，您將希望使用日期部分的名稱，因為它們通常會更有意義。對於基本的兩層應用程序，最簡單的方法可能是讓 SQL Server 為您返回這些名稱，而不是通過客戶端應用程序中的其他某些方式來轉換這些名稱。如果登錄語言的設置不正確，這顯然不能按預期方式工作。www.come on babyiTbulo.comLk8q0HN
對於涉及到中間層或連接池的更復雜環境，假設特定最終用戶的登錄語言設置將是正確的可能不切實際。在這些情況下，一種更好的方法是從 T-SQL 代碼中返回明確的二進制日期/時間值，從而可以在客戶端應用程序中將其轉換為有意義的字符串。www.come on babyiTbulo.comLk8q0HN
比較和存儲日期
當在 DML 或比較語句中指定日期時，請務必使用在所有語言中都具有相同含義的常量。在客戶端應用程序中做到這一點的最簡單且最安全的方法是，將此類語句作為 RPC 事件提交給服務器（例如，使用托管代碼中的 SqlCommand 對象），並且使用顯式參數傳遞日期。從客戶端應用程序傳遞的並且以其內置格式編碼的參數在本質上是明確的。 www.come on babyiTbulo.comLk8q0HN
另一種選擇是使用 ODBC 轉義子句來表示在各種語言中一致的特定格式。應用程序不需要是 ODBC 應用程序，因為轉義子句由服務器解釋。前面提到過的聯機圖書主題“Writing International Transact-SQL Statements”（編寫國際性 Transact-SQL 語句）詳細介紹了受支持的轉義子句。www.come on babyiTbulo.comLk8q0HN
應用程序還可以使用不帶分隔符的、各個部分按重要性從高到低的順序（即 yyyymmdd）排序的字符串。無論語言設置如何，SQL Server 都將正確地解釋具有這種格式的日期。 www.come on babyiTbulo.comLk8q0HN
另一種選擇是使用 CONVERT() Transact-SQL 函數及其樣式參數，以便顯式指定轉換日期的目標格式或源格式。這將消除日期/時間字符串的歧義，並且可以在各種語言之間移植。www.come on babyiTbulo.comLk8q0HN
Unicode
在編寫可以在各種語言之間移植的 Transact-SQL 代碼時，最簡單最直接的方法是使用 Unicode 數據類型來表示和存儲字符數據。這意味著您應該使用 nchar 來代替 char，使用 nvarchar 來代替 varchar，以及使用 ntext 來代替 text。盡管使用 Unicode 數據類型會在可存儲的字符串的長度方面有所限制，並且可能比使用非 Unicode 類型稍微慢一些和麻煩一些，但它仍不失為處理語言可移植性問題的最簡單方法，並且是唯一的、無須考慮其他編碼注意事項就能行得通的解決方案。www.come on babyiTbulo.comLk8q0HN
在采用這一方法時，必須按照聯機圖書主題“Using Unicode Data”（使用 Unicode 數據）中所規定的那樣，小心地在 Unicode 字符串常量前面加上一個大寫字母 N 前綴。正如知識庫文章 239530, INF:Unicode String Constants in SQL Server Require N Prefix 所指出的，不這樣做將導致 SQL Server 在使用相應的字符串之前將其轉換為當前數據庫的非 Unicode 代碼頁。下面是一個示例：www.come on babyiTbulo.comLk8q0HN
-- Assumes the default code page is not Greek
create table #t1 (c1 nchar(1))
insert #t1 values(N'Ω')
insert #t1 values('Ω')
select * from #t1
這將返回：www.come on babyiTbulo.comLk8q0HN
c1 ---- Ω O
兩個 insert 語句都試圖將單個字符（希臘字母 Ω）插入到表中。正如您所看到的，在第一行的 insert 中沒有數據丟失。然而，在第二行中，Ω 已經被轉換為不同的字符（一個大寫的 O），這是因為在第二個 INSERT 語句中缺少 N 前綴，從而導致字符串被轉換為默認的代碼頁，而在該代碼頁中不存在 Ω（大寫的 O 是最近似的字符）。 www.come on babyiTbulo.comLk8q0HN
盡管該表的列使用 Unicode 數據類型，仍然不能防止數據丟失。發生數據丟失的原因是在源數據中省略了大寫 N 前綴，因而 SQL Server 在插入源數據之前將其轉換為默認代碼頁。對於日期值，如果您將采用 Unicode 參數的查詢作為 RPC 事件提交，則可以緩解通過 N 前綴來區分 Unicode 字符串和非 Unicode 字符串的需要 — RPC 參數本身就可以向服務器定義其數據類型。 www.come on babyiTbulo.comLk8q0HN
排序規則
請觀察下面的代碼，看一下您是否能夠確定它有什麼錯誤：www.come on babyiTbulo.comLk8q0HN
create function hexnum(@hexstr varchar(10))
returns bigint
as
begin
if left(@hexstr,2) in ('0x','0X') set @hexstr=substring(@hexstr,3,10) -- Lop off 0x prefix
declare @i int, @res bigint, @l int, @c char
select @i=1, @l=len(@hexstr), @res=0
if @hexstr is null OR @l=0 return null
while @i<=@l begin
set @c=upper(substring(@hexstr,@i,1))
if @c<'0' OR @c>'F' return(null)
set @res=@res+cast(1.0 as bigint)*case when isnumeric(@c)=1 then
cast(@c as int) else ascii(@c)-55 end*power(16,@l-@i)
set @i=@i+1
end
return(@res)
end
hexnum() UDF 的明顯目的是將傳遞給該函數的十六進制字符串轉換為相應的整數值。您看出該函數有什麼錯誤了嗎？讓我給您一點提示：它與排序規則依賴性有關。讓我們仔細看一下以下的代碼行：www.come on babyiTbulo.comLk8q0HN
if @c<'0' OR @c>'F' return(null)
SQL Server 2000 將一個字符視為小於還是大於另一個字符與排序規則相關：排序規則定義了用於表示字符串中各個字符的位模式，以及對字符的排序和比較方式進行控制的規則。 www.come on babyiTbulo.comLk8q0HN
上述代碼行的目的是篩選掉函數的無效輸入。如果一個字符不在 0 和 9 之間或者 A 和 F 之間，則它不可能是有效的十六進制數字，從而將導致函數失敗。該代碼行試圖利用以下事實：在默認的 ASCII 字符集中，字符 0 到 9 恰好位於 A 的前面。其編寫方式的問題在於檢查是與排序規則相關的，對於某些輸入將無法正確工作。例如，請看以下輸入：www.come on babyiTbulo.comLk8q0HN
select dbo.hexnum('0x')
它將返回：www.come on babyiTbulo.comLk8q0HN
-------------------- 145
這顯然是不正確的。原因在於字符比較與排序規則相關。在默認的排序規則中，示例字符恰好位於 0 和 F 之間，但它仍然不是有效的十六進制數字。初學者可能將該代碼行重寫為：www.come on babyiTbulo.comLk8q0HN
if @c NOT LIKE '[0-9ABCDEF]' return(null)
然而，LIKE 在這裡不是最佳的解決方案。它不需要使用，並且沒有更為簡單的技術有效。那麼，我們如何編寫檢查以使其不會受到排序規則細微差別的影響呢？請看下面的代碼：www.come on babyiTbulo.comLk8q0HN
if not ascii(@c) between @ascii0 and @asciiF return(null)
當然，這些代碼需要使用幾個新的變量：@ascii0 和 @asciiF。我們可以在函數頂部的 SELECT 語句中相當簡單地對其進行賦值：www.come on babyiTbulo.comLk8q0HN
select @i=1, @l=len(@hexstr), @res=0, @ascii0=ascii('0'), @asciiF=ascii('F')
下面是修改後的完整函數：www.come on babyiTbulo.comLk8q0HN
create function hexnum(@hexstr varchar(10))
returns bigint
as
begin
if left(@hexstr,2) in ('0x','0X') set @hexstr=substring(@hexstr,3,10) -- Lop off 0x prefix
declare @i int, @res bigint, @l int, @c char, @ascii0 int, @asciiF int
select @i=1, @l=len(@hexstr), @res=0, @ascii0=ascii('0'), @asciiF=ascii('F')
if @hexstr is null OR @l=0 return null
while @i<=@l begin
set @c=upper(substring(@hexstr,@i,1))
if not ascii(@c) between @ascii0 and @asciiF return(null)
set @res=@res+cast(1.0 as bigint)*case when isnumeric(@c)=1 then
cast(@c as int) else ascii(@c)-55 end*power(16,@l-@i)
set @i=@i+1
end
return(@res)
end
讓我們用測試字符串再次對其進行測試，看一下它是否能夠正確工作：www.come on babyiTbulo.comLk8q0HN
SELECT dbo.hexnum('0x')
這將返回：www.come on babyiTbulo.comLk8q0HN
-------------------- NULL
這正是我們所需要的。用其他輸入測試時，看起來可以確認該函數能夠按預期方式工作，並正確地篩選掉無效值。www.come on babyiTbulo.comLk8q0HN
讓我們看一下另一個與排序規則相關的編碼問題。假設您要使用默認的數據庫排序規則 Latin1_General_BIN（以便確保區分大小寫），並且具有一項用不區分大小寫的謂詞篩選針對特定表進行的查詢的業務需要。您可能喜歡編寫與下面類似的代碼：www.come on babyiTbulo.comLk8q0HN
select au_id FROM authors WHERE LOWER(au_lname) = LOWER(@au_lname)
但是，您不應該假設在每種排序規則和語言中，術語的大寫形式和小寫形式都具有完全相同的含義。在土耳其語中，大寫的“i”不是“I”，而是“?”（請注意圓點）。即使對於完全相同的輸入，LOWER(@au_lname) 也可能在不同的服務器上具有不同的輸出。www.come on babyiTbulo.comLk8q0HN
該技術也有性能問題。通過將列包裝到函數中，代碼妨礙了優化器識別索引以便為查詢提供服務的能力。要解決該問題，您可能需要創建一個計算列，將您的列轉換為特定的字符大小寫，並且在該計算列上創建索引，如下所示：www.come on babyiTbulo.comLk8q0HN
alter table authors add au_lname_lower as LOWER(au_lname) create index foo on authors (au_lname_lower) select au_id from authors where au_lname_lower=LOWER(@au_lname)
但是，添加新列和索引不是最佳的 SQL Server 2000 解決方案。它不需要使用，並且沒有更為簡單的技術有效。那麼，我們可以進行哪些更改，以便使查詢不會受到排序規則細微差別的影響呢？這裡，最佳的解決方案是用不區分大小寫的排序規則定義相應的列，如下所示：www.come on babyiTbulo.comLk8q0HN
create table authors (... au_lname varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ...)
任何其他解決方案都相當於試圖用 Transact-SQL 編寫排序規則，這沒有必要，而且充其量在 SQL Server 2000 中是靠不住的。www.come on babyiTbulo.comLk8q0HN
上述事實的教訓是，對於 Transact-SQL 之類支持排序規則的語言，自編程語言問世以來便存在的有關字符比較的傳統觀念未必適用。在與排序規則相關的環境中，您必須采用不同的編碼方式，但結果將是您的代碼可以在各種語言之間移植。www.come on babyiTbulo.comLk8q0HN
小結
如果您能夠使用 Unicode 數據類型並遵循幾個簡單的規則，則編寫可移植的 Transact-SQL 代碼並不困難。應用程序越來越需要滿足可以在各種語言之間移植的要求。如果您的業務需要要求您編寫可以在多種語言環境中運行的 Transact-SQL 代碼，則 SQL Server 2000 可為您提供需要的工具。www.come on babyiTbulo.comLk8q0HN
相關書籍
The Guru's Guide to SQL Server Architecture and Internalswww.come on babyiTbulo.comLk8q0HN
The Guru's Guide to SQL Server Stored Procedures, XML, and HTMLwww.come on babyiTbulo.comLk8q0HN
面向開發人員的 SQL Serverwww.come on babyiTbulo.comLk8q0HN
Ken Henderson 居住在美國德克薩斯州達拉斯市的郊區，身兼丈夫和父親之職。他著有八部涉及各種與技術有關的主題的著作，包括最近出版的 The Guru's Guide to SQL Server Architecture and Internals (Addison-Wesley, 2003)。作為達拉斯小牛隊的一名熱心球迷，Ken 在業余時間照料自己的孩子，參加體育鍛煉，並從事園藝活動。www.come on babyiTbulo.comLk8q0HN
關鍵詞：代碼
