- •Контрольная работа
- •Вариант № 16
- •Задание 16
- •Содержание:
- •3. Создать sql-сценарий, который позволяет сгенерировать базу данных в формате ms sql Server, а также наполнить созданную базу данных конкретными данными.
- •4. Дополнить сценарий текстами перечисленных ниже хранимых процедур:
- •Список использованной литературы:
4. Дополнить сценарий текстами перечисленных ниже хранимых процедур:
Создать хранимую процедуру, которая для указанной таблицы подсчитывает число строк, содержащихся в этой таблице. Эта процедура должна иметь один входной параметр (с помощью которого задается имя таблицы) и один выходной параметр.
На панели инструментов нажмём кнопку New Query, и откроется новое пустое окно Query, предназначенное для формирования нового сценария Script3.sql.
CREATE PROCEDURE Подсчёт_числа_строк_в_таблице
@Имя_табл VARCHAR(20),
@число_строк INT OUTPUT
AS
IF @Имя_табл='Типы_домов'
SELECT @число_строк=COUNT(*)
FROM Типы_домов
IF @Имя_табл='Типы_квартир'
SELECT @число_строк=COUNT(*)
FROM Типы_квартир
IF @Имя_табл='Районы'
SELECT @число_строк=COUNT(*)
FROM Районы
IF @Имя_табл='Заявки_на_покупку'
SELECT @число_строк=COUNT(*)
FROM Заявки_на_покупку
IF @Имя_табл='Заявки_на_продажу'
SELECT @число_строк=COUNT(*)
FROM Заявки_на_продажу
GO
SQL-код для проверки работы созданной хранимой процедуры:
DECLARE @Name VARCHAR(20), @Number INT
SET @Name ='Районы'
EXEC Подсчёт_числа_строк_в_таблице @Name, @Number OUTPUT
SELECT @Number AS Число_строк
GO
Создать хранимую процедуру, которая изменяет все даты, имеющиеся в таблицах базы данных, добавляя к ним или отнимая от них некоторое число дней. Эта процедура должна иметь два входных параметра. Первый параметр определяет, нужно ли увеличивать или уменьшать все даты. Второй параметр указывает количество дней, которые должны добавляться или отниматься.
Примечание. Если в таблицах отсутствуют даты, то аналогичную операцию необходимо проделать применительно ко всем имеющимся ценам и стоимостям. При этом второй параметр будет задавать процент, на который должны измениться все цены и стоимости.
CREATE PROCEDURE Увелич_или_умен_цены_в_проц
@УвеличУменьш VARCHAR(1),
@Процент INT
AS
IF @УвеличУменьш='+'
UPDATE Заявки_на_продажу
SET Цена = Цена*(100+@Процент)/100
ELSE
UPDATE Заявки_на_продажу
SET Цена = Цена*(100-@Процент)/100
IF @УвеличУменьш='-'
UPDATE Заявки_на_покупку
SET ПредельнаяЦена=ПредельнаяЦена*(100-@Процент)/100
ELSE
UPDATE Заявки_на_покупку
SET ПредельнаяЦена=ПредельнаяЦена*(100+@Процент)/100
GO
SQL-код для проверки работы созданной хранимой процедуры:
SELECT Цена FROM Заявки_на_продажу
SELECT ПредельнаяЦена FROM Заявки_на_покупку
DECLARE @Percent INT, @УвелУмен VARCHAR(1)
SET @Percent =5
SET @УвелУмен='+'
EXEC Увелич_или_умен_цены_в_проц @УвелУмен, @Percent
SELECT Цена FROM Заявки_на_продажу
SELECT ПредельнаяЦена FROM Заявки_на_покупку
GO
Создать хранимую процедуру, которая для конкретной таблицы подсчитывает минимальное, среднее, максимальное и суммарное значения в столбце числового типа. Эта процедура должна иметь один входной параметр (с помощью которого указывается имя или номер столбца) и четыре выходных параметра.
CREATE PROCEDURE Мин_Среднее_Макс_Сумм
@Имя_столбца VARCHAR(20)='Цена',
@мин INT OUTPUT,
@средн INT OUTPUT,
@макс INT OUTPUT,
@сумм INT OUTPUT
AS
IF @Имя_столбца='НомерЗаявкиНаПрод'
SELECT @мин=MIN(НомерЗаявкиНаПрод), @средн=AVG(НомерЗаявкиНаПрод), @макс=MAX(НомерЗаявкиНаПрод), @сумм=SUM(НомерЗаявкиНаПрод)
FROM Заявки_на_продажу
IF @Имя_столбца='Цена'
SELECT @мин=MIN(Цена), @средн=AVG(Цена), @макс=MAX(Цена), @сумм=SUM(Цена)
FROM Заявки_на_продажу
IF @Имя_столбца='НомерКвартиры'
SELECT @мин=MIN(НомерКвартиры), @средн=AVG(НомерКвартиры), @макс=MAX(НомерКвартиры), @сумм=SUM(НомерКвартиры)
FROM Заявки_на_продажу
IF @Имя_столбца='НомерЭтажа'
SELECT @мин=MIN(НомерЭтажа), @средн=AVG(НомерЭтажа), @макс=MAX(НомерЭтажа), @сумм=SUM(НомерЭтажа)
FROM Заявки_на_продажу
GO
SQL-код для проверки работы созданной хранимой процедуры:
DECLARE @ИмяСтолбца VARCHAR(20), @min INT, @avg INT, @max INT,
@sum INT
SET @ИмяСтолбца ='НомерЭтажа'
EXEC Мин_Среднее_Макс_Сумм @ИмяСтолбца, @min OUTPUT, @avg OUTPUT, @max OUTPUT, @sum OUTPUT
SELECT @min AS Минимальное, @avg AS Среднее, @max AS Максимальное, @sum AS Суммарное
GO
DECLARE @ИмяСтолбца VARCHAR(20), @min INT, @avg INT, @max INT, @sum INT
SET @ИмяСтолбца ='Цена'
EXEC Мин_Среднее_Макс_Сумм @ИмяСтолбца, @min OUTPUT, @avg OUTPUT, @max OUTPUT, @sum OUTPUT
SELECT @min AS Минимальное, @avg AS Среднее, @max AS Максимальное, @sum AS Суммарное
GO
Создать хранимую процедуру, которая из конкретной таблицы отбирает строки по условию, налагаемому на значения конкретных двух столбцов. Эта процедура должна иметь два входных параметра, задающих значения для отбора данных по каждому из этих столбцов. При этом значение NULL должно трактоваться как отсутствие какого-либо критерия отбора строк по данному столбцу (например, если оба параметра будут иметь значение NULL, то должны быть выбраны все строки таблицы).
CREATE PROCEDURE Отбор_строк_по_знач_2_столбц
@Условие_для_ТипДома VARCHAR(20),
@Условие_для_Этажность INT
AS
IF @Условие_для_ТипДома IS NOT NULL
BEGIN
IF @Условие_для_Этажность IS NOT NULL
SELECT *
FROM Типы_домов
WHERE ТипДома=@Условие_для_ТипДома AND Этажность<=@Условие_для_Этажность
ELSE
SELECT *
FROM Типы_домов
WHERE ТипДома=@Условие_для_ТипДома
END
ELSE
IF @Условие_для_Этажность IS NOT NULL
SELECT *
FROM Типы_домов
WHERE Этажность<=@Условие_для_Этажность
IF @Условие_для_ТипДома IS NULL AND @Условие_для_Этажность IS NULL
SELECT *
FROM Типы_домов
GO
SQL-код для проверки работы созданной хранимой процедуры:
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT
SET @Условие_для_ТипДома ='Кирпичный'
SET @Условие_для_Этажность=9
EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность
GO
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT
SET @Условие_для_ТипДома ='Блочный'
SET @Условие_для_Этажность=5
EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность
GO
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT
SET @Условие_для_ТипДома =NULL
SET @Условие_для_Этажность=5
EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность
GO
DECLARE @Условие_для_ТипДома VARCHAR(20), @Условие_для_Этажность INT
SET @Условие_для_ТипДома =NULL
SET @Условие_для_Этажность=NULL
EXEC Отбор_строк_по_знач_2_столбц @Условие_для_ТипДома, @Условие_для_Этажность
GO
Создать хранимую процедуру, которая на основе текстовой строки, содержащей фамилию, имя и отчество, формирует текстовую строку, содержащую фамилию и инициалы, и при этом все строчные буквы должны быть заменены прописными. Эта процедура должна иметь один входной параметр (с помощью которого задается исходная текстовая строка) и один выходной параметр.
При этом необходимо расширить возможности процедуры таким образом, чтобы была допустима исходная строка (задаваемая входным параметром), содержащая не один, а несколько пробелов между фамилией и именем или между именем и отчеством, а также допускающая наличие лидирующих пробелов перед фамилией.
CREATE PROCEDURE Преобразование_текс_строки
@Строка1 VARCHAR(50),
@Строка2 VARCHAR(30) OUTPUT
AS
DECLARE @Ind1 INT, @Фамилия VARCHAR(30), @И VARCHAR(1), @О VARCHAR(1), @Строка VARCHAR(30), @ИмяОтчество VARCHAR(40), @Ind2 INT, @Отчество VARCHAR(20)
SET @Строка1 = LTRIM(@Строка1)--удаляет лидирующие пробелы
SET @Ind1=CHARINDEX(' ', @Строка1)--первый пробел в строке
SET @Фамилия=LEFT(@Строка1, @Ind1-1) --возвращает крайние левые символы из строки
SET @ИмяОтчество=SUBSTRING(@Строка1, @Ind1, LEN(@Строка1)-@Ind1+1)
--извлекает LEN(@Строка1)-@Ind1+1 из строки начиная с позиции @Ind1
SET @ИмяОтчество=LTRIM(@ИмяОтчество)
SET @Ind2=CHARINDEX(' ', @ИмяОтчество)
SET @И=LEFT(@ИмяОтчество,1)
SET @Отчество=
SUBSTRING(@ИмяОтчество, @Ind2, LEN(@ИмяОтчество)-@Ind2+1)
SET @Отчество=LTRIM(@Отчество)
SET @О=LEFT(@Отчество,1)
SET @Строка=@Фамилия+' '+@И+'.'+@О+'.'
SET @Строка2=LOWER(@Строка)--преобразует все символы строки в символы нижнего регистра
GO
SQL-код для проверки работы созданной хранимой процедуры:
DECLARE @Строка_1 VARCHAR(50), @Строка_2 VARCHAR(30)
SET @Строка_1 ='Писарчик Екатерина Викторовна'
EXEC Преобразование_текс_строки @Строка_1, @Строка_2 OUTPUT
SELECT @Строка_2 AS Результат
GO
DECLARE @Строка_1 VARCHAR(50), @Строка_2 VARCHAR(30)
SET @Строка_1 =' Писарчик Екатерина Викторовна'
EXEC Преобразование_текс_строки @Строка_1, @Строка_2 OUTPUT
SELECT @Строка_2 AS Результат
GO
5. Создать в базе данных таблицу Протокол со структурой, приведенной ниже.
-
Номер
ДатаВремя
Пользователь
Действие
ЧислоСтрок
. . .
. . .
. . .
. . .
. . .
Здесь столбец Номер является автоинкрементным первичным ключом. В столбце Действие будет указываться одна из трех возможных операций с данными: «Вставка», «Обновление», «Удаление». Столбец ЧислоСтрок будет содержать данные о числе вставленных, либо обновленных, либо удаленных строк.
CREATE TABLE Протокол
(
Номер INT IDENTITY(1,1)PRIMARY KEY,
ДатаВремя DATETIME NOT NULL,
Пользователь VARCHAR(20) NULL,
Действие VARCHAR(20) NULL,
ЧислоСтрок INT NOT NULL
)
6. Создать триггер, связанный с конкретной таблицей базы данных, который предназначен для автоматической фиксации в таблице Протокол всех действий, связанных со вставкой, обновлением или удалением данных в таблице, снабженной триггером. Каждая SQL-команда, изменяющая содержимое этой таблицы, должна быть отражена отдельной строкой в таблице Протокол.
CREATE TRIGGER tr_Триггер1
ON Заявки_на_продажу
FOR INSERT
AS
INSERT INTO Протокол
VALUES (getdate(), suser_sname(), 'Вставка', @@ROWCOUNT)
GO
CREATE TRIGGER tr_Триггер2
ON Заявки_на_продажу
FOR UPDATE
AS
INSERT INTO Протокол
VALUES (getdate(), suser_sname(), 'Обновление', @@ROWCOUNT)
GO
CREATE TRIGGER tr_Триггер3
ON Заявки_на_продажу
FOR DELETE
AS
INSERT INTO Протокол
VALUES (getdate(), suser_sname(), 'Удаление', @@ROWCOUNT)
GO
SQL-код для проверки работы созданного триггера:
SELECT *
FROM Протокол
INSERT INTO Заявки_на_продажу --добавить строку
VALUES (120000, 'Зелёная', '23', 120, '74м2','59м2',10,'Нет',NULL,2)
UPDATE Заявки_на_продажу --обновить все строки
SET Цена=Цена/2
DELETE --удалить строки
FROM Заявки_на_продажу
WHERE КодТипаКвартиры=2
SELECT *
FROM Протокол
GO