Как использовать курсор в функциях на 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

Заметка! Для комплексного изучения языка SQL рекомендую почитать мою книгу «SQL код». Данный книга рассчитана на изучение языка SQL как стандарта, т.е. на изучение тех возможностей SQL, которые доступны и точно будут работать во всех популярных системах управления базами данных (СУБД).

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

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

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