Лаб 20 УД Голиков Илья
.docxФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ АВТОНОМНОЕ ОБРАЗОВАТЕЛЬНОЕ
УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
БЕЛГОРОДСКИЙ ГОСУДАРСТВЕННЫЙ НАЦИОНАЛЬНЫЙ
ИССЛЕДОВАТЕЛЬСКИЙ УНИВЕРСИТЕТ
(НИУ «БелГУ»)
ИНСТИТУТ ИНЖЕНЕРНЫХ И ЦИФРОВЫХ ТЕХНОЛОГИЙ
КАФЕДРА ИНФОРМАЦИОННЫХ И РОБОТОТЕХНИЧЕСКИХ СИСТЕМ
Отчет по лабораторной работе № 20
Тема работы «Разработка бизнес-логики на стороне SQL-сервера»
по дисциплине “Управление данными”
студента (ки) очного отделения
2 курса 12002105 группы
Голикова Ильи Александровича
Проверил:
Асс. Лысакова Татьяна Андреевна
(ученая степень, звание, Фамилия Инициалы)
Белгород, 2023
Цель работы
Научиться созданию бизнес-логики на стороне СУБД, изучить PSQL, научиться созданию представлений, хранимых процедур, исключений, генераторов и триггеров для создания бизнес-логики
Ход работы
С помощью сервера базы данных PostgreSQL и программы для работы с базами данных JetBrains DataGrip реализовал все необходимые команды и запросы к базе данных на языке SQL.
В ходе выполнения работы применялись следующие запросы:
-----------------
-- Виды
-----------------
-- простой вид таблицы apartment
CREATE OR REPLACE VIEW apartment_simple AS
SELECT * FROM apartment;
-- С подстановкой
CREATE OR REPLACE VIEW apartment_with_payer AS
SELECT apartment.area, payer.full_name
FROM apartment
JOIN payer ON apartment.payer_id = payer.id;
-- С псевдонимами
DROP VIEW apartment_with_payer_and_house;
CREATE OR REPLACE VIEW apartment_with_payer_and_house AS
SELECT apartment.area AS "Площадь", payer.full_name AS "Имя плательщика", h.address AS "Адрес дома"
FROM apartment
JOIN payer ON apartment.payer_id = payer.id
JOIN house h on h.id = apartment.house_id;
-- Группировка и сортировка + агрегатные функции
-- Сколько квартир у каждого
DROP VIEW apartment_with_payer_sorted;
CREATE OR REPLACE VIEW apartment_with_payer_sorted AS
SELECT payer.full_name AS "ID", COUNT(payer.full_name) AS "Всего квартир"
FROM apartment
JOIN payer ON apartment.payer_id = payer.id
GROUP BY payer.full_name
ORDER BY count(payer_id);
-----------------
-- Хранимые процедуры
-----------------
-- Ввод информации
CREATE PROCEDURE add_john_doe()
LANGUAGE SQL
AS
$$
INSERT INTO payer (full_name, date_of_birth, phone, address)
VALUES
('John Doe', '01-01-1970', '12-21-23', 'New York, USA');
$$;
CALL add_john_doe();
-- Изменение информации
DROP PROCEDURE john_to_jane_dow();
CREATE OR REPLACE PROCEDURE john_to_jane_dow()
LANGUAGE sql
AS
$$
UPDATE payer
SET full_name = 'Jane Doe'
WHERE full_name = 'John Doe';
$$;
CALL john_to_jane_dow();
-- Удаление информации
CREATE PROCEDURE delete_janes_johns()
LANGUAGE SQL
AS
$$
DELETE FROM payer
WHERE full_name = 'Jane Doe' or full_name = 'John Doe';
$$;
CALL delete_janes_johns();
-- Процедуры выборки
DROP PROCEDURE select_all_payers();
CREATE PROCEDURE select_all_payers()
LANGUAGE sql
AS
$$
SELECT * FROM payer;
$$;
CALL select_all_payers();
-------------------
-- Исключения
-------------------
-- Исключение при недостаточном количестве нужных строк
CREATE OR REPLACE FUNCTION is_enough_jones()
RETURNS boolean
AS
$$
DECLARE
row_count integer;
BEGIN
SELECT COUNT(*) INTO row_count FROM payer WHERE full_name = 'John Doe';
IF row_count < 3 THEN
RAISE EXCEPTION 'There is not enough Johns!!!';
ELSE
RETURN false;
END IF;
END
$$ LANGUAGE plpgsql;
CALL add_john_doe();
CALL john_to_jane_dow();
CALL delete_janes_johns();
SELECT is_enough_jones();
-- Исключение просто так
CREATE OR REPLACE FUNCTION do_not_press_the_button()
RETURNS boolean
AS
$$
BEGIN
RAISE EXCEPTION 'СКАЗАНО БЫЛО -- НЕ НАЖИМАТЬ!';
END
$$ LANGUAGE plpgsql;
SELECT do_not_press_the_button();
-------------------
-- Генераторы и триггеры
-------------------
-- Генератор
CREATE SEQUENCE seq
START WITH 1
INCREMENT BY 15
MAXVALUE 1000
NO MINVALUE
CACHE 10;
SELECT nextval('seq');
-- Таблица лога для вставки
CREATE TABLE log (
id SERIAL PRIMARY KEY,
log_message VARCHAR(511),
time timestamptz
);
-- Функция для записи лога вставки
CREATE OR REPLACE FUNCTION log_writer_insert () RETURNS trigger AS $my_trigger$
BEGIN
INSERT INTO log(log_message,time)
VALUES ('Была добавлена строка ' || new.full_name, now());
RETURN NEW;
END;
$my_trigger$ language plpgsql;
drop function log_writer_insert();
-- Триггер на вставку в таблицу
-- Пишет в лог каждый раз, когда в таблицу добавляют данные
CREATE TRIGGER my_trigger
AFTER INSERT ON payer
FOR EACH ROW
EXECUTE FUNCTION log_writer_insert();
DROP TRIGGER my_trigger ON payer;
CALL add_john_doe();
-- Триггер на модификацию данных в таблице
DROP FUNCTION log_writer_modify();
CREATE OR REPLACE FUNCTION log_writer_modify() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO log (log_message, time) VALUES ('Была изменена строка '|| OLD.full_name || ' на ' || NEW.full_name , NOW()) ;
RETURN NEW;
end;
$$ LANGUAGE plpgsql;
create or replace trigger update_trigger before update on payer for each row execute function log_writer_modify();
call john_to_jane_dow();
call delete_janes_johns();
call add_john_doe();
-- Триггер на удаление из таблицы
create or replace function log_writer_deletion() returns trigger as
$$
begin
insert into log(log_message, time) values ('Была удалена строка ' || old.full_name, now());
return new;
end;
$$ language plpgsql;
create or replace trigger delete_trigger after delete on payer for each row execute function log_writer_deletion();
Вывод
Научился созданию бизнес-логики на стороне СУБД, изучить PSQL, научился созданию представлений, хранимых процедур, исключений, генераторов и триггеров для создания бизнес-логики