ЛИНТЕР

Процедуры с курсорным параметром

Все типы входных параметров (IN, INOUT, OUT) могут быть объявлены с типом данных CURSOR.

Входной курсорный параметр должен быть предварительно открыт в процедуре, которая вызывает на выполнение процедуру с данным курсорным параметром.

Курсорный параметр не может быть SQL-параметром.

Входные курсоры (выборки данных) в качестве входных параметров имеет смысл использовать в следующих случаях:

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

Примеры

Пусть есть таблица bank, содержащая данные об организациях и текущих денежных суммах на их расчетных счетах (баланс).

create or replace table bank(id_org int, summa numeric);
insert into bank (id_org, summa) values (1001, 27356.0), (2705, 110227.15), (4903, 2.75);
select * from bank;

Есть процедура, которая выполняется в конце каждого календарного месяца и производит корректировку баланса организации с учетом начисленных этой организации налоговых вычетов, отчислений, возвратов и т.п. Вычисление суммы корректировки баланса является довольно сложной процедурой, но в данном примере эта процедура просто возвращает для организации якобы вычисленную сумму корректировки:

create or replace procedure nalog(in id_org int) result numeric
code
  if id_org=1001 then return(-1577.65); endif;
  if id_org=2705 then return(3000.5); endif;
  if id_org=4903 then return(-86664.35); endif;
end;

Алгоритм работы:

в процедуре tst_cursor:
- открываем курсор для получения выборки данных обо всех организациях и их балансах;
- вызываем процедуру upd_cursor, передавая ей в качестве входного параметра имя открытого курсора;
```
create or replace procedure tst_cursor() result int for debug
declare
  var curs cursor(id int, sm numeric);
code
  open curs for "select id_org, summa from bank;";
  call upd_cursor(curs);
  return 0;
end;
```
процедура upd_cursor:
- используя переданный ей курсорный параметр, организует цикл для перемещения по всем записям полученной выборки данных;
- для каждой организации (записи выборки данных) применяется процедура nalog для вычисления суммы отчислений/доначислений и производится корректировка баланса организации в курсорной переменной;
- новая сумма баланса организации записывается из курсорной переменной в таблицу bank.
```
create or replace procedure upd_cursor(in query cursor(ident int, current_sum numeric)) result int
declare
  var upd_summa numeric;
code
  while not outofcursor(query) loop
    upd_summa:= query.current_sum + nalog(query.ident);
    execute "update bank set summa = ? where id_org = ?;" using upd_summa, query.ident;
    fetch query;
  endloop
  return 0;
end;
```