SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Films ( FilmID INT IDENTITY(0,1) PRIMARY KEY, FilmName NVARCHAR(50) NOT NULL, FilmGenre TINYINT DEFAULT 0, CONSTRAINT CK_FilmGenre CHECK (FilmGenre IN (0, 1, 2)), CreatedDate DATE NOT NULL, Language TINYINT DEFAULT 0, CONSTRAINT CK_Language CHECK (Language IN (0, 1, 2, 3)), FullName AS FilmName + ' ' + CONVERT(NVARCHAR, CreatedDate) ); INSERT INTO Films VALUES (N'Прибытие поезда на вокзал города Ла-Сьота', 2, '1896-01-06', 3), (N'Огни большого города', 2, '1931-01-30', 1), (N'Броненосец "Потёмкин"', 2, '1925-12-24', 0), (N'Проверка на дорогах', 2, '1971-03-16', 0), (N'Солярис', 2, '1972-02-5', 0), (N'Добро пожаловать, или Посторонним вход воспрещён', 2, '1964-10-09', 0), (N'Четыреста ударов', 2, '1959-05-04', 2), (N'Не Горюй', 2, '1969-12-22', 0); SELECT * FROM Films; ------------------Задание 1---------------------------------------------- -- Создать хранимую процедуру, производящую выборку из некоторой таблицы -- и возвращающую результат выборки в виде курсора. ------------------------------------------------------------------------- IF OBJECT_ID(N'dbo.film_cursor', N'P') IS NOT NULL DROP PROCEDURE dbo.film_cursor; GO CREATE PROCEDURE dbo.film_cursor @film_cursor CURSOR VARYING OUTPUT AS SET @film_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT FilmID, FullName FROM dbo.Films; OPEN @film_cursor; GO DECLARE @film_cursor CURSOR; EXEC dbo.film_cursor @film_cursor = @film_cursor OUTPUT; DECLARE @FilmID INT, @FullName NVARCHAR(81); FETCH NEXT FROM @film_cursor INTO @FilmID, @FullName; WHILE (@@FETCH_STATUS = 0) BEGIN PRINT 'Film ID: ' + @FilmID + ', Full name of the film: ' + @FullName; FETCH NEXT FROM @film_cursor INTO @FilmID, @FullName; END; CLOSE @film_cursor; DEALLOCATE @film_cursor; GO -------------------------Задание 2----------------------------- --Модифицировать хранимую процедуру п.1. таким образом, чтобы --выборка осуществлялась с формированием столбца, значение --которого формируется пользовательской функцией. --------------------------------------------------------------- --Пользовательская функция вычисления возраста фильма IF OBJECT_ID(N'dbo.GetFilmAge', N'FN') IS NOT NULL DROP FUNCTION dbo.GetFilmAge; GO CREATE FUNCTION dbo.GetFilmAge(@CreatedDate DATE) RETURNS INT AS BEGIN DECLARE @CurrentYear INT; DECLARE @FilmAge INT; SET @CurrentYear = YEAR(GETDATE()); SET @FilmAge = @CurrentYear - YEAR(@CreatedDate); RETURN @FilmAge; END; GO IF OBJECT_ID(N'dbo.usp_get_film_age', N'P') IS NOT NULL DROP PROCEDURE dbo.usp_get_film_age; GO CREATE PROCEDURE dbo.usp_get_film_age @film_age_cursor CURSOR VARYING OUTPUT AS SET NOCOUNT ON; SET @film_age_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT FilmName, dbo.GetFilmAge(CreatedDate) AS FilmAge FROM Films; OPEN @film_age_cursor; GO DECLARE @film_cursor CURSOR; EXEC dbo.usp_get_film_age @film_age_cursor = @film_cursor OUTPUT; DECLARE @FilmID INT, @FullName NVARCHAR(81); WHILE (@@FETCH_STATUS = 0) BEGIN PRINT 'Film ID: ' + @FilmID + ', Full name of the film: ' + @FullName; FETCH NEXT FROM @film_cursor INTO @FilmID, @FullName; END; CLOSE @film_cursor; DEALLOCATE @film_cursor; GO -------------------------Задание 3--------------------------------------- --Создать хранимую процедуру, вызывающую процедуру п.1., осуществляющую --прокрутку возвращаемого курсора и выводящую сообщения, сформированные --из записей при выполнении условия, заданного еще одной --пользовательской функцией. ------------------------------------------------------------------------- --Пользовательская функция для проверки на первую русскую гласную букву IF OBJECT_ID(N'dbo.DoesStartWithRusVowel ', N'FN') IS NOT NULL DROP FUNCTION dbo.DoesStartWithRusVowel ; GO CREATE FUNCTION dbo.DoesStartWithRusVowel (@FullName NVARCHAR(81)) RETURNS BIT AS BEGIN DECLARE @FirstChar NCHAR(1) = SUBSTRING(@FullName, 1, 1); DECLARE @LowerFirstChar NCHAR(1) = @FirstChar; IF @LowerFirstChar IN (N'А', N'О', N'Е', N'Ё', N'И', N'Ы', N'Э', N'Я', N'У', N'Ю') RETURN 1 ELSE RETURN 0; END; GO IF OBJECT_ID(N'dbo.usp_scrolling', N'P') is not null DROP PROCEDURE dbo.usp_scrolling GO CREATE PROCEDURE dbo.usp_scrolling AS DECLARE @scrolling_cursor CURSOR; EXEC dbo.currency_cursor @film_cursor = @scrolling_cursor OUTPUT; DECLARE @FilmID INT, @FullName NVARCHAR(81); FETCH NEXT FROM @scrolling_cursor INTO @FilmID, @FullName; WHILE (@@FETCH_STATUS = 0) BEGIN IF dbo.DoesStartWithRusVowel(@FullName) = 1 PRINT 'Film ID: ' + @FilmID + ', Full name of the film: ' + @FullName; FETCH NEXT FROM @film_cursor INTO @FilmID, @FullName; END; CLOSE @scrolling_cursor; DEALLOCATE @scrolling_cursor; GO EXEC dbo.usp_scrolling GO -------------------Задание 4----------------------------------- --Модифицировать хранимую процедуру п.2. таким образом, чтобы --выборка формировалась с помощью табличной функции. --------------------------------------------------------------- --Табличная функция IF OBJECT_ID(N'dbo.GetFilmInfo', N'IF') IS NOT NULL DROP FUNCTION dbo.GetFilmInfo; GO CREATE FUNCTION dbo.GetFilmInfo() RETURNS TABLE AS RETURN ( SELECT FilmName,dbo.GetFilmAge(YEAR(CreatedDate)) AS FilmAge FROM Films WHERE dbo.DoesStartWithRusVowel(FilmName) = 1 ); GO IF OBJECT_ID(N'dbo.usp_table_function', N'P') IS NOT NULL DROP PROCEDURE dbo.usp_table_function; GO CREATE PROCEDURE dbo.usp_table_function @table_cursor CURSOR VARYING OUTPUT AS BEGIN SET NOCOUNT ON; SET @table_cursor = CURSOR SCROLL STATIC FOR SELECT FilmName, FilmAge FROM dbo.GetFilmInfo(); OPEN @table_cursor; END; GO DECLARE @film_cursor CURSOR; EXEC dbo.usp_table_function @table_cursor = @film_cursor OUTPUT; DECLARE @FilmID INT, @FullName NVARCHAR(81); FETCH NEXT FROM @film_cursor INTO @FilmID, @FullName; WHILE (@@FETCH_STATUS = 0) BEGIN PRINT 'Film ID: ' + @FilmID + ', Full name of the film: ' + @FullName; FETCH NEXT FROM @film_cursor INTO @FilmID, @FullName; END; CLOSE @film_cursor; DEALLOCATE @film_cursor; GO