PL/PGSQL. Лекция 7

1.

Лекция 7
PL/PGSQL

2.

PL/pgSQL это процедурный язык для СУБД PostgreSQL. Целью
проектирования PL/pgSQL было создание загружаемого
процедурного языка, который:
• используется для создания функций и триггеров,
• добавляет управляющие структуры к языку SQL,
• может выполнять сложные вычисления,
• наследует все пользовательские типы, функции и операторы,
• может быть определён как доверенный язык,
• прост в использовании.

3.

• Функции PL/pgSQL могут использоваться везде, где
допустимы встроенные функции. Например, можно
создать функции со сложными вычислениями и условной
логикой, а затем использовать их при определении
операторов или в индексных выражениях.
• В версии PostgreSQL 9.0 и выше PL/pgSQL устанавливается
по умолчанию. Тем не менее это по-прежнему
загружаемый
модуль
и
администраторы,
особо
заботящиеся о безопасности, могут удалить его при
необходимости.

4.

Преимущества использования
PL/pgSQL

5.

• PostgreSQL и большинство других СУБД используют SQL в качестве
языка запросов. SQL хорошо переносим и прост в изучении. Однако
каждый оператор SQL выполняется индивидуально на сервере базы
данных.
• Это значит, что ваше клиентское приложение должно каждый запрос
отправлять на сервер, ждать пока он будет обработан, получать
результат, делать некоторые вычисления, затем отправлять
последующие запросы на сервер. Всё это требует межпроцессного
взаимодействия, а также несёт нагрузку на сеть, если клиент и сервер
базы данных расположены на разных компьютерах.

6.

PL/pgSQL позволяет сгруппировать блок вычислений и
последовательность запросов внутри сервера базы данных, таким
образом, мы получаем силу процедурного языка и простоту
использования SQL при значительной экономии накладных расходов на
клиент-серверное взаимодействие.
• Исключаются дополнительные обращения между клиентом и
сервером
• Промежуточные ненужные результаты не передаются между
сервером и клиентом
• Есть возможность избежать многочисленных разборов одного запроса

7.

В результате это приводит к значительному увеличению
производительности по сравнению с приложением, которое не
использует хранимых функций.
Кроме того, PL/pgSQL позволяет использовать все типы данных,
операторы и функции SQL.

8.

Недостатки PostgreSQL PL/pgSQL
• Медленнее при разработке программного обеспечения,
поскольку PL/pgSQL требует специальных навыков, которыми не
обладают многие разработчики.
• Трудно управлять версиями и сложно отлаживать.
• Может быть непереносим в другие системы управления базами
данных.

9.

Функции, написанные на PL/pgSQL,
определяются на сервере командами CREATE
FUNCTION. Такая команда обычно выглядит,
например, так:

10.

PL/pgSQL это блочно-структурированный язык.
Текст тела функции должен быть блоком.
Структура блока:
• Каждое объявление и каждый оператор в блоке должны
завершаться символом «;» (точка с запятой). Блок, вложенный в
другой блок, должен иметь точку с запятой после END, как
показано выше. Однако финальный END, завершающий тело
функции, не требует точки с запятой.

11.

• В PostgreSQL вы используете одинарные кавычки для
строковой константы, например:
• Если строковая константа содержит одинарную кавычку ( '), вам
необходимо экранировать ее, удвоив одинарную кавычку.
Например:
• Если вы используете старую версию PostgreSQL, вы можете
добавить к строковой константе символ, Eчтобы объявить
синтаксис escape-строки постфикса, и использовать обратную
косую черту, \чтобы экранировать одинарную кавычку,
например:

12.

• Если строковая константа содержит обратную косую черту,
вам необходимо экранировать ее, используя другую
обратную косую черту.
• Проблема возникает, когда строковая константа содержит
много одинарных кавычек и обратных косых черт. Удвоение
каждой отдельной кавычки и обратной косой черты
затрудняет чтение и поддержку строковой константы.
• В PostgreSQL версии 8.0 появилась функция кавычек в
долларах, чтобы сделать строковые константы более
читабельным

13.

В этом синтаксисе параметр tagне является обязательным. Он
может содержать ноль или много символов.
Между $tag$, вы можете поместить любую строку с одинарными
кавычками ( ') и обратной косой чертой ( \). Например:
В этом примере мы не указали tagмежду двумя знаками доллара (
$).
В следующем примере используется синтаксис строковой
константы в долларовых кавычках с тегом:

14.

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

15.

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

16.

Использование строковых констант в
долларовых кавычках в функциях
• Синтаксис оператора CREATE
пользовательскую функцию
FUNCTION,
позволяющего
создать
• В этом синтаксисе это function_bodyстроковая константа. Например,
следующая функция находит фильм по его идентификатору:
Как видите, тело функции find_film_by_id()заключено в
одинарные кавычки.

17.

Если функция имеет много операторов, ее
становится сложнее читать. В этом случае вы
можете использовать синтаксис строковой
константы в долларовых кавычках:
• Теперь вы можете поместить любой фрагмент
кода между $$и $$без использования
одинарных кавычек или обратной косой
черты, чтобы избежать одинарных кавычек и
обратной косой черты.

18.

Использование строковых констант в
долларовых кавычках в хранимых процедурах
• Аналогичным образом вы можете использовать синтаксис
строковой константы в долларовых кавычках в таких хранимых
процедурах :

19.

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

20.

Блочная структура PL/pgSQL
Рассмотрим структуру блока более подробно:
• Каждый блок состоит из двух разделов: объявления и
тела. Раздел объявления не является обязательным, а
раздел тела является обязательным. Блок заканчивается
точкой с запятой ( ;) после ENDключевого слова.
• Блок
может
иметь
необязательную
метку,
расположенную в начале и в конце. Вы используете метку
блока, когда хотите указать ее в EXITоператоре тела
блока или когда хотите уточнить имена переменных ,
объявленных в блоке.
• В разделе объявлений вы объявляете все переменные ,
используемые в разделе тела. Каждый оператор в
разделе объявлений заканчивается точкой с запятой ( ;).
• Раздел body — это место, где вы размещаете код. Каждый
оператор в разделе body также заканчивается точкой с
запятой (;).

21.

Пример структуры блока PL/pgSQL
• Следующий пример иллюстрирует очень простой блок. Это
называется анонимный блок.

22.

Чтобы выполнить блок из pgAdmin, нажмите
кнопку «Выполнить», как показано на рисунке:
• Обратите внимание, что
DOоператор не
принадлежит блоку. Он
используется для
выполнения анонимного
блока. PostgreSQL
представил этот
DOоператор начиная с
версии 9.0.

23.

Анонимный блок должен быть заключен в
одинарные кавычки, например:

24.

• Однако мы использовали синтаксис строковой константы в долларовых кавычках , чтобы
сделать его более читабельным.
• В разделе объявлений мы объявили переменную film_count и установили ее значение в
ноль.
• film_count integer := 0;
• Внутри раздела body мы использовали select intoоператор с count()функцией, чтобы
получить количество фильмов из filmтаблицы и присвоить результат переменной
film_count.
• После этого мы показали сообщение с помощью raise noticeоператора:
• Это % заполнитель, который заменяется содержимым переменной film_count.
• Обратите внимание, что first_blockэтикетка предназначена только для демонстрационных
целей. В этом примере он ничего не делает.

25.

Подблоки PL/pgSQL
• PL/pgSQL позволяет размещать блок
внутри тела другого блока.
• Блок, вложенный в другой блок,
называется подблоком. Блок,
содержащий подблок, называется
внешним блоком.
• Обычно вы делите большой блок на
более мелкие и более логичные
подблоки. Переменные в подблоке
могут иметь имена, такие же , как во
внешнем блоке, хотя это не
рекомендуется.

26.

Краткое содержание
• PL/pgSQL — это язык с блокированной структурой. Он организует
программу по блокам.
• Блок состоит из двух частей: объявления и тела. Часть
объявления не является обязательной, а часть тела является
обязательной.
• Блоки могут быть вложенными. Вложенный блок — это блок,
помещенный внутри тела другого блока.