Тема  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(UserId, Pass) — зарегистрировать нового пользователя. Возвращает истину, если удалось и ложь — в противном случае.
    2. ManageFlight(UserId, Pass, 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.