Базы данных/Лабораторная работа 4 — различия между версиями

Версия 17:14, 26 мая 2016

Цели лабораторной работы:

• Знакомство с языком PL/pgSQL
• Создание процедур, функций и триггеров, обеспечивающих удобство работы с базой и обеспечение целостности
• Использование транзакция для обеспечения целостности

Зачем использовать PL/pgSQL

PL/pgSQL - процедурный язык программирования, позволяющий выполнять несложные скрипты. Язык PL/pgSQL является расширенной версией стандарта языка PL/SQL. В PostgreSQL есть также возможность расширить синтаксис встроенного языка за счет интерпретаторов популярных языков: Python, Perl, Java, Lua, R и тд.

Основное преимущество использования PL/pgSQL состоит в том, что скрипты выполняются непосредственно на сервере СУБД в отличии от любых типов взаимодействия, которые предполагают, что скрипт выполняется на стороне клиента, лишь через драйвер взаимодействуя с сервером, передавая ему запросы и получая ответы. Таким образом, PL/pgSQL востребован для операций с интенсивным вводом/выводом информации.

К наиболее типичным задачам, реализуемым на PL/SQL относятся:

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

Также к преимуществам использования PL/SQL является использование одного языка программирования для выполнения запросов и для выполнения инструкций.

Как правило, язык PL/pgSQL не используют в задачах, где некритичны его преимущества из-за относительно сложного процесса поддержки и нагрузки на сервер СУБД.

Процедуры и функции

Отличие процедур и функций состоит лишь в том, что процедуры не возвращают никаких значений, в отличии от функций. И процедуры и функции могут принимать входные параметры.

Но в PostgreSQL нет различий в синтаксисе создания функций и процедур, обе создаются с помощью ключевых слов create function.

Пример создания процедуры:

 CREATE OR REPLACE FUNCTION add_movie(title VARCHAR(70), production_year INTEGER) 
 RETURNS void AS $$
   DECLARE phonetic_code VARCHAR(70);
 BEGIN
   SELECT soundex(title) INTO phonetic_code;
   RAISE NOTICE 'The phonetic code for % is %', title, phonetic_code;
   INSERT INTO title (title, production_year, phonetic_code, kind_id) VALUES (title, production_year, phonetic_code, 1);
   COMMIT;
 END;
 $$ LANGUAGE plpgsql;

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

В примере также используется soundex, которая доступна после подключения модуля (выполните до создания процедуры):

 imdb=# create extension fuzzystrmatch;

Чтобы выполнить созданную процедуру, выберите ее:

 select add_movie('How I will pass all the exams', 2016);

Этот пример сейчас скорее всего не сработает с ERROR: duplicate key value violates unique constraint "title_pkey". В конце этой части есть процедура, которая поможет вам добавить фильм.

Определение переменных

Определение переменных происходит с помощью команды DECLARE, после которой нужно указать имя и тип. Также можно сразу присвоить значение:

 DECLARE production_year =: 2016;

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

Чтобы записать в переменную кортеж или одно значение, нужно использовать SELECT ... INTO var:

  SELECT count(1) INTO actors_num from cast_info WHERE ...

Управляющие конструкции

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

 BEGIN
   SELECT * INTO movie FROM title WHERE title like '%'||movie_name||'%';
   EXCEPTION
       WHEN NO_DATA_FOUND THEN
           RAISE EXCEPTION 'movie % not found', movie_name;
       WHEN TOO_MANY_ROWS THEN
           RAISE EXCEPTION 'movie % not unique', movie_name;
 END;

В этом примере также есть конструкция title like '%'||movie_name||'%', в которой movie_name - переменная, хранящая часть название фильма. Конкатенация строк выполняется оператором ||.

Также доступны циклы и условия:

   FOR movie_row IN SELECT * FROM title limit 10 LOOP
       RAISE NOTICE 'Currently working on movie %s ...', quote_ident(movie_row.title);
       IF movie_row.production_year > 2000 THEN
           RAISE NOTICE 'This movie is of this century';
       END IF;
   END LOOP;

Эта конструкция проходит по кортежам, возвращаемым запросом SELECT * FROM title limit 10, для каждого выводит его название и, если фильм выпущен после 2000 года, то сообщение о его новизне.

Использование курсоров

Логирование и обработка ошибок

Вывод информационного сообщения:

 RAISE NOTICE 'Data processing...'

Команда завершающая процедуру или транзакцию с ошибкой:

 RAISE EXCEPTION 'Something went wrong'

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

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

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

Триггеры

Триггеры - это процедуры, которые срабатывают в случае наступления в системе какого-либо события. В триггерах доступны переменные, связанные с происходящим событием.

Типы триггеров

Триггер может быть привязан к командам UPDATE, INSERT и DELETE конкретных отношений. Также триггер может быть запущен как до выполнения указанной операции, так и после. Например:

 CREATE TRIGGER cool_trigger_name BEFORE INSERT OR UPDATE ON movie_info
 ...

Этот триггер выполнится перед операциями добавления и обновления данных в таблице movie_info.

Транзакции

Задания лабораторной работы

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

2. Создать функцию с входным параметром - имя актера. Если актер с таким именем не найден, функция должна вернуть 0. Если найден, то вычислить через person_info birth date (info_type_id=21) и death date (info_type_id=23) возраст актера. Если актер еще не умер, то вычислить, сколько сейчас лет. Функция должна вернуть целое число прожитых лет.

3. Используя функцию, созданную ранее, создайте процедуру, которая по имени актера выводит в лог текст следующего содержания:

 Name: ...
 Nicknames: nickname1, nickname2, ... строка выводится только если у человека есть клички, выводятся все клички через запятую
 Age: ... вычисляется из функции
 First appear: ... фильм и год выхода, в котором актер впервые снялся.

Если актер не найден или не удалось определить его первую роль, то процедура должна завершиться с ошибкой и вывести в лог "Invalid data"

Защита лабораторной работы

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

Дополнительно

• http://www.postgresql.org/docs/9.3/static/plpgsql.html
• http://www.postgresql.org/docs/9.3/static/tutorial-transactions.html
• http://www.postgresql.org/docs/9.3/static/plpython.html - позволяет писать хранимые процедуры на питоне, нужно включить расширение