Доброго здоровья читателям моего канала programmer's notes. Поддерживаем мой канал.
Курсоры на PLpgSQL
Перейдём теперь к новому объекту, который удобно использовать при программировании на PLpgSQL, это курсор (см. предыдущую статью).
Курсор, это такой объект, который позволяет работать с одной или несколькими строками запроса, не перегружая память. Это очень похоже на итераторы в программировании. По другому можно сказать, что курсор это таблица, в которой установлен порядок. Есть последующие и предыдущие строки. Можно последовательно переходить от одной строки к другой. Это противоречит общим принципам реляционных баз данных, но зато, в частности, позволяет интегрировать алгоритмический язык программирования с языком запросов SQL. Курсор как объект может существовать и на стороне сервера и на стороне клиента. Мы рассматриваем о курсорах на стороне сервере PostgreSQL.
Рассмотрим пример простейшего курсора, в котором реализован простейший алгоритм вычисление средней оценки указанного ученика. Курсор в данном примере определён в разделе declare.
create or replace function cursor1(idd integer)
returns real
as
$$
declare
curs cursor for select ocenka from ocenki
where id_s=idd;
sm real := 0.0;
n real := 0.0;
oc integer;
begin
open curs;
loop
fetch curs into oc;
if not found then
exit;
end if;
sm := sm + oc;
n := n + 1;
end loop;
close curs;
sm := sm/n;
return sm;
end;
$$
language 'plpgsql';
Сделаем несколько пояснений к представленному коду функции с курсором.
1. Курсор (cursor) определён нами в разделе declare. По сути он представляет собой обычный запрос: список оценок студента, первичный ключ которого был передан в функцию в качестве параметра.
2. Для того, что начать работать курсором надо его открыть (open), по окончанию работы курсор следует закрывать (close).
3. Проход по курсору от строки к строке осуществляется в цикле loop и командой fetch, которая осуществляет выборку очередной строки курсора. При этом мы указываем какой переменной следует присвоить значение поле выбранной строки.
4. Необходимость выхода из цикла прохода по курсору осуществляется в конструкции if not found.
Перейдём к следующему примеру. На этот раз курсор будет задан внутри кода между begin и end. На входе функция получает фамилию студента и название предмета. На выходе мы получаем среднюю оценку студента по заданному предмету. Если один из параметров не корректен (нет такого студента или предмета) или просто у студента нет оценок по данному предмету, то выдаётся ноль. В данном случае мы пренебрегаем тем фактом, что могут несколько студентов с одинаковыми ФИО.
create or replace function cursor2(nm text, np text)
returns real
as
$$
declare
curs2 refcursor;
sm real := 0.0;
n real := 0.0;
oc integer;
nm1 text;
begin
open curs2 for execute 'select a.fio, b.ocenka
from student a inner join ocenki b on a.id=b.id_s
inner join predmeti c on c.id=b.id_predm
where c.predmet=$1' using np;
loop
fetch curs2 into nm1, oc;
if not found then
exit;
end if;
if trim(nm1) = trim(nm) then
sm := sm + oc;
n := n + 1;
end if;
end loop;
close curs2;
if n > 0 then
sm := sm/n;
end if;
return sm;
end;
$$
language 'plpgsql';
Представим некоторые пояснения к записанной функции.
1. В разделе declare мы объявляем не сам курсор, а ссылку на него. Объявление и создание происходит в разделе кода.
2. При объявлении курсора мы используем уже известный нами способ с использованием номеров параметров и ключевого слова using. Подчеркнём лишний раз, что такой подход наиболее удобен при программировании на PlpgSQL.
3. Мы разделили проверку условий отбора данных на две части. В select мы отбираем строки по предметы, а при проходе курсора с помощью if выделяем строки с нужной фамилией студента: if trim(nm1) = trim(nm) then.
4. Кроме того, мы добавили проверку на тот случай, если нужные строки не будут найдены: if n > 0 then.
5. вызов функции осуществляется обычным способом.
select * from cursor2('Кирилов', 'Математика');
Ранее мы указывали, как в цикле можно формировать выходное данное в виде таблицы. Как мы видим, обработка курсора это тоже циклический алгоритм, так что здесь должно быть всё понятно. Ниже представлен пример.
create or replace function cursor3(idd integer)
returns table(nm text, predm text, oc integer)
as
$$
declare
curs1 refcursor;
nm1 text;
predm1 text;
oc1 integer;
begin
open curs1 for execute 'select a.fio, c.predmet, b.ocenka
from student a inner join ocenki b on a.id=b.id_s
inner join predmeti c on c.id=b.id_predm
where a.id=$1' using idd;
loop
fetch curs1 into nm1, predm1, oc1;
if not found then
exit;
end if;
nm := nm1;
predm := predm1;
oc := oc1;
return next; --строку в таблицу
end loop;
end;
$$
language plpgsql;
Как видим алгоритмическая составляющая нам уже знакома, только вместо перебора строк непосредственно полученных из запроса здесь перебираются строки курсора, которые опять же получаются из запроса. Но использование курсора это, конечно, ещё экономия памяти.
Заканчивая вопрос, связанный с курсорами нельзя обойти вопрос передачи курсоров между хранимыми функциями. И здесь всё действительно интересно. Курсор, точнее ссылку на него, можно передавать в качестве параметра, а можно и возвращать из функции. Ниже представим три такие функции.
create or replace function fcr1()
returns refcursor
as
$$
declare
cr1 refcursor;
begin
open cr1 for execute 'select id, fio from student';
return cr1;
end;
$$
language 'plpgsql';
Функция fcr1() создаёт и открывает курсор, который возвращает таблицу списка студентов вместе с идентификаторами. Затем этот курсор возвращается, больше ничего эта функция не делает. Важно, что курсор не закрывается, в противном случае его использовать.
create or replace function fcr2(refcursor)
returns table(fio text, oc integer)
as
$$
declare
cr2 refcursor;
cr3 refcursor = $1;
id1 integer;
oc1 integer;
nm text;
id_st integer;
begin
cr2 = (select fcr1());
loop
fetch cr2 into id1, nm;
if not found then
exit;
end if;
fio = nm;
loop
fetch cr3 into id_st, oc1;
if not found then
exit;
end if;
if id1 = id_st then
oc = oc1;
return next;
end if;
end loop;
move first from cr3;
end loop;
close cr2;
close cr3;
end;
$$
language 'plpgsql';
Функция fcr2() получает в своё распоряжение два курсора. Один курсор она получает вызвав функцию fcr1(). Второй курсор она получает в качестве параметра. Функция работает следующим образом. Внешний курсор cr2 перебирает имена студентов и их идентификаторов. Внутренний курсор ищет оценки для конкретного студента. Команда move переводит указатель cr3 на начало (первую строку). На команде move следует остановиться особо. Эта команда перемещает указатель в зависимости от параметра: next — следующая строка (по умолчанию), prior — к предыдущей строке, first — на первую строку, last — на последнюю строку, absolute — можно указать количество строк строк от начал, relative — указать количество строк перемещения относительно данного положения, forward — вперёд на указанное количество строк, backward — назад на указанное количество строк. Команда move не возвращает данных.
create or replace function fcr3()
returns table(fio1 text, oc1 integer)
as
$$
declare
cr3 refcursor;
begin
open cr3 for execute 'select id_s, ocenka from ocenki';
return query select fio, oc from fcr2(cr3);
end;
$$
language 'plpgsql';
Функция fcr3() создаёт курсор cr3 и передает его в функцию fcr2() в качестве параметра. Курсор содержит список оценок и идентификаторов студентов, которые получили эти оценки. Далее функция возвращает полученный табличный результат.
Заканчивая разговор о курсорах стоит остановиться на команде fetch, которая как вы уже поняли является важнейшим элементом работы с курсорами. Команда по умолчанию передвигает указатель на следующую строку курсора, если она есть (см. примеры представленных выше текстах хранимых функций), возвращая при этом указанные в ней данные, относящиеся к строке, на которую был сделан переход. Чаще всего команда так и используется. Но у команды есть параметры, позволяющие расширить её возможности. next — переход на следующую строку (по умолчанию), prior — переход на предыдущую строку, first — переход на первую строку, last — переход на последнюю строку, forward — переход на указанное число строк вперёд, backward — переход на указанное число строк назад. Следует отметить, что если указатель находится на последней строке и указано перейти вперёд, то он оказывается за последней строкой. Аналогично, если указатель находится на первой строке и указано перейти назад, то о оказывается за первой строкой. При открытии курсора указатель находится за первой строкой курсора. Поэтому первая команда fetch переводит указатель на первую строку.
Следующая статья о PLpgSQL здесь
Пока всё!
Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.
