Версия для печати

На главную страницу

Новые возможности T-SQL в SQL Server 2005 - Часть 2/3

В статье за прошлый месяц я описал возможности PIVOT, OUTPUT и TOP N в T-SQL 2005. Сейчас я хочу рассказать еще о трех новинках языка T-SQL 2005:

• Новый оператор APPLY, который (как указывает название) позволяет разработчикам более эффективно применяться/интегрировать таблично-значные функции пользователя (UDF) в запросах SQL.
• Новые функции ранжирования для более легкого и эффективного присвоения ранговых номеров в результирующем наборе.
• Новая возможность обработки ошибок TRY….CATCH, позволяющая разработчикам и администраторам баз данных писать более понятный код хранимых процедур

1 - APPLY

Как разработчик приложений для баз данных, я часто пишу таблично-значные функции UDF в SQL Server. Если Вы не знакомы с таблично-значными UDF, скажу, что это определяемые пользователем функции, которые выполняют запрос и возвращают результаты в виде табличной переменной. Обычно таблично-значные функции создают, если Вы хотите многократно использовать результирующий набор в различных запросах (таким же образом Вы бы писали любые другие повторно используемые функции и вызывали их из различных программ). Например, в базе данных AdventureWorks в SQL Server 2005, если бы я захотел возвратить результирующий набор TOP N заказов на поставку для определенного служащего, то написал бы UDF, возвращающую табличную переменную:

USE [AdventureWorks]
GO /****** Object: UserDefinedFunction [dbo].[GetTopPurchaseOrders] Script Date: 03/28/2007 07:18:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTopPurchaseOrders]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetTopPurchaseOrders]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[GetTopPurchaseOrders]
(@EmployeeID INT, TopN INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@TopN) PurchaseOrderID, EmployeeID, VendorID, OrderDate, TotalDue
FROM Purchasing.PurchaseOrderHeader WHERE EmployeeID = @EmployeeID
ORDER BY TotalDue DESC

Чтобы выполнить эту UDF, которая должна вернуть 10 лучших заказов для Служащего с идентификатором 164, я бы написал следующий запрос:

SELECT * FROM [dbo].[GetTopPurchaseOrders] (164,10)

Пока я могу все это сделать в SQL 2000 (за исключением того, что я должен буду закодировать TOP N по-другому - см. статью за прошлый месяц). Однако, если я хочу выполнить UDF для каждой записи в базе данных employee, я должен был бы использовать временную таблицу или табличную переменную как часть моего запроса.

Другими словами, SQL Server 2000 не позволял мне непосредственно ПРИМЕНЯТЬ таблично-значные UDF к строкам в другой таблице как часть SQL-запроса. Однако Сервер SQL 2005 вводит новый оператор APPLY, который дает возможность разработчикам более эффективно интегрировать таблично-значные UDF:

SELECT Emp.EmployeeID, Emp.LoginID,
TopOrders.PurchaseOrderID, TopOrders.VendorID, TopOrders.OrderDate, TopOrders.TotalDue
FROM HumanResources.Employee Emp
CROSS APPLY [dbo].[GetTopPurchaseOrders] (Emp.EmployeeID,5) AS TopOrders
ORDER BY Emp.EmployeeID, TotalDue DESC

Вышеприведенный запрос читает таблицу Employee и передает идентификатор каждого Служащего (EmployeeID) как параметр в UDF. Синтаксис включает оператор CROSS APPLY, чтобы применить UDF к каждому EmployeeID:

CROSS APPLY [dbo].[GetTopPurchaseOrders] (Emp.EmployeeID,5) AS TopOrders

2 - РАНЖИРОВАНИЕ

Теперь мы хотим взять результаты предыдущего запроса (top N заказов по сумме для каждого служащего) и присвоить порядковый номер каждой строке в результирующем наборе. Это часто используется в приложениях, генерирующих отчеты, где необходима последовательная нумерация. (то есть. #1, #2, #3, и т.д.) До SQL Server 2005 разработчик либо должен был написать некоторый код, назначающий ранговый номер для каждой строки, либо вставить отсортированный результирующий набор в таблицу, содержащую ключ идентичности (identity). Хотя и реализуемый, этот процесс становился еще более трудным, если ранжируемый результирующий набор должен был быть сгруппирован в пределах другой сущности (например, переназначать ранговый номер каждый раз при изменении числа клиентов или служащих).

К счастью, T-SQL 2005 содержит новую функцию ROW_NUMBER(), которая делает эту задачу почти смехотворно простой!!! Для начала давайте изменим приведенный выше запрос (см. предыдущую статью - прим.перев.), чтобы присвоить общий ранговый номер каждой продаже из TOP 5 для каждого служащего:

DECLARE @nTop INT
SET @nTop = 5
SELECT Emp.EmployeeID, Emp.LoginID,
TopOrders.PurchaseOrderID, TopOrders.VendorID, TopOrders.OrderDate, TopOrders.TotalDue,
ROW_NUMBER() OVER ( ORDER BY TotalDue DESC) AS RankNum
FROM HumanResources.Employee Emp
CROSS APPLY [dbo].[GetTopPurchaseOrders] (Emp.EmployeeID,@nTop) AS TopOrders
ORDER BY RankNum

Этот код создаст результирующий набор, который пронумерует лучшие 5 заказов каждого служащего по общей причитающейся сумме (TotalDue) в порядке убывания. Отметьте для себя новую функцию ROW_NUMBER () OVER в операторе SELECT, которая добавляет ранговый номер к результирующему набору на основании убывания значения TotalDue:

ROW_NUMBER() OVER ( ORDER BY TotalDue DESC) AS RankNum

Если Вы хотите назначить последовательные номера в пределах служащего, Вы можете сгруппировать ранжирование, определив Partition в операторе:

ROW_NUMBER() OVER ( PARTITION BY Emp.EmployeeID ORDER BY TotalDue DESC) AS RankNum
ORDER BY Emp.EmployeeID, RankNum

Заметим, что в этом случае мы хотим упорядочить результирующий набор по столбцу RankNum в пределах EmployeeID.

Наконец, Вы можете столкнуться с ситуацией, когда порядок зависит от условия во время выполнения. Предположим, что нам необходимо назначить ранговые номера результирующему набору, когда порядок сортировки определяется одним из нескольких столбцов. Тут можно применить оператор CASE:

ROW_NUMBER() OVER (ORDER BY
CASE @SortCol
WHEN 'Column1' THEN Column1
WHEN 'Column2' THEN Column2
ELSE Column1 END)
AS RankNum

Замечание: некоторые разработчики сразу решают использовать динамический SQL, когда сталкивающийся с условной обработкой (такой, как в примере выше). Хотя иногда сложно полностью избежать динамического SQL, подумайте, не можете ли Вы в таких случаях использовать оператор CASE.

3 - Обработка ошибок с помощью Try…Catch

В течение многих лет, разработчики SQL Server искали способы обеспечить разумную обработку ошибок в хранимых процедурах, и ждали возможности использовать Try…Catch, имевшуюся в языках программирования типа C# и VB.NET. T-SQL 2005 ввел основные возможности Try…Catch, которые позволяют Вам более эффективно тестировать ошибки, и возвращать их в слой приложения.

Например, предположим, что мы пытаемся удалить запись из таблицы заголовков заказа по номеру заказа:

DELETE FROM Purchasing.PurchaseOrderHeader WHERE PurchaseOrderID = 44

Эта строка кода прерывается в SQL Server ошибкой, поскольку мы пытались нарушить ограничение между таблицами Order Header и Order Detail. Если мы выполняем этот оператор в хранимой процедуре и вызываем ее из приложения .NET (или любого другого приложения, но я буду использовать .NET в качестве примера), выполнение оператора будет прервано в базе данных, не возвращая сколь-нибудь осмысленного сообщения в слой приложения.

То, что мы хотели бы сделать, - перехватить эту ошибку в хранимой процедуре, и затем сгенерировать ошибку, сообщение о которой база данных могла бы вернуть в слой приложения, которое, в свою очередь, обнаружит ее в части своего собственного блока TRY…CATCH. Ниже приведен полный код для такой хранимой процедуры, который: мы разберем подробно по частям:

USE AdventureWorks
BEGIN
BEGIN TRANSACTION
BEGIN TRY
DELETE FROM Purchasing.PurchaseOrderHeader WHERE PurchaseOrderID = 44
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
END CATCH
COMMIT TRANSACTION
END
GO

Первое, что мы должны сделать, - это обертку вокруг оператора DELETE, поместив его внутри блока BEGIN TRY…END TRY. Сразу после этого мы напишем наш блок Begin Catch…End Catch, который будет выполняться, только если код внутри BEGIN TRY…END TRY вызывает некоторую ошибку:

BEGIN TRY
DELETE FROM Purchasing.PurchaseOrderHeader WHERE PurchaseOrderID = 44
END TRY
BEGIN CATCH
-- процедура обработки ошибки
END CATCH

Внутри блока CATCH мы хотим определить для ошибки уровень серьезности, номер ошибки, сообщение об ошибке и состояние ошибки с тем, чтобы вернуть в приложение эту информацию. Для этого мы создаем переменные и используем соответствующие функции SQL, чтобы получить информацию об ошибке:

DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()

Теперь мы собираемся использовать функцию SQL RAISERROR, которую наше приложение .NET обнаружит в своем собственном блоке TRY….CATCH:

RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)

Однако в меде имеется одна маленькая муха. Ошибки нарушения ограничения не сообщают конкретный код состояния ошибки, а RAISERROR требует состояние ошибки. Таким образом, мы должны проверить состояние ошибки, чтобы выяснить, является ли значение нолем, и если так, установить его в значение 1 ПЕРЕД генерацией ошибки в SQL Server:

IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)

Наконец, мы можем откатить транзакцию, если SQL Server в настоящее время имеет активную транзакцию в сессии, которая, однако, не может быть зафиксирована из-за ошибки. Мы используем XACT_STATE (), чтобы определить, классифицировал ли SQL Server 2005 транзакцию как uncommittable (нефиксируемая):

IF XACT_STATE() < 0
ROLLBACK TRANSACTION

Еще два замечания относительно обработки ошибок в SQL Server. Первое (плохие новости), T-SQL 2005 не содержит оператора FINALLY, который существует в языках программирования, которые реализуют TRY…CATCH. Второе (хорошие новости), разработчики могут использовать вложенные блоки TRY…CATCH в хранимой процедуре.

01.06.2007

На главную страницу

  Версия для печати