Бази даних / lab14
.docx
Мета роботи: Набути навичок по написанню на використанню вбудованих процедур та користувацьких функції.
Завдання:
1. Для довільної БД розробити користувацьку процедуру.
2. Завантажити процедуру в СУБД і протестувати її роботу
3. Створити власні функції, що місцять частини бізнес-логіки
4. Розробити ряд запитів що застосовуватимуть цю функцію
Хід Роботи:
Для виконання лабораторної роботи буде використано клієнт для роботи з базами даних HeidiSQL. Створюэмо нову базу даних(рис. 1.)
Рисунок 1 – Створення бази даних
Після створення бази даних потрібно створити дві таблиці які будуть зв’язані між собою. Предметна область бази даних це – це список людей які увзяли кредит у банку. Поля які представлені у базі даних: імя, прізвище, адреса, дата взяття кредиту, дата до якої потрібно погасити кредит, сума, відсотки, і сума повернення. Сворення таблиці представлено на рисунку 2, діаграма бази даних на рисунку 3.
Рисунок 2 – Процес створення таблиць
Рисунок 3 – Діаграма бази даних
Sql – код створених таблиць у базы даних:
CREATE TABLE `client` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`last_name` VARCHAR(50) NOT NULL,
`first_name` VARCHAR(50) NOT NULL,
`adress` TEXT NOT NULL,
`data_nar` DATE NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5;
CREATE TABLE `money` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`data_pod_zay` DATE NOT NULL,
`data_zak` DATE NOT NULL,
`suma_poch` DOUBLE NOT NULL,
`procent` DOUBLE NOT NULL,
`sum_vid` DOUBLE NOT NULL,
`id_mon` INT(10) NOT NULL,
PRIMARY KEY (`id`),
INDEX `id_mon` (`id_mon`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=12;
Після створення таблиць наступним кроком буле створення збережуваних процедур і функцій, Для початку створимо збережувані процедури. Процедур буде 4: перша це додавання записів у таблицю «Клієнт», друга – це додавання записів у таблицю «Гроші», наступна – це видалення записів із таблиць і остання – це виведення даних із бази даних.
Процес створення процедур зображений на рисунку 4.
Код процедур зображений нище:
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddClient`(IN `last_name` VARCHAR(50), IN `first_name` VARCHAR(50), IN `adress` TEXT, IN `dat` DATE)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
insert into `client` (`id`,`last_name`,`first_name`,`adress`,`data_nar`) values (null,`last_name`,`first_name`,`adress`,`dat`);
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddMoney`(IN `id_сlient` INT, IN `data_pod_zay` DATE, IN `data_zak` DATE, IN `suma_poch` DOUBLE, IN `procent` DOUBLE)
LANGUAGE SQL
NOT DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE a Double;
IF (`procent`>100) then
SET `procent`=100;
end if;
IF (`procent`<0) then
SET `procent`=0;
end if;
SET `a`=(`suma_poch`/100*`procent`)+`suma_poch`;
insert into `money` (`id`, `data_pod_zay`, `data_zak`, `suma_poch`, `procent`, `sum_vid`, `id_mon`) values
(null,`data_pod_zay`,`data_zak`,`suma_poch`,`procent`,`a`,`id_сlient`) ;
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `DleteRow`(IN `table` VARCHAR(50), IN `id` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF (`table`='client') THEN
DELETE FROM `client` WHERE `client`.id = `id`;
ELSE
DELETE FROM `money` WHERE `money`.id = `id`;
END IF;
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `ViewData`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT `client`.last_name, `client`.first_name, `client`.adress,
`client`.data_nar,
`money`.data_pod_zay,
`money`.data_zak,
`money`.suma_poch ,
`money`.procent, `money`.sum_vid FROM `client`,`money` WHERE `client`.id=`money`.id_mon;
END
Для тестування щоб викликати процедуру потрібно скористатися командою CALL після чого назва функції з параметрами.
Наступним кроком у виконанні лабораторної роботи є створення функцій які будуть виконуватись на сервері БД. Це процедури, які повертають значення.