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

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

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

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

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

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

  2. передаваемая выборка данных перед последующим использованием должна быть обработана (модифицирована, обогащена данными) в соответствии со встроенным алгоритмом обработки.

Примеры

Пусть есть таблица 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;

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

  1. в процедуре 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;

  2. процедура 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;