DECLARE
DECLARE — определить курсор
Синтаксис
DECLAREимя[ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FORquery
Описание
Оператор DECLARE позволяет пользователю создавать курсоры, с помощью которых можно выбирать по очереди некоторое количество строк из результата большого запроса. Когда курсор создан, через него можно получать строки, применяя команду FETCH.
Примечание
На этой странице описывается применение курсоров на уровне команд SQL. Если вы попытаетесь использовать курсоры внутри функции PL/pgSQL, правила будут другими — см. Раздел 41.7.
Параметры
имяИмя создаваемого курсора.
BINARYКурсор с таким свойством возвращает данные в двоичном, а не текстовом формате.
INSENSITIVEУказывает, что данные, считываемые из курсора, не должны зависеть от изменений, которые могут происходить в нижележащих таблицах после создания курсора. В Postgres Pro это поведение подразумевается по умолчанию, так что это ключевое слово ни на что не влияет и принимается только для совместимости со стандартом SQL.
SCROLLNO SCROLLУказание
SCROLLопределяет, что курсор может прокручивать набор данных и получать строки непоследовательно (например, в обратном порядке). В зависимости от сложности плана запроса указаниеSCROLLможет отрицательно отразиться на скорости выполнения запроса. УказаниеNO SCROLL, напротив, определяет, что через курсор нельзя будет получать строки в произвольном порядке. По умолчанию прокрутка в некоторых случаях разрешается; но это не равнозначно эффекту указанияSCROLL. За подробностями обратитесь к разделу Замечания.WITH HOLDWITHOUT HOLDУказание
WITH HOLDопределяет, что курсор можно продолжать использовать после успешной фиксации создавшей его транзакции.WITHOUT HOLDопределяет, что курсор нельзя будет использовать за рамками транзакции, создавшей его. Если не указано ниWITHOUT HOLD, ниWITH HOLD, по умолчанию подразумеваетсяWITHOUT HOLD.queryКоманда SELECT или VALUES, выдающая строки, которые будут получены через курсор.
Ключевые слова BINARY, INSENSITIVE и SCROLL могут указываться в любом порядке.
Замечания
Обычный курсор выдаёт данные в текстовом виде, в каком их выдаёт SELECT. Однако с указанием BINARY курсор может выдавать их и в двоичном формате. Это упрощает операции преобразования данных для сервера и клиента, за счёт дополнительных усилий, требующихся от программиста для работы с платформозависимыми двоичными форматами. Например, если запрос получает значение 1 из целочисленного столбца, обычный курсор выдаст строку, содержащую 1, тогда как через двоичный курсор будет получено четырёхбайтовое поле, содержащее внутреннее представление значения (с сетевым порядком байтов).
Двоичные курсоры должны применяться с осмотрительностью. Многие приложения, в том числе psql, не приспособлены к работе с двоичными курсорами и ожидают, что данные будут поступать в текстовом формате.
Примечание
Когда клиентское приложение выполняет команду FETCH, используя «расширенный» протокол запросов, в сообщении Bind этого протокола указывается, в каком формате, текстовом или двоичном, должны быть получены данные. Это указание переопределяет свойство курсора, заданное в его объявлении. Таким образом, концепция курсора, объявляемого двоичным, становится устаревшей при использовании расширенного протокола запросов — любой курсор может быть прочитан как текстовый или двоичный.
Если в команде объявления курсора не указано WITH HOLD, созданный ей курсор может использоваться только в текущей транзакции. Таким образом, оператор DECLARE без WITH HOLD бесполезен вне блока транзакции: курсор будет существовать только до завершения этого оператора. Поэтому Postgres Pro сообщает об ошибке, если такая команда выполняется вне блока транзакции. Чтобы определить блок транзакции, примените команды BEGIN и COMMIT (или ROLLBACK).
Если в объявлении курсора указано WITH HOLD и транзакция, создавшая курсор, успешно фиксируется, к этому курсору могут продолжать обращаться последующие транзакции в этом сеансе. (Но если создавшая курсор транзакция прерывается, курсор уничтожается.) Курсор со свойством WITH HOLD (удерживаемый) может быть закрыт явно, командой CLOSE, либо неявно, по завершении сеанса. В текущей реализации строки, представляемые удерживаемым курсором, копируются во временный файл или в область памяти, так что они остаются доступными для следующих транзакций.
Объявить курсор со свойством WITH HOLD можно, только если запрос не содержит указаний FOR UPDATE и FOR SHARE.
Указание SCROLL добавляется при определении курсора, который будет выбирать данные в обратном порядке. Это поведение требуется стандартом SQL. Однако для совместимости с предыдущими версиями, Postgres Pro допускает выборку в обратном направлении и без указания SCROLL, если план запроса курсора достаточно прост, чтобы реализовать прокрутку назад без дополнительных операций. Тем не менее разработчикам приложений не следует рассчитывать на то, что курсор, созданный без указания SCROLL, можно будет прокручивать назад. С указанием NO SCROLL прокрутка назад запрещается в любом случае.
Выборка в обратном направлении также запрещается, если запрос содержит указания FOR UPDATE и FOR SHARE; в этом случае указание SCROLL не принимается.
Внимание
Прокручиваемые курсоры могут выдавать неожиданные результаты, если они вызывают изменчивые функции (см. Раздел 36.7). Когда повторно выбирается ранее прочитанная строка, функции могут вызываться снова и выдавать результаты, отличные от полученных в первый раз. Для запроса, вызывающего изменчивые функции, лучше всего указать NO SCROLL. Если такой способ не подходит, другая возможность обойти эту проблему — объявить курсор с указанием WITH HOLD и зафиксировать транзакцию, прежде чем читать из него какие-либо строки. В этом случае весь набор данных курсора будет материализован во временном хранилище, так что изменчивые функции будут выполнены для каждой строки лишь единожды.
Если запрос в определении курсора включает указания FOR UPDATE или FOR SHARE, возвращаемые курсором строки блокируются в момент первой выборки, так же, как это происходит при выполнении SELECT с этими указаниями. Кроме того, при чтении строк будут возвращаться их наиболее актуальные версии; таким образом, с этими указаниями курсор будет вести себя как «чувствительный курсор», определённый в стандарте SQL. (Указать INSENSITIVE для курсора с запросом FOR UPDATE или FOR SHARE нельзя.)
Внимание
Обычно рекомендуется использовать FOR UPDATE, если курсор предназначается для применения в командах UPDATE ... WHERE CURRENT OF и DELETE ... WHERE CURRENT OF. Указание FOR UPDATE предотвращает изменение строк другими сеансами после того, как они были считаны, и до того, как выполнится команда. Без FOR UPDATE последующая команда с WHERE CURRENT OF не сработает, если строка будет изменена после создания курсора.
Ещё одна причина использовать указание FOR UPDATE в том, что без него последующие команды с WHERE CURRENT OF могут выдать ошибку, если запрос курсора не удовлетворяет оговоренному в стандарте SQL критерию «простой изменяемости» (в частности, курсор должен ссылаться только на одну таблицу и не должен использовать группировку и сортировку (ORDER BY)). Курсоры, не удовлетворяющие этому критерию, могут работать либо не работать, в зависимости от конкретного выбранного плана; так что в худшем случае приложение может работать в тестовой, но сломается в производственной среде. С указанием FOR UPDATE курсор гарантированно будет изменяемым.
Не использовать же FOR UPDATE для команд с WHERE CURRENT OF в основном имеет смысл, только если требуется получить прокручиваемый курсор или курсор, не отражающий последующие изменения (то есть, продолжающий показывать прежние данные). Если это действительно необходимо, обязательно учтите при реализации приведённые выше замечания.
В стандарте SQL механизм курсоров предусмотрен только для встраиваемого SQL. Сервер Postgres Pro не реализует для курсоров оператор OPEN; курсор считается открытым при объявлении. Однако ECPG, встраиваемый препроцессор SQL для Postgres Pro, следует соглашениям стандарта, в том числе поддерживая для курсоров операторы DECLARE и OPEN.
Получить список всех доступных курсоров можно, обратившись к системному представлению pg_cursors.
Примеры
Объявление курсора:
DECLARE liahona CURSOR FOR SELECT * FROM films;
Другие примеры использования курсора можно найти в FETCH.
Совместимость
В стандарте SQL говорится, что чувствительность курсоров к параллельному обновлению нижележащих данных по умолчанию определяется реализацией. В Postgres Pro курсоры по умолчанию нечувствительные, а чувствительными их можно сделать с помощью указания FOR UPDATE. Другие СУБД могут работать иначе.
Стандарт SQL допускает курсоры только во встраиваемом SQL и в модулях. Postgres Pro позволяет использовать курсоры интерактивно.
Двоичные курсоры являются расширением Postgres Pro.