Программирование на T-SQL. Хранимые процедуры. Создание хранимых процедур. Переменные, параметры и коды возврата, страница 7

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 '-------------------- ТОВАРЫ ПОСТАВЩИКОВ --------------------' 

DECLARE vendor_cursor CURSOR FOR 

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

WHILE @@FETCH_STATUS = 0

BEGIN     PRINT ' '

    SELECT @message = '----- Товары поставщика: ' + @vendor_name

PRINT @message

    DECLARE product_cursor CURSOR FOR 

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 '      <<Нет товаров>>'     

    WHILE @@FETCH_STATUS = 0

    BEGIN

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

END 

CLOSE vendor_cursor

DEALLOCATE vendor_cursor

7. Сохраните сценарии и закройте окна запроса.