ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 11.01.2024
Просмотров: 212
Скачиваний: 5
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Задание на лабораторную работу №6
Выберите предметную область (можно из л/р №1) и опишите структуру
БД, используя SQL-запросы. К обязательным требованиям относится использование:
1. объектно-реляционных связей;
2. ограничений в таблицах;
3. массивов;
4. последовательностей;
5. а также backup и restore БД для переноса с домашнего ПК.
Прием работы производится только, если она удовлетворяет всем требованиям.
Прием работы
Прием происходит при наличии оформленного отчета и работающей БД.
Вопросы
1. Что такое PostgreSQL, какой язык использует в качестве языка БД, к какому классу ПО (открытое или закрытое) относится? Какая архитектура? Какие клиентские приложения входят в пакет?
2. Как организуется объектно-реляционные связи в СУБД postgreSQL и какие особенности организации могут приводить к визуальному нарушению ограничений установленных в таблицах?
3. Что такое ограничения полей, ограничения таблиц? Как они используются и для чего?
4. Как использовать поля-массивы: как обращаться к элементам массивов, как создавать массивы?
5. Автоматизация стандартных процедур.
6. Что такое последовательности? Как могут быть использованы?
7. Что такое триггеры? На каких языках могут быть реализованы?
Лабораторная работа №7
1 2 3 4 5
Тема: Хранимые процедуры на языке PL/pgSQL.
Цель: познакомиться с возможностями языка PL/pgSQL.
Навыки и умения: написание хранимых процедур на языке PL/pgSQL, создания триггерных функций.
Теоретический базис
PL/pgSQL относится к семейству процедурных языков и обладает определенным сходством с процедурным языком Oracle, PL/SQL.
Процедурным языком называется язык программирования, в котором желаемый результат достигается последовательностью шагов.
Язык PL/pgSQL позволяет группировать на сервере код SQL и программные команды, что приводит к снижению затрат сетевых и коммуникационных ресурсов, обусловленных частыми запросами данных со стороны клиентских приложении и выполнением логической обработки этих данных на удаленных хостах.
В программах PL/pgSQL могут использоваться все типы данных, операторы и функции PostgreSQL. «SQL» в название PL/pgSQL указывает на то, что программист может напрямую использовать команды языка SQL в своих программах. Использование SQL в коде PL/pgSQL расширяет возможности, а также повышает гибкость и быстродействие программ.
Несколько команд SQL в программном блоке PL/pgSQL выполняются за одну операцию вместо обычной обработки каждой команды.
Другой важной особенность PL/pgSQL является хорошая адаптируемость программ; функции языка совместимы со всеми платформами, на которых работает СУБД PostgreSQL.
Структура языка
Язык PL/pgSQL имеет относительно простую структуру, что объясняется в основном тем, что каждый логически обособленный фрагмент кода существует в виде функции. Регистр символов в именах функций
PL/pgSQL не учитывается. В ключевых словах и идентификаторах допускается использование произвольных комбинаций символов верхнего и нижнего регистров.
Блоки
Программы PL/pgSQL состоят из блоков. Такой метод организации программного кода обычно называется блочной структурой. Программные блоки вводятся в командах SQL CREATE FUNCTION, которые используются для определения функций PL/pgSQL в базах данных PostgreSQL. Команда
CREATE FUNCTION определяет имя функции, типы ее аргументов и
возвращаемого значения. Основной блок функции начинается с секции объявлений.
Все переменные объявляются
(а также могут инициализироваться значениями по умолчанию) в секции объявлении программного блока. В объявлении указывается имя и тип переменной.
Секция объявлений обозначается ключевым словом DECLARE, а каждое объявление завершается символом точки с запятой (;).
После объявления переменных следует ключевое слово BEGIN, обозначающее начало основного программного блока. За ключевым словом
BEGIN находятся команды, входящие в блок. Конец программного блока обозначается ключевым словом END.
Пример 7.1:
Создание функции на языке PL/pgSQL:
CREATE OR REPLACE FUNCTION a_function (i int4) RETURNS
int4 AS
$BODY$
DECLARE
an_integer int4;
-- Объявление целочисленной константы.
-- инициализированной значением 5.
five CONSTANT integer := 5;
-- Объявление целочисленной переменной.
-- инициализированной значением 10.
-- Переменной не может присваиваться NULL,
ten integer NOT NULL := 10;
BEGIN
an_integer := five * ten * i;
return an_integer;
END;
$BODY$
LANGUAGE 'plpgsql';
Для того чтобы вызвать функцию необходимо записать ее имя, а также аргументы в секции FROM оператора SELECT.
Пример 7.2:
Вызов функции PL/pgSQL:
SELECT * FROM a_function (4);
Типы данных
Переменные PL/pgSQL могут относиться к любому из стандартных типов данных SQL (например, integer или char). Помимо типов данных SQL, в PL/pgSQL также предусмотрен дополнительный тип RECORD, предназначенный для хранения записей без указания полей — эта информация передается при сохранении данных в переменной.
Все переменные объявляются
(а также могут инициализироваться значениями по умолчанию) в секции объявлении программного блока. В объявлении указывается имя и тип переменной.
Секция объявлений обозначается ключевым словом DECLARE, а каждое объявление завершается символом точки с запятой (;).
После объявления переменных следует ключевое слово BEGIN, обозначающее начало основного программного блока. За ключевым словом
BEGIN находятся команды, входящие в блок. Конец программного блока обозначается ключевым словом END.
Пример 7.1:
Создание функции на языке PL/pgSQL:
CREATE OR REPLACE FUNCTION a_function (i int4) RETURNS
int4 AS
$BODY$
DECLARE
an_integer int4;
-- Объявление целочисленной константы.
-- инициализированной значением 5.
five CONSTANT integer := 5;
-- Объявление целочисленной переменной.
-- инициализированной значением 10.
-- Переменной не может присваиваться NULL,
ten integer NOT NULL := 10;
BEGIN
an_integer := five * ten * i;
return an_integer;
END;
$BODY$
LANGUAGE 'plpgsql';
Для того чтобы вызвать функцию необходимо записать ее имя, а также аргументы в секции FROM оператора SELECT.
Пример 7.2:
Вызов функции PL/pgSQL:
SELECT * FROM a_function (4);
Типы данных
Переменные PL/pgSQL могут относиться к любому из стандартных типов данных SQL (например, integer или char). Помимо типов данных SQL, в PL/pgSQL также предусмотрен дополнительный тип RECORD, предназначенный для хранения записей без указания полей — эта информация передается при сохранении данных в переменной.
Использование SELECT INTO
Команда SELECT INTO в основном требуется для сохранения данных записей в переменных, объявленных с типами %ROWTYPE и RECORD.
Чтобы команда SELECT INTO могла использоваться с обычной переменной, тип этой переменной должен соответствовать типу поля, упоминаемому в команде SQL SELECT. Синтаксис команды SELECT INTO:
CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS '
DECLARE
команда;
BEGIN
SELECT INTO переменная [. ...] поле [. ...] секции_select;
END;
' LANGUAGE 'plpgsql';
В этом описании переменная — имя переменной, участвующей в присваивании, а секции_select — любые поддерживаемые секции команды
SQL SELECT, обычно следующие за списком целевых полей в команде
SELECT.
Чтобы узнать, успешно ли были присвоены значения переменным командой SELECT INTO, воспользуйтесь специальной логической переменной FOUND:
IF NOT FOUND THEN
-- если не присвоены, то
[…]
-- делать
END IF;
Использование атрибута %TYPE
Атрибут %ТУРЕ используется при объявлении переменных с типом данных, совпадающих с типом некоторого объекта базы данных (чаще всего поля). Синтаксис объявления переменной с атрибутом %ТУРЕ:
переменная таблица. поле%ТУРЕ
Это позволяет нам не привязываться к конкретному типу поля и еще более абстрагироваться от структуры хранения данных.
Использование атрибута %ROWTYPE
Атрибут %ROWTYPE используется в PL/pgSQL для переменной- записи, имеющей одинаковую структуру с записями заданной таблицы. Не путайте атрибут %ROWTYPE с типом данных RECORD — переменная с атрибутом %ROWTYPE точно воспроизводит структура туру записи конкретной таблицы, а переменная RECORD не структурирована и ей можно присвоить запись любой таблицы.
Работа с атрибутом %ROWTYPE отражается в примере 7.3. Для ссылки на имя поля в полученной переменной типа authors%ROWTYPE используется точка (.) по аналогии с составными структурами в языках программирования (Pascal, С).
Пример 7.3:
Использование %ROWTYPE PL/pgSQL:
CREATE FUNCTION get_author (integer) RETURNS text AS '
DECLARE
-- Объявление псевдонима для аргумента функции.
-- в котором должен передаваться код автора.
author_id ALIAS FOR $1;
-- Объявление переменной, структура которой
-- совпадает со структурой таблицы authors.
found_author authors%ROWTYPE;
BEGIN
-- Найти в таблице authors фамилию автора.
-- код которого совпадает с переданным аргументом.
SELECT INTO found_author * FROM authors WHERE id =
author_id;
-- Вернуть имя и фамилию, разделенные пробелом.
RETURN found_author.first_name || " " || found_author.last_name:
END:
' LANGUAGE 'plpgsql';
Передача управления
Команды передачи управления существуют практически во всех современных языках программирования, и PL/pgSQL не является исключением. С технической точки зрения сам вызов функции можно рассматривать как передачу управления последовательности команд
PL/pgSQL. Тем не менее существуют и другие, более совершенные средства, определяющие последовательность выполнения команд PL/pgSQL. Здесь речь пойдет об условных командах IF/THEN и циклах.
Команда IF/THEN/ELSE задает команду (или блок команд), выполняемых в случае истинности некоторого условия. Синтаксис команды
IF/THEN/ELSE:
CREATE FUNCTION функция (.аргументы) RETURNS тип AS '
DECLARE
объявления
BEGIN
IF условие THEN
Команда;
[...]
[ELSE
Команда;
[...]
]END
IF;
END:
LANGUAGE 'plpgsql':
Квадратные скобки, обрамляющие секцию ELSE, означают, что эта секция может быть опущена.
Циклы
Самый простой вид цикла, используемого в PL/pgSQL, – безусловный.
Его синтаксис:
LOOP
команда ;
[...]
END LOOP;
Для выхода из цикла служит команда EXIT. Она также может содержать метку или условие заверешения:
[ «метка» ]
LOOP
команда;
[...]
EXIT [ метка ] [ WHEN условие ];
END LOOP;
Цикл WHILE выполняет блок команд до тех пор, пока заданное условие не станет ложным. Синтаксис использования следующий:
[ «метка» ]
WHILE условие LOOP
команда;
[...]
END LOOP;
Перейдем к рассмотрению циклов FOR. Возможно, циклы FOR — самая важная разновидность циклов, реализованных в PL/pgSQL. Цикл FOR выполняет программный блок для целых чисел из заданного интервала. Ниже будет описан синтаксис цикла FOR.
[ «метка» ]
FOR переменная IN [ REVERSE ] выражение1 .. выражение2 LOOP
команда;
END LOOP:
Цикл FOR выполняет одну итерацию для каждого значения переменной переменная в интервале, границы которого определяются выражениями выражение1 и выражение2 (включительно). Управляющую переменную цикла не обязательно объявлять вне блока FOR, если вы не собираетесь работать с ней после завершения цикла.
Циклы FOR также могут использоваться для перебора результатов запросов – ниже представлен соответствующий пример 7.4. Обратите внимание, что в примере используются не двойные кавычки, а дважды одинарные, как способ экранирования, т.к. тело функции само заключено в одинарные кавычки.
Пример 7.4:
Использование цикла FOR в PL/pgSQL:
CREATE FUNCTION extract_all_titles2 () RETURNS text AS '
DECLARE
-- Объявление переменной для кода темы.
sub_id integer:
-- Объявление переменной для хранения списка названий книг
text_output text = '' '';
-- Объявление переменной для названия темы.
sub_title text;
-- Объявление переменной для хранения записей.
-- полученных при выборке из таблицы books.
rowdata_books%ROWTYPE;
BEGIN
-- Внешний цикл FOR: тело цикла выполняется до тех пор.
-- пока переменная i не станет равна 15. Перебор начинается с 0.
-- Следовательно, тело цикла будет выполнено 16 раз
-- (по одному для каждой темы).
FOR i IN 0..15 LOOP
-- Получить из таблицы subjects название темы.
-- код которой совпадает со значением переменной i.
SELECT INTO subtitle subject FROM subjects WHERE id = i;
-- Присоединить название темы, двоеточие и символ новой строки
-- к переменной text_output.
text_output = text_output || ''\n'' || sub_title || '':\n'';
-- Перебрать все записи таблицы books.
--у которых код темы совпадает со значением переменной i.
FOR row_data IN SELECT * FROM books WHERE subjected = i
LOOP
-- Присоединить к переменной text_output название книги
-- и символ новой строки.
text_output := text_output || row_data.title || ''\n'';
END LOOP;
END LOOP;
-- Вернуть список.
RETURN text_output;
END;
' LANGUAGE 'plpgsql';
Обработка ошибок и исключений
Команда RAISE предназначена для инициирования ошибок и исключений в функциях PL/pgSQL. Она передает заданную информацию механизму PostgreSQL elog. В команде RAISE также указывается уровень ошибки и строка, передаваемая PostgreSQL. Кроме того, в команду можно
включить переменные и выражения, значения которых будут содержаться в выходных данных. Соответствующие позиции строки помечаются знаками процента (%). Синтаксис команды RAISE:
RAISE уровень "сообщение" [. идентификатор [...] ];
Уровень ошибки может принимать следующие значения: DEBUG,
NOTICE, EXCEPTION.
Для того, чтобы обработать исключение, возникшее в текущей транзакции (т.е. между BEGIN и END) необходимо записать следующую конструкцию:
EXCEPTION
WHEN код_исключения THEN
команда;
[WHEN код_исключения THEN
команда;]
На следующем примере 7.5 представлена функция, в теле которой обрабатываются два вида исключений: деление на ноль и взятие корня от отрицательного числа. Попробуйте создать эту функцию, а затем вызывайте ее с параметрами: 1, 0, -1.
Пример 7.5:
Демонстрация обработки исключений:
CREATE OR REPLACE FUNCTION tt(ii integer) RETURNS boolean
AS $BODY$
DECLARE
i integer := 100;
result bool := true;
BEGIN
i:=i/ii;
i:=sqrt(i);
return result;
EXCEPTION
WHEN DIVISION_BY_ZERO THEN
result := false;
return
result;
WHEN INVALID_ARGUMENT_FOR_POWER_FUNCTION THEN
result := false;
return
result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Таблицу констант (кодов) ошибок, возникающих в PostgreSQL, можно посмотреть в документации. Для этого запустите PgAdmin, запустите редактор SQL-запросов. В редакторе выберите пункт меню «Help»-«Help».
Затем перейдите не раздел «Appendix A. PostgreSQL Error Codes».
RAISE уровень "сообщение" [. идентификатор [...] ];
Уровень ошибки может принимать следующие значения: DEBUG,
NOTICE, EXCEPTION.
Для того, чтобы обработать исключение, возникшее в текущей транзакции (т.е. между BEGIN и END) необходимо записать следующую конструкцию:
EXCEPTION
WHEN код_исключения THEN
команда;
[WHEN код_исключения THEN
команда;]
На следующем примере 7.5 представлена функция, в теле которой обрабатываются два вида исключений: деление на ноль и взятие корня от отрицательного числа. Попробуйте создать эту функцию, а затем вызывайте ее с параметрами: 1, 0, -1.
Пример 7.5:
Демонстрация обработки исключений:
CREATE OR REPLACE FUNCTION tt(ii integer) RETURNS boolean
AS $BODY$
DECLARE
i integer := 100;
result bool := true;
BEGIN
i:=i/ii;
i:=sqrt(i);
return result;
EXCEPTION
WHEN DIVISION_BY_ZERO THEN
result := false;
return
result;
WHEN INVALID_ARGUMENT_FOR_POWER_FUNCTION THEN
result := false;
return
result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Таблицу констант (кодов) ошибок, возникающих в PostgreSQL, можно посмотреть в документации. Для этого запустите PgAdmin, запустите редактор SQL-запросов. В редакторе выберите пункт меню «Help»-«Help».
Затем перейдите не раздел «Appendix A. PostgreSQL Error Codes».
Вызов функций в теле процедуры
При вызове функции PL/pgSQL из кода PL/pgSQL имя функции обычно включается в команду SQL SELECT или в команду присваивания:
SELECT функция (.аргументы);
переменная := функция(аргументы);
Подобный способ вызова функций при выборке и присваивании стал стандартным, поскольку любая функция PostgreSQL должна возвращать значение некоторого типа. Ключевое слово PERFORM позволяет вызвать функцию и проигнорировать возвращаемое значение:
PERFORM функция (аргументы);
PL/pgSQL и триггеры
Определения триггеров PostgreSQL могут содержать ссылки на триггерные функции (то есть функции, которые должны вызываться при срабатывании триггера), написанные на языке PL/pgSQL. Триггер определяет операцию, которая должна выполняться при наступлении некоторого события в базе данных.
Не путайте определение триггера с определением триггерной функции.
Триггер определяется командой SQL CREATE TRIGGER, а триггерная функция определяется командой SQL CREATE FUNCTION. Синтаксис определения триггера следующий:
CREATE FUNCTION функция () RETURNS opaque AS '
DECLARE
объявления;
BEGIN
команды;
END:
' LANGUAGE 'plpgsql';
Ниже приведен пример создания триггерной функции. Обратите внимание, что opaque заменено на “trigger”, что допустимо.
Пример 7.6:
Триггерная функция, которая автоматически для вставляемого значения поля считает его md5 сумму:
CREATE OR REPLACE FUNCTION hashpassword()
RETURNS "trigger" AS
$BODY$
BEGIN
NEW."password"=md5(NEW."password");
RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "UMUAcessManagement".hashpassword()
OWNER TO postgres;