отчет. Отчет По дисциплине "Базы данных" Студент гр. Бис1902 Е. Р. Тимохина

• Название: Отчет По дисциплине "Базы данных" Студент гр. Бис1902 Е. Р. Тимохина
• Анкор: отчет
• Дата: 14.06.2022
• Размер: 1.44 Mb.
• Имя файла: OTChET_BD_33.docx
• Тип: Отчет; #590769
• страница: 6 из 7

Лабораторная работа №6

1. 
   Создать курсор, который выводит список покупателей с информацией по всем их заказам. Перемещаясь по курсору, вывести для каждого покупателя количество заказов, с указанием их общей стоимости рисунок 35а, 35б.
   

CREATE TABLE IF NOT EXISTS `mydb`.`clients_info` (

`id` INT NOT NULL auto_increment,

`cfio` VARCHAR(100) NULL,

`articule` VARCHAR(45) NULL,

`status` VARCHAR(45) NULL,

`good` VARCHAR(45) NULL,

`csumma` INT NULL,

`quantity` INT NULL,

`start_date` date NULL,

`end_date` date NULL,

PRIMARY KEY (`id`))

ENGINE = InnoDB;//

CREATE PROCEDURE clients()

BEGIN

DECLARE fio,fiotest VARCHAR(100);

DECLARE articule,status, good VARCHAR(45);

DECLARE summa, quantity INT;

DECLARE start_date, end_date DATE;

DECLARE done INT DEFAULT 0;

DECLARE klients CURSOR FOR

select c.name as fio, o.articule, o.summa, o.status, o.start_date, g.name as good, gio.quantity from client as c

join contracts as con on (c.id_client=con.fromClient_id) join Neworder as o on(con.id_contract = o.fromContracts_id)

join goods_in_Neworder as gio on (o.id_Neworder = gio.fromNeworder_id) join goods as g on (g.id_good=gio.fromGoods_id) order by c.name;

OPEN klients;

set @schet = 0;

select count(*) from client as c join contracts as con on (c.id_client=con.fromClient_id) join Neworder as o on(con.id_contract = o.fromContracts_id)

join goods_in_Neworder as gio on (o.id_Neworder = gio.fromNeworder_id) join goods as g on (g.id_good=gio.fromGoods_id) into @schetchick;

REPEAT

IF NOT done THEN

FETCH from klients INTO fio, articule, summa, status, start_date, good, quantity;

set @schet = @schet+1;

insert into clients_info (`cfio`, `articule`, `csumma`, `status`, `start_date`, `good`, `quantity`) VALUES (fio, articule, summa, status, start_date, good, quantity);

END IF;

if (@schet = @schetchick) then

select * from clients_info;

select cfio, count(*) as count_orders, sum(csumma) as total_sum from clients_info group by cfio;

end if;

UNTIL done

END REPEAT;

CLOSE klients;

END//

call clients();//

select * from clients_info;//



рисунок 35а - список покупателей с информацией по всем их заказам



рисунок 35б - количество заказов каждого покупателей

2. 
   Создать курсор, который выводит список покупок по определенному товару. Перемещаясь по курсору, вывести для каждого товара количество покупок по месяцам за последние несколько лет рисунок 36.
   

CREATE TABLE IF NOT EXISTS `mydb`.`buy_goods` (

`id` INT NOT NULL auto_increment,

`gname` VARCHAR(100) NULL,

`gquantity` INT NULL,

`garticule` VARCHAR(45) NULL,

`month` INT NULL,

PRIMARY KEY (`id`))

ENGINE = InnoDB;//

CREATE PROCEDURE Buy_goods()

BEGIN

DECLARE name,articule VARCHAR(100);

DECLARE quantity INT;

DECLARE done INT DEFAULT 0;

DECLARE goods CURSOR FOR

select g.name, gio.quantity, o.articule from Neworder as o join goods_in_Neworder as gio

on (o.id_Neworder = gio.fromNeworder_id) join goods as g on (g.id_good=gio.fromGoods_id);

#where g.name like g;

OPEN goods;

REPEAT

IF NOT done THEN

FETCH from goods INTO name, quantity, articule;

select month(o.start_date) from Neworder as o join goods_in_Neworder as gio on (o.id_Neworder = gio.fromNeworder_id) join goods as g on (g.id_good=gio.fromGoods_id)

where (year(current_date())-year(o.start_date))<=3 and o.articule = articule and g.name = name into @month;

insert into buy_goods(`gname`,`gquantity`, `garticule`,`month`) VALUES (name, quantity, articule,@month);

set @articule=articule;

set @name=name;

END IF;

UNTIL done

END REPEAT;

CLOSE goods;

END//

call Buy_goods();//

select * from buy_goods;//



рисунок 36 – список покупок по определенному товару по месяцам

3. 
   Выполнить расчет стоимости каждой покупки в заказе и общей стоимости заказа используя курсоры рисунок 37а, 37б.
   

CREATE TABLE IF NOT EXISTS `mydb`.`Cena_orders` (

`id` INT NOT NULL auto_increment,

`carticule` VARCHAR(45) NULL,

`gname` VARCHAR(100) NULL,

`csumma` INT NULL,

PRIMARY KEY (`id`))

ENGINE = InnoDB;//

CREATE PROCEDURE Cena_order()

BEGIN

DECLARE name,articule VARCHAR(100);

DECLARE summ INT;

DECLARE done INT DEFAULT 0;

DECLARE Cena CURSOR FOR

select o.articule, g.name, gio.application_price * gio.quantity as summ FROM Neworder as o

join goods_in_Neworder as gio on (o.id_Neworder = gio.fromNeworder_id) join goods as g on (g.id_good=gio.fromGoods_id) order by o.articule;

OPEN Cena;

set @schet = 0;

select count(*) FROM Neworder as o join goods_in_Neworder as gio on (o.id_Neworder = gio.fromNeworder_id) join goods as g on (g.id_good=gio.fromGoods_id) into @schetchick;

REPEAT

IF NOT done THEN

FETCH from Cena INTO articule, name, summ;

set @schet = @schet+1;

insert into Cena_orders(`carticule`,`gname`, `csumma`) VALUES (articule, name, summ);

END IF;

if (@schet = @schetchick) then

select * from Cena_orders;

select carticule, sum(csumma) as all_sum from Cena_orders group by carticule;

end if;

UNTIL done

END REPEAT;

CLOSE Cena;

END//

call Cena_order();//



рисунок 37а - расчет стоимости каждой покупки в заказе



рисунок 37б – общая стоимость заказов