|
Читайте также: |
USE Northwind
GO
IF OBJECT_ID(N'dbo.GetInfoAboutProducts', 'IF') IS NOT NULL
DROP FUNCTION dbo.GetInfoAboutProducts;
GO
-- ****************************************************************************************
-- Получить сведения о продуктах (ID продукта, целевая цена, средняя цена, минимальная цена -- и название продукта) для всех продуктов категории @CategoryID
-- ****************************************************************************************
CREATE FUNCTION dbo.GetInfoAboutProducts (@CategoryID int) RETURNS TABLE
AS
RETURN (
SELECT ProductID, UnitPrice,
(
SELECT AVG(UnitPrice)
FROM [Order Details]
WHERE [Order Details].ProductID = Products.ProductID
) AS AvgPrice,
(
SELECT MIN(UnitPrice)
FROM [Order Details]
WHERE [Order Details].ProductID = Products.ProductID
) AS MaxPrice, ProductName
FROM Products
WHERE CategoryID = @CategoryID
)
GO
-- ****************************************************************************************
-- Тестируем функцию
-- ****************************************************************************************
USE Northwind
GO
SELECT * FROM dbo.GetInfoAboutProducts(8)
GO
Вариант 10
Во временной базе данных tempdb создать таблицу, содержащую следующие поля: имя базы данных, тип объекта, владелец объекта, имя объекта, текущая дата. Затем создать и протестировать хранимую процедуру, которая сохраняет сведения об объектах пользовательских баз данных (таблицах, представлениях, процедурах и функциях), не принадлежащих владельцам баз данных, в предварительно созданной таблице. Тип объекта должен быть представлен словами: 'Table', 'View', 'Procedure', 'Function'.
Вариант 11
Создать и протестировать хранимую процедуру на T-SQL, которая обновляет статистики для всех таблиц в схеме 'dbo' в текущей базе данных.
----------------------
(Юра)
ALTER PROCEDURE dbo.UpdateStatistics AS
BEGIN
UPDATE STATISTICS dbo.attendance;
UPDATE STATISTICS dbo.students;
UPDATE STATISTICS dbo.subjects;
UPDATE STATISTICS dbo.rooms;
END;
GO
EXEC dbo.UpdateStatistics;
GO
---------------------
/*
Написать хранимую процедуру без параметров, которая в произвольной базе данных, например, Northwind, обновляет все статистики для таблиц в схеме dbo.
*/
USE Northwind
GO
ALTER PROCEDURE UPDATE_STATISTICS
AS
BEGIN
DECLARE @tablename varchar(255), @tablename_header varchar(600), @sql varchar(600)
DECLARE tnames_cursor CURSOR FOR
SELECT 'tablename'=o.name
FROM sys.objects o
WHERE o.type = 'U' AND o.principal_id IS NULL
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @tablename_header = '*** Updating '+rtrim(upper(@tablename))+'('+convert(varchar, getdate(), 20) + ') ***'
PRINT @tablename_header
SELECT @sql = 'UPDATE STATISTICS '+'dbo.'+@tablename
EXEC (@sql)
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT '*** Statistics has been updated for all tables ('+convert(varchar,getdate(),20) + ') ***'
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
END
GO
EXEC UPDATE_STATISTICS
GO
Вариант 12
Создать и протестировать хранимую процедуру на T-SQL, которая в созданной вами таблице, допускающей дубликаты записей, удаляет дубликаты.
---------------------
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; просмотров: 76 | Поможем написать вашу работу | Нарушение авторских прав |