Лаба4
.docxМинистерство образования Республики Беларусь
Учреждение образования
БЕЛОРУССКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
ИНФОРМАТИКИ И РАДИОЭЛЕКТРОНИКИ
Кафедра экономической информатики
ЛАБОРАТОРНАЯ РАБОТА №4
ОСНОВЫ ПРОГРАММИРОВАНИЯ С ПОМОЩЬЮ ВСТРОЕННОГО ЯЗЫКА T-SQL
Вариант 13
Выполнил: студент гр. 972303
Рушева М.В.
Проверил:
Минск, 2022
1 запрос для создания временной таблицы через переменную типа TABLE
select agreementId, sum(price) as total_price
into #AgreementSummary
from Agreement
group by agreementId
select * from Agreement
join #AgreementSummary on Agreement.agreementId= #AgreementSummary.agreementId
2 запроса с использованием условной конструкции IF
declare @youngest Date
select @youngest = max(birthday) from Employee
if datediff(year, @youngest, getdate()) >= 18
print 'Все сотрудники совершеннолетние'
else
print 'НЕ ВСЕ совершеннолетние'
declare @sum money, @departamentName varchar(100), @countOfWork int, @amountOfGroup int
SELECT @sum = sum(price),
@departamentName = departamentName,
@countOfWork = count(*)
from DesignWork
inner join Departament on Departament.departamentId = DesignWork.departamentId
inner join Agreement on Agreement.agreementId = DesignWork.agreementId
group by Departament.departamentName
having count(*) >= 2
IF @sum > 4000
BEGIN
PRINT 'Название отдела: ' + @departamentName
PRINT 'Количество работ ' + CONVERT(varchar(50), @countOfWork)
PRINT 'Сумма ' + CONVERT(varchar(50), @sum)
END;
ELSE
PRINT 'Слишком дешево'
2 запроса с использованием цикла WHILE
DECLARE @number INT, @factorial INT
SET @factorial = 1;
SET @number = 10;
WHILE @number > 0
BEGIN
SET @factorial = @factorial * @number
SET @number = @number - 1
END;
PRINT @factorial
CREATE TABLE #Accounts ( CreatedAt DATE, Balance MONEY)
DECLARE @rate FLOAT, @period INT, @summa MONEY, @date DATE
SET @date = GETDATE()
SET @rate = 0.065;
SET @period = 5;
SET @summa = 10000;
WHILE @period > 0
BEGIN
INSERT INTO #Accounts VALUES(@date, @summa)
SET @period = @period - 1
SET @date = DATEADD(year, 1, @date)
SET @summa = @summa + @summa * @rate
END;
SELECT * FROM #Accounts
1 запрос для создания скалярной функции
GO
CREATE FUNCTION dbo.calculate_work_days(@startDate date, @endDate date)
RETURNS int AS
BEGIN
RETURN datediff(DAY, @startDate, @endDate)
END
GO
SELECT dbo.calculate_work_days(startDate, endDate) as 'number of work days'
FROM DesignWork
1 запрос для создания функции, которая возвращает табличное значение
GO
CREATE FUNCTION dbo.getMonthName(@DATE date) RETURNS CHAR(15)
WITH EXECUTE AS CALLER AS
BEGIN
DECLARE @month int;
DECLARE @monthName char(15);
SET @month= MONTH(@DATE)
IF (@month=1) SET @monthName='Январь';
IF (@month=2) SET @monthName='Февраль';
IF (@month=3) SET @monthName='Март';
IF (@month=4) SET @monthName='Апрель';
IF (@month=5) SET @monthName='Май';
IF (@month=6) SET @monthName='Июнь';
IF (@month=7) SET @monthName='Июль';
IF (@month=8) SET @monthName='Август';
IF (@month=9) SET @monthName='Сентябрь';
IF (@month=10) SET @monthName='Октябрь';
IF (@month=11) SET @monthName='Ноябрь';
IF (@month=12) SET @monthName='Декабрь';
RETURN(@monthName);
END;
GO
SELECT *, dbo.getMonthName(birthday) AS 'Месяц'
FROM Employee;
2 запроса для создания процедуры без параметров
create procedure CurrentMonthName as
begin
print dbo.monthName(getdate())
end
GO
exec CurrentMonthName
GO
create procedure multiplication as
begin
declare @a int;
set @a = 2;
declare @b int;
set @b = 10;
print convert(varchar(5), @a * @b)
end
GO
exec multiplication
2 запроса для создания процедуры с входными параметрами
CREATE PROCEDURE AddEmployee
@name VARCHAR(50),
@position VARCHAR(100),
@isMale bit,
@address VARCHAR(100),
@birthday date,
@departamentId int
AS
IF(not @birthday >= getdate())
INSERT INTO Employee
VALUES(@name, @position , @isMale, @address, @birthday, @departamentId)
ELSE
PRINT 'Неверная дата рождения'
RETURN
GO
EXEC AddEmployee 'Боровой Владислав', 'Техник', 1, 'Jakuba Kolasa 53/3/83', '2031-08-17', null
GO
CREATE PROCEDURE UpdateEmployee
@id int,
@birthday date
AS
update Employee
set birthday= @birthday
where @id = employeeId
GO
exec UpdateEmployee 4, '2021-08-17'
GO
2 запроса для создания процедуры с входными параметрами и RETURN
CREATE PROCEDURE AddEmployee
@name VARCHAR(50),
@position VARCHAR(100),
@isMale bit,
@address VARCHAR(100),
@birthday date,
@departamentId int
AS
IF(not @birthday >= getdate())
INSERT INTO Employee
VALUES(@name, @position , @isMale, @address, @birthday, @departamentId)
ELSE
PRINT 'Неверная дата рождения'
RETURN
GO
EXEC AddEmployee 'Боровой Владислав', 'Техник', 1, 'Jakuba Kolasa 53/3/83', '2031-08-17', null
GO
CREATE PROCEDURE UpdateOrganizationCity
@id int,
@city nvarchar(50)
AS
IF(@city = 'Гродна')
RETURN
update Organization
set city= @city
where @id = organizationId
GO
exec UpdateOrganizationCity 4, 'Гродна'
GO
2 запроса для создания процедуры обновления данных в таблице базы данных UPDATE
CREATE PROCEDURE UpdateEmployee
@id int,
@birthday date
AS
update Employee
set birthday= @birthday
where @id = employeeId
GO
exec UpdateEmployee 4, '2021-08-17'
GO
CREATE PROCEDURE UpdateOrganizationCity
@id int,
@city nvarchar(50)
AS
IF(@city = 'Гродна')
RETURN
update Organization
set city= @city
where @id = organizationId
GO
exec UpdateOrganizationCity 4, 'Гродна'
GO
2 запроса для создания процедуры извлечения данных из таблицы базы данных SELECT
create procedure Employees as
begin
select * from Employee
end
GO
exec Employees
GO
create procedure EmployeesWithMonthName as
begin
SELECT *, dbo.monthName(birthday) AS 'Месяц'
FROM Employee;
end
GO
exec EmployeesWithMonthName
GO
Создать функцию для выполнения четырех арифметических ‘+’, ‘-’, ‘*’ и ‘/’ над целыми операндами типа bigint, выполнив кодирование и проверку
CREATE FUNCTION dbo.Calculator
(
@opd1 bigint,
@opd2 bigint,
@oprt char(1) = '*'
)
RETURNS bigint
AS
BEGIN
declare @result bigint;
set @result =
case @Oprt
when '+' then @opd1 + @opd2
when '-' then @opd1 - @opd2
when '*' then @opd1 * @opd2
when '/' then @opd1 / @opd2
else 0
end
return @result
END
GO
SELECT dbo.Calculator(4,5, '*')
GO
Создать функцию, возвращающую таблицу с динамическим набором столбцов, выполнив кодирование и тестирование
CREATE FUNCTION DYNTAB (@birthday date)
RETURNS Table AS
RETURN SELECT * FROM Employee WHERE birthday = @birthday
GO
SELECT * FROM DYNTAB('2002-07-06')
GO
Создать функцию, разбивающую строку на подстроки, использую я в качестве разделителя пробелы, выполнив кодирование и тестирование
CREATE FUNCTION Parse
(
@string varchar(500)
)
RETURNS @table TABLE (number int identity not null, substr varchar(30))
AS
BEGIN
DECLARE @Str1 nvarchar(500), @Pos int
SET @Str1 = @String
WHILE 1 > 0
BEGIN
SET @Pos = CHARINDEX(' ', @Str1)
IF @POS > 0
BEGIN
INSERT INTO @table
VALUES (SUBSTRING (@Str1, 1, @Pos))
SET @Str1 = REPLACE(@Str1, SUBSTRING (@Str1, 1, @Pos), '')
END
ELSE
BEGIN
INSERT INTO @table VALUES (@Str1)
BREAK
END
END
RETURN
END
GO
DECLARE @TestString nvarchar(500)
Set @TestString = 'SQL Server 2019'
SELECT * FROM Parse(@TestString)
GO
С помощью Transact-SQL создать три хранимые процедуры, семантика которых аналогична рассмотренным функциям, введя при этом дополнительный параметр для получения результата работы соответствующей процедуры.
CREATE PROCEDURE dbo.ProcCalculator
(
@opd1 bigint,
@opd2 bigint,
@oprt char(1) = '*',
@result bigint OUTPUT
)
AS
BEGIN
set @result =
case @Oprt
when '+' then @opd1 + @opd2
when '-' then @opd1 - @opd2
when '*' then @opd1 * @opd2
when '/' then @opd1 / @opd2
else 0
end
END
GO
declare @result bigint
EXEC ProcCalculator 2, 5, '*', @result OUTPUT
PRINT @result
