Нормативные ссылки

Определение внешнего ключа при создании таблицы Delivery:

CREATE TABLE Delivery

(Part_ID INTEGER UNSIGNED,

Quantity INTEGER CHECK (Quantity >0),

FOREIGN KEY (Part_ID) REFERENCES Parts (Part_ID));

Определение внешнего ключа при модификации таблицы Delivery:

ALTER TABLE DELIVERY ADD FOREIGN KEY (Part_ID) REFERENCES Parts(Part_ID);

Описание созданной таблицы, полученное при помощи DESCRIBE:

1.9. Задание на лабораторную работу

1. Получить у преподавателя имя хоста, на котором работает сервер MySQL, имя вашей БД, а также логин и пароль для доступа к серверу MySQL.

2. Запустить командную строку cmd.exe и перейти в каталог, где находится файл mysql.exe.

3. Подключиться к серверу MySQL, запустив программу mysql.exe со следующими параметрами (host – имя хоста сервера, user – Ваш логин):

mysql -h host -u user –p

4. Установить пароль для доступа к серверу MySQL:

Set password for <имя_пользователя> = password('<пароль>');

5. Выполните запрос для получения версии MySQL-сервера, текущей даты и времени, имени текущего пользователя:

select version(), now(), current_date, current_time, user();

6. Создать базу данных с указанным преподавателем именем:

create database <имя_базы>;

7. Выполнить запрос для просмотра существующих БД:

show databases;

8. Выполнить команду для подключения к вашей базе данных:

USE <имя_базы>;

9. Закрыть соединение с сервером и выйти из программы mysql.exe

exit;

10. Написать командный файл (*.bat) для определения параметров соединения и запуска mysql.exe. Запустить mysql.exe при помощи созданного командного файла.

11. Написать сценарий (набор SQL-команд) для создания базы данных «Предприятие». Концептуальное описание БД «Предприятие»:

База данных «Предприятие» содержит информацию о работе некоторого промышленного предприятия, которое осуществляет сборку изделий (проектов) из деталей. В базе данных присутствуют следующие сущности: Детали, Проекты, Поставщики, Поставки, Города. Таблица «Детали» содержит описания деталей. Поля: Номер_детали ^[1] , Наименование, Материал, Вес. Таблица «Поставщики» содержит описания организаций, поставляющих детали. Поля: Номер_поставщика, Наименование, Номер_города. Таблица «Проекты» содержит описание проектов (сборочных изделий). Поля: Номер_проекта, Наименование, Номер_города. Таблица «Города» содержит описание городов, в которых могут находиться проекты и поставщики. Поля: Номер_города, Наименование. Каждый проект содержит некоторое количество различных деталей, поставляемых различными поставщиками в определенном количестве. Проект рассматривается как совокупность поставок. Таблица «Поставки» содержит записи о типе, количестве деталей и цене одной детали, поставляемых определенным поставщиком для определенного проекта. Поля: Номер_проекта, Номер_поставщика, Номер_детали, Количество, Цена, Дата_начала, Дата_конца.

В сценарии необходимо определить первичные и внешние ключи в таблицах. Для генерации значений первичных ключей использовать спецификатор AUTO_INCREMENT. Задать для первичных ключей требование обязательности данных NOT NULL. Задать для полей ВЕС, КОЛИЧЕСТВО и ЦЕНА беззнаковый целочисленный тип.

12. Добавить в начало сценария команды очистки базы данных. Внести в сценарий операторы заполнения таблиц тестовыми данными (не менее 5-и строк в каждой таблице). Запустить сценарий в пакетном режиме. При необходимости произвести отладку (устранение ошибок).

13. Проверить работу ограничений целостности CHECK и ограничений целостности внешних ключей путем добавления в таблицы некорректных данных.

1.10. Содержание отчета

Отчет должен содержать тексты и результаты выполнения всех команд (в виде скриншотов) с указанием пункта задания на лабораторную работу, а так же сценарий для создания БД «Предприятие» с комментариями.

Лабораторная работа 2. Работа с сервером MySQL при помощи клиента dbForge Studio for MySQL

2.1. Введение

dbForge Studio for MySQL является удобным профессиональным инструментом для разработки БД и выполнения широкого набора дополнительных функций на сервере MySQL.

dbForge Studio for MySQL является свободным программным продуктом. Его можно бесплатно получить через сайт разработчика https://www.devart.com/ru/dbforge.

Целью данной лабораторной работы является изучение основных приемов работы и выполнение действий по созданию и работе с БД на сервере MySQL при помощи клиента dbForge Studio for MySQL.

2.2. Создание соединения и подключение к серверу БД

После первого запуска dbForge Studio for MySQL предложит создать соединение с сервером. В диалоговом окне «Свойства соединения базы данных» необходимо ввести адрес (имя) компьютера, на котором работает сервер MySQL, логин и пароль для доступа к серверу MySQL. После этого можно открыть выпадающий список и выбрать базу данных (рис. 2.1). Далее желательно задать для соединения название, например, совпадающее с именем хоста сервера.

Рис. 2.1. Создание соединения с БД в dbForge Studio for MySQL

После подключения к серверу в окне «Проводник» будет доступен иерархический список доступных для пользователя БД и объектов, которые в них содержатся (рис. 2.2).

Рис. 2.2. Список БД в проводнике dbForge Studio for MySQL

2.3. Создание и редактирование таблиц

Для создания таблицы необходимо в проводнике БД в контекстном меню узла «Таблицы» выбрать пункт «Новая таблица». В появившемся окне можно задать имя таблицы и комментарий (рис. 2.3).

Рис.2.3. Создание таблицы

Для создания столбцов таблицы необходимо в окне редактирования таблицы в контекстном меню поля «Столбцы» выбрать пункт «Новый столбец» (рис. 2.4).

Рис.2.4. Создание столбца

Для создаваемого столбца таблицы можно задать имя, тип данных, длину, атрибуты допустимости пустых значений, атрибут уникальности, атрибут первичного ключа, значение по умолчанию и комментарий (рис. 2.5).

Рис. 2.5. Создание столбца

Редактирование таблицы и столбца производится аналогично (рис. 2.6).

Рис. 2.6 Редактирование таблицы

2.4. Определение внешних ключей

Для создания внешнего ключа в таблице необходимо в проводнике БД в контекстном меню узла «Таблицы» - «Ограничения» выбрать пункт «Новый внешний ключ» (рис. 2.7).

Рис. 2.7. Создание внешнего ключа

Далее необходимо выбрать заранее созданный столбец внешнего ключа, базу данных и таблицу в которой находится связанный первичный ключ, а также имя ограничения и столбец первичного ключа.

Рис. 2.8 Определение ограничения внешениго ключа

2.5. Просмотр и редактирование данных

Для просмотра и редактирования данных в таблице необходимо в окне редактирования таблицы перейти на вкладку «Данные» (рис. 2.9). Доступны операции редактирования значений столбцов, добавлении и удаления строк. Для фиксации изменений необходимо нажать кнопку «Запомнить». Изменения данных будут сохранены в БД.

Рис. 2.9. Просмотр и редактирование данных в таблице

2.6. Построение диаграммы БД

Для построения диаграммы БД необходимо выбрать пункт меню «Файл – Создать – Диаграмма БД» (Рис. 2.10). Появится пустое окно, куда можно перетащить необходимые таблицы из проводника БД.

Рис. 2.10. Создание Диаграммы БД

Связи между таблицами на диаграмме БД появятся автоматически. После добавления таблиц можно разместить таблицы на диаграмме с учетом удобства чтения диаграммы (минимального количества пересечений связей) (Рис. 2.11).

Рис. 2.11. Диаграмма БД

2.7. Экспорт схемы и данных

Экспорт схемы и данных позволяет автоматически сгенерировать сценарий на языке DDL, содержащий операторы создания объектов БД и наполнения ее данными. Такой сценарий удобно использовать для переноса БД на другой сервер или в качестве «твердой копии» БД. Для экспорта схемы и данных необходимо выбрать пункт «База данных – Экспорт схемы» в главном меню. Дальнейшие действия выполняются при помощи мастера экспорта схемы. Необходимо выбрать соединение, схему (БД), задать имя файла для сохранения сценария (рис. 2.12).

Рис.2.12. Мастер экспорта схемы

Далее необходимо выбрать вид информации для экспорта – структура, данные или и то и другое (рис. 2.13.)

Рис. 2.13. Выбор информации для экспорта

Следующим шагом предлагается выбрать объекты для экспорта (рис. 2.14). Это могут быть не только таблицы, но и представления, процедуры, функции, триггеры и др.

Рис. 2.14 Выбор объектов для экспорта

Если был выбран экспорт структуры и данных, то будет предложено выбрать таблицы из которых необходимо экспортировать данные (рис. 2.15).

Рис. 2.15 Выбор таблиц для экспорта данных

Заключительным шагом предлагается выбрать опции экспорта схемы (рис. 2.16). Среди перечисленных опций полезно выбрать опции «Включать выражения DROP» и «Включать IF EXIST в выражении DROP». Это позволит избежать ошибок связанных с запуском сценария в БД где уже были созданы объекты с идентичными именами.

Рис. 2.16 Выбор опций экспорта

После нажатия кнопки «Экспорт» появится сообщение с опцией открытия сценария в среде dbForge Studio for MySQL (рис. 2.17). Сценарий будет сохранен с указанным ранее именем.

Рис. 2.17 Завершение экспорта

Если была выбрана опция «Открыть скрипт» то после завершения экспорта откроется окно с полученным сценарием (рис. 2.18). Сценарий будет содержать операторы DDL и DML. Особенностью сценария является наличие в нем директивы отключения проверки ограничений целостности внешних ключей на время выполнения сценария:

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;

Данная директива запоминает значение системной переменной FOREIGN_KEY_CHECKS и устанавливает ее значение равным 0. Это позволяет избежать ошибок, связанных с нарушением ограничений ссылочной целостности при удалении/создании объектов БД и добавления данных в таблицы в процессе выполнения сценария.

После выполнения сценария значение системной переменной восстанавливается:

/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;

Рис. 2.18.Сценарий, полученный в результате экспорта

2.8. Экспорт схемы и данных в DBForge Studio for MySQL версии 4.5

Для экспорта схемы и данных в версии 4.5 необходимо выбрать пункт «База данных – Создать резервную копию БД» в главном меню. Дальнейшие действия выполняются при помощи мастера резервирования БД. На вкладке «Общие» необходимо выбрать соединение, базу данных, задать путь и имя файла для сохранения сценария (рис. 2.19).

Рис. 2.19. Мастер резервирования БД

На вкладке «Содержимое» необходимо выбрать вид информации для экспорта – структура, данные или и то и другое, а также выбрать объекты для экспорта. Это могут быть не только таблицы, но и представления, процедуры, функции, триггеры и др. (рис. 2.20.)

Рис. 2.20. Выбор объектов для сохранения

На вкладке «Опции» предлагается выбрать опции экспорта схемы (рис. 2.21). Среди перечисленных опций полезно выбрать опции «Включать выражения DROP» и «Включать IF EXIST в выражении DROP». Это позволит избежать ошибок связанных с запуском сценария в БД где уже были созданы объекты с идентичными именами.

Рис. 2.21. Выбор опций для сохранения

После нажатия кнопки «Выполнить» появится сообщение о завершении создания резервной копии с опцией открытия сценария в среде dbForge Studio for MySQL и сохранения проекта (рис. 2.22). Сценарий будет сохранен с указанным ранее именем.

Рис. 2.22. Завершение создания резервной копии

2.9. Задание на лабораторную работу

1. Запустить dbForge Studio for MySQL, настроить подключение к серверу MySQL.

2. Просмотреть структуру и данные таблиц БД «Предприятие», созданной в лабораторной работе №1.

3. Построить диаграмму БД «Предприятие».

4. Произвести экспорт схемы (таблиц) и данных БД «Предприятие». Сравнить полученный сценарий со сценариями для создания объектов БД и наполнения БД данными, разработанными в лабораторной работе №1. Описать отличия и привести в отчете.

5. При помощи dbForge Studio for MySQL реализовать на сервере БД согласно теме курсового проекта по дисциплине.

6. Построить диаграмму БД согласно теме курсового проекта по дисциплине.

7. Произвести экспорт схемы (таблиц) и данных БД согласно теме курсового проекта по дисциплине.

2.10. Содержание отчета

Отчет должен содержать:

8. результаты выполнения всех пунктов работы (в виде скриншотов) с указанием пункта задания на лабораторную работу;

9. сценарий для создания БД «Предприятие» и наполнения ее данными, полученный при помощи экспорта схемы;

10. диаграмму БД «Предприятие»;

11. сценарий для создания БД согласно теме курсового проекта по дисциплине и наполнения ее данными;

12. диаграмму БД согласно теме курсового проекта по дисциплине.

Лабораторная работа 3. Разработка хранимых процедур и функций на сервере MySQL

3.1. Введение

Хранимые процедуры и функции являются важнейшим элементом современных промышленных СУБД. В MySQL хранимые процедуры поддерживаются начиная с версии 5. Хранимые процедуры и функции (а также триггеры как разновидность процедур) реализуются в виде подпрограмм. При этом процедуры и функции называются хранимыми т.к. они хранятся в базе данных наряду с другими объектами (таблицами, ограничениями и т.д.). Хранимые подпрограммы представляют собой набор команд SQL. Кроме операторов SQL в хранимой подпрограмме могут быть использованы основные элементы свойственные любому языку программирования – переменные, операторы ветвления, циклы и др.

Хранимые процедуры и функции позволяют реализовать часть логики работы прикладной системы на стороне сервера, что оптимизирует производительность, упрощает разработку клиентской части системы, существенно повышает безопасность. Преимущество, связанное с безопасностью, заключается в защите программного кода бизнес - логики от несанкционированного просмотра и изменения. Защитить подпрограмму, которая находится на сервере намного проще, чем гарантировать безопасность клиентского приложения.

3.2. Создание, запуск и удаление простой процедуры

Для создания простой процедуры в клиенте mysql.exe можно выполнить следующие операторы:

DELIMITER //

CREATE PROCEDURE Hello_World()

BEGIN

SELECT(‘Hello, world!’);

END

//

В данном примере первая строка задает последовательность символов, которая будет использоваться в качестве признака завершения ввода. Обычно для этого используется точка с запятой, однако, в приведенном выше примере точка с запятой уже используется в качестве разделителя операторов в теле функции.

Во второй строке следует оператор создания процедуры CREATE PROCEDURE, после которого указывается имя процедуры. Создаваемая процедура не имеет параметров, поэтому скобки после имени процедуры – пустые.

Далее следует конструкция BEGIN.. END, в которую должны быть заключены все операторы процедуры. Операторы разделяются символом точка с запятой.

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

CALL Hello_World;

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

DROP PROCEDURE Hello_World;

3.3. Создание, запуск и удаление простой функции

Для создания простой процедуры в клиенте mysql.exe можно выполнить следующие операторы:

DELIMITER //

CREATE FUNCTION Hello_World()

RETURNS VARCHAR(20)

BEGIN

RETURN ‘Hello, world!’;

END

//

Для запуска функции необходимо ввести

SELECT Hello_World()//

3.4. Простые и системные переменные

Объявить переменную в хранимой подпрограмме можно в любом месте тела подпрограммы (внутри блока BEGIN.. END). Синтаксис оператора объявления переменной:

DECLARE имя [, имя]... тип_данных [DEFAULT значение]

Объявить переменную в хранимой подпрограмме можно в любом месте тела подпрограммы. Если параметр DEFAULT отсутствует, то переменная инициализируется со значением NULL.

Для присвоения значения переменной может быть использован оператор SET. В следующем примере переменной S присваивается текстовое значение, которое затем выводится на экран:

CREATE PROCEDURE Hello_World()

BEGIN

DECLARE S VARCHAR(20);

SET S=‘Hello, world!’;

SELECT(S);

END

//

Результат вызова процедуры на выполнение:

Иногда бывает необходимо присвоить переменной значение, возвращаемое в результате запроса. Это можно сделать при помощи оператора SELECT..INTO. При этом запрос должен возвращать только одну строку. Если запрос возвращает пустой результат, это приведет к ошибке 1329 (No data). Если запрос содержит более одной строки, это приведет к ошибке 1172 (Result consisted of more than one row). Количество строк, возвращаемых запросом, можно ограничить опцией LIMIT оператора SELECT. Данная опция имеет два параметра. Первый параметр указывает смещение возвращаемого набора строк относительно начала, второй – количество возвращаемых строк. При использовании опции только с одним параметром он интерпретируется как количество возвращаемых строк от начала результата. Таким образом, совместно с оператором SELECT..INTO можно использовать опцию LIMIT 1. Следующая процедура выводит наименование самой тяжелой детали:

CREATE PROCEDURE Heavy()

BEGIN

DECLARE S VARCHAR(20);

SELECT weight INTO S FROM Parts ORDER BY Weight DESC LIMIT 1;

SELECT(S);

END

//

В процессе выполнения оператора SELECT..INTO выполняется неявное приведение типа возвращаемого запросом значения типу переменной:

CREATE PROCEDURE Parts_count()

BEGIN

DECLARE S VARCHAR(20);

SELECT count(*) INTO S FROM Parts;

SELECT(S);

END

//

Разница между простыми и системными переменными в том, что системные переменные доступны извне хранимой процедуры. Системную переменную не нужно инициализировать. Разница в простой и системной переменной пользовании префикса @ в имени системной переменной.

SET @S=‘Hello, world!’;

Значение системной переменной можно узнать после выполнения хранимой процедуры:

CREATE PROCEDURE Parts_count()

BEGIN

SELECT count(*) INTO @S FROM Parts;

END

//

3.5. Параметры процедур и функций

Параметры процедуры или функции указываются в операторе CREATE после имени в скобках. Если параметров нет, то необходимо указывать пустые скобки. Параметры бывают типов IN, OUT INOUT (см. таблицу).

Режим	Предназначение	Использование параметра
IN	Только для чтения	Значение параметра может применяться, но не может быть изменено в модуле
OUT	Только для записи	В модуле можно присваивать значение параметру, но нельзя использовать его.
IN OUT	Для чтения и записи	В модуле можно использовать и изменять значение параметра

Пример процедуры, в которой используется один параметр IN и один параметр OUT:

CREATE PROCEDURE Parts_count(IN Mat VARCHAR(20),OUT PNum INT)

BEGIN

SELECT count(*) INTO PNum FROM Parts WHERE Material=Mat;

END

//

CALL Parts_count(‘Rubber’,@S)//

Пример функции с параметром:

CREATE FUNCTION Hello_World(S VARCHAR(20))

RETURNS VARCHAR(20)

BEGIN

RETURN CONCAT(‘Hello ’,S,’!!!’);

END

//

Для запуска функции необходимо ввести

SELECT Hello_World(‘Root’)//

3.6. Операторы управления ходом выполнения программы

Условный оператор IF..THEN имеет следующий синтаксис:

IF <условие> THEN <оператор 1>

[ELSEIF <условие> THEN <оператор 2>]...

[ELSE <оператор 3>]

END IF

Оператор CASE имеет следующий синтаксис:

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list]...

[ELSE statement_list]

END CASE

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

[Метка_начала:] LOOP

<оператор>

END LOOP [Метка_конца]

Операторы цикла выполняются до тех пор, пока внутри цикла не будет выполнен оператор LEAVE < Метка_начала >, который прерывает цикл с указанной меткой.

Оператор цикла с постусловием имеет следующий синтаксис:

REPEAT

<оператор>

UNTIL <условие_выхода>

END REPEAT

Цикл будет выполняться до тех пор, пока условие выхода не станет истинным.

Оператор цикла с предусловием имеет следующий синтаксис:

WHILE <условие_выполнения> DO

<оператор>

END WHILE

Цикл будет выполняться до тех пор, пока условие выполнения будет истинным.

3.7. Курсоры

Курсор представляет собой временную таблицу, получаемую в результате запроса, которая служит для построчной обработки данных. Курсор позволяет в цикле «перебирать» строки, выполняя над ними необходимые действия. Таки образом, курсор является таблицей, возможности которой выходят за рамки классической реляционной модели (в классическом отношении отсутствует понятие порядка следования строк – строки представляют собой множество).

Для объявления курсора используется следующий оператор:

DECLARE <имя_курсора> CURSOR FOR <SQL-выражение>;

Прежде чем курсор может быть использован, его необходимо открыть:

OPEN <имя_курсора>;

После открытия указатель курсора устанавливается на первую строку. Для доступа к текущей строке открытого курсора используется оператор:

FETCH <имя_курсора> INTO <имя_переменной> [,<имя_переменной>]...

Этот оператор помещает значения строки курсора в переменные, количество и типы данных которых соответствуют схеме (столбцам) курсора. После выполнения оператора FETCH происходит автоматическое продвижение на следующую строку курсора. Если более нет доступных строк (достигнута последняя строка) происходит изменение значения переменной SQLSTATE в 02000. Для обработки этого события необходимо установить обработчик: HANDLER FOR SQLSTATE '02000'.

Следующий оператор закрывает курсор:

CLOSE <имя_курсора>;

Если оператор закрытия курсора не указан явно, то курсор закрывается автоматически при закрытии соответствующего блока подпрограммы.

Для примера создадим процедуру, которая изменяет имя всех деталей с определенным именем на имя, формируемое как «Имя-N», где N – порядковый номер в списке всех деталей «Gasket» в порядке возрастания веса детали. Имя детали передается в качестве параметра.

CREATE PROCEDURE Parts_rename(PName VARCHAR(20))

BEGIN

DECLARE Done INT DEFAULT 0;

DECLARE S VARCHAR(20);

DECLARE N,I INTEGER;

DECLARE Cur1 CURSOR FOR SELECT Part_ID, Part_name FROM Parts WHERE Part_name=PName ORDER BY WEIGHT;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN Cur1;

SET I=1;

REPEAT

FETCH Cur1 INTO N,S;

IF Done=0 THEN UPDATE Parts SET Part_name=CONCAT(S,’-’,I) WHERE Part_ID=N;

END IF;

SET I=I+1;

UNTIL Done END REPEAT;

CLOSE Cur1;

END

Содержимое таблицы до выполнения процедуры:

Содержимое таблицы после выполнения процедуры:

3.8. Задание на лабораторную работу

1. Для БД «Предприятие» реализовать и проверить работу следующих процедур и функций:

a. Реализовать функцию, которая возвращает количество различных городов, в которых находятся поставщики определенного проекта (входной параметр).

b. Реализовать функцию, которая возвращает количество различных городов, в которых находятся проекты, обеспечиваемые определенным поставщиком (входной параметр).

c. Реализовать функцию, которая возвращает количество различных городов, в которых находятся поставщики определенной детали (входной параметр).

d. Реализовать процедуру, которая выводит информацию обо всех поставщиках и количестве различных городов, в которых находятся обслуживаемые ими проекты. Примечание: использовать функцию, реализованную в п. 1.b.

e. Реализовать процедуру, которая выводит информацию обо всех проектах и количестве различных городов, в которых находятся обслуживающие их поставщики. Примечание: использовать функцию, реализованную в п. 1.a.

2. Написать процедуру, которая изменяет имя каждой детали на имя, формируемое как «Имя_детали-N», где N – порядковый номер в списке всех одноименных деталей в порядке возрастания веса детали. Примечание: использовать вызов вышеописанной процедуры Parts_rename.

3.9. Содержание отчета

В отчете привести код процедур и функций, результаты выполнения, содержимое таблиц до и после (при необходимости) выполнения процедур и функций.

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

CREATE FUNCTION Qty_of_Prj_Towns(S VARCHAR(20))

RETURNS INTEGER

BEGIN

DECLARE N INTEGER;

SELECT COUNT(*) INTO N FROM Towns WHERE Town_ID IN (SELECT Projects.Town_ID FROM Parts, Projects, Supply WHERE Parts.Part_ID= Supply.Part_ID AND Projects.Project_ID=Supply.Project_ID AND Parts.Part_name=S);

RETURN N;

END

Лабораторная работа 4. Исключения и триггеры

4.1. Исключения

Исключения, или исключительные ситуации – это ошибки, которые возникают в процессе выполнения инструкций SQL. MySQL перехватывает ошибки и реагирует на них при помощи т.н. обработчиков (handlers). Механизм функционирования обработчиков ис­ключений позволяет четко отделить код обработки ошибок от исполняемых опера­торов.

Когда происходит ошибка, инициируется исключение. В результате выполнение исполняе­мого блока прерывается и управление передается отдельному разделу исключений в текущем блоке, если таковой имеется. После обработки исключения возврат в тот блок, из которого оно было инициировано, невозможен, поэтому управле­ние переходит во внешний блок.

Существует два вида исключений:

Системное исключение. Определяется в MySQL и обычно инициируется ядром СУБД, обнаружившим ошибку.

Пользовательское исключение. Определяется программистом, следовательно оно специфично для данного приложения.

Системные исключения возникают при попытке выполнения недопустимых SQL-инструкций. О возникновении исключения можно узнать из сообщения клиента mysql.exe. Например, при попытке выбрать данные из несуществующей таблицы произойдет следующее:

Информация о произошедшем исключении стоит из трех элементов:

1. Число – код ошибки (1146). Этот код определен в MySQL и несовместим с другими СУБД.

2. Пятисимвольный код системной переменной SQLSTATE (42S02). Эти значения определяются в стандартах ANSI SQL и ODBC и стандартизированы в большей степени.

3. Текстовое описание ошибки (Table ‘database1.no_such_table’ doesn’t exist).

При создании обработчика исключения можно использовать различные способы идентификации исключения. Синтаксис оператора создания исключения:

DECLARE <тип_обработчика> HANDLER

FOR <идентификатор_исключения> [,<идентификатор_исключения>]...

<операторы>;

Здесь параметр <тип_обработчика> может принимать значения CONTINUE, EXIT или UNDO. Для обработчиков типа CONTINUE выполнение программы, вызвавшей исключение, продолжается после выполнения кода обработчика. Для обработчиков типа EXIT выполнение текущего блока BEGIN…END программы, вызвавшей исключение, прерывается. Обработчики типа UNDO не поддерживаются в версии MySQL 5.5.

Параметр <идентификатор_исключения> может представлять собой значение SQLSTATE, имя исключения, которое было задано при его создании оператором DECLARE... CONDITION, а также одно из ключевых слов:

SQLWARNING – синоним для значений SQLSTATE, начинающихся с 01

NOT FOUND - синоним для значений SQLSTATE, начинающихся с 02

SQLEXCEPTION - синоним для значений SQLSTATE, НЕ начинающихся с 00, 01 или 02

Например, создадим процедуру, в которой определим обработчик для исключения с кодом 42S02:

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

END;

Вариант процедуры с предварительно заданным именем исключения при помощи DECLARE... CONDITION:

CREATE PROCEDURE h()

BEGIN

DECLARE No_my_table CONDITION FOR SQLSTATE '42S02';

DECLARE CONTINUE HANDLER FOR No_my_table SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

END;

Вариант процедуры с использованием синонима SQLEXCEPTION для идентификации исключения:

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

END;

Следующий пример демонстрирует продолжение выполнения программы после обработчика типа CONTINUE:

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

SET @A=1;

END;

Если использовать обработчик типа EXIT, то выполнение программы прерывается:

CREATE PROCEDURE h()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

SET @A=1;

END;

Следующая процедура содержит обработчик исключения 23000 (ER_DUP_KEY)

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT ‘Первичный ключ уже существует’;

INSERT INTO Parts VALUES (9,’Spoon’,’Wood’,30);

INSERT INTO Parts VALUES (9,’Spoon’,’Wood’,30);

END;

Оператор SIGNAL

Оператор SIGNAL позволяет вызвать пользовательское исключение и сообщить обработчику или внешней программе информацию о произошедшей ошибке. Синтаксист оператора SIGNAL:

SIGNAL <идентификатор_исключения>

[SET <параметр> = <значение>, [<параметр> = <значение>]...]

Здесь параметр <идентификатор_исключения> может представлять собой значение SQLSTATE или имя исключения, которое было задано при его создании оператором DECLARE... CONDITION. Значение SQLSTATE не должно начинаться с цифр ‘00’. Для пользовательского исключения следует использовать значение SQLSTATE 45000.

Оператор SIGNAL может включать опцию SET, после которой следуют пары <параметр>=<значение>, разделенные запятыми.

Пример:

CREATE PROCEDURE p (val INT)

BEGIN

DECLARE my_error CONDITION FOR SQLSTATE ‘45000’;

DECLARE EXIT HANDLER FOR my_error SELECT(‘Произошла ошибка!’);

IF val = 0 THEN

SIGNAL my_error;

END IF;

END;

Пример с использованием параметра MESSAGE_TEXT:

CREATE PROCEDURE p (val INT)

BEGIN

DECLARE my_error CONDITION FOR SQLSTATE ‘45000’;

IF val = 0 THEN

SIGNAL my_error SET MESSAGE_TEXT = ‘Произошла ошибка!’;

END IF;

END;

4.2. Триггеры

Триггеры - это особые хранимые процедуры, выполняемые в ответ на происхо­дящие в базе данных события. Они относятся к числу наиболее важных элемен­тов промышленных приложений базы данных. Основным назначением триггеров является поддержка ограничений целостности, которые не реализуются при помощи внешних ключей и ограничений, накладываемых на значение столбца (NOT NULL, CHEK и т.д.).

Триггеры уровня инструкций языка манипулирования данными (триггеры DML) запускаются после вставки, обновления или удаления строки конкретной табли­цы. Это наиболее распространенный тип триггеров, особенно часто применяемый разработчиками.

Триггер BEFORE. Вызывается до внесения каких-либо изменений, в том чис­ле до вставки записи (BEFORE INSERT).

Триггер AFTER. Выполняется после того, как производятся все изменения, в частности после операции вставки записи (AFTER INSERT).

Существуют, также, следующие виды триггеров:

Триггер уровня инструкции. Выполняется для отдельной SQL-инструкции, которая может обрабатывать одну или более записей базы данных.

Триггер уровня записи. Вызывается для отдельной записи, обрабатываемой SQL-инструкцией. Если, предположим, таблица books содержит 1000 строк, то следующая инструкция UPDATE модифицирует все эти строки: UPDATE books SET title = UPPER (title); И если для данной таблицы определен триггер уровня записи, он будет запу­щен 1000 раз.

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

Псевдозапись OLD. Структура данных с именем OLD, которая так же выгля­дит и обладает такими же свойствами, как и запись таблицы. Эта псевдозапись Доступна только внутри триггеров обновления и удаления; она содержит зна­чения модифицируемой записи до внесения изменений.

Синтаксис оператора создания триггера:

CREATE TRIGGER <имя_триггера> {BEFORE | AFTER} <событие_БД>

ON <имя_таблицы> FOR EACH ROW

BEGIN

<операторы>

END

<событие_БД> - определение типа DML-инструкции, с которой связывается триггер: INSERT, UPDATE или DELETE. У каждой таблицы для каждого события может существовать только один триггер.

Триггер, осуществляющий проверку веса детали при добавлении ее в таблицу Parts (вес не должен превышать заданного значения)

CREATE TRIGGER Check_Weight BEFORE INSERT ON Parts FOR EACH ROW

BEGIN

DECLARE Wrong_weight CONDITION FOR SQLSTATE ‘45000’;

IF NEW.Weight > 1000 THEN

SIGNAL Wrong_weight SET MESSAGE_TEXT = ‘Вес детали превышает 1000!’;

END IF;

END

Триггер, осуществляющий проверку на совпадение наименований деталей:

CREATE TRIGGER Check_Part_Name BEFORE INSERT ON Parts FOR EACH ROW

BEGIN

DECLARE Duplicate_part_name CONDITION FOR SQLSTATE ‘45000’;

DECLARE N INTEGER;

SELECT COUNT(*) INTO N FROM Parts WHERE Part_name=NEW. Part_name;

IF N > 0 THEN

SIGNAL Duplicate_part_name SET MESSAGE_TEXT = ‘Такая деталь уже есть в базе!’;

END IF;

END

Триггер, который удаляет все детали, наименование которых совпадает с удаляемой деталью (не будет работать):

CREATE TRIGGER Delete_the_same_parts AFTER DELETE ON Parts FOR EACH ROW

BEGIN

DELETE FROM Parts WHERE Part_name=OLD.Part_name;

END

https://doc.prototypes.ru/database/mysql/triggers/info/

4.3. Задание на лабораторную работу

1. Реализовать триггер, автоматически добавляющий к имени детали символ ‘-‘ и порядковый номер детали (с учетом уже имеющихся одноименных деталей в базе) при добавлении каждой новой детали в таблицу. Т.е. детали должны получать уникальные имена «Деталь-1», «Деталь-2», «Деталь-3» и т.д.

2. Реализовать триггер, ограничивающий максимальную суммарную стоимость всех деталей, поставляемых для определенного проекта.

3. Реализовать триггер, ограничивающий максимальный суммарный вес всех деталей, поставляемых для определенного проекта.

4. Реализовать триггер, который разрешает только поставки, в которых поставщик и проект находятся в одном городе.

5. Реализовать триггер, который удаляет проект при удалении последней поставки для данного проекта.

4.4. Содержание отчета

В отчете привести код триггеров, содержимое таблиц до модификации, результаты выполнения операций модификации (добавления, удаления) в случае нормального выполнения и в случае нарушения дополнительных ограничений целостности.

Лабораторная работа 5. Работа с сервером МуSQL в Visual Studio 2010

5.1. Введение

Для работы потребуется установить MS Visual Studio 2010 и ADO.NET драйвер MySQL Connector/Net (https://dev.mysql.com/downloads/connector/net/).

Документация по работе с MySQL Connector/Net расположена по адресу
https://dev.mysql.com/doc/refman/5.1/en/connector-net.html

5.2. Создание проекта и подключение к базе данных

Создайте новый проект типа «Консольное приложение C#». Имя проекта должно содержать ваш логин, например 1204abc_lab5:

В контекстном меню вкладки «Общие» панели элементов выберете пункт «Выбрать элементы»:

В открывшемся списке выбрать элементы MySQLConnection, MySQLDataAdapter, MySQLCommand:

Убедиться в том, что компоненты появились на панели элементов:

В меню «Проект» выберете пункт «Добавить форму Windows» и создайте новую форму (Form1):

Для созданной формы задайте свойство «Text», в панели элементов найдите и поместите на форму элементы MenuStrip, MySQLConnection, MySQLDataAdapter, MySQLCommand: Создайте пункт меню «Подключение»:

Добавьте в проект форму для ввода параметров подключения к БД (Form2). Для созданной формы задайте свойство «Text». Поместите на форму элементы label, textBox, comboBox, Button как показано на рисунке:

В модуль формы Form1 добавьте обработчик меню для вызова формы Form2:

В модуль формы Form2 добавьте директивы using для использования пространств имен:

Определите переменные:

Создайте обработчик кнопки «Проверить»:

Создайте обработчик кнопки «Подключиться»:

5.3. Просмотр и редактирование данных при помощи dataGridView

Добавьте в проект новую форму (Form3). Для созданной формы задайте свойство «Text». Поместите на форму элементы dataGridView, Button как показано на рисунке:

Аналогично предыдущей форме в модуль формы Form1 добавьте обработчик меню для вызова формы Form3, в модуль формы Form3 добавьте директивы using для использования пространств имен.

Определите конструктор формы:

Для свойства SelectionMode элемента dataGridView задайте значение FullRowSelect. Создайте обработчики кнопок «Удалить» и «Сохранить»:

Проверьте работу формы в режиме добавления и удаления данных.

5.4. Визуальное проектирование интерфейса для работы со связанными данными

Добавьте в проект новую форму (Form4). Для созданной формы задайте значение «Поставщики» для свойства «Text».

В системном меню «Данные» выберите пункт «Показать источники данных». В появившемся окне «Источники данных» нажмите на кнопку «Добавить новый источник данных». В запустившемся мастере настройки выберите тип источника «База данных», нажмите кнопку «Далее»:

Выберите модель «Набор данных», нажмите кнопку «Далее»:

Нажмите кнопку «Создать подключение», измените источник данных на «MySQL Database (MySQL Data Provider)». Введите имя сервера, имя пользователя, пароль (запомнить) и имя БД. Проверьте подключение:

В мастере настройки нажмите кнопку «Далее»:

Выберите таблицы «Поставщики» и «Города», нажмите кнопку готово:

В окне «Источники данных» раскройте дерево объектов. При помощи выбора значений из выпадающего списка установите для таблиц элемент управления «Таблица», для внешнего ключа таблицы «Поставщики» – «ComboBox», для остальных полей – «TextBox».

Перетащите на форму дочернюю таблицу «Поставщики». На форме появятся элементы управления с подписями, панель навигатора, а также невизуальные элементы DataSet, TableAdapter, BindingSource, TableAdapterManager. Установите значение свойства «Text» для элементов Label:

Аналогично предыдущей форме в модуль формы Form1 добавьте обработчик меню для вызова формы Form4. Запустите приложение и проверьте работу формы.

Для создания подстановки наименований городов в выпадающий список ComboBox перетащите родительскую таблицу «Города» непосредственно на элемент «ComboBox». Запустите приложение и проверьте работу формы.

Для исключения ошибки, возникающей при добавлении новой записи в окне «Источники данных» нажмите кнопку «Изменить набор данных в конструкторе». Для первичного ключа таблицы «Поставщики» задайте значение атрибута, а для «AutoIncrement» – True:

Запустите приложение и проверьте работу формы.

5.5. Работа с изображениями в БД

Добавить в таблицу «Детали» поле типа LONGBLOB для хранения изображений:

В окне «Источники данных» нажать кнопку «Настроить источник данных при помощи мастера». Добавить в источник данных таблицу «Детали»:

Добавьте в проект новую форму (Form5). Для созданной формы задайте значение «Детали» для свойства «Text».

В окне «Источники данных» раскройте дерево объектов. При помощи выбора значений из выпадающего списка установите для таблицы элемент управления «Таблица», для поля LONGBLOB – «Image», для остальных полей – «TextBox».

Перетащите на форму таблицу «Детали». На форме появятся элементы управления с подписями, панель навигатора, а также невизуальные элементы DataSet, TableAdapter, TableAdapterManager, BindingSource. Установите значение свойства «Text» для элементов Label. Установите для свойства «ReadOnly» элемента TextBox c номером детали значение «true». Установите для свойства «SizeMode» элемента imagePictureBox значение «Zoom». Добавьте на форму кнопку, установить для нее свойство «Text».

Создайте обработчик нажатия на кнопку со следующим кодом:

Проверьте работу формы в режимах просмотра, редактирования и добавления деталей.

5.6. Задание на лабораторную работу

1. Выполнить этапы создания приложения для работы с БД в среде Visual Studio C# в соответствии с пунктами 5.2 – 5.5. В отчете привести скриншоты разработанных форм в режиме разработки и выполнения, программный код модулей форм.

Лабораторная работа 6. Разработка web-приложений для MySQL на языке PHP

6.1. Установка и настройка Denver

https://www.denwer.ru/base.html

Создание виртуальных хостов.

6.2. Соединение с БД и вывод данных

<html>

<boby>

Файл <b>index.php</b>

<br>

<?php

echo "Проверка работы с БД на PHP<br>";

$db=mysql_connect("localhost","kda","kda");

mysql_select_db("db1",$db);

mysql_query("set names cp1251",$db);

$result=mysql_query("select * from parts",$db);

$myrow=mysql_fetch_array($result);

do

{

echo "Деталь № ".$myrow['Part_ID']." ";

echo $myrow['Part_name']." ";

echo $myrow['Material']." ";

echo $myrow['Weight']." ";

echo "<br>";

}

while($myrow=mysql_fetch_array($result));

echo "<br>";

mysql_data_seek($result, 0);

do

{

printf ("Деталь № %s %s из материала %s весом %s<br>",$myrow['Part_ID'],$myrow['Part_name'],$myrow['Material'],$myrow['Weight']);

}

while($myrow=mysql_fetch_array($result));

?>

</boby>

</html>

6.3. Задание на лабораторную работу

1. Создать веб-страницу для просмотра таблицы «Проекты». Вывод данных организовать с использованием html-таблицы. В таблице выводить наименования городов.

2. Создать веб-страницу с формой для ввода данных нового проекта. Для указания города проекта использовать тэг <select> c выбором городов из таблицы «Города».

3. Создать php-скрипт для добавления нового проекта в таблицу проекты.

4. Защитить веб-страницу с формой и php-скрипт для добавления нового проекта проверкой имени пользователя и пароля. Для хранения имен пользователей и паролей создать дополнительную таблицу в БД.

^[1] Ключевые поля подчеркнуты и выделены жирным шрифтом

Нормативные ссылки

В разработанных методических указаниях использованы ссылки на следующие стандарты:

ГОСТ 2.102 - 68 ЕСКД. Виды и комплектность конструкторских до­кументов.

ГОСТ 2.105 - 95 ЕСКД. Общие требования к текстовым документам.

ГОСТ 2.109 - 73 ЕСКД. Основные требования к чертежам.

ГОСТ 2.301 - 68 ЕСКД. Форматы.

ГОСТ 2.302 - 68 ЕСКД. Масштабы.

ГОСТ 2.303 - 68 ЕСКД. Линии.

ГОСТ 2.306 - 68 ЕСКД. Обозначения графических материалов и пра­вила их нанесения на чертежах.

ГОСТ 2.307 - 68 ЕСКД. Нанесение размеров и предельных отклоне­ний.

ГОСТ 2.316 - 68 ЕСКД. Правила нанесения на чертежах надписей, технических требований и таблиц.

СНИП П-3-79 Строительная теплотехника, М.:1995 г.

СНИП 23-01-99 Строительная: климатология, М.: 2000 г.

Задание на проектирование

Выполнить расчет системы отопления и вентиляции типового 3-этажного жилого дома. Система отопления однотрубная, водяная с пара­метрами теплоносителя 95/70 °С, подключена через тепловой пункт с эле­ватором к наружным тепловым сетям от ТЭЦ с параметрами теплоносите­ля 130/70 °С.

Вариант задания принять по двум последним цифрам зачетной книжки из таблицы 1.1.

Оформление выполнить в соответствии с требованиями ЕСКД.

Структура расчетно-пояснительной записки содержит следующие разделы:

■ Титульный лист;

■ Задание на проектирование;

■ Реферат;

■ Содержание;

■ Нормативные ссылки;

■ Введение;

■ Основная часты'

1 Расчет теплопотерь через ограждающие конструкции.

1.1 Краткое описание здания, основных его частей и конструкций.

1.2 Определение массивности здания и расчетной температуры.

1.3 Определение толщины и состава слоев наружных стен.

1.4 Проверка массивности ограждения.

1.5 Расчет чердачного перекрытия.

1.6 Проверка на отсутствие конденсации.

1.7 Выбор конструкции заполнения световых проемов.

1.8 Расчет полов.

1.9 Расчет теплопотерь помещениями здания.

1.10 Определение удельной тепловой характеристики.

1.11 Определение годового расхода тепла и топлива на отопление здания.

2 Расчет систем отопления здания.

2.1 Краткое описание проектируемой системы отопления.

2.2 Выбор типа и расчет поверхности нагревательных приборов

2.3 Гидравлический расчет системы отопления.

2.4 Описание места расположения теплового пункта и его расчет.

3 Расчет системы вентиляции

3.1 Обоснование принимаемой системы вентиляции и её описание. -

3.2 Определение воздухообмена по помещениям.

3.3 Расчет воздуховодов и проверка правильности расчета. Список использованных источников.

Записка выполняется на листах формата А4 по ГОСТ 2.301 - 68 ЕСКД, ГОСТ 2.105 - 95 ЕСКД в количестве 25-30 листов. Графическая часть вы­полняется на листе формата А1 ГОСТ 2.301 - 68 ЕСКД и содержит:

- план типового этажа с нанесением на нем элементов системы отопле­ния и вентиляции в масштабе 1:100 по ГОСТ 2.302 - 68 ЕСКД;

- для системы с нижней разводкой план подвала, а для системы с верх­ней разводкой план чердака, с нанесением на нем элементов системы ото­пления и вентиляции в масштабе 1:100 по ГОСТ 2.302 - 68 ЕСКД;

- аксонометрическую схему системы отопления в масштабе 1:100 по ГОСТ 2.302 - 68 ЕСКД;

- схему теплового пункта с нанесением оборудования;

- аксонометрическую схему вытяжной системы вентиляции в масштабе 1:50 по ГОСТ 2.302 - 68 ЕСКД.

Таблица 1.1 – Климатологические данные

Номер варианта	Наименование пункта	Средняя температура наиболее холодной пятидневки	Средняя температура наиболее холодных суток	Расчетная температура для проектирования вентиляции	Ориентация здания	Тип системы отопления	Отопительный период, суток	Средняя температура отопительного периода,
	Барнаул	-39	-43	-23	С	ВР		-8,3
	Бийск	-38	-42	-22	СВ	НР		-8,7
	Благовещенск	-34	-37	-27	В	ВР		-11,3
	Архангельск	-32	-36	-17	ЮВ	НР		-4,7
	Котлас	-33	-39	-19	Ю	ВР		-5,5
	Астрахань	-22	-26	-11	ЮВ	НР		-1,8
	Стерлитамак	-36	-38	-20	З	ВР		-6,4
	Уфа	-29	-29	-19	СЗ	НР		-6,6
	Белгород	-23	-28	-12	С	ВР		-2,2
	Ржев	-29	-34	-14	СВ	НР		-6
	Владимир	-27	-33	-16	В	ВР		-4
	Вологда	-22	-29	-16	ЮВ	НР		-4,2
	Череповец	-31	-36	-16	Ю	ВР		-4,3
	Воронеж	-25	-30	-14	ЮВ	НР		-3,4
	Дербент	-10	-13	-2	З	ВР		+3,8
	Махачкала	-14	-19	-4	СЗ	НР		+2,6
	Бодайбо	-47	-49	-37	С	ВР		-14,6
	Братск	-43	-46	-30	СВ	НР		-10,3
	Нижнеудинск	-39	-43	-27	В	ВР		-8,8
	Тайшет	-40	-44	-25	ЮВ	НР		-8,4
	Нальчик	-17	-21	-8	Ю	ВР		-0,4
	Калининград	-18	-22	-6	ЮВ	НР		-2,2
	Калинин	-29	-33	-14	З	ВР		-3,4
	Элиста	-23	-23	-10	СЗ	НР		-1,8
	Петропаловск	-23	-24	-13	С	ВР		-2,1
	Гурьев	-24	-30	-23	СВ	НР		-3,8
	Ухта	-28	-34	-23	В	ВР		-3,5
	Краснодар	-19	-23	-6	ЮВ	НР		+1,5
	Майкоп	-17	-20	-5	Ю	ВР		+1,7
	Новороссийск	-13	-19	-4	ЮВ	НР		+4,4
	Сочи	-3	-5		З	ВР		+5,9
	Тихорецк	-22	-26	-8	СЗ	НР		+0,2

Продолжение таблицы 1.1

---

---

	Абакан	-42	-43	-26	С	ВР		-9,6
	Ачинск	-41	-46	-23	СВ	НР		-7