Документация на PostgreSQL 9
Вместо да изпълнявате цялата заявка наведнъж, възможно е да настроите курсор, който капсулира заявката и след това да извлича резултата от заявката няколко реда наведнъж. Една от причините да направите това е да избегнете препълване на паметта, когато резултатът съдържа голям брой редове. (Потребителите на PL/pgSQL не трябва да се тревожат за това, тъй като циклите FOR автоматично използват курсори, за да избегнат проблеми с паметта.) По-интересен случай на използване е връщане от функция за препратка към курсора, която позволява на повикващия да извлича низове от заявки. Това е ефективен начин за получаване на големи набори от редове от функции.
41.7.1. Деклариране на курсорни променливи
(За съвместимост с Oracle FOR може да се замени с IS.) С SCROLL курсорът може да се превърта назад. При NO SCROLL превъртането назад не е разрешено. Ако нищо не е посочено, възможността за превъртане назад зависи от заявката. Ако са посочени аргументи, те трябва да бъдат разделени със запетая двойки datatypename. Тези двойки дефинират имената, които ще бъдат заменени от стойностите на параметрите в тази заявка. Действителните стойности, които да заменят тези имена, ще се появят по-късно, когато курсорът се отвори.
И трите променливи са от тип данни refcursor. Първият може да се използва с всяка заявка, вторият е обвързан ( bound ) към напълно оформена заявка, а вторият е обвързан към параметризирана заявка. (ключът ще бъде заменен с целочислената стойност на параметъра, когато курсорът се отвори.) Казва се, че Curs1 е необвързан, защото към него не е обвързана заявка.
41.7.2. Отваряне на курсора
Преди да получите редове от курсора, той трябва да бъде отворен. (Това е еквивалентно на SQL командата DECLARE CURSOR .) PL/pgSQL иматри форми на оператора OPEN, две от които се използват за необвързани променливи на курсора, а третата за обвързани.
Забележка
Обвързаните курсорни променливи могат да се използват с FOR цикъл без изрично отваряне на курсор, както е описано в раздел 41.7.4.
41.7.2.1. ОТВОРЕНО ЗА запитване
Курсорната променлива се отваря и получава конкретна заявка за изпълнение. Курсорът не може вече да е отворен и променливата на курсора трябва да бъде необвързана (т.е. просто променлива от тип refcursor). Заявката трябва да бъде команда SELECT или друга, която връща редове (напр. EXPLAIN). Заявката се обработва по същия начин като другите SQL команди в PL/pgSQL: имената на променливите в PL/pgSQL се заменят със стойности, планът на заявката се кешира за повторна употреба. Замяната на стойностите на променливите в PL/pgSQL се извършва, когато курсорът е отворен с командата OPEN, последващите промени в стойностите на променливите не засягат курсора. SCROLL и NO SCROLL имат същото значение като за свързания курсор.
41.7.2.2. ОТВОРЕНО ЗА ИЗПЪЛНЕНИЕ
Курсорната променлива се отваря и получава конкретна заявка за изпълнение. Курсорът вече не може да бъде отворен и трябва да бъде деклариран като несвързана курсорна променлива (тоест като проста рекурсорна променлива). Заявката е зададена като низов израз, точно както в командата EXECUTE. Както обикновено, това дава гъвкавост за промяна на плана на заявката от време на време (вижте подраздел 41.10.2). Това също означава, че заместването на променливи не се извършва в самия команден ред. Както при EXECUTE, стойностите на параметрите се вмъкват в динамична команда с помощта на format() и USING. Опциите SCROLL и NO SCROLL имат същия ефект тук, както и със свързания курсор.
Този пример вмъква името на таблицата в текста на заявката с помощта на format().Стойността, която се сравнява с col1, се вмъква с помощта на параметъра USING, така че няма нужда да се поставя в апостроф.
41.7.2.3. Отваряне на асоцииран курсор
Можете да използвате позиционна нотация или нотация за име, когато предавате стойности на аргументи. В позиционната нотация всички аргументи са посочени по ред. В нотацията за именуване името на всеки аргумент е отделено от аргументния израз с :=. Това е подобно на извикването на функция, описано в раздел 4.3. Също така е позволено да се смесват позиционни и номинални обозначения.
Примери (тук се използват предварително декларираните курсори):
Тъй като заместването на променливи се извършва върху свързания курсор, всъщност има два начина за предаване на стойности към курсора. Или използвайте изрични аргументи в OPEN, или имплицитно, като се позовавате на PL/pgSQL променливи в заявката. В обвързан курсор можете да препращате само към променливи, които са били декларирани преди самия курсор. Във всеки случай стойността на променливата, която трябва да бъде заменена в заявката, ще бъде определена по време на изпълнение на OPEN. Ето друг начин да получите същия резултат с curs3, както в примера по-горе:
41.7.3. Използване на курсори
След като курсорът бъде отворен, той може да бъде манипулиран с помощта на описаните тук оператори.
Не е необходимо да работите с курсора в същата функция, в която е бил отворен. Възможно е да се върне стойност от тип refcursor от функция, което позволява на повикващия да продължи да работи с курсора. (Вътрешно, refcursor е обичайното име на низ на така наречения портал, съдържащ активната заявка за курсор. Това име може да бъде предадено, присвоено на други променливи от тип refcursor и т.н., без да нарушава портала.)
Всички портали са имплицитно затворени в края на транзакцията, така че стойността на рекурсора може да се използвада препраща към отворен курсор само до края на транзакцията.
41.7.3.1. ИЗВЪРШВАНЕ
FETCH извлича следващия ред от курсора към целта. Целта може да бъде низова променлива, променлива от тип record или разделен със запетая списък от прости променливи, като в SELECT INTO. Ако няма следващ ред, целта е зададена на NULL. Както при SELECT INTO, можете да проверите дали е получен запис, като използвате специалната променлива FOUND.
Тук посоката може да бъде всяка опция, разрешена в командата SQL FETCH, с изключение на тези, които извличат повече от един ред. А именно: СЛЕДВАЩО, ПРЕДИШНО, ПЪРВО, ПОСЛЕДНО, АБСОЛЮТНО число, ОТНОСИТЕЛНО число, НАПРЕД или НАЗАД. Без посока се подразбира опцията NEXT. Където и да се използва число, то може да бъде дефинирано от произволен израз за цяло число (за разлика от командата FETCH SQL, която позволява само цели константи). Стойностите на посоката, които изискват движение назад, ще доведат до грешка, ако курсорът не е деклариран или отворен с SCROLL.
курсорът е променлива от тип refcursor, която препраща към отворения портал на курсора.
41.7.3.2. ХОД
MOVE премества курсора, без да извлича данни. MOVE работи точно като FETCH, но само премества курсора и не извлича реда, на който се е преместил. Както при SELECT INTO, можете да проверите дали преместването е било успешно, като използвате специалната променлива FOUND.
41.7.3.3. АКТУАЛИЗИРАНЕ/ИЗТРИВАНЕ КЪДЕ ТЕКУЩО НА
Когато курсорът е позициониран върху ред от таблица, този ред може да бъде модифициран или изтрит с помощта на курсора. Има ограничения за това какво може да бъде запитването на курсора (по-специално, не трябва да има групиране) и е много желателно да се използва клаузата FOR UPDATE. За повече информация, моля, вижтеДЕКЛАРИРАЙТЕ помощна страница.
41.7.3.4. БЛИЗО
CLOSE затваря портала, свързан с курсора. Използва се за освобождаване на ресурси преди приключване на транзакция или за освобождаване на променлива на курсора за повторно отваряне.
41.7.3.5. Връщане на курсор от функция
Курсорите могат да бъдат върнати от PL/pgSQL функция. Това е полезно, когато трябва да върнете много редове и колони, особено ако извадките са много големи. За да направи това, функцията отваря курсор и връща името му на повикващия (или просто отваря курсора, използвайки указаното име на портала, известно по някакъв начин на повикващия). Тогава повикващият може да извлече редове от курсора. Курсорът може да бъде затворен от повикващия или ще бъде затворен автоматично, когато транзакцията приключи.
Името на портала, използвано за курсора, може да бъде посочено от разработчика или ще бъде генерирано автоматично. За да посочите име на портал, просто присвоете низ към променливата refcursor, преди да я отворите. Стойността на низа на променливата refcursor ще се използва от командата OPEN като име на портала. Ако обаче променливата refcursor е NULL, OPEN автоматично генерира име, което не е в конфликт с нито един съществуващ портал, и го присвоява на променливата refcursor.
Забележка
Свързаната курсорна променлива се инициализира към низова стойност, представляваща името на самата променлива. По този начин името на портала е същото като името на променливата на курсора, освен ако разработчикът не е заменил името, като е присвоил нова стойност, преди да отвори курсора. Необвързана курсорна променлива се инициализира на NULL и ще получи автоматично генерирано уникално име, освен ако не бъде заменено.
Следващият пример показва един начин за предаване на имекурсор към обаждащия се:
Следващият пример използва автоматично генериране на име на курсора:
Следният пример показва един начин за връщане на множество курсори от една функция:
41.7.4. Обработка на курсора в цикъл
Един вариант на цикъла FOR ви позволява да обикаляте редовете, върнати от курсора. Ето неговия синтаксис:
Тази променлива за запис се дефинира автоматично като променлива от тип record и съществува само вътре в цикъла (други декларирани променливи със същото име се игнорират в цикъла). Всеки ред, върнат от курсора, се присвоява последователно на тази променлива и тялото на цикъла се изпълнява.