DECLARE#
DECLARE
DECLARE — определить курсор
Синтаксис
DECLAREname[ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FORquery
Описание
DECLARE позволяет пользователю создавать курсоры, которые
могут использоваться для извлечения
небольшого количества строк за раз из большого запроса.
После создания курсора строки извлекаются из него с помощью
FETCH.
Примечание
Эта страница описывает использование курсоров на уровне команд SQL. Если вы пытаетесь использовать курсоры внутри функции PL/pgSQL, правила отличаются — см. Раздел 40.7.
Параметры
nameИмя создаваемого курсора. Оно должно отличаться от имени любого другого активного курсора в сессии.
BINARYЗаставляет курсор возвращать данные в двоичном формате, а не в текстовом формате.
ASENSITIVEINSENSITIVEЧувствительность курсора определяет, видны ли изменения в данных, лежащих в основе курсора, сделанные в той же транзакции после объявления курсора.
INSENSITIVEозначает, что они не видны,ASENSITIVEозначает, что поведение зависит от реализации. Третье поведение,SENSITIVE, означающее, что такие изменения видны в курсоре, не доступно в Tantor BE. В Tantor BE все курсоры нечувствительны, поэтому эти ключевые слова не имеют эффекта и принимаются только для совместимости со стандартом SQL.Спецификация
INSENSITIVEвместе сFOR UPDATEилиFOR SHAREявляется ошибкой.SCROLLNO SCROLLSCROLLуказывает, что курсор может использоваться для извлечения строк в не последовательном порядке (например, назад). В зависимости от сложности плана выполнения запроса, указаниеSCROLLможет повлечь штраф к производительности времени выполнения запроса.NO SCROLLуказывает, что курсор не может использоваться для извлечения строк в не последовательном порядке. По умолчанию разрешено прокручивание в некоторых случаях; это не то же самое, что указаниеSCROLL. См. раздел Notes ниже для получения подробной информации.WITH HOLDWITHOUT HOLDWITH HOLDуказывает, что курсор может продолжать использоваться после успешного завершения транзакции, создавшей его.WITHOUT HOLDуказывает, что курсор не может использоваться за пределами транзакции, создавшей его. Если не указано ниWITHOUT HOLD, ниWITH HOLD, по умолчанию используетсяWITHOUT HOLD.queryКоманда
SELECTилиVALUES, которая предоставит строки, которые будут возвращены курсором.
Ключевые слова ASENSITIVE, BINARY,
INSENSITIVE и SCROLL могут
появляться в любом порядке.
Примечания
Все обычные курсоры возвращают данные в текстовом формате, так же, как и SELECT. Опция BINARY указывает, что курсор должен возвращать данные в бинарном формате. Это уменьшает усилия по преобразованию как на сервере, так и на клиенте, но требует больше усилий программиста для работы с платформозависимыми бинарными форматами данных. Например, если запрос возвращает значение "1" из столбца с целочисленными данными, то с обычным курсором вы получите строку 1, а с бинарным курсором вы получите 4-байтовое поле, содержащее внутреннее представление значения (в порядке байтов big-endian).
Бинарные курсоры следует использовать осторожно. Многие приложения, включая psql, не готовы обрабатывать бинарные курсоры и ожидают, что данные будут возвращаться в текстовом формате.
Примечание
Когда клиентское приложение использует протокол “расширенного запроса” для выполнения команды FETCH, сообщение протокола Bind определяет, будет ли извлекаться данные в текстовом или двоичном формате. Этот выбор переопределяет способ определения курсора. Понятие двоичного курсора как такового устарело при использовании протокола расширенного запроса — любой курсор может быть обработан как текстовый или двоичный.
Если не указано WITH HOLD, курсор, созданный этой командой, может использоваться только в пределах текущей транзакции. Таким образом, DECLARE без WITH HOLD бесполезен вне блока транзакции: курсор будет существовать только до завершения оператора. Поэтому Tantor BE сообщает об ошибке, если такая команда используется вне блока транзакции.
Используйте BEGIN и COMMIT (или ROLLBACK) для определения блока транзакции.
Если указано WITH HOLD и транзакция, которая создала курсор, успешно коммитится, курсор может продолжать использоваться последующими транзакциями в той же сессии. (Но если создающая транзакция отменяется, курсор удаляется). Курсор, созданный с использованием WITH HOLD, закрывается при явном выполнении команды CLOSE или при завершении сессии. В текущей реализации строки, представленные удерживаемым курсором, копируются во временный файл или область памяти, чтобы они оставались доступными для последующих транзакций.
WITH HOLD не может быть указано, когда запрос включает FOR UPDATE или FOR SHARE.
Опция SCROLL должна быть указана при определении курсора, который будет использоваться для получения данных в обратном порядке. Это требуется стандартом SQL. Однако, для обеспечения совместимости с предыдущими версиями, Tantor BE позволяет выполнять обратные выборки без указания SCROLL, если план запроса курсора достаточно простой и не требует дополнительных затрат для его поддержки. Однако, разработчикам приложений рекомендуется не полагаться на использование обратных выборок из курсора, который не был создан с использованием SCROLL. Если указана опция NO SCROLL, то обратные выборки запрещены в любом случае.
В случае, когда запрос содержит FOR UPDATE или FOR SHARE, также запрещены обратные выборки; поэтому в этом случае нельзя указывать SCROLL.
Предостережение
Прокручиваемые курсоры могут давать неожиданные результаты, если они вызывают какие-либо волатильные функции (см. Раздел 35.7). При повторном получении ранее выбранной строки функции могут быть повторно выполнены, что может привести к результатам, отличным от первого раза. Лучше всего указать NO SCROLL для запроса, включающего волатильные функции. Если это не практично, одним из способов обхода является объявление курсора SCROLL WITH HOLD и коммит транзакции перед чтением строк из него. Это приведет к тому, что весь вывод курсора будет материализован во временном хранилище, так что волатильные функции будут выполнены ровно один раз для каждой строки.
Если запрос курсора включает FOR UPDATE или FOR
SHARE, то возвращаемые строки блокируются в момент первого получения, так же, как и для обычной команды SELECT с этими опциями.
Кроме того, возвращаемые строки будут самыми актуальными версиями.
Предостережение
Считается хорошей практикой использовать 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. Сервер Tantor BEне реализует оператор OPEN для курсоров; курсор считается открытым при его объявлении. Однако ECPG, встроенный SQL-препроцессор для Tantor BE, поддерживает стандартные соглашения SQL по курсорам, включая операторы DECLARE и OPEN.
Структура данных сервера, лежащая в основе открытого курсора, называется
порталом. Имена порталов отображаются в
клиентском протоколе: клиент может извлекать строки непосредственно из открытого
портала, если он знает имя портала. При создании курсора с помощью
DECLARE, имя портала совпадает с
именем курсора.
Вы можете увидеть все доступные курсоры, выполнив запрос к системному представлению pg_cursors.
Примеры
Для объявления курсора:
DECLARE liahona CURSOR FOR SELECT * FROM films;
См. FETCH для получения дополнительных примеров использования курсоров.
Совместимость
Стандарт SQL позволяет использовать курсоры только во встроенном SQL и в модулях. Tantor BE позволяет использовать курсоры в интерактивном режиме.
Согласно стандарту SQL, изменения, внесенные в нерегистрозависимые курсоры с помощью операторов UPDATE ... WHERE CURRENT OF и DELETE ... WHERE CURRENT OF, видны в этом же курсоре. Tantor BE обрабатывает эти операторы так же, как и все другие операторы изменения данных, то есть они не видны в нерегистрозависимых курсорах.
Бинарные курсоры - это расширение Tantor BE.