Microsoft SQL Server

ID_CUST=CurrTickets.Id_Cust;Рис. 8. Результат вызова представления TempBuses.2.3. Создание хранимых процедур и триггеровХранимые процедуры в MS SQL Server похожи на процедуры в других языках программирования.Хранимые процедуры – это набор инструкций, выполняющихся как единое целое. Хранимая процедура по сути SQL-код, который можно сохранить и повторно использовать каждый раз, как возникнет потребность.Хранимые процедуры могут просто выполнять последовательность SQL-инструкций, а могутвыполнятьцелую программу, с собственной логикой (операторы IF) и циклами. Процедура может принимать параметры (заранее определенные переменные) и использовать их в своих расчетах.В хранимой процедуре можно:использовать операторы, которые выполняют любые операции в базе данных (выборка, вставка, изменение или удаление данных), включая возможность вызова других встроенных процедур;использовать входные параметры;возвращать статус выполнения в вызывающую процедуру или модуль для отображения удачного или ошибочного выполнения;возврат нескольких значений в вызывающую процедуру или модуль в форме выходных параметров.Хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект, позволяя упростить управление и ускорить производительность БД. Также хранимые процедуры позволяют ограничить доступ к данным в таблицах и таким образом уменьшить вероятность нежелательных действий в отношении этих данных.Триггеры являются специальным типом хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением.Триггер связывается с определенной таблицей и чаще всего выполняет защитную роль для данных. Существуют три события, на которые могут реагировать триггеры – добавление, изменение и вставка данных. Если на какое-то из этих действий для таблицы объявлен триггер, то при попытке вставки, обновления или удаления данных в таблице, триггер вызывается автоматически. Его нельзя обойти. В отличие от хранимых процедур, триггеры не могут вызываться напрямую и не получают или принимают параметры.Триггеры могут:делать каскадные изменения зависимых таблиц в базе данных;объявлять индивидуальные сообщения об ошибках;содержать не нормализованные данные;сравнивать состояние данных до, и после изменения.В базе данных создадим несколько хранимых процедур и триггеров.Хранимая процедура, вызывающая список рейсов на заданное число:CREATEPROCEDURE ListRoutes (@date_rdate)ASSELECT*fromRouteswheredate_r=@date_r;Сумма проданных билетов на заданное число(рис. 9):CREATEPROCEDURE SumTickOnDate (@ddate)ASBEGINSELECTid_tick,place,price,baggage,date_r,Tickets.id_rINTO#tmpfromTickets,RouteswhereRoutes.ID_R=Tickets.ID_RSELECTsum(price)ASSumTickFROM#tmpWHEREdate_r=Convert(date,@d)ENDРис. 9. Результат выполнения хранимой процедуры SumTickOnDate.Маршрутпо номерус указанием пунктов назначения:CREATEPROCEDURE NumItinerary (@numNVARCHAR(5))ASBEGINSELECTid_itn,num_r,n_nameasname_dest,id_dep,DISTANCEINTO#tmpfromitinerary,townswhereid_dest=id_townANDnum_r=@num;SELECTid_itn,num_r,name_dest,n_nameasname_dep,towns.DISTANCEfrom#tmp,townswhereid_dep=id_townANDnum_r=@num;ENDСписок маршрутовс указанием пунктов назначения:CREATEPROCEDURE ListItineraryASBEGINSELECTid_itn,num_r,n_nameasname_dest,id_depINTO#tmpfromitinerary,townswhereid_dest=id_town;SELECTid_itn,num_r,name_dest,n_nameasname_dep,from#tmp,townswhereid_dep=id_town;ENDСписок водителей рейсов на указанное число (рис. 10).CREATEPROCEDURE ListDriverOnDate (@ddate)ASBEGINSELECTid_auto,fio,phoneINTO#tmpfromEmployees,BuseswhereBuses.id_driver=Employees.id_emp;SELECTdate_r,fio,phonefromRoutes,#tmpwhere#tmp.id_auto=Routes.id_autoanddate_r=convert(date,@d);ENDРис. 10. Результат выполнения хранимой процедурыListDriverOnDate.Список купивших билеты на рейсы заданного дня (рис. 11):CREATEPROCEDURE ListClientsOnDate (@ddate)ASBEGINSELECTid_tick,place,price,baggage,date_r,Tickets.id_r,Id_CustINTO#tmpfromTickets,RouteswhereRoutes.ID_R=Tickets.ID_RANDdate_r=convert(date,@d);SELECTfio,passport,date_rfromCustomers,#tmpWHERECustomers.ID_CUST=#tmp.Id_Cust;ENDРис. 11. Результат выполнения хранимой процедуры ListClientsOnDate.Удаление данных из таблиц БД производится каскадно, поэтому при удалении водителя будут удалены данные и об автобусе. Создадим хранимую процедуру удаления водителя с предварительной заменой удаляемого водителя на указанного в таблице Buses.CREATEPROCEDURE del_Driver(@NameNVARCHAR(80),@NewDriverNVARCHAR(80))ASDECLARE@idintDECLARE@id_newintSELECT@id=id_empFROMEmployeesWHEREFIO=@NameSELECT@id_new=id_empFROMEmployeesWHEREFIO=@NewDriverIF(@id>0AND@id_new>0)BEGINUPDATEBusesSETId_driver=@id_newWHEREId_driver=@idDELETEFROMEmployeesWHEREid_emp=@idENDELSEPRINT'Такой сотрудник не существует!'Создадим триггер, запрещающий удалять должность директора из списка должностей:CREATETRIGGERdel_DirectorONdbo.PostsFORDELETEASDECLARE@postINTSELECT@post=id_postFROMdeletedIF@post=6BEGINPRINT'ОШИБКА!Нельзя удалить должность директора!'ROLLBACKTRANSACTIONENDСоздадим триггер, запрещающий добавлять в список городов Москву:CREATETRIGGERins_townsONdbo.townsFORINSERTASDECLARE@NameVARCHAR(20)SELECT@Name=N_NAMEFROMinsertedIF@Name='Москва'BEGINPRINT'ОШИБКА'ROLLBACKTRANSACTIONENDСоздадим триггер, ограничивающий ввод в расписание записей, в которых время прибытия меньше времени отправления:CREATETRIGGERins_scheduleONdbo.scheduleFORINSERTASDECLARE@time1timeDECLARE@time2timeSELECT@time1=time_depFROMinsertedSELECT@time2=time_arrFROMinsertedIF@time2<@time1BEGINPRINT'ОШИБКА!Время прибытия не может быть меньше времени отправления!'ROLLBACKTRANSACTIONENDТаким образом, в настоящей курсовой работе была спроектирована и разработана база данных «Автовокзал» в соответствиии с предметной областью, заданными входными и выходными данными. База данных содержит представления, хранимые процедуры и триггеры.Работу с базой данных можно осуществлять посредством любого менеджера баз данных, позволяющего подключиться к MSSQLServer, например, SQL Server Management Studio или подобной ей и тоже бесплатной программе HeidiSQL. В качестве дальнейшего развития можно создать полноценную программную оболочку с удобными формами для ввода данных и вывода информации на каком-либо языке высокого уровня.ЗаключениеВ данной курсовой работе была разработана база данных «Автовокзал».Разработанная база данных содержит следующую информацию: данные о рейсе (дата, номер рейса, пункт отправления, пункт назначения, водитель ТС, время отбытия, время прибытия), сведения о сотрудниках (ФИО, паспортные данные, номер телефона, адрес, должность, оклад), данные о билетах (рейс, место, стоимость, данные покупателя (ФИО, паспортные данные, номер телефона)), данные об автобусе (номер автобуса,марка, количество мест,дата последнего техосмотра).Разработанная база данных позволяет добавлять, изменять, удалять и просматривать все имеющиеся и вводимые данные. Созданные таблицы позволяют просматривать и корректировать обозначенную в первой главе входную информацию (данные о рейсах, автобусах, водителях, диспетчерах, проданных билетах). Созданные в процессе работы представления позволяют выводить актуальную информацию, в частностиинформацию о водителях, вышедших в рейсы на текущее число, информацию об автобусах на рейсах, информацию об имеющихся рейсах на текущее число, проданных на текущее число билетах, общей сумме продаж за текущее число.Созданные в процессе работы хранимые процедуры позволяют получить информацию об имеющихся рейсах, вышедших водителях, проданных билетах, клиентах, купивших билеты, общей сумме продажза указанное число, узнать пункты назначения по номеру рейса, удалить водителя из БД с заменой для автобуса на другого водителя.Созданные в процессе работы триггеры ограничивают ввод данных и удаление, в частности, осуществлен запрет ввода в расписании времени прибытия, меньшего, чем время отправления, запрет удаления должности директора из базы данных.Данная курсовая работа является актуальной и была разработана с использованием MSSQLServer 2019 и менеджера баз данных HeidiSQL.Дальнейшее развитие базы данных может предполагать создание полноценного приложения, разработанного на одном из языков программирования высокого уровня, позволяющего использовать разработанную базу данных пользователю любого уровня подготовки в области информационных технологий.Список литературыBasic help on using HeidiSQL. [Электронный ресурс] – Режим доступа: https://www.heidisql.com/help.phpГарсиа-Молина, Гектор. Системы баз данных : Полный курс / Гектор Гарсиа-Молина, Джеффри Д. Ульман, Дженнифер Уидом ; [Пер. с англ. и ред. А.С. Варакина].– М.: Издательство «Вильямс», 2017 г. – 1088 с.Грабер,Мартин. SQL для простых смертных. – М.: Издательство «ЛОРИ», 2020 г. – 389 с.Документация по SQL. Представления. [Электронный ресурс] – Режим доступа: https://docs.microsoft.com/ru-ru/sql/relational-databases/views/views?view=sql-server-ver15Документация по SQL. Справочник по Transact-SQL (T-SQL) [Электронный ресурс] – Режим доступа: https://docs.microsoft.com/ru-ru/sql/t-sql/language-reference?view=sql-server-ver15Осетрова И.С., Разработка баз данных в MS SQL Server 2014. - СПб: Университет ИТМО, 2016. – 114 с.[Электронный ресурс] – Режим доступа: https://books.ifmo.ru/file/pdf/2141.pdfНОУ ИНТУИТ. Работа с базами данных. Лекция 6: Реляционный подход к построению инфологической модели. [Электронный ресурс] – Режим доступа: https://intuit.ru/studies/courses/3439/681/lecture/14024Триггеры. [Электронный ресурс] – Режим доступа:https://metanit.com/sql/sqlserver/12.1.phpФорта,Бен.SQL за 10 минут, 5-е изд.: Пер. с англ. – М.: ООО «Вильямс»,2021. –352 с.Хранимые процедуры. [Электронный ресурс] – Режим доступа:https://www.flenov.info/books/read/transact-sql/41Что такое база данных?[Электронный ресурс] – Режим доступа:https://www.oracle.com/ru/database/what-is-database/ПРИЛОЖЕНИЕ 1