PL/PGSQL

1.

PL/PGSQL

2.

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

3.

$tag$<string_constant>$tag$
В этом синтаксисе TAG является необязательным. Он может содержать
ноль или много символов.

4.

• % — это заполнитель, который
заменяется содержимым
переменной Film_count.

5.

Блок DO

6.

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

7.

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

8.

В этом синтаксисе:
• Сначала укажите имя переменной. Хорошей практикой
является присвоение переменной осмысленного имени.
Например, вместо имени переменной i следует
использовать индекс или счетчик.
• Во-вторых, свяжите с переменной определенный тип
данных. Тип данных может быть любым допустимым типом
данных, например целым, числовым, varchar и char.
• В-третьих, при необходимости присвойте переменной
значение по умолчанию. Если вы этого не сделаете,
начальное значение переменной будет NULL.

9.

10.

Выполнение команды, не возвращающей
результат
• Иногда бывает полезно вычислить значение
выражения или запроса SELECT, но отказаться от
результата, например, при вызове функции, у которой
есть побочные эффекты, но нет полезного результата.
Для этого в PL/pgSQL, используется оператор PERFORM.
• PERFORM запрос;
• Эта команда выполняет запрос и отбрасывает
результат. Запросы пишутся таким же образом, как и в
команде SQL SELECT, но ключевое слово SELECT
заменяется на PERFORM.
Примечание
Можно предположить, что такой же
результат получается
непосредственно командой SELECT,
но в настоящее время использование
PERFORM является единственным
способом. Команда SQL, которая
может возвращать строки, например
SELECT, будет отклонена с ошибкой,
если не имеет предложения INTO.

11.

pg_sleep(seconds)

12.

Переменное время инициализации
PostgreSQL оценивает значение переменной по умолчанию и
присваивает его переменной при входе в блок.
Сначала объявите переменную, значение которой по
умолчанию инициализируется текущим временем.
Во-вторых, распечатайте значение переменной и передайте
выполнение через 10 секунд с помощью функции
pg_sleep().
В-третьих, снова распечатайте значение переменной
create_at.
Как ясно видно из выходных данных, значение
созданного_at инициализируется только один раз при
входе в блок.

13.

Копирование типов данных
• %type предоставляет тип данных столбца таблицы или другой
переменной. Обычно вы используете %type для объявления
переменной, содержащей значение из базы данных или другой
переменной.
• Ниже показано, как объявить переменную с типом данных
столбца таблицы:
• Ниже показано, как объявить переменную с типом данных другой
переменной:

14.

В этом примере объявлены две
переменные:
• Переменная Film_title имеет тот же тип
данных, что и столбец title в таблице
фильмов из образца базы данных.
• Featured_title имеет тот же тип данных,
что и тип данных переменной Film_title.
Используя функцию копирования типов,
вы получаете следующие преимущества:
• Во-первых, вам не нужно знать тип
столбца или ссылки, на которую вы
ссылаетесь.
• Во-вторых, если тип данных имени
столбца (или переменной), на который
ссылаются, изменится, вам не нужно
менять определение функции.

15.

Переменные в
блоке и
подблоке
• Когда вы объявляете
переменную в
подблоке, имя которой
совпадает с именем
другой переменной во
внешнем блоке,
переменная во
внешнем блоке
скрывается в
подблоке.
• Если вы хотите
получить доступ к
переменной во
внешнем блоке, вы
используете метку
блока для уточнения
ее имени.

16.

В этом примере:
• Сначала объявите переменную с именем counter во внешнем_блоке.
• Затем объявите переменную с тем же именем в подблоке.
• Тогда перед входом в подблок значение счетчика равно единице. В
подблоке увеличиваем значение счетчика до десяти и распечатываем
его. Обратите внимание, что изменение влияет только на переменнуюсчетчик в подблоке.
• После этого обратитесь к переменной счетчика во внешнем блоке,
используя метку блока, чтобы уточнить ее имя external_block.counter.
• Наконец, распечатайте значение переменной-счетчика во внешнем
блоке, ее значение останется неизменным.

17.

Типы строк PL/pgSQL
• Сначала объявите переменную строки с
именем selected_actor, тип данных которой тот
же, что и строка в таблице актеров.
• Во-вторых, присвойте строку, значение
которой в столбце act_id равно 10,
переменной selected_actor с помощью
оператора select to.
• В-третьих, покажите имя и фамилию
выбранного актера, используя оператор
уведомления о повышении. Он обращался к
полям first_name и Last_name, Last_update
используя точечную запись.

18.

• Используйте переменные типа строки (%ROWTYPE) для хранения
строки результирующего набора, возвращаемого оператором
select to.

19.

Типы записей PL/pgSQL
PostgreSQL предоставляет «тип», называемый записью, который аналогичен типу строки.
Чтобы объявить переменную записи, вы используете имя переменной, за которым следует
ключевое слово записи, например:
Переменная записи аналогична переменной типа строки. Он может содержать только одну
строку набора результатов.
В отличие от переменной типа строки, переменная записи не имеет предопределенной
структуры. Структура переменной записи определяется, когда оператор select или for
присваивает ей фактическую строку.
Чтобы получить доступ к полю в записи, вы используете синтаксис точечной записи (.),
например:

20.

• Сначала объявите
переменную записи с
именем Rec в разделе
объявлений.
• Во-вторых, используйте
оператор select to, чтобы
выбрать строку, чей
Film_id равен 200, в
переменную Rec.
• В-третьих, распечатайте
информацию о фильме
через переменную
записи.

21.

Использование переменных записи в
операторе цикла for • Сначала объявите переменную с
именем r с типом записи.
• Во-вторых, используйте оператор
цикла for для получения строк из
таблицы фильмов (в образце
базы данных). Оператор цикла for
присваивает строку, состоящую
из заголовка и длины,
переменной Rec на каждой
итерации.
• В-третьих, покажите содержимое
полей переменной записи,
используя точечную запись
(rec.title и Rec.length).

22.

Константы PL/pgSQL
В отличие от переменной, значение константы не может быть изменено
после ее инициализации.
• Во-первых, константы делают код более читабельным и удобным в
сопровождении. Например, представьте, что у вас есть следующая
формула:
Что значит 0,1? Его можно интерпретировать как что угодно.
Но когда вы используете следующую формулу, каждый знает смысл
расчета продажной цены, которая равна цене нетто плюс налог на
добавленную стоимость (value-added tax НДС).

23.

• Во-вторых, константы сокращают усилия по обслуживанию.
Предположим, у вас есть формула, которая рассчитывает цену
продажи во всех местах функции. Когда НДС меняется, например, с
0,1 на 0,5, вам необходимо изменить все эти жестко
закодированные значения.
Используя константу, вам просто нужно изменить ее значение в
одном месте, где вы определяете константу.

24.

А что если поменяем константу?

25.

• Подобно значению
переменной по умолчанию,
PostgreSQL оценивает
значение константы, когда
блок вводится во время
выполнения, а не во время
компиляции. Например: