PostgreSQL Транзакции и курсоры. Полезные советы по PostgreSQL Что такое курсор в SQL

Вместо того чтобы сразу выполнять весь запрос, есть возможность настроить курсор, инкапсулирующий запрос, и затем получать результат запроса по нескольку строк за раз. Одна из причин так делать заключается в том, чтобы избежать переполнения памяти, когда результат содержит большое количество строк. (Пользователям PL/pgSQL не нужно об этом беспокоиться, так как циклы FOR автоматически используют курсоры, чтобы избежать проблем с памятью.) Более интересным вариантом использования является возврат из функции ссылки на курсор, что позволяет вызывающему получать строки запроса. Это эффективный способ получать большие наборы строк из функций.

Доступ к курсорам в PL/pgSQL осуществляется через курсорные переменные, которые всегда имеют специальный тип данных refcursor . Один из способов создать курсорную переменную, просто объявить её как переменную типа refcursor . Другой способ заключается в использовании синтаксиса объявления курсора, который в общем виде выглядит так:

имя [ [ NO ] SCROLL ] CURSOR [ ( аргументы ) ] FOR запрос ;

(Для совместимости с Oracle, FOR можно заменять на IS .) С указанием SCROLL курсор можно будет прокручивать назад. При NO SCROLL прокрутка назад не разрешается. Если ничего не указано, то возможность прокрутки назад зависит от запроса. Если указаны аргументы , то они должны представлять собой пары имя тип_данных , разделённые через запятую. Эти пары определяют имена, которые будут заменены значениями параметров в данном запросе. Фактические значения для замены этих имён появятся позже, при открытии курсора.

DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

Все три переменные имеют тип данных refcursor . Первая может быть использована с любым запросом, вторая связана (bound) с полностью сформированным запросом, а последняя связана с параметризованным запросом. (key будет заменён целочисленным значением параметра при открытии курсора.) Про переменную curs1 говорят, что она является несвязанной (unbound), так как к ней не привязан никакой запрос.

Прежде чем получать строки из курсора, его нужно открыть. (Это эквивалентно действию SQL-команды DECLARE CURSOR .) В PL/pgSQL есть три формы оператора OPEN , две из которых используются для несвязанных курсорных переменных, а третья для связанных.

40.7.2.1. OPEN FOR запрос

OPEN [[NO ] SCROLL ] FOR запрос ;

Курсорная переменная открывается и получает конкретный запрос для выполнения. Курсор не может уже быть открытым, а курсорная переменная обязана быть несвязанной (то есть просто переменной типа refcursor). Запрос должен быть командой SELECT или любой другой, которая возвращает строки (к примеру EXPLAIN). Запрос обрабатывается так же, как и другие команды SQL в PL/pgSQL : имена переменных PL/pgSQL заменяются на значения, план запроса кешируется для повторного использования. Подстановка значений переменных PL/pgSQL проводится при открытии курсора командой OPEN , последующие изменения значений переменных не влияют на работу курсора. SCROLL и NO SCROLL имеют тот же смысл, что и для связанного курсора.

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

40.7.2.2. OPEN FOR EXECUTE

OPEN не [[NO ] SCROLL ] FOR EXECUTE строка_запроса [USING выражение [, ... ] ];

Переменная курсора открывается и получает конкретный запрос для выполнения. Курсор не может быть уже открыт и он должен быть объявлен как несвязанная переменная курсора (то есть, как просто переменная refcursor). Запрос задаётся строковым выражением, так же, как в команде EXECUTE . Как обычно, это даёт возможность гибко менять план запроса от раза к разу (см. Подраздел 40.10.2). Это также означает, что замена переменных происходит не в самой строке команды. Как и с EXECUTE , значения параметров вставляются в динамическую команду, используя format() и USING . Параметры SCROLL и NO SCROLL здесь действуют так же, как и со связанным курсором.

OPEN curs1 FOR EXECUTE format("SELECT * FROM %I WHERE col1 = $1",tabname) USING keyvalue;

В этом примере в текст запроса вставляется имя таблицы с применением format() . Значение, сравниваемое с col1 , вставляется посредством параметра USING , так что заключать его в апострофы не нужно.

OPEN связанная_переменная_курсора [([ имя_аргумента := ] значение_аргумента [, ... ]) ];

Эта форма OPEN используется для открытия курсорной переменной, которая была связана с запросом при объявлении. Курсор не может уже быть открытым. Список фактических значений аргументов должен присутствовать только в том случае, если курсор объявлялся с параметрами. Эти значения будут подставлены в запрос.

План запроса для связанного курсора всегда считается кешируемым. В этом случае нет эквивалента EXECUTE . Обратите внимание, что SCROLL и NO SCROLL не могут быть указаны в этой форме OPEN , возможность прокрутки назад была определена при объявлении курсора.

При передаче значений аргументов можно использовать позиционную или именную нотацию. В позиционной нотации все аргументы указываются по порядку. В именной нотации имя каждого аргумента отделяется от выражения аргумента с помощью:= . Это подобно вызову функций, описанному в Разделе 4.3 . Также разрешается смешивать позиционную и именную нотации.

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

OPEN curs2; OPEN curs3(42); OPEN curs3(key:= 42);

Так как для связанного курсора выполняется подстановка значений переменных, то, на самом деле, существует два способа передать значения в курсор. Либо использовать явные аргументы в OPEN , либо неявно, ссылаясь на переменные PL/pgSQL в запросе. В связанном курсоре можно ссылаться только на те переменные, которые были объявлены до самого курсора. В любом случае значение переменной для подстановки в запрос будет определяться на момент выполнения OPEN . Вот ещё один способ получить тот же результат с curs3 , как в примере выше:

DECLARE key integer; curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key; BEGIN key:= 42; OPEN curs4;

После того как курсор будет открыт, с ним можно работать при помощи описанных здесь операторов.

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

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

40.7.3.1. FETCH

FETCH [ направление { FROM | IN } ] курсор INTO цель ;

FETCH извлекает следующую строку из курсора в цель . В качестве цели может быть строковая переменная, переменная типа record , или разделённый запятыми список простых переменных, как и в SELECT INTO . Если следующей строки нет, цели присваивается NULL. Как и в SELECT INTO , проверить, была ли получена запись, можно при помощи специальной переменной FOUND .

Здесь направление может быть любым допустимым в SQL-команде FETCH вариантом, кроме тех, что извлекают более одной строки. А именно: NEXT , PRIOR , FIRST , LAST , ABSOLUTE число , RELATIVE число , FORWARD или BACKWARD . Без указания направления подразумевается вариант NEXT . Везде, где используется число , оно может определяться любым целочисленным выражением (в отличие от SQL-команды FETCH , допускающей только целочисленные константы). Значения направления , которые требуют перемещения назад, приведут к ошибке, если курсор не был объявлен или открыт с указанием SCROLL .

курсор это переменная с типом refcursor , которая ссылается на открытый портал курсора.

FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x;

40.7.3.2. MOVE

MOVE [ направление { FROM | IN } ] курсор ;

MOVE перемещает курсор без извлечения данных. MOVE работает точно так же как и FETCH , но при этом только перемещает курсор и не извлекает строку, к которой переместился. Как и в SELECT INTO , проверить успешность перемещения можно с помощью специальной переменной FOUND .

MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4;

UPDATE таблица SET ... WHERE CURRENT OF курсор ; DELETE FROM таблица WHERE CURRENT OF курсор ;

Когда курсор позиционирован на строку таблицы, эту строку можно изменить или удалить при помощи курсора. Есть ограничения на то, каким может быть запрос курсора (в частности, не должно быть группировок), и крайне желательно использовать указание FOR UPDATE . За дополнительными сведениями обратитесь к странице справки DECLARE .

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

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

Имя портала, используемое для курсора, может быть указано разработчиком или будет генерироваться автоматически. Чтобы указать имя портала, нужно просто присвоить строку в переменную refcursor перед его открытием. Значение строки переменной refcursor будет использоваться командой OPEN как имя портала. Однако, если переменная refcursor имеет значение NULL, OPEN автоматически генерирует имя, которое не конфликтует с любым существующим порталом и присваивает его переменной refcursor .

Примечание

Связанная курсорная переменная инициализируется в строковое значение, представляющее собой имя самой переменной. Таким образом, имя портала совпадает с именем курсорной переменной, кроме случаев, когда разработчик переопределил имя, присвоив новое значение перед открытием курсора. Несвязанная курсорная переменная инициализируется в NULL и получит автоматически сгенерированное уникальное имя, если не будет переопределена.

Следующий пример показывает один из способов передачи имени курсора вызывающему:

CREATE TABLE test (col text); INSERT INTO test VALUES ("123"); CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS " BEGIN OPEN $1 FOR SELECT col FROM test; RETURN $1; END; " LANGUAGE plpgsql; BEGIN; SELECT reffunc("funccursor"); FETCH ALL IN funccursor; COMMIT;

В следующем примере используется автоматическая генерация имени курсора:

CREATE FUNCTION reffunc2() RETURNS refcursor AS " DECLARE ref refcursor; BEGIN OPEN ref FOR SELECT col FROM test; RETURN ref; END; " LANGUAGE plpgsql; -- для использования курсоров, необходимо начать транзакцию BEGIN; SELECT reffunc2(); reffunc2 -------------------- (1 row) FETCH ALL IN ""; COMMIT;

В следующем примере показан один из способов вернуть несколько курсоров из одной функции:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$ BEGIN OPEN $1 FOR SELECT * FROM table_1; RETURN NEXT $1; OPEN $2 FOR SELECT * FROM table_2; RETURN NEXT $2; END; $$ LANGUAGE plpgsql; -- для использования курсоров необходимо начать транзакцию BEGIN; SELECT * FROM myfunc("a", "b"); FETCH ALL FROM a; FETCH ALL FROM b; COMMIT;

Один из вариантов цикла FOR позволяет перебирать строки, возвращённые курсором. Вот его синтаксис:

[ << метка >> ] FOR переменная-запись IN связанная_переменная_курсора [ ([ имя_аргумента := ] значение_аргумента [, ... ]) ] LOOP операторы END LOOP [ метка ];

Курсорная переменная должна быть связана с запросом при объявлении. Курсор не может быть открытым. Команда FOR автоматически открывает курсор и автоматически закрывает при завершении цикла. Список фактических значений аргументов должен присутствовать только в том случае, если курсор объявлялся с параметрами. Эти значения будут подставлены в запрос, как и при выполнении OPEN (см. Подраздел 40.7.2.3).

Данная переменная-запись автоматически определяется как переменная типа record и существует только внутри цикла (другие объявленные переменные с таким именем игнорируется в цикле). Каждая возвращаемая курсором строка последовательно присваивается этой переменной и выполняется тело цикла.

И снова 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 в PostgreSQL представляет собой доступный только для чтения указатель на итоговый набор выполненной команды SELECT. Курсоры часто используются в приложениях, хранящих информацию о состоянии подключения к серверу PostgreSQL. Создание курсора и работа со ссылкой на полученный итоговый набор позволяет приложению организовать более эффективную выборку разных записей итогового набора без повторного выполнения запроса с другими значениями LIMIT и OFFSET.

В прикладных интерфейсах (API ) курсоры часто используются для объединения нескольких запросов с последующим их отслеживанием и управлением ими через ссылку на курсор на уровне приложения. Тем самым предотвращается необходимость хранения всех результатов в памяти приложения.

Курсоры часто обладают абстрактным представлением в прикладных интерфейсах (пример - класс PgCursor в libpq++), хотя приложение может напрямую создавать курсоры и работать с ними при помощи стандартных команд SQL. В этом подразделе описаны обобщенные принципы работы с курсорами в SQL, продемонстрированные на примере клиента psql. В PostgreSQL существуют четыре команды, предназначенные для работы с курсорами: DECLARE, FETCH, MOVE и CLOSE.

Команда DECLARE определяет и одновременно открывает курсор, после чего заполняет его информацией по результатам итогового набора выполненного запроса. Команда FETCH позволяет получить записи из открытого курсора. Команда MOVE перемещает "текущую" позицию курсора в итоговом наборе, а команда CLOSE закрывает курсор.

Примечание
Если вас интересует тема использования курсоров в конкретном интерфейсе API, обращайтесь к документации на API .

Объявление курсора

Команда SQL DECLARE создает курсор и выполняет его. Этот процесс также называется открытием курсора. Курсор может быть объявлен только в существующем транзакционном блоке, поэтому перед объявлением курсора должна быть выполнена команда BEGIN. Синтаксис команды DECLARE:

DECLARE курсор [ BINARY ] [ INSENSITIVE ] [ SCROLL ] CURSOR FOR запрос [ FOR { READ ONLY | UPDATE [ OF none [….]]}]

DECLARE курсор . Имя создаваемого курсора.
[ BINARY ] . Ключевое слово BINARY означает, что выходные данные должны возвращаться в двоичном формате вместо стандартного ASCII-кода. Иногда переключение на двоичный формат повышает эффективность курсора, но это относится лишь к пользовательским приложениям, поскольку стандартные клиенты (такие, как psql) работают только с текстовым выводом.
[ INSENSITIVE ] [ SCROLL ] . Ключевые слова INSENSITIVE и SCROLL существуют для совместимости со стандартом SQL, но они описывают поведение PostgreSQL по умолчанию, поэтому их присутствие не обязательно. Ключевое слово SQL INSENSITIVE обеспечивает независимость данных, возвращенных курсором, от других курсоров или подключении. Поскольку PostgreSQL требует, чтобы курсоры определялись в транзакционных блоках, это требование заведомо выполняется. Ключевое слово SQL SCROLL указывает, что курсор поддерживает одновременную выборку нескольких записей. Этот режим поддерживается в PostgreSQL по умолчанию, даже если ключевое слово SCROLL не указано.
CURSOR FOR запрос . Запрос, после выполнения которого итоговый набор становится доступным через курсор.
FOR { READ ONLY | UPDATE [ OF поле [….] ] } . В PostgreSQL 7.1.x поддерживаются курсоры, доступные только для чтения (READ ONLY), поэтому секция FOR оказывается лишней.

В листинге 7.42 мы создаем транзакцию командой BEGIN и открываем курсор с именем all_books, ассоциированный с командой SELECT * FROM books.

Листинг 7.42 . Объявление курсора.

Booktown=# BEGIN; BEGIN booktown=# DECLARE all_books CURSOR booktown-# FOR SELECT * FROM books; SELECT

Сообщение SELECT в конце листинга 7.42 говорит о том, что команда была выполнена успешно, а записи, полученные в результате запроса, стали доступными для курсора all_books.

имя [ [ NO ] SCROLL ] CURSOR [ ( аргументы ) ] FOR запрос ;

DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

40.7.2.1. OPEN FOR запрос

OPEN [[NO ] SCROLL ] FOR запрос ;

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

40.7.2.2. OPEN FOR EXECUTE

OPEN не [[NO ] SCROLL ] FOR EXECUTE строка_запроса [USING выражение [, ... ] ];

OPEN curs1 FOR EXECUTE format("SELECT * FROM %I WHERE col1 = $1",tabname) USING keyvalue;

OPEN связанная_переменная_курсора [([ имя_аргумента := ] значение_аргумента [, ... ]) ];

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

OPEN curs2; OPEN curs3(42); OPEN curs3(key:= 42);

DECLARE key integer; curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key; BEGIN key:= 42; OPEN curs4;

После того как курсор будет открыт, с ним можно работать при помощи описанных здесь операторов.

40.7.3.1. FETCH

FETCH [ направление { FROM | IN } ] курсор INTO цель ;

курсор это переменная с типом refcursor , которая ссылается на открытый портал курсора.

FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x;

40.7.3.2. MOVE

MOVE [ направление { FROM | IN } ] курсор ;

MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4;

UPDATE таблица SET ... WHERE CURRENT OF курсор ; DELETE FROM таблица WHERE CURRENT OF курсор ;

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

Примечание

Следующий пример показывает один из способов передачи имени курсора вызывающему:

В следующем примере используется автоматическая генерация имени курсора:

В следующем примере показан один из способов вернуть несколько курсоров из одной функции:

Один из вариантов цикла FOR позволяет перебирать строки, возвращённые курсором. Вот его синтаксис:

Транзакции и курсоры

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

Возникает очевидная проблема - что произойдет, если два пользователя одновременно попытаются зафиксировать взаимоисключающие изменения в одном объекте базы данных? В некоторых СУБД подобные конфликты предотвращаются путем блокировки (locking).

Блокировкой называется механизм, запрещающий выборку из объекта базы данных на время его модификации, и наоборот. Применение блокировки связано с очевидными проблемами из области быстродействия. Например, выборка заблокированных данных становится невозможной до момента завершения обновления.

В PostgreSQL используется механизм MVCC (Multi-Version Concurrency Control), позволяющий выполнять команды SQL в отложенных транзакционных блоках. Таким образом, каждое подключение к серверу PostgreSQL до фиксации результатов фактически поддерживает временный «образ» объектов базы данных, модифицируемых в транзакцпопном блоке.

Если в программе отсутствует команда начала транзакциопного блока, все команды SQL, передаваемые PostgreSQL, фиксируются автоматически, то есть база данных синхронизируется с. результатами команд сразу же после их выполнения. Тем не менее при явном создании транзакщюнного блока изменения в базе данных остаются невидимыми для других пользователей вплоть до фиксации. Таким образом, появляется возможность одновременного внесения изменений в разные объекты базы данных. Далее все изменения либо фиксируются, либо откатываются (отменяются).

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

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

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

Транзакционные блоки

Транзакционные блоки создаются командой SQL BEGIN, за которой могут следовать необязательные ключевые слова WORK или TRANSACTION. Эти ключевые слова делают команду более наглядной, но никак не влияют на работу ее пли транзакци-онного блока.

В листинге 7.38 приведен пример создания транзакционного блока в базе данных booktown.

Листинг 7.38. Создание транзакции

bOOktown=# BEGIN;

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

Транзакционный блок завершается командой SQL COMMIT, за которой также могут следовать необязательные ключевые слова WORK или TRANSACTION. В листинге 7.39 команда SQL COMMIT синхронизирует состояние базы данных с результатами команды UPDATE.

Листинг 7.39. Фиксация транзакции

booktown-# BEGIN; BEGIN

booktown=# UPDATE subjects SET location = NULL

booktown-f WHERE id = 12;

booktown=# SELECT location FROM subjects WHERE id - 12:

booktown=# COMMIT;

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

Транзакции откатываются командой SQL ROLLBACK, за которой также могут следовать необязательные ключевые слова WORK или TRANSACTION.

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

Листинг 7.40. Откат транзакции

id | subject | location

12 | Religion | (1 row)

booktown=# UPDATE subjects SET location = "Sunset Dr"

booktown-# WHERE id = 12;

booktown=# SELECT * FROM subjects WHERE id = 12;

id | subject | location

12 | Religion | Sunset Dr

booktown=# ROLLBACK;

booktown=# SELECT * FROM subjects WHERE id = 12;

id | subject I location

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

(COMMIT или ROLLBACK).

Листинг 7.41. Выход из аварийного состояния

booktown=# BEGIN:

booktown=# SELECT * FROM;

ERROR: parser: parse error at or near ";"

booktown=# SELECT * FROM books;

NOTICE: current transaction is aborted, queries ignored until end of transaction

booktown=# COMMIT;

Использование курсоров

В прикладных интерфейсах (API) курсоры часто используются для объединения нескольких запросов с последующим их отслеживанием и управлением ими через ссылку на курсор на уровне приложения. Тем самым предотвращается необходимость хранения всех результатов в памяти приложения.

Команда DECLARE определяет и одновременно открывает курсор, после чего заполняет его информацией по результатам итогового набора выполненного запроса. Команда FETCH позволяет получить записи из открытого курсора. Команда MOVE перемещает «текущую» позицию курсора в итоговом наборе, а команда CLOSE закрывает курсор.

ПРИМЕЧАНИЕ

Если вас интересует тема использования курсоров в конкретном интерфейсе API, обращайтесь к документации на API.

Объявление курсора

DECLARE курсор [ BINARY ] [ INSENSITIVE ] [ SCROLL ]

CURSOR FOR запрос

[ FOR { READ ONLY | UPDATE [ OF none [. ...]]}]

DECLARE курсор. Имя создаваемого курсора.
[ BINARY ]. Ключевое слово BINARY означает, что выходные данные должны возвращаться в двоичном формате вместо стандартного ASCII-кода. Иногда переключение на двоичный формат повышает эффективность курсора, но это относится лишь к пользовательским приложениям, поскольку стандартные клиенты (такие, как psql) работают только с текстовым выводом.
[ INSENSITIVE ] [ SCROLL ]. Ключевые слова INSENSITIVE и SCROLL существуют для совместимости со стандартом SQL, но они описывают поведение PostgreSQL по умолчанию, поэтому их присутствие не обязательно. Ключевое слово SQL INSENSITIVE обеспечивает независимость данных, возвращенных курсором, от других курсоров или подключении. Поскольку PostgreSQL требует, чтобы курсоры определялись в транзакционных блоках, это требование заведомо выполняется. Ключевое слово SQL SCROLL указывает, что курсор поддерживает одновременную выборку нескольких записей. Этот режим поддерживается в PostgreSQL по умолчанию, даже если ключевое слово SCROLL не указано.
CURSOR FOR запрос. Запрос, после выполнения которого итоговый набор становится доступным через курсор.
FOR { READ ONLY | UPDATE [ OF поле [. ...] ] }. В PostgreSQL 7.1.x поддерживаются курсоры, доступные только для чтения (READ ONLY), поэтому секция FOR оказывается лишней.

Листинг 7.42. Объявление курсора

booktown=# BEGIN;

booktown=# DECLARE all_books CURSOR

booktown-# FOR SELECT * FROM books;

Сообщение SELECT в конце листинга 7.42 говорит о том, что команда была выполнена успешно, а записи, полученные в результате запроса, стали доступными для курсора a! l_books.

Выборка из курсора

Выборка записей из курсора производится командой FETCH. Синтаксис команды

FETCH [ FORWARD BACKWARD | RELATIVE ]

[ число ALL | NEXT | PRIOR ]

{ IN | FROM } курсор

В этом объявлении курсор - имя курсора, из которого производится выборка записей. Курсор всегда указывает па «текущую» позицию итогового набора выполненной команды, а в выборке могут участвовать записи, находящиеся до или после текущей позиции. Направление выборки определяется ключевыми словами FORWARD и BACKUARD, но умолчанию используется прямая выборка (FORWARD). Ключевое слово RELATIVE не обязательно и поддерживается лишь для совместимости со стандартом SQL92.

ВНИМАНИЕ

В команде также может использоваться ключевое слово ABSOLUTE, но в PostgreSQL 7.1.x возможности абсолютного позиционирования и выборки в курсорах не реализованы. Курсор использует относительное позиционирование и выводит сообщение о том, что абсолютное позиционирование не поддерживается.

За ключевым словом, идентифицирующим направление, может указываться следующий аргумент - количество записей. Допускается указание конкретного числа записей (в виде целочисленной константы) или одного из нескольких ключевых слов. Ключевое слово ALL означает, что команда возвращает все записи, начиная с текущей позиции курсора. С ключевым словом NEXT (используется по умолчанию) команда возвращает следующую запись от текущей позиции курсора. С ключевым словом PRIOR возвращается запись, находящаяся перед текущей позицией курсора.

Ключевые слова IN и FROM эквивалентны, из них в команде должно присутствовать одно.

В листинге 7.43 выбираются первые четыре записи итогового набора, на который ссылается курсор all_books. Направление не указано, поэтому по умолчанию используется ключевое слово FORWARD. Далее команда FETCH с ключевым словом NEXT выбирает пятую запись, после чего команда FETCH с ключевым словом PRIOR снова возвращается к четвертой записи.

Листинг 7.43. Выборка записей из курсора

booktown=# FETCH 4 FROM all_books;

Id | title | authored | suojectjd

7808 | The Shining | 4156 | 9

4513 | Dune 1 1866 | 15

4267 I 2001: A Space Odyssey | 2001 | 15

1608 I The Cat in the Hat j 1809 2

booktown=# FETCH NEXT FROM all_books;

id | title | authorjd | subjectjd

1590 Bartholomew and the Oobleck 1809 2

booktown=# FETCH PRIOR FROM all_books:

id | title | authorjd subjectjd

1608 | The Cat in the Hat | 1809 | 2

Перемещение курсора

Курсор поддерживает информацию о текущей позиции в итоговом наборе команды SELECT. Перемещение курсора к заданной записи выполняется командой MOVE. Синтаксис команды MOVE:

MOVE [ FORWARD | BACKWARD | RELATIVE ]

[ число ALL | NEXT | PRIOR ]

{ IN | FROM } курсор

Как видно из приведенного объявления, синтаксис команды MOVE очень близок к синтаксису команды FETCH. Впрочем, команда MOVE никаких записей не возвращает и лишь перемещает текущую позицию курсора. Смещение задается целочисленной константой или ключевым словом ALL (перемещение в заданном направлении на максимально возможное расстояние), NEXT или PRIOR. В листинге 7.44 текущая позиция курсора перемещается на 10 записей вперед.

Листинг 7.44. Перемещение текущей позиции курсора

booktown=# MOVE FORWARD 10

booktown-# IN all_books;

Закрытие курсора

Команда CLOSE закрывает ранее открытый курсор. Курсор также автоматически закрывается при выходе из транзакционного блока, в котором он находится, при фиксации транзакции командой COMMIT или ее откате командой ROLLBACK. Синтаксис команды CLOSE (курсор - имя закрываемого курсора):

CLOSE курсор

В листинге 7.45 курсор all_books закрывается и освобождает занимаемую им память, вследствие чего данные курсора становятся недоступными.

Листинг 7.45. Закрытие курсора

Категории

Популярное