Файл: Практикум направление подготовки 09. 03. 03 Прикладная информатика Уровень высшего образования Бакалавриат.docx

Министерство науки и высшего образования Российской Федерации

Федеральное государственное бюджетное образовательное учреждение высшего образования

«Российский экономический университет имени Г.В. Плеханова»

Институт цифровой экономики и информационных технологий

Кафедра Прикладной информатики и информационной безопасности

Базы данных

ПРАКТИКУМ

Направление подготовки 09.03.03 Прикладная информатика

Уровень высшего образования Бакалавриат

Утвержден

на заседании кафедры ПИиИБ

протокол №___ от «____» __________ 2019 г.

Заведующий кафедрой ПИиИБ

__________ Ю.Ф. Тельнов

Москва-2020

Гаврилов А.В. Базы данных: ПРАКТИКУМ. Москва: Российский экономический университет имени Г.В. Плеханова, 2020.

Практикум предназначен для проведения практических занятий по дисциплине «Базы данных» для направления подготовки 09.03.03 Прикладная информатика и направлен на формирование у обучающихся навыков проектирования и управления базами данных, изучение современных языковых средств СУБД.

Представленные в практикуме материалы основаны на практическом решении задач обработки информации, ориентированных на применение технологий работы с базами данных. В качестве инструментальных средств используются популярная система управления базами данных MySQL, интегрированная среда разработки баз данных MySQL Workbench, а также CASE-средство проектирования баз данных Oracle SQL Developer Data Modeler.

©Гаврилов А.В., 2020.

Практическое занятие 10. Хранимые процедуры

Цель практического занятия: Формирование навыков использования хранимых процедур при работе с базами данных.

Задачи:

1. 
   Создать базу данных MySQL «Продажи» в соответствии с приведенным описанием.
   
2. 
   Разобрать учебные примеры по написанию и запуску хранимых процедур к разработанной БД.
   
3. 
   В соответствии с заданием написать и запустить процедуры к БД «Продажи».
   

---

1. Общие сведения о хранимых процедурах

Хранимые процедуры – это объекты базы данных, которые представляют собой программы, манипулирующие данными и выполняемые на сервере. Эти программы, кроме команд языка SQL, могут использовать немногочисленные управляющие команды.

Структура хранимой процедуры следующая:


DELIMITER //

CREATE PROCEDURE имя_процедуры [(параметры)]

#Код процедуры

//

Объявление переменных имеет вид

DECLARE имя_переменной тип_переменной [(длина)];

Блок операторов заключается в команды BEGIN … END
Оператор присвоения выглядит так:

SET переменная=значение;

Если нужно присвоить переменной результат команды SELECT, то используется следующий формат (многоточие означает стандартное продолжение команды):

SELECT имя_столбца INTO переменная FROM ...;

Условный оператор имеет вид:

IF условие THEN

Оператор1 или Группа операторов1

[ELSE

Оператор2 или Группа операторов2]

END IF;
Есть несколько операторов цикла, самый распространенный из них:

WHILE условие DO

Оператор или Группа операторов

END WHILE;
Выражение CASE применяется для выбора на основании нескольких опций:

CASE выражение

WHEN вариант1 THEN выражение1

WHEN вариант2 THEN выражение2

…

ELSE выражениеN

END CASE;

Для удаления процедур используется команда:

DROP PROCEDURE IF EXISTS Имя_процедуры;
2. Описание базы данных

В качестве учебного примера данных рассмотрим базу данных «Продажи» (Sales), включающую 8 таблиц (см. рис. 10.1).


Рис. 10.1. Модель базы данных «Продажи» (Sales)
Таблица k_dept (Отделы):

Имя поля	Тип данных	Параметры поля	Хранимая информация
dept_num	INT	PRIMARY KEY, AUTO_INCREMENT	условный номер отдела (суррогатный ключ)
dept_full_name	VARCHAR(45)		полное имя отдела
dept_short_name	VARCHAR(10)		краткое имя отдела
k_staff_staff_num	INT	NULL DEFAULT NULL	внешний ключ к таблице k_staff

---

Таблица k_staff (Сотрудники):

Имя поля	Тип данных	Параметры поля	Хранимая информация
staff_num	INT	PRIMARY KEY, AUTO_INCREMENT	условный номер сотрудника (суррогатный ключ)
staff_name	VARCHAR(45)	NOT NULL	фамилия
staff_post	VARCHAR(45)		должность
staff_hiredate	DATE		Дата приема на работу
staff_termdate	DATE		Дата окончания контракта
k_dept_dept_num	INT	NOT NULL	внешний ключ к таблице k_ dept

Таблица k_firm (Компании-контрагенты):

Имя поля	Тип данных	Параметры поля	Хранимая информация
firm_num	INT	PRIMARY KEY, AUTO_INCREMENT	условный номер компании (суррогатный ключ)
firm_name	VARCHAR(45)	NOT NULL	название компании
firm_addr	VARCHAR(45)		адрес
firm_phone	VARCHAR(20)		телефон

Таблица k_contract (Контракты):

Имя поля	Тип данных	Параметры поля	Хранимая информация
contract_num	INT	PRIMARY KEY, AUTO_INCREMENT	условный номер сотрудника (суррогатный ключ)
contract_date	DATE	NOT NULL	дата контракта
contract_type	ENUM('A','B','C')	NOT NULL	тип контракта
k_firm_firm_num	INT	NOT NULL	внешний ключ к таблице k_firm
k_staff_staff_num	INT	NOT NULL	внешний ключ к таблице k_staff

---

Таблица k_bill (Счета):

Имя поля	Тип данных	Параметры поля	Хранимая информация
bill_num	INT	PRIMARY KEY, AUTO_INCREMENT	условный номер счета (суррогатный ключ)
bill_date	DATE	NOT NULL	дата выставления счета
bill_sum	DECIMAL(9,2)	NOT NULL	сумма счета
bill_term	DATE	NOT NULL	срок оплаты счета
bill_peni	DECIMAL(6,2)		пени
k_contract_contract_num	INT	NOT NULL	внешний ключ к таблице k_contract

Таблица k_payment (Платежи):

Имя поля	Тип данных	Параметры поля	Хранимая информация
payment_num	INT	PRIMARY KEY, NOT NULL	условный номер платежа
payment_date	DATE	NOT NULL	дата платежа
payment_sum	DECIMAL(9,2)	NOT NULL	сумма платежа
k_bill_bill_num	INT	PRIMARY KEY, NOT NULL	внешний ключ к таблице k_bill

Таблица k_price (Прайс-лист):

Имя поля	Тип данных	Параметры поля	Хранимая информация
price_num	INT	PRIMARY KEY, AUTO_INCREMENT	условный номер товара
price_name	VARCHAR(45)	NOT NULL	наименование товара
price_sum	DECIMAL(9,2)		цена товара
price_type	VARCHAR(1)		тип товара

---

Таблица k_protokol (Протокол счета):

Имя поля	Тип данных	Параметры поля	Хранимая информация
k_bill_bill_num	INT	PRIMARY KEY, NOT NULL	внешний ключ к таблице k_bill
k_price_price_num	INT	PRIMARY KEY, NOT NULL	внешний ключ к таблице k_price
kolvo	INT	NOT NULL	число единиц товара
price_sum	DECIMAL(9,2)	NOT NULL	стоимость единицы товара

Таблицы k_staff (Сотрудники) и k_dept (Отделы) связаны двумя связями (см. рис. 10.1). Первая связь «Работают в отделе» с мощностью 1:М означает, что в одном отделе работает много сотрудников, но каждый сотрудник работает в одном и только одном отделе. В этой связи дочерней является таблица k_staff, содержащая внешний ключ. Вторая связь «Руководит» с мощностью 1:1 и необязательным участием в связи таблицы k_dept означает, что отделом может может руководить один и только сотрудник и, в свою очередь, сотрудник может руководить одним и только одним отделом.

Таблицы k_firm (Компании-контрагенты) и k_contract (Контракты) связаны связью «Заключили контракт» (см. рис. 10.1) с мощностью 1:М. Эта связь означает, что с одной компанией может быть заключено много контрактов, но каждый контракт заключен с одной и только одной компанией. В этой связи дочерней является таблица k_contract, содержащая внешний ключ.

Таблицы k_staff (Сотрудники) и k_contract (Контракты) связаны связью «Оформляют контракт» (см. рис. 10.1) с мощностью 1:М. Эта связь означает, что один сотрудник может оформить много контрактов, но каждый контракт оформлен одним и только одним сотрудником. В этой связи дочерней является таблица k_contract, содержащая внешний ключ.

Таблицы k_contract (Контракты) и k_bill (Счета) связаны связью «Выставлен счет» (см. рис. 10.1) с мощностью 1:М. Эта связь означает, что по одному контракту может быть выставлено много счетов, но каждый счет выставляется по одному и только одному контракту. В этой связи дочерней является таблица

---