|
Читайте также: |
USE Northwind
GO
-- **********************************************************************
-- Создаем функцию уровней подчинения без ограничения на уровень
-- **********************************************************************
CREATE FUNCTION dbo.Subordinates(@root int)
RETURNS @Subs Table (empid int NOT NULL PRIMARY KEY, lvl int NOT NULL)
AS
BEGIN
DECLARE @lvl int = 0; -- Initialize level counter with 0
INSERT INTO @Subs(empid, lvl) -- Insert root node to @Subs
SELECT EmployeeID, @lvl FROM dbo.Employees WHERE EmployeeID = @root;
WHILE @@rowcount > 0 -- while previous level had rows
BEGIN
SET @lvl = @lvl + 1; -- Increment level counter
INSERT INTO @Subs(empid, lvl) -- Insert next level of subordinates to @Subs
SELECT C.EmployeeID, @lvl
FROM @Subs AS P /* P = Parent */ JOIN dbo.Employees AS C /* C = Child */
ON P.lvl = @lvl - 1 /* Filter parents from previous level */
AND C.ReportsTo = P.empid;
END
RETURN;
END
GO
-- ************************************************************
-- Node ids of descendants of a given node
-- ************************************************************
SELECT empid, lvl FROM dbo.Subordinates(5)
GO
Вариант 05
Создать и протестировать скалярную функцию на T-SQL, которая для строки, указываемой параметром функции, возвращает строку, удаляя все однострочные комментарии T-SQL. При тестировании удалить все однострочные комментарии в процедурах, имена которых начинаются с префикса 'usp_'.
-------------------------------
Создать и протестировать скалярную функцию на T-SQL, которая для символьной строки, указываемой параметром функции, возвращает строку, удаляя все многострочные комментарии T-SQL. Протестировать функцию на примере процедур, имена которых начинаются с префикса 'usp_'.
-------------------
Отредактировать! (вариант 23)
ProblemForExamSQLQuery23.sql - Solution
-- ****************************************************************************************
-- Удалить все многострочные комментарии в процедурах, имена которых не начинаются с 'usp_'
-- ****************************************************************************************
USE demodb
GO
IF OBJECT_ID(N'dbo.DelMultiLineComments') IS NOT NULL
DROP FUNCTION dbo.DelMultiLineComments;
GO
CREATE FUNCTION dbo.DelMultiLineComments (@Input nvarchar(4000))
RETURNS nvarchar(4000)
BEGIN
DECLARE @Output nvarchar(4000)
DECLARE @i int
IF @Input NOT LIKE '%/*%*/%'
BEGIN
SET @Output = REPLACE(@Input, CHAR(10)+CHAR(13), '')
RETURN @Output
END
ELSE
BEGIN
SET @Input = @Input + char(13)
SET @i = 1
WHILE @i <= 5
BEGIN
IF charindex('/*', @Input) > 0 and charindex('*/', @Input, charindex('/*', @Input)) -
charindex('/*',@Input) + 2 > 0
BEGIN
SET @Input = REPLACE(@Input,
substring(@Input,
charindex('/*',@Input),
charindex('*/',@Input, charindex('/*',@Input)) - charindex('/*',@Input) + 2)
, '')
END
SET @i = @i+1
END
SET @Output = dbo.DelMultiLineComments (@Input)
END
RETURN @Output
END
GO
-- ************************************************************************************
-- Тестирование хранимой процедуры
-- ************************************************************************************
USE demodb
GO
SELECT o.object_id AS ID, o.name AS ProcName, dbo.DelMultiLineComments (m.definition) AS ProcText
INTO #T1
FROM sys.objects o INNER JOIN sys.sql_modules m on o.object_id = m.object_id
WHERE o.type = 'P' AND o.name NOT LIKE 'usp_%'
GO
SELECT * FROM #T1
GO
DROP TABLE #T1
GO
Вариант 06
Создать и протестировать хранимую процедуру на T-SQL, которая для базы данных, указываемой параметром процедуры, выводит сведения об индексах таблицы, также указываемой параметром процедуры. Если имя базы данных и/или имя таблицы заданы неверно, то вывести соответствующие сообщения.
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 из SQL 1,)
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
Вариант 07
Создать и протестировать многооператорную табличную функцию на T-SQL, которая для символьной строки, указываемой параметром функции, и для символа-разделителя, также указываемого параметром функции, возвращает одностолбцовую таблицу, содержащую строки, расположенные между символами-разделителями.
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
Вариант 08
Создать и протестировать скалярную функцию на T-SQL, которая проверяет правильность символьной строки, указываемой параметром функции. Символьная строка считается правильной, если она состоит из букв английского алфавита, цифр и символа нижнего подчеркивания. В случае успеха функция возвращает 0, в случае неудачи – количество запрещенных символов.
create function Var08 (@temp varchar(1000))
returns int
as
begin
declare @NumRange as varchar(50) = '%[0-9a-zA-Z_]%'
while PatIndex(@NumRange, @Temp) > 0
set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')
return LEN (@temp)
end
go
Вариант 09
В базе данных Northwind создать и протестировать однооператорную табличную функцию, которая возвращает сведения о продуктах (ID продукта, максимальная цена, средняя цена, минимальная цена и название продукта) для всех продуктов категории, указываемой параметром функции.
Дата добавления: 2015-02-16; просмотров: 81 | Поможем написать вашу работу | Нарушение авторских прав |