Читайте также:
|
EXEC usp_01 @path = 'D:\BACKUP\'
GO
Вариант 02
Создать хранимую процедуру, которая, не уничтожая базу данных, уничтожает все те таблицы текущей базы данных в схеме 'dbo', имена которых начинаются с фразы 'TableName'. Созданную хранимую процедуру протестировать.
Рекомендации.
----------------------------
Создать и протестировать хранимую процедуру на T-SQL, которая, не уничтожая текущую базу данных, уничтожает все таблицы в схеме 'dbo', имена которых начинаются с префикса, указываемого параметром процедуры.
CREATE PROCEDURE usp_02 @param VARCHAR(150)
AS
BEGIN
DECLARE @tname VARCHAR(150)
DECLARE @strsql VARCHAR(300)
SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' and [name] like @param+'%' ORDER BY [name])
WHILE @tname IS NOT NULL
BEGIN
SELECT @strsql = 'DROP TABLE [dbo].[' + RTRIM(@tname) + ']';
EXEC (@strsql);
PRINT 'Dropped Table: ' + @tname;
SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' AND [name] LIKE @param+'%' AND [name] > @tname ORDER BY [name]);
END;
END;
GO
Условия тестирования
CREATE TABLE TableNameXXX (c1 int);
GO
CREATE TABLE TableNameYYY (c1 float);
GO
CREATE TABLE TableNameZZZ (c1 char(2));
GO
EXEC usp_02 @param='TableName';
GO
Результаты тестирования
Dropped Table: TableNameXXX
Dropped Table: TableNameYYY
Dropped Table: TableNameZZZ
-----------------
Напишите сценарий, который, не уничтожая базу данных, уничтожает все те таблицы базы данных, имена которых начинаются с фразы 'TableName'. Созданный сценарий протестировать.
USE demodb
GO
DECLARE @tname VARCHAR(150)
DECLARE @strsql VARCHAR(300)
SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' and [name] like N'TableName%' ORDER BY [name])
WHILE @tname IS NOT NULL
BEGIN
SELECT @strsql = 'DROP TABLE [dbo].[' + RTRIM(@tname) + ']'
EXEC (@strsql)
PRINT 'Dropped Table: ' + @tname
SELECT @tname = (SELECT TOP 1 [name] FROM sys.objects WHERE [type] = 'U' AND [name] like N'TableName%' AND [name] > @tname ORDER BY [name])
GO
Условия тестирования
USE demodb
GO
CREATE TABLE TableNameXXX (c1 int)
GO
CREATE TABLE TableNameYYY (c1 float)
GO
CREATE TABLE TableNameZZZ (c1 char(2))
GO
Результаты тестирования
Dropped Table: TableNameXXX
Dropped Table: TableNameYYY
Dropped Table: TableNameZZZ
Вариант 03
Создать хранимую процедуру с входным параметром, которая выводит имена и описания типа объектов (только хранимых процедур и скалярных функций), в тексте которых на языке SQL встречается строка, задаваемая параметром процедуры. Созданную хранимую процедуру протестировать.
Рекомендации.
-------------------
Написать хранимую процедуру с входным параметром, которая выводит имена и описания типа объектов (хранимых процедур и скалярных функций), в тексте которых на языке SQL встречается строка, задаваемая параметром процедуры. Созданную процедуру протестировать.
USE demodb
GO
IF OBJECT_ID('dbo.search_procedure', 'P') IS NOT NULL
DROP PROCEDURE dbo.search_procedure
GO
-- Создание хранимой процедуры
CREATE PROCEDURE dbo.search_procedure @search_text nvarchar(100)
AS
BEGIN
DECLARE @sql_string nvarchar(1000)
SET @sql_string =
'SELECT DISTINCT o.name AS [Имя объекта], o.type_desc AS [Описание типа объекта]
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition LIKE ''%'+@search_text+'%'' AND (o.type=''P'' OR o.type=''FN'')'
EXEC (@sql_string)
END
GO
-- Тестирование хранимой процедуры
EXEC dbo.search_procedure @search_text = N'XML'
Вариант 04
Создать хранимую процедуру с двумя входными параметрами – имя базы данных и имя таблицы, которая выводит сведения об индексах указанной таблицы в указанной базе данных. Созданную хранимую процедуру протестировать.
Рекомендации.
USE demodb;
GO
-- ********************************************************************************
-- Создаем хранимую процедуру получения сведений об индексах
-- ********************************************************************************
IF OBJECT_ID(N'dbo.GetInfoAboutIndex', 'P') IS NOT NULL
DROP PROCEDURE dbo.GetInfoAboutIndex;
GO
CREATE PROCEDURE dbo.GetInfoAboutIndex @dbname nvarchar(50), @tblname nvarchar(50)
AS
BEGIN
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(@dbname);
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
RETURN;
END;
SET @object_id = OBJECT_ID(@dbname+'.'+@tblname, 'U');
IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
RETURN;
END;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL, NULL);
END;
GO
-- ********************************************************************************
-- Проверяем хранимую процедуру на четырех примерах
-- ********************************************************************************
USE demodb;
GO
EXEC GetInfoAboutIndex @dbname=N'AdventureWorks', @tblname=N'Person.Address'
EXEC GetInfoAboutIndex @dbname=N'Northwind', @tblname=N'dbo.Orders'
EXEC GetInfoAboutIndex @dbname=N'Northwint', @tblname=N'dbo.Orders' -- Invalid database
EXEC GetInfoAboutIndex @dbname=N'Northwind', @tblname=N'Orders' -- Invalid object
GO
------------------
Отредактировать (вариант 16)
IF OBJECT_ID ('dbo.Metadata', 'P') IS NOT NULL
DROP PROCEDURE dbo.Metadata
GO
CREATE PROCEDURE dbo.Metadata
@nametable varchar(10)
AS
if(OBJECT_ID(@nametable)is null)
print 'Invalid table!'
else
begin
SELECT sm.object_id AS TableID, OBJECT_NAME(sm.object_id) AS TableName
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(@nametable), null, null, null) as sm
end;
GO
EXEC dbo.Metadata 'dbo.H'
GO
Вариант 05
Создать хранимую процедуру с входным параметром – имя таблицы, которая удаляет дубликаты записей из указанной таблицы в текущей базе данных. Созданную хранимую процедуру протестировать.
Рекомендации.
---------------------
USE master
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'demodb') CREATE DATABASE demodb
GO
USE demodb
GO
IF OBJECT_ID(N'dbo.Employee', 'U') IS NOT NULL DROP TABLE dbo.Employee
GO
CREATE TABLE dbo.Employee
(
Id int NOT NULL,
Name nvarchar(20) NOT NULL,
Salary decimal NOT NULL
);
GO
INSERT INTO dbo.Employee (Id, Name, Salary)
VALUES (1, N'Ram', 1000), (1, N'Ram', 1000), (2, N'Joe', 2000), (2, N'Joe', 1000),
(3, N'Mary', 1000), (4, N'Julie', 5000), (2, N'Joe', 1000), (1, N'Ram', 1000)
GO
/*
Хранимая процедура удаления дубликатов (второго или следующих экземпляров) записей из таблицы Employee
*/
CREATE PROCEDURE DelAllDuplicateRecords
AS
BEGIN
DECLARE @id int, @name varchar (20), @cnt int, @salary numeric
DECLARE GetAllDuplicateRecords CURSOR LOCAL STATIC FOR
SELECT COUNT (*), Id, Name, Salary
FROM dbo.Employee (NOLOCK)
GROUP BY Id, Name, Salary
HAVING COUNT(*)>1
OPEN GetAllDuplicateRecords
FETCH NEXT FROM GetAllDuplicateRecords INTO @cnt, @id, @name, @salary
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = @cnt-1
SET ROWCOUNT @cnt
DELETE FROM Employee WHERE Id=@id and Name=@name and Salary=@salary
SET ROWCOUNT 0
FETCH NEXT FROM GetAllDuplicateRecords INTO @cnt, @id, @name, @salary
END
CLOSE GetAllDuplicateRecords
DEALLOCATE GetAllDuplicateRecords
END
GO
-- ************************************************************
-- Тестирование хранимой процедуры
-- ************************************************************
SELECT Id, Name, Salary FROM dbo.Employee ORDER BY Id
GO
EXEC DelAllDuplicateRecords
GO
SELECT Id, Name, Salary FROM dbo.Employee ORDER BY Id
GO
Дата добавления: 2015-02-16; просмотров: 60 | Поможем написать вашу работу | Нарушение авторских прав |
|