PL/pgSQL
Курсоры
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Причины использования
Объявление и открытие курсора
Операции с курсором
Циклы по курсору и по результатам запроса
Передача курсора клиенту
3
Причины использования
Курсор подразумевает итеративную обработку
полная выборка занимает слишком много памяти
нужна не вся выборка, но размер заранее неизвестен
способ отдать управление выборкой клиенту
действительно требуется построчная обработка (обычно нет)
С концепцией курсоров мы уже знакомы по теме «Архитектура. Общее
устройство PostgreSQL». Там речь шла о курсорах как о возможности,
предоставляемой сервером, и мы смотрели, как к этой возможности
обращаться средствами SQL. Теперь поговорим о том, как
использовать те же самые курсоры в языке PL/pgSQL.
Почему вообще может возникнуть необходимость в курсорах?
Декларативный SQL в первую очередь предназначен для работы
с множествами строк — в этом его сила и преимущество. PL/pgSQL,
как процедурный язык, вынужден работать со строками по одной за
раз, используя явные циклы. Этого как раз можно добиться, используя
курсоры.
Например, полная выборка может занимать слишком много места, так
что приходится обрабатывать результаты по частям. Или требуется
выборка неизвестного заранее размера — то есть в процессе выборки
нужно вовремя остановиться. Или есть необходимость предоставить
управление выборкой клиенту.
(Однако еще раз отметим, что, хотя необходимость такой построчной
обработки может возникать, во многих случаях ее можно заменить
чистым SQL, и код в итоге окажется проще и будет быстрее работать.)
4
Объявление и открытие
Несвязанные с запросом курсорные переменные
объявляется переменная типа refcursor
конкретный запрос указывается при открытии
Связанные с запросом курсорные переменные
при объявлении указывается запрос (возможно, с параметрами)
при открытии указываются фактические значения параметров
Особенности
значение курсорной переменной — имя курсора
(можно задать явно или сгенерируется автоматически)
переменные PL/pgSQL в запросе становятся неявными параметрами
(значения подставляются при открытии курсора)
запрос предварительно подготавливается
нет в SQL
Как мы видели, в SQL курсор объявлялся и открывался одновременно
командой DECLARE. В PL/pgSQL это два отдельных шага. Кроме того,
для доступа к курсорам используются так называемые курсорные
переменные, имеющие тип refcursor и, фактически, содержащие имя
курсора (причем если не указывать это имя явно, PL/pgSQL сам
позаботится о его уникальности).
Курсорную переменную можно объявить, не связывая ее с конкретным
запросом. Тогда при открытии курсора нужно будет указать запрос.
Другой вариант — уже при объявлении переменной указать запрос,
возможно с параметрами. Тогда при открытии курсора указываются
только фактические параметры.
Оба способа равноценны; какой использовать — дело вкуса. И в том,
и в другом варианте запрос может иметь неявные параметры —
переменные PL/pgSQL.
Напомним, что запрос, открытый с помощью курсора, автоматически
подготавливается.
6
Операции с курсором
Выборка
только по одной строке
Обращение к текущей строке курсора
возможно только для простых запросов
(одна таблица, без группировок и сортировок)
Обычно обработка выполняется в цикле
цикл FOR по курсору
цикл FOR по запросу без явного определения курсора
Закрытие
явно или автоматически при завершении транзакции
в SQL
DECLARE
WITH HOLD
в SQL
размер выборки
определяется
Выборка данных из курсора возможна в PL/pgSQL только построчно.
Для этого служит команда FETCH INTO.
Если запрос достаточно простой (одна таблица, без группировок и
сортировок), то в процессе работы с курсором можно обращаться
к текущей строке, например, в командах UPDATE или DELETE.
Процедурная обработка данных подразумевает работу с циклами.
Можно организовать перебор и обработку всех строк, возвращаемых
курсором, с помощью тех управляющих команд, что мы уже знаем.
Но, поскольку часто нужен именно такой цикл, в PL/pgSQL есть
специальная команда FOR. Целочисленный вариант FOR мы уже
видели в теме «Обзор и конструкции языка», а этот вариант работает
с курсором. Более того, есть еще один вариант цикла FOR, в котором
не требуется даже объявления курсора — в команде указывается сам
запрос.
Курсор можно закрыть явно командой CLOSE, но он в любом случае
будет закрыт при окончании транзакции (в SQL курсор может
оставаться открытым и после завершения транзакции, если указать
фразу WITH HOLD).
8
Передача курсора клиенту
backend
клиентское
приложение
обслуживающий процесс
функция на PL/pgSQL
refcursor
курсор (портал)
Как уже говорилось, курсорная переменная PL/pgSQL (типа refcursor)
содержит имя открытого SQL-курсора. Когда говорят о курсоре как
о памяти, отведенной в обслуживающем процессе для хранения
состояния, используют термин портал.
Таким образом, функция на PL/pgSQL может открыть курсор и вернуть
его имя клиенту. Дальше клиент сможет работать с курсором так, как
будто он сам его и открыл, но доступ будет иметь только к тем данным,
которые ему предоставлены. Это дает еще один способ организации
взаимодействия приложения и базы данных.
10
Итоги
Курсор позволяет получать
и обрабатывать данные построчно
Цикл FOR упрощает работу с курсорами
Обработка в цикле естественна для процедурных языков,
но этим не стоит злоупотреблять
11
Практика
1. Измените функцию book_name: если у книги больше двух
авторов, то в названии указываются только первые два
и в конце добавляется «и др.».
Проверьте работу функции в SQL и в приложении.
2. Попробуйте написать функцию book_name на SQL.
Какой вариант нравится больше — PL/pgSQL или SQL?
1. Например:
Хрестоматия. Пушкин А. С., Толстой Л. Н., Тургенев И. С. →
→ Хрестоматия. Пушкин А. С., Толстой Л. Н. и др.
12
Практика
1. Требуется распределить расходы на электроэнергию
по отделам компании пропорционально количеству
сотрудников (перечень отделов находится в таблице).
Напишите функцию, которая примет общую сумму расходов
и запишет распределенные расходы в строки таблицы.
Числа округляются до копеек; сумма расходов всех отделов
должна в точности совпадать с общей суммой.
2. Напишите табличную функцию, имитирующую сортировку
слиянием. Функция принимает две курсорные переменные;
оба курсора уже открыты и возвращают упорядоченные по
неубыванию целые числа. Требуется выдать общую
упорядоченную последовательность чисел из обоих
источников.
1. В качестве таблицы можно взять:
CREATE TABLE depts(
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
employees integer,
expenses numeric(10,2)
);
INSERT INTO depts(employees) VALUES (10),(10),(10);
Функция:
FUNCTION distribute_expenses(amount numeric) RETURNS void;
Ожидаемый результат после вызова функции с параметром 100.00:
expenses
----------
33.33
33.34
33.33
2. Функция:
FUNCTION merge(c1 refcursor, c2 refcursor) RETURNS SETOF integer;
Например, если первый курсор возвращает последовательность 1, 3, 5,
а второй — 2, 3, 4, то ожидается результат:
merge
-------
1
2
3
3
4
5
