Циклы и курсоры

Циклы — повторяющиеся действия (повторение блока кода) для каждой строки отдельно.

Виды циклов:

Бесконечный цикл

Общий вид:

loop
	Действие
	exit when условие
end loop;

Пример:

do $$
declare i int = 1;
Begin
	loop
		raise notice 'i = %', i;
		i=i+1;
	exit when i > 5;
	end loop;
End; $$

Пока условие истинно (while loop)

Общий вид:

while условие
loop
	Действие;
end loop;

По диапазону (for in loop)

Общий вид:

for Пер in условие
loop
	Действие;
end loop;

Пример:

do $$
declare i int = 1;
Begin
for i in 2..5 loop
	raise notice '%',i;
	i=i+1;
end loop;
End; $$

По результату select (for in select)

Общий вид:

for пер in select ...
loop
	Действие;
end loop;

Пример:

do $$
declare rec record;
Begin
	for rec in select s.id, s.name from s
	loop
		raise notice 'id %, Name %', rec.id, rec.name;
	End loop;
End; $$

Курсор

Это механизм, который позволяет выполнять SQL-запросы, при этом сохранять их во временной области и позволяет пошагово извлекать данные (выделять конкретные строки), что позволяет управлять порядком обхода данных.

Виды курсора:

Не именованный

Специальный курсор типа refcursor Позволяет:

1. Создавать курсор внутри функции или процедуры
2. Не имеет фиксированного имени
3. Используется для возврата результата запроса постепенно, а не разом.

Общий вид:

create or replace function ИНДФ()
returns refcursor as $$
declare cur refcursor;
Begin
	Действие
End;$$ language plpgsql;

Пример: Вариант 1

create or replace function gr632_cursor()
returns refcursor as $$
declare cur refcursor;
Begin
	open cur for select s.id, s.name from s;
	return cur;
End;$$ language plpgsql;

Вариант 2

create or replace function gr632_cursor(cur2 refcursor)
returns refcursor as $$
Begin
	open cur2 for select s.id, s.name from s;
	return cur2;
End;$$ language plpgsql;

Именованный

Курсор у которого есть имя

Общий вид: declare ИМЯ ТИП_КУРСОРА cursor for select ...;

Типы курсора:

1. scroll | no scroll позволяет двигаться по курсору в вверх и вниз|позволяет двигаться только вниз
2. with hold позволяет пользоваться курсором после commit(курсор существует до конца сессии) без with hold курсор удаляется при commit

Пример:

Begin;
declare cur1 scroll cursor for select id, name from s;

fetch 3 from cur1;
fetch next from cur1;

close cur1;
commit;

fetch

позволяет выводить данные из курсора (Обращаться к строке в курсоре)

Варианты fecth:

fetch next from ИМЯ_Кур - следующая строка
fetch prior from ИМЯ_Кур - предыдущая строка
fetch first from ИМЯ_Кур - переход 1 строку
fetch last from ИМЯ_Кур - переход к последней строке
fetch absolute N from ИМЯ_Кур - переход к строке N

fetch relative N from ИМЯ_Кур - переход вперед N строк от текущей строки
fetch relative -N from ИМЯ_Кур - переход вперед N строк от текущей строки
fetch N from ИМЯ_Кур - вывод N строк с первой строки
fetch all from ИМЯ_Кур - Вывод всех строк

move - перемещение курсора
move next from ИМЯ_Кур - пропустить 1 строку
move relative N from ИМЯ_Кур - пропустить N строк
move absolute N from ИМЯ_Кур - переместиться к N строке

Разница между	Именованный	Не именованный
Создание	через declare	через open .. for в функции
Имя	Имеет	Не имеет
Использование	В транзакции	Функции и процедуры
Жизненный цикл	До commit | close	До конца транзакции
Основное применение	Постепенная выборка	Возврат курсора клиенту

Циклы с курсорами

1. loop ... fetch ... exit when Общий вид: declare 189: Имя_курсора cursor for select...; Имя_переменной record;

begin
    open Имя_курсора
    loop
        fetch Имя_курсора into Имя_переменной;
        Действия;
        exit when not found;
    end loop;
end;

2. for Пер in cursor

declare
Переменные
begin
    for Имя_переменной in Имя_курсора
    loop
        Действие;
    end loop;
end;

3. for Имя_переменной in select (без объявления курсора)

begin 
    for Имя_переменной in select...
    loop
        Действия;
    end loop;
end;

Пример:

create table authorBooks (
id serial primary key,
authorName text not null,
bookCount int not null,
status text);

insert into authorBooks (authorName, bookCount) values ('lev', 48),
('docka', 22),
('pymka', 33),
('typka', 11);

do $$
declare
rec record;
cur cursor for select authorName, bookCount, status from authorBooks;
Begin
	open cur;
	loop
		fetch cur into rec;
		exit when not found;
	begin
		if rec.bookCount >= 20 then
			update authorBooks
			set status = 'Великий'
			where authorBooks.id = rec.id;
		else
			update authorBooks
			set status = 'Хорошо'
			where authorBooks.id = rec.id;
		end if;
	end;
	end loop;
end;
$$ language plpgsql;

select * from authorBooks

do $$
declare 
rec record;
cur cursor for select authorName, bookCount, status from authorBooks;
total int = 0;
Begin
	open cur;
	loop
		fetch cur into rec;
		exit when not found;
	if rec.status = 'Великий' then
		total = total + 1;
	end if;
	end loop;
	raise notice 'количество великих авторов = %', total;
End;$$ language plpgsql;