PL/pgSQL
Курсоры
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
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.0:
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
