v SCROLLLOCKS— запрашивается блокировка при считывании каждой строк; в курсор, гарантируя успешное выполнение транзакции, работающей с курсором;
v OPTIMISTIC — блокировка не запрашивается. Вместо этого SQL Server использует либо метку времени (timestamp), либо в случае отсутствия столбца с меткой времени вычисляемую контрольную сумму для того, чтобы определить, изменялись ли данные после того, как были считаны в курсор. Если данные были изменены, модификация завершается аварийно.
ПРАКТИЧЕСКОЕ ЗАНЯТИЕ 2.
Создание хранимой процедуры
Упражнение. Создание хранимых процедур
Создать две хранимые процедуры, чтобы сравнить эффективность обработки в подпрограмме, ориентированной на наборы данных, и в подпрограмме, основанной на курсоре.
1. Откройте среду SSMS и установите соединение с экземпляром сервера SQL Server.
2. Откройте окно создания запроса и измените настройки на использование базы данных WorksDB.
3. В существующем окне запроса введите, выделите и выполните следующий программный код, создающий основанную на курсоре хранимую процедуру, которая изменяет дату приема на работу у всех сотрудников.
USE WorksDB
GO
CREATE PROCEDURE HumanResources.UpdateAll
AS
BEGIN TRY
SET XACT_ABORT ON
DECLARE curemployee CURSOR FOR SELECT EmployeeID FROM HumanResources.Employee
OPEN curemployee
FETCH FROM curemployee
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE HumanResources.Employee
SET HireDate = GETDATE()ё
WHERE CURRENT OF curemployee
FETCH FROM curemployee
END
CLOSE curemployee
DEALLOCATE curemployee
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT '------ Ошибка: откат транзакции' END CATCH
GO
4. В существующем окне запроса введите, выделите и выполните следующий программный код для создания хранимой процедуры, изменяющий дату приема на работу у всех сотрудников, используя подход, основанный на множествах.
USE WorksDB
GO
CREATE PROCEDURE HumanResources.UpdateAllEmployee
AS
DECLARE @now DATETIME = GETDATE()
BEGIN TRY
SET XACT_ABORT ON
UPDATE HumanResources.Employee SET HireDate = @now
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT '----- Ошибка: откат транзакции' END CATCH
GO
5. Сравните выводимые сообщения в результате выполнения следующего программного кода.
EXEC HumanResources.UpdateAll
GO
EXEC HumanResources.UpdateAllEmployee
GO
6. В новом окне запроса введите и выполните следующий программный код для вывода в качестве сообщения сложного отчета. Для каждого поставщика объявляется внутренний курсор.
USE WorksDB
GO
CREATE PROCEDURE Purchasing.VendorReport
AS
DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)
PRINT '-------------------- ТОВАРЫ ПОСТАВЩИКОВ --------------------'
SELECT VendorID, Name FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1 ORDER BY VendorID
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name
SELECT @message = '----- Товары поставщика: ' + @vendor_name
PRINT @message
SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
IF @@FETCH_STATUS <> 0
PRINT ' <<Нет товаров>>'
SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product END
CLOSE product_cursor
DEALLOCATE product_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
7. Сохраните сценарии и закройте окна запроса.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.