45795 (665139), страница 2
Текст из файла (страница 2)
Инструкция TABLESAMPLE
Иногда бывает необходимо получить некоторую выборку записей из базы данных, отражающую характер данных, содержащихся в базе. Для осуществления подобной выборки в SQL Server 2005 добавлена инструкция TABLESAMPLE, которая в качества параметра принимает количество строк или относительное количество строк в процентах от общего числа в таблице. Используется инструкция следующим образом:
| SELECT СпискоПолей FROM ИмяТаблицы TABLESAMPLE(КоличествоПроцентов PERCENT) |
либо, если нужно выбрать определенное количество строк
| SELECT СпискоПолей FROM ИмяТаблицы TABLESAMPLE(ЧислоСтрок ROWS) |
Однако стоит отметить, что будет возвращено не заданное количество строк (ROWS) или процентов (PERCENT), а лишь приблизительно соответствующее заданному количество.
Если необходимо получать выборку постоянного содержания в течение нескольких повторяющихся запросов, то нужно указать после инструкции TABLESAMLE дополнительно инструкцию REPEATTABLE:
| SELECT СпискоПолей FROM ИмяТаблицы TABLESAMPLE(ЧислоСтрок ROWS) REPEATTABLE(ЧислоПовторений) |
Функция OUTPUT
Новая функция OUTPUT служит для повторного использования данных запроса. С помощью этой функции можно получить измененные в текущем запросе данные и использовать для вставки в другую таблицу, либо вернуть эти данные в вызывающий код.
Представим, что в магазине, использующем демонстрационную базу данных, произошло радостное событие, и вся партия поступивших в продажу ноутбуков была закуплена крупным заказчиком в момент поступления. В этом случае необходимо данные, вставляемые в таблицу Products, поместить также и в таблицу Orders. В SQL Server 2005 это можно сделать в одном запросе к базе данных:
| INSERT INTO Products(BrandID, Model, Configuration, Price, Quantity) OUTPUT GETDATE(), inserted.ProductID, inserted.Quantity INTO Orders([Date], ProductID, Quantity) VALUES (@BrandID, @Model, @Configuration, @Price, @Quantity) |
Читатель легко увидит из примера, что для доступа к изменяемым данным (вставляемым в таблицу в данном примере) используется идентификатор (имя псевдо таблицы), указывающий на характер операции, проводимой с данными. Допустимо использование следующих идентификаторов:
inserted, для команды INSERT
deleted, для команды DELETE
В случае использования функции OUTPUT в запросе UPDATE, измененные данные будут доступны в псевдо таблице inserted, а данные, которые подверглись изменению в псевдо таблице deleted.
Функция OUTPUT не может быть использована в запросе INSERT, в котором вставка проводится в представление данных (View), а также для вставки измененных данных в представление или табличную функцию.
Также стоит помнить о том, что OUTPUT не гарантирует, что элементы будут вставляться в таблицу в том же порядке, в котором происходит применение изменений. При этом, если в процессе выполнения запроса UPDATE изменяются какие-либо переменные или параметры, то OUTPUT возвращает не модифицированные значения параметров или переменных, то есть такие значения, которые переменные или параметры имели до выполнения запроса.
Применения функции OUTPUT
Выборка вставленных данных
Иногда бывает удобно получить в качестве результата выполнения процедуры, вставляющей данные в таблицу, результирующую строку, особенно когда эта строка содержит колонку с уникальным значением. Используя OUTPUT это можно следующим образом:
| DECLARE @TempBrands TABLE (BrandID int, [Name] nvarchar(32)) INSERT INTO Brands([Name]) OUTPUT inserted.* INTO @TempBrands VALUES(@Name) SELECT * FROM @TempBrands |
Отметим, что этот пример показывает работу с одной строкой, поскольку при втавке большого количества строк, поряок следования может быть нарушен (как уже было написано выше, OUTPUT не гарантирует порядок строк) и использовать значения BrandID в вызывающем коде без дополнителных проверок будет проблематично.
Конечно, нет никакой проблемы в том, чтобы получить в результате запроса BrandID, не используя OUTPUT, поскольку обычно все данные уже имеются в вызывающем процедуру коде (они же и передаются в качестве аргументов самой процедуре), за исключением элемента с уникальным значением.
| INSERT INTO Brands([Name]) VALUES(@Name) SELECT @@IDENTITY |
Реализация функциональности очереди
Функция OUTPUT также позволяет удобно реализовать функциональность очереди, «извлекая» из таблицы запись, при этом удалять ее. Например, если потребуется функциональность очередей на выполнение заказа, то необходимо будет создать таблицу-очередь, например
| CREATE TABLE [Queue]( [QueueID] [int] IDENTITY(1,1) NOT NULL, [OrderID] [int] NOT NULL ) |
и с помощью нее реализовать необходимую функциональность, используя функцию OUTPUT:
| DECLARE @Queue TABLE (QueueID int, OrderID int) DELETE TOP 1 FROM [Queue] ORDER BY QueueID OUTPUT deleted.QueueID, deleted.OrderID INTO @Queue SELECT * FROM @Queue |
Без использования функции OUTPUT, код получается несколько более громоздким:
| DECLARE @Queue TABLE (QueueID int, OrderID int) INSERT INTO @Queue(QueueID, OrderID) SELECT TOP 1 [Queue].QueueID, [Queue].OrderID FROM [Queue] DELETE [Queue] FROM [Queue] AS Q1 INNER JOIN @Queue AS Q2 ON Q1.QueueID = Q2.QueueID SELECT * FROM @Queue |
Функции PIVOT и UNPIVOT
Магазин ноутбуков с успехом использует демонстрационную базу данных в течение многих лет, и накопил огромную статистику по продажам ноутбуков. Естественно желание знать, для сравнения, объемы продаж за разные годы и общую сумму прибыли. Для того, чтобы из таблиц Orders и Products получить интересующую владельцев магазина информацию лучшим способом является использование ключевого функции PIVOT, позволяющей как бы «развернуть» данные в таблице.
| SELECT Model, [2005], [2004] FROM ( SELECT P.Model, DATEPART(year, O.[Date]) AS [Year], O.Quantity FROM Orders O INNER JOIN Products P ON P.ProductID = O.ProductID ) AS C PIVOT (SUM(Quantity) FOR [Year] IN ([2005], [2004])) AS PVT |
С использованием виртуального представления код можно написать несколько иначе:
| WITH C(Model, [Year], Quantity) AS ( SELECT P.Model, DATEPART(year, O.[Date]) AS [Year], O.Quantity FROM Orders O INNER JOIN Products P ON P.ProductID = O.ProductID ) SELECT Model, [2005], [2004] FROM C PIVOT (SUM(Quantity) FOR [Year] IN ([2005], [2004])) AS PVT |
Результатом выполнения данного кода в демонстрационной базе данных будет таблицу с тремя колонками: Model, 2005 и 2004. Например:
| Model 2005 2004 ----------------------------------- A75-S206 10 24 M40-110 17 38 S215SR 2 10 T2XRP 35 12 V6800V 12 4 |
В предыдущих версиях SQL Server, где не была реализована функция PIVOT и CTE, чтобы достичь требуемого результата, пришлось бы писать код вроде приведенного ниже.
| SELECT C.Model, SUM(CASE C.[Year] WHEN 2005 THEN C.Quantity ELSE 0 END) AS [2005], SUM(CASE C.[Year] WHEN 2004 THEN C.Quantity ELSE 0 END) AS [2004] FROM ( SELECT P.Model, DATEPART(year, O.[Date]) AS [Year], O.Quantity FROM Orders O INNER JOIN Products P ON P.ProductID = O.ProductID ) AS C GROUP BY C.Model |
Читатель, даже неопытный в программировании на T-SQL, наверняка отметит сложность работы с кодом, написанный без использования функции PIVOT, в случае более сложных запросов. В то же время, PIVOT является лишь синтаксической оболочкой для приведенной выше конструкции. Посмотрев планы исполнения примера, приведенного выше и примера, с использованием функции PIVOT, можно убедиться в их идентичности.
Функция UNPIVOT выполняет процедуру обратную PIVOT, «разворачивая» обратно таблицу данных, подвергшихся «обработке» функцией PIVOT , в исходное состояние. Положим, что для ведения статистики имеется следующая таблица
| CREATE TABLE [Statistics]( [Model] [nvarchar](32) NOT NULL, [2005] [int] NOT NULL, [2004] [int] NOT NULL ) |
содержащая данные, полученные в ходе выполнения предыдущего запроса – примера использования функции PIVOT.
| SELECT * FROM [Statistics] UNPIVOT(TotalQuantity FOR [Year] IN ([2005], [2004])) AS PVT |
Результатом выполнения будет таблица трех из столбцов: Model, TotalQuantity, Year.
| Model TotalQuantity Year ------------------------------------------ A75-S206 10 2004 A75-S206 24 2005 M40-110 17 2004 M40-110 38 2005 |
Обновленный оператор TOP
Оператор TOP широко используется для ограничения числа строк, возвращаемых командой SELECT. В предыдущих версиях SQL Server, оператор TOP принимал в качестве параметра только константу. В SQL Server 2005 параметром этого оператора может быть переменная, выражение или вложенный вопрос.
Например, следующим образом можно осуществить выборку такого числа моделей некоторого производителя, которое соответствует среднему количеству моделей каждого производителя в таблице Products:
| SELECT TOP (SELECT AVG(AvgNum) * FROM (SELECT COUNT(*) AS AvgNum FROM Products GROUP BY BrandID) AS NumTable) P.Model FROM Products P |
Для использования в качестве параметра оператора TOP переменной, выражения или вложенного запроса необходимо заключать ее в круглые скобки:
| SELECT TOP (@Num) * FROM ИмяТаблицы |
При использовании константы, скобки не обязательны в команде SELECT, но обязательны при использовании с командами, изменяющими данные, например:
| DELETE TOP(10) FROM Orders ORDER BY Date DESC |
В предыдущих версиях SQL Server для использования переменной в качестве параметра оператора TOP приходилось прибегать к сложным конструкциям или динамическому созданию запроса.
| DECLARE @DynamicQuery varchar(100) SET @DynamicQuery = 'SELECT TOP ' + CAST (@N AS varchar) + ' * FROM Products' EXECUTE(@DynamicQuery) |
Разбиение данных на страницы с использованием оператора TOP
Наиболее простым способом использования оператора TOP для разбиения на страницы является использование смещения по колонке ID. Процедура принимает параметр – количество записей на странице и «последний» ID, полученный в предыдущем запросе. При этом, благодаря новым возможностям оператора TOP, можно варьировать количество записей на странице, используя такую простую процедуру.
| SELECT TOP (@N) * FROM ИмяТаблицы WHERE КолонкаID > @ID |
Более общий подход, не требующий сохранения «последнего» ID предыдущего запроса и формально не зависящий от значения ID выглядит так:
| SELECT * FROM (SELECT TOP (@N * (@PageNum + 1)) * FROM ИмяТаблицы ORDER BY КолонкаID) AS PTable WHERE КолонкаID NOT IN (SELECT TOP (@N * @PageNum) КолонкаID FROM ИмяТаблицы ORDER BY КолонкаID) |
Следует обратить внимание читателя, что запрос будет выполнять свою задачу лишь при условии того, что колонка КолонкаID содержит уникальные значения. Таким образом, для обеспечения разбиения данных с помощью такой процедуры нужно иметь в таблице колонку IDENTITY.
Для того, чтобы подобная процедура работала в более ранних версиях SQL Server, необходимо использовать динамическое создание запроса:
| DECLARE @Query nvarchar(200) SET @Query = 'SELECT * FROM (SELECT TOP ' + CAST(@N * (@PageNum + 1) AS nvarchar) + ' * FROM ИмяТаблицы ORDER BY КолонкаID) AS P WHERE КолонкаID NOT IN (SELECT TOP ' + CAST(@N * @PageNum AS nvarchar) + 'КолонкаID FROM ИмяТаблицы ORDER BY КолонкаID)' EXECUTE(@Query) |
Обработка ошибок в SQL
В связи с интеграцией SQL Server с платформой .NET, языки которой поддерживают гибкий механизм обработки исключений, разработчики SQL Server включили в T-SQL давно желанную SQL-программистами возможность обработки исключений. Текущая реализация в SQL Server 2005 позволяет обрабатывать некритические ошибки с помощью похожего на ставший уже стандартным синтаксис TRY … CATCH.
| BEGIN TRY -- «Опасный» запрос END TRY BEGIN CATCH -- Обработка ошибки END CATCH |
Механизм обработки ошибок в T-SQL, конечно, не такой гибкий, как в .NET языках, но, тем не менее, позволяет сделать достаточно много, анализируя код ошибки, возвращаемый функцией @@ERROR. Например, если таблица Products задана так, что не позволяет хранить отрицательное значение в колонке Quantity:
| CREATE TABLE [Products]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [BrandID] [int] NOT NULL, [Model] [nvarchar](32) NOT NULL, [Configuration] [nvarchar](128) NOT NULL, [Price] [money] NOT NULL, [Quantity] [int] NOT NULL, CHECK ([Quantity] >= 0) ) |
то можно использовать следующий код для изменения количества имеющихся на складе ноутбуков и создания заказа:
| BEGIN TRY BEGIN TRAN -– Создание транзакции INSERT INTO Orders([Date], ProductID, Quantity, [Year] ) VALUES(GETDATE(), @ProductID, @Quantity, @Year) SET @OrderID = @@IDENTITY UPDATE Products2 SET Quantity = Quantity - @Quantity WHERE ProductID = @ProductID UPDATE Orders SET Quantity = @Quantity WHERE OrderID = @OrderID COMMIT –- Если нет ошибок, то подтверждаем транзакцию END TRY BEGIN CATCH DECLARE @Err int SET @Err = @@ERROR ROLLBACK –- Откат транзакции IF @ERR = 547 BEGIN SELECT 'Недостаточно ноутов на складе' AS Error END ELSE BEGIN SELECT 'Неизвестная ошибка' AS Error, @Err AS ErrorNumber END END CATCH |
Заключение
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
