Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Функции в PostgreSQL. Проектирование структуры БД.
Цель работы: Изучить правила создания функций. Приобрести практические навыки создания функций в среде PostgreSQL. Научиться проектировать БД в целом на основе поставленного задания с использованием всех полученных ранее базовых знаний и навыков.
Задание. Общая часть: Ознакомиться с теоретическими сведениями о возможностях создания пользовательских функций в PostgreSQL. Разработать БД в соответствии с индивидуальным заданием. Создать функции, реализующие интерфейс для работы с базой данных. Проверить работоспособность функций путем выполнения этих функций с параметрами, обеспечивающими как успешное выполнение функции, так и невыполнение функции.
Обязательные требования к БД:
Создать функции, реализующие интерфейс для работы с базой данных. Проверить работоспособность функций путем выполнения этих функций с параметрами, обеспечивающими как успешное выполнение функции, так и невыполнение функции.
Вариант 1: База данных хоккейной лиги. Должна содержать следующие данные: составы команд и информацию о каждом игроке, проведенные игры с информацией о проданных билетах и затраченных средствах. Предусмотреть анализ следующих показателей: популярность команд, рейтинг и эффективность игроков за указанный период.
Вариант 2: База данных сети магазинов продуктов питания. Должна содержать следующие данные: информация о магазинах и имеющихся запасах продуктов, данные о продажах. Предусмотреть анализ следующих показателей: рейтинг популярности товаров, сумма среднего чека по магазину в разное время суток.
Вариант 3: База данных транспортной компании. Должна содержать следующие данные: информацию о водителях, поставщиках товаров и потребителях, завершенные и незавершенные доставки. Предусмотреть анализ следующих показателей: среднее время простоя водителя, соотношение доходности междугородних и местных доставок.
Вариант 4: База данных сети книжных магазинов. Должна содержать следующие данные: текущие складские запасы печатной продукции, информацию о заказах и продажах. Предусмотреть анализ следующих показателей: наиболее часто заказываемые книги, средний чек по разным группам товаров.
Вариант 5: База данных сети автосалонов. Должна содержать следующие данные: информацию об автосалонах, продавцах-консультантах, имеющихся в наличии и проданных автомобилях. Предусмотреть анализ следующих показателей: рейтинг продаж для продавцов-консультантов по различным моделям, рекомендации к заказу моделей на основании имеющегося запаса и популярности модели.
Вариант 6: База данных складского комплекса. Должна содержать следующие данные: данные о товарах, данные о складах, имеющихся товарах и выполненных и невыполненных заявках на них. Предусмотреть анализ следующих показателей: рейтинг востребованности товаров, рейтинг дефицитности товаров.
Вариант 7: База данных библиотеки. Должна содержать следующие данные: информацию об имеющихся изданиях, информацию о читателях, формуляры для каждого издания. Предусмотреть анализ следующих показателей: выдать рекомендации для читателя с учетом его пола, возраста и прочитанных книг на основании общей статистики.
Вариант 8: База данных жилищной управляющей компании. Должна содержать следующие данные: информацию об исполнителях работ и выполненных работах, жильцах, выставленных им счетах и выполненных ими платежах. Предусмотреть анализ следующих показателей: составить рейтинг злостных неплательщиков, рейтинг исполнителей работ с указанием их доли в статье расходов.
Вариант 9: База данных гарантийного ремонта. Должна содержать следующие данные: информацию о выпускаемых производителем товарах, гарантийных мастерских в разных городах и товарах, ремонт которых они могут производить, данные о выполненных ремонтах. Предусмотреть анализ следующих показателей: рейтинг убыточности товаров, обеспеченность каждого города мастерскими по разным группам товаров и всему ассортименту в целом.
Содержание отчета
Отчет должен содержать титульный лист, цель работы, задание, коды команд на каждом этапе выполнения работы, результаты выполнения команд (скриншоты), выводы и анализ результатов работы.
Контрольные вопросы
3.1. Создание функций в СУБД PostgreSQL
PostgreSQL не ограничивает пользователя встроенными функциями и операторами, позволяя ему создавать собственные расширения. Если вам приходится часто выполнять некоторую стандартную последовательность команд SQL или программных операций, пользовательские функции помогут решить эту задачу более надежно и эффективно. Также в PostgreSQL предусмотрена возможность определения операторов для вызова пользовательских (или встроенных) функции, что делает команды SQL понятнее и эффективнее.
Функции и операторы тоже существуют как объекты базы данных и поэтому связываются с конкретной базой.
Разновидность команды SQL99 CREATE FUNCTION, поддерживаемая в PostgreSQL, не обладает прямой совместимостью со стандартом, но зато обеспечивает широкие возможности для расширения PostgreSQL за счет создания пользовательских функции.
Синтаксис команды CREATE FUNCTION:
CREATE FUNCTION имя
([тип_аргумента [...]])
RETURNS тип_возвращаемого_значеиия
AS 'определение' LANGUAGE 'язык'
[WITH (атрибут [...])]
CREATE FUNCTION имя ([тип_аргумента [...]]). После ключевых слов CREATE FUNCTION указывается имя создаваемой функции, после чего в круглых скобках перечисляются типы аргументов, разделенные запятыми. Если список в круглых скобках пуст, функция вызывается без аргументов (хотя сами круглые скобки обязательно должны присутствовать как в определении функции, так и при ее использовании).
RETURNS тип_возвращаемого_значения. Тип данных, возвращаемый функцией.
AS 'определение'. Программное определение функции. В процедурных языках (таких, как PL/pgSQL) оно состоит из кода функции. Для откомпилированных функций С указывается абсолютный системный путь к файлу, содержащему объектный код.
LANGUAGE 'язык'. Название языка, на котором написана функция. В аргументе может передаваться имя любого процедурного языка (такого, как plpgsql или plperl, если соответствующая поддержка была установлена при компиляции), С или SQL.
[WITH (атрибут [...])]. Аргумент атрибут может принимать два значения: iscachablen и isstrict. Оптимизатор может использовать предыдущие вызовы функций для ускоренной обработки будущих вызовов с тем же набором аргументов. Кэширование обычно применяется при работе с функциями, сопряженными с большими затратами ресурсов, но возвращающими один и тот же результат при одинаковых значениях аргументов. isstrict: Функция всегда возвращает NULL в случае, если хотя бы один из ее аргументов равен NULL. При передаче атрибута isstrict результат возвращается сразу, без фактического выполнения функции.
Из всех разновидностей функций в PostgreSQL проще всего создаются «чистые» функции SQL, поскольку их создание не требует ни знания других языков, ни серьезного опыта программирования. Функция SQL определяется как обычная команда с позиционными параметрами.
Позиционный параметр представляет собой ссылку на один из аргументов, переданных при вызове функции SQL. Он называется позиционным, поскольку в ссылке указывается его позиция в списке переданных аргументов. Позиционный параметр состоит из знака $, за которым следует номер (нумерация начинается с 1). Например, $1 означает первый аргумент в переданном списке.
Позиционный параметр не заключается в отдельные апострофы, поскольку апострофы являются частью переданного аргумента. Остальные составляющие определения функции являются либо идентификаторами, либо стандартными ключевыми словами SQL.
Сообщение CREATE означает, что создание функции прошло успешно.
Созданная функция доступна для всех пользователей, обладающих соответствующими правами.
СУБД PostgreSQL, написанная на языке С, может динамически подгружать откомпилированный код С без перекомпиляции пакета. Использование команды CREATE FUNCTION для компоновки с функциями С разрешено только суперпользователям, поскольку эти функции могут содержать системные вызовы, представляющие потенциальную угрозу для безопасности системы.
Функции уничтожаются владельцем или суперпользователем при помощи команды SQL DROP FUNCTION.
Синтаксис команды DROP FUNCTION:
DROP FUNCTION имя ([тип_аргумента [...]])
Сообщение сервера DROP означает, что функция была успешно удалена. Команда DROP FUNCTION, как и большинство команд DROP, необратима, поэтому перед ее выполнением убедитесь в том, что функцию действительно требуется удалить.
3.1. Язык PL/pgSQL в СУБД PostgreSQL
Синтаксис функции на языке PL/pgSQL следующий:
create [or replace] function <имя функции>(<аргументы>)
returns <тип возврата> as ‘<тело функции>’
language ‘plpgsql’;
Необязательная фраза «or replace» позволяет перезапись функции при попытке создать другую с таким же именем (например, записать исправленный вариант). Без этого ключевого слова будет сгенерирована ошибка и потребуется сначала удалить существующую функцию и лишь затем записать на ее место новую.
Скобки после имени функции обязательны, даже если функция не имеет аргументов. Слово «returns» задает тип возвращаемых функцией данных. Указание языка также обязательно.
Тело функции имеет следующую структуру:
DECLARE
необязательный раздел определений
BEGIN
Операторы функции
END;
Последним оператором функции должен быть оператор «return», возвращающий данные указанного выше типа, даже если выход из функции осуществляется раньше (например, в блоке проверки условия) и эта команда никогда не получит управление.
Как и любой нормальный язык программирования, PL/pgSQL позволяет оперировать переменными. Все переменные (за одним исключением, о котором будет упомянуто ниже) должны быть описаны в разделе DECLARE, т.е. их необходимо перечислить с указанием типа данных. В этом же разделе допускается и инициализация переменной начальным значением с помощью ключевого слова DEFAULT. Общий синтаксис описания следующий:
<переменная> <тип> [ DEFAULT <значение>];
Вместо слова «DEFAULT» допускается использование оператора присваивания «:=». Точка с запятой в конце каждого описания обязательна. Переменная может быть любого типа, который поддерживается в PostgreSQL. Кроме того, существуют три специальных типа данных: RECORD, table%RECTYPE и table.field%TYPE. Первый описывает запись любой таблицы, второй - запись указанной таблицы table, третий создает переменную такого же типа, как и тип указанного поля field таблицы table.
В основной секции, заключенной в операторные скобки BEGIN-END, могут использоваться операторы присваивания, математические операторы, ветвления, циклы, вызовы других функций. Кроме того, тело функции может содержать вложенные блоки, имеющие ту же структуру (т.е. DECLARE-BEGIN-END). Видимость переменных распространяется на блок, в котором она описана, и на все вложенные блоки. Ниже конспективно перечислены основные операторы языка PL/pgSQL, которые понадобятся нам в дальнейшем:
Оператор присваивания :=. Такой же, как в языке Pascal. Сопоставляет переменную с некоторым значением или результатом выражения.
Оператор ветвления IF-ELSE-END IF. Знакомая всем с детства конструкция, позволяющая выполнить проверку некоторого условия и в зависимости от результата проверки перейти на выполнение того или иного блока команд. Синтаксис:
If <условие> then
<операторы;…>
[else
<операторы;…>]
end if;
Оператор цикла FOR. Наиболее распространенный цикл. Переменная цикла <var> - то самое исключение, когда переменная может не описываться в секции DECLARE. В этом случае зона ее видимости ограничивается циклом. Синтаксис:
For <var> in <start>..<stop> loop
<тело цикла>
End loop;
Существует вариант этого цикла и для «прохода» по результату выборки:
For <row-var> in <select> loop
<тело цикла>
End loop;
В этом случае в переменную <row-var> последовательно подставляются строки из выборки, и тип этой переменной должен быть либо <table>%rowtype, отражающий запись конкретной таблицы <table>, либо RECORD, описывающий обобщенную запись таблицы.