Как использовать курсор в функциях на PL/pgSQL?

И снова SQL! А если быть точнее PL/pgSQL, сегодня поговорим именно об этом расширение языка SQL, а конкретней о том, как использовать курсор при написании функции в СУБД PostgreSQL. И о том, для чего вообще нужны курсоры, и когда их лучше использовать.

Надеюсь, Вы не забыли все те примеры и уроки, которые мы рассматривали ранее, так как для прочтения этой статьи необходимы минимальные знания SQL, для того чтобы Вы вспомнили, вот эти материалы: Как написать функцию на PL/pgSQL, Написание табличной функции на PL/pgSQL — функция, которая возвращает таблицу в последней, кстати, уже затрагивалась тема курсоров, но не подробно, поэтому сегодня мы поговорим о курсорах уже подробней.

Что такое курсор в SQL?

Курсор в SQL – это временная выборка записей в процессе выполнения функции, над которой могут выполняться необходимые Вам действия, данная выборка является указателем на область памяти.

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

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

О том, что курсоры могут быть полезны, мы поговорили, но когда их лучше использовать? А использовать их лучше всего только тогда, когда у Вас нет другого выхода! Потому что курсор является очень ресурсоемким решением. В принципе если Вы будете выполнять операции над небольшим количеством записей, то это приемлемо, а если необходимо обработать большой объем данных, то Вы можете очень долго ждать, пока будет выполняться Ваша функция, а как Вы знаете быстрота в нашем деле чуть ли не главный фактор.

Пример использования курсора в функции на PL/pgSQL

О теории мы поговорили пора переходить к практике, и для начала общий синтаксис курсора в функции.

   
   CREATE OR REPLACE FUNCTION название функции(типы переменных)
    RETURNS тип возвращаемого значения  AS
  $BODY$
   DECLARE
        объявление переменных
        объявление курсора
   BEGIN
     открытие курсора
        перебор данных и операции над ними
     закрытие курсора
     RETURN возвращение значения;
   END;
   $BODY$
     LANGUAGE 'plpgsql' VOLATILE

Теперь давайте приведем рабочий пример использования курсора, т.е. напишем функцию, в которой мы будем использовать курсор.

Примечание! Данный пример не из жизни, он смоделирован мной, поэтому у Вас такой ситуации может и не возникнуть.

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

Пример таблицы с несколькими записями:

id_per id_user rashod summa pr
1 1 100 100 0
1 2 100 110 0
2 1 100 90 0
2 2 100 90 0
3 1 110 100 0
3 2 100 100 10

где,

  • id_per – период по которому идет отчет;
  • id_user – идентификатор сотрудника;
  • rashod – сумма расходов за этот период;
  • summa – сумма, которая выделялась на расходы;
  • pr – возможная премия, на погашение задолженности в прошлом периоде.

Стоит следующая задача, нам необходимо определить тот период у сотрудников, в котором они расходовали средств больше, чем им выдали, и потом не возместили. При условии, что в следующем месяце им могут возместить этот расход (колонка pr), а могут и не возместить.

Т.е. в нашем примере у сотрудника с id_user = 1, этот период будет с id_per = 2, а у сотрудника с id_user = 2, этот период будет с id_per = 3. Другими словами, во втором периоде они оба перерасходовали выданные им средства, но сотруднику с id_user = 2 в следующем месяце их возместили, а с id_user = 1 нет, поэтому первый период возникновения перерасхода (причем не погашенного) у сотрудника с id_user = 1 будет именно период с id_per = 2.

В общем как то так:). Но для нас главное научиться использовать курсор в функциях, и наша функция будет выглядеть вот так.

Схема в базе PostgreSQL называется test и таблица тоже называется test, а функцию я назвал test.my_fun(numeric). Numeric – это как Вы помните тип входящего параметра.

   
   CREATE OR REPLACE FUNCTION test.my_fun(numeric)
     RETURNS numeric  AS
   $BODY$
   DECLARE
        _id_user ALIAS FOR $1;
        --объявляем курсор
        crs_my CURSOR FOR select id_per, rashod, summa from test.test where 
                                                      id_user = _id_user order by id_per;
        --объявляем нужные нам переменные
        _id_per numeric;
        _rashod numeric;
        _summa numeric;
        _pr numeric;
        _var numeric;
        _rezult numeric;
   BEGIN
    _pr:=0;
    OPEN crs_my;--открываем курсор
    LOOP --начинаем цикл по курсору
    --извлекаем данные из строки и записываем их в переменные
    FETCH crs_my INTO _id_per, _rashod, _summa;
    --если такого периода и не возникнет, то мы выходим
    IF NOT FOUND THEN EXIT;END IF;
    --ищем сумму возмещения, если она была
    select into _pr pr from test.test where id_user=_id_user and id_per = _id_per+1;
    _var = _rashod - _summa;
        if _var > 0 then
                _var = _var - _pr;
        End if;
        _rezult=_id_per;
        --если _var даже после возмещения больше нуля, то выходим и возвращаем период
        EXIT when _var > 0;
     END LOOP;--заканчиваем цикл по курсору
     CLOSE crs_my; --закрываем курсор
    RETURN _rezult;--возвращаем результат
   END;
   $BODY$
     LANGUAGE 'plpgsql' VOLATILE

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

Использовать функцию можно вот так:

   
   SELECT test.my_fun(1)

Результат, как Вы помните, будет 2.

Если хотите запустить по всем записям, то используйте вот такой запрос:

   
   SELECT test.my_fun(id_user) FROM test.test

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.