Тема  9. Хранимые процедуры

Содержание

  1. Хранимые процедуры
  2. Операторы
    1. Составной и присваивания
    2. Ветвления
    3. Циклы
    4. Обработка ошибок
  3. Курсоры
  4. Хранимые функции
  5. Управление доступом

Слайды

HTML

Экзаменационные вопросы

  1. Хранимые процедуры и функции. Сходства и различия
  2. Императивное подмножество SQL
  3. Data Control Language

Практические навыки

  1. Реализация хранимых процедур и функций
  2. Реализация сложных триггеров
  3. Управление правами доступа

Библиография

  1. Дейт К. Введение в системы баз данных (главы 4 и 7)
  2. Уидом Д., Ульман Д. Основы реляционных баз данных (раздел 7.4)
  3. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 25 – SQL-Invoked Routine)
  4. Gulutzan P., Pelzer T. SQL-99 complete, really (Chapter 26 – PSM: Not Just Persistent Stored Modules)
  5.  MySQL 8.0: CREATE PROCEDURE and CREATE FUNCTION Statements

Домашнее задание 9. Хранимые процедуры

Хранимые процедуры

В базе данных Airline информация о рейсах самолётов задана в виде таблиц

    Flights(
        FlightId integer,
        FlightTime timestamp,
        PlaneId integer,
        -- Дополнительные столбцы, при необходимости
    )
    Seats(
        PlaneId integer,
        SeatNo varchar(4), -- 123A
        -- Дополнительные столбцы, при необходимости
    )

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

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

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

  1. Администрирование. Для вызова этих процедур нужно иметь права администратора.
    1. RegisterUser(AdminId, AdminPass, UserId, Pass) — зарегистрировать нового пользователя.
    2. ManageUser(AdminId, AdminPass, UserId, IsAdmin) — выдать/забрать права администратора.
    3. ManageFlight(AdminId, AdminPass, FlightId, SellAllowed, ReservationAllowed) — изменить настройки рейса. Примечание: автоматические настройки имеют более высокий приоритет.
  2. Покупка и бронирование.
    1. FreeSeats(FlightId) — список мест рейса, доступных для продажи/бронирования.
    2. Reserve(UserId, Pass, FlightId, SeatNo) — бронирует место на сутки начиная с момента бронирования.
    3. ExtendReservation(UserId, Pass, FlightId, SeatNo) — продлевает бронь места на сутки начиная с момента продления.
    4. BuyFree(FlightId, SeatNo) — покупает свободное место.
    5. BuyReserved(UserId, Pass, FlightId, SeatNo) — выкупает забронированное место
  3. Статистика (административные действия).
    1. FlightsStatistics(UserId, Pass) — статистика по рейсам: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
    2. FlightStat(UserId, Pass, FlightId) — статистика по рейсу: возможность бронирования и покупки, число свободных, забронированных и проданных мест.
  4. CompressSeats(UserId, Pass, FlightId) (административныое действие) — оптимизирует занятость мест в самолете. В результате оптимизации, в начале самолета должны быть купленные места, затем — забронированные, а в конце — свободные. Примечание: клиенты, которые уже выкупили билеты, также должны быть пересажены.

Для возврата результата (успех/провал) из изменяющих процедур можно использовать один из двух подходов (но не их смесь).

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

Форма для сдачи ДЗ

В рамках проекта:

  1. Определите хранимые процедуры и функции, необходимые для работы проекта.
  2. Реализуйте хранимые процедуры (функции) на языке SQL.