|
Читайте также: |
USE demodb
GO
IF OBJECT_ID (N'dbo.DROP_VIEWS', N'P') IS NOT NULL
DROP PROCEDURE dbo.DROP_VIEWS;
GO
-- ****************************************************************************************
-- Хранимая процедура удаления всех представлений в схеме 'dbo', не начинающихся с 'vw_'
-- ****************************************************************************************
CREATE PROCEDURE dbo.DROP_VIEWS @cnt int OUTPUT
AS
BEGIN
DECLARE @view_name nvarchar(128), @SQLString nvarchar(1000);
SET @cnt = 0;
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type='V'
AND SCHEMA_NAME(schema_id) = 'dbo')
RETURN;
DECLARE view_list CURSOR FORWARD_ONLY STATIC FOR
SELECT name FROM sys.objects WHERE type = 'V'
AND SCHEMA_NAME(schema_id) = 'dbo'
AND name NOT LIKE 'vw_%';
OPEN view_list
FETCH NEXT FROM view_list INTO @view_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = @cnt+1;
SET @SQLString = N'IF OBJECT_ID(N''[dbo].['+@view_name+']'', ''V'') IS NOT NULL
DROP VIEW [dbo].['+@view_name+'];'
EXEC (@SQLString);
FETCH NEXT FROM view_list INTO @view_name;
END
CLOSE view_list;
DEALLOCATE view_list;
END
GO
-- *******************************************************************************
-- Тестирование процедуры удаления представлений
-- *******************************************************************************
CREATE VIEW dbo.vw_1
AS
SELECT 'Hello world!' AS [Hello world!]
GO
CREATE VIEW dbo.vv_2
AS
SELECT 'Test' AS [Test]
GO
CREATE VIEW dbo.vv_3
AS
SELECT 'No' AS [No]
GO
DECLARE @cnt int
EXEC dbo.DROP_VIEWS @cnt OUTPUT
SELECT @cnt AS Number
GO
Вариант 27
Во временной базе данных tempdb создать две однотипных трехстолбцовых таблицы с ограничениями PRIMARY KEY, UNIQUE и CHECK. Затем создать и протестировать SQL DML триггер INSTEAD OF на событие INSERT для первой таблицы, который делает попытку добавить строки, удовлетворяющие ограничению CHECK в первую таблицу, а не удовлетворяющие ограничению CHECK во вторую таблицу. При возникновении исключения, если число инструкций BEGIN TRANSACTION, выполненных в текущем соединении больше нуля, выполнить откат транзакции.
----------------------------
USE Northwind
GO
IF OBJECT_ID ('dbo.Product$Insert', 'P') IS NOT NULL
DROP PROCEDURE dbo.Product$Insert;
GO
CREATE PROCEDURE dbo.Product$Insert
(
@product_name nvarchar(40), -- имя добавляемого продукта
@new_product_id int = null OUTPUT -- последнее значение, вставленного первичного ключа
)
AS
BEGIN
SET NOCOUNT ON
-- создаем уникальное имя точки сохранения, обрезая его до 125 символов, если оно слишком большое
DECLARE @savepoint nvarchar(128) = CAST(OBJECT_NAME(@@PROCID) AS nvarchar(125)) + -- имя текущей хранимой процедуры
CAST(@@NESTLEVEL AS nvarchar(3)) -- уровень вложенности текущей хранимой процедуры
-- запоминаем число инструкций BEGIN TRANSACTION, выполненных в текущем соединении
DECLARE @entry_trancount int = @@TRANCOUNT
BEGIN TRY
BEGIN TRANSACTION -- Отмечает начало явной транзакции
SAVE TRANSACTION @savepoint -- Устанавливает точку сохранения внутри транзакции
INSERT INTO dbo.Products(ProductName) VALUES (@product_name) -- Добавляет одну строку в таблицу Products
SET @new_product_id = SCOPE_IDENTITY() -- Возвращает последнее значение первичного ключа
COMMIT TRANSACTION -- Отмечает успешное завершение транзакции
RETURN 0
END TRY
BEGIN CATCH
SELECT 'In error handler'
-- если в текущем запросе есть активная транзакция, однако произошла ошибка, из-за которой транзакция классифицируется как нефиксируемаяа
-- и число инструкций BEGIN TRANSACTION, выполненных в текущем соединении равно 0, то ROLLBACK TRANSACTION
IF XACT_STATE()= -1 and @entry_trancount = 0
BEGIN
SELECT 'Transaction Doomed'
ROLLBACK TRANSACTION -- Откатывает транзакцию до начала
END
-- иначе если текущий запрос содержит активную пользовательскую транзакциюo, то ROLLBACK TRANSACTION к точке сохранения
ELSE IF XACT_STATE() = 1
BEGIN
SELECT 'Savepoint Rollback'
ROLLBACK TRANSACTION @savepoint -- Откатывает транзакцию до точки сохранения транзакции
COMMIT TRANSACTION -- Отмечает успешное завершение транзакции
END
-- Создается сообщение об ошибке и запускается обработка ошибок для сеанса с помощью инструкции RAISERROR
DECLARE @msg nvarchar(1000)
SET @msg = 'Error Occurred in procedure ''' + OBJECT_NAME(@@PROCID) + ''', Original Message: ''' + ERROR_MESSAGE() + ''''
RAISERROR (@msg, 16, 1)
RETURN -100
END CATCH
END
GO
-- Тестирование созданной процедуры
DECLARE @out int
DECLARE @ret int
EXEC @ret = dbo.Product$insert N'QWERTY', @out OUTPUT
IF @ret = 0 SELECT @out
GO
Вариант 28
Создать и протестировать хранимую процедуру на T-SQL, которая создает и заполняет временную таблицу #Numbers(Number int NOT NULL, Symbol nchar(1)). Столбец Number содержит номер ASCII-символа, а столбец Symbol его изображение. Начинать надо с символа, задаваемого параметром процедуры, а длина последовательности определяется вторым параметром процедуры.
Вариант 29
Создать и протестировать табличную функцию на T-SQL, которая для символьной строки, указываемой параметром функции и содержащая последовательность целых чисел, разделенных запятыми, возвращает одностолбцовую таблицу, содержащую числа.
Вариант 30
Написать и протестировать хранимую процедуру, которая в XML-документе, передаваемом в качестве параметра процедуры, выполняет следующие модификации: добавляет элемент <WarehouseID>77</WarehouseID>, удаляет элемент <Price>15</Price>, изменяет значение элемента <QuantityOnHand>500</QuantityOnHand>.
Дата добавления: 2015-02-16; просмотров: 70 | Поможем написать вашу работу | Нарушение авторских прав |