Что такое курсор в sql

Курсоры в Mysql.

По долгу службы мне пришлось сталкиваться с курсорами. Хотелось бы рассказать, что это такое и о некоторых особенностях работы с ними. Официальная документация тут — dev.mysql.com/doc/refman/5.1/en/cursors.html Википедия даёт такое определение курсору курсор:

Курсор — ссылка на контекстную область памяти. В некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) — получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. бы сказал, что курсор — это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.
Используются курсоры в хранимых процедурах. Довольно теории давайте рассмотрим пример:
У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре )

/*данные о банке */
CREATE TABLE `bank` (
`BankId` INTEGER (11) NOT NULL ,
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
PRIMARY KEY (`BankId`)

)ENGINE=InnoDB
CHARACTER SET ‘utf8’ COLLATE ‘utf8_bin’ ;
/*данные о вкладах */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY (`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*данные о вкладчиках*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT » ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY (`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`)

)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET ‘utf8’ COLLATE ‘utf8_bin’

* This source code was highlighted with Source Code Highlighter .

Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос

Select `bank`.* FROM `bank` LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1

Таким образом используя LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1 мы извлекаем в цикле из таблицы bank по очереди каждую запись и производим с ней нужные нам действия, при этом увеличивая значение НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ на 1. Теперь сделаем то же самое но используя курсор

Begin
/* переменные куда мы извлекаем данные */
Declare vBankId integer ;
Declare vBankName VARCHAR (50);
Declare vAddress VARCHAR (50);
Declare vPhone VARCHAR (50);
/* переменная hadler — a*/
Declare done integer default 0;
/*Объявление курсора*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*HANDLER назначение, которого поясним чуть ниже*/
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
/* открытие курсора */
Open BankCursor;
/*извлекаем данные */
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
делаем нужные нам действия
END WHILE ;
/*закрытие курсора */
Close BankCursor;
END ;

* This source code was highlighted with Source Code Highlighter .

Поясним теперь подробнее. Сначала HANDLER, он нужен для обработки исключения — что делать когда данные закончатся ( то есть курсор будет пустым ). Таким образом когда данные закончатся, не с генерируется сообщение об ошибке, а значение переменной done выставиться в 1, изначально done = 0; подробнее об SQLSTATE читаем тут — dev.mysql.com/doc/refman/5.1/en/error-messages-server.html;

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Message: No data — zero rows fetched, selected, or processed

SQLSTATE: 02000 срабатывает когда достигнут конец курсора, или когда select или update возвращяет пустую строку.

Следующей строкой мы объявили курсор DECLARE cursor_name CURSOR FOR select_statement;
Открываем курсор Open cursor_name;
Дальше пока не достигаем конец курсора (WHILE done = 0 DO ) извлекаем данные и обрабатываем их.
Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name;

Вроде ничего сложного. Но с SQLSTATE ‘02000’ связанно много подводных камней.

WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом

WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

первым запросом мы проверили а есть ли вклады (если их нет то vContributeAmountSUM == 0 ) и только если таковые имеются мы извлекаем данные.

теперь допустим нам нужно излечь общую сумму на счетах в разных банках у каждого клиента
Declare ClientSummCursor Cursor for Select sum

Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`.`ClientId`;

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* извлекаем нужные нам данные */
FETCH ClientSummCursor INTO vSum,vClientId;
делаем какие то действия .
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

может возникнуть та же ситуация, когда данные в курсоре ClientSummCursor, закончатся раньше чем данные в BankCursor, сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* до извлечения данных из второго курсора запомним состояние sqlstate */
SET old_status = done;
/* извлекаем нужные нам данные */
FETCH ClientSummCursor INTO vSum,vClientId;
/* проверяем были ли извлечены данные , не стработал ли sqlstate 0200 */
if (done = 0 ) then
делаем какие то действия .
end if ;
/* перед окончанием while восттановим значение переменной done */
set done = old_status;
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Всем дочитавшим до этого места спасибо, надеюсь это статься покажется кому то полезной.

Что такое курсор в sql

В прошлый раз, я заморочил вам голову с аналогиями! Но надеюсь, что понятие транзакция для вас не пустой звук! 🙂 Что ж, давайте определим, что же такое «курсор». Если говорить по большому счету понятие курсор в серверах SQL является фундаментальным и вот почему. Все базовые операторы DML — это не что иное, как — да! да! «курсоры». При выполнении INSERT, UPDATE и т.д. в «глобальной системной области», (применительно к Oracle) — всегда, открывается «курсор»! Курсоры имеют ряд атрибутов для облегчения работы с ними. Так же курсоры могут быть явными и не явными. Кстати все операторы DML (благо их не так много) — это не что иное, как неявные курсоры. Так же курсоры могут быть параметризованные или нет. Что ж, много всего навалилось, давайте разбирать это все последовательно. Итак, немного анатомии курсора сервера Oracle. При обработке SQL — операторов Oracle выделяет область памяти называемую «контекстной областью» (context area). Она содержит информацию необходимую для начала и завершения обработки SQL оператора. В том, числе — число строк обрабатываемых оператором, указатель на представление этого оператора после синтаксического анализа (еще это называют парсинг) и активный набор (active set) — например набор строк возвращаемых запросом.

Итак: КУРСОР — это указатель (хотя как такового, понятия «указатель» в PL/SQL нет!) на контекстную область памяти, с помощью которого программа на языке PL/SQL может управлять контекстной областью и ее состоянием во время обработки оператора.

Объявление курсора определяет какое выражение языка SQL — будет передано программе SQL Statement Executor (системе исполнителю выражения SQL). Курсор может представлять собой любое допустимое предложение языка SQL! Так же, курсор является основным базовым «кирпичиком» для построения блоков PL/SQL. Курсоры обеспечивают циклический механизм оперирования наборами данных в БД. Курсор может возвращать одну или несколько строк данных или вообще ни одной.

1. Объявление курсора и структуры данных, в которую, будут помещены найденные строки.
2. Открытие курсора.
3. Последовательная выборка данных.
4. Закрытие курсора.

Полный синтаксис определения явного курсора таков:

------ CURSOR -- имя (передаваемые параметры) --- IS ----- ---------------- SELECT список полей FROM таблица выбора ---------------- WHERE условия выбора в курсор -----------

Вот так определяется явный курсор. Давайте запишем несколько определений курсоров, и вы посмотрите как это делается. Если пока что-то не понятно можете не расстраиваться, я все поясню по ходу.

DECLARE -- 1. -- Выбрать все заказы: CURSOR get_orders IS SELECT * FROM ORDERS; -- 2. -- Выбрать несколько столбцов -- для определенного номера заказа CURSOR get_orders(Pord_num ORDERS.order_num%TYPE) IS SELECT ORDER_DATE, MFR, AMOUNT FROM ORDERS WHERE order_num = Pord_num; -- 3. -- Получить полную запись -- для определенного номера заказа CURSOR get_orders(Pord_num ORDERS.order_num%TYPE) IS SELECT * FROM ORDERS WHERE order_num = Pord_num RETURN ORDERS%ROWTYPE; -- 4. -- Получение имени сотрудника -- по его номеру CURSOR get_name(empl_nm SALESREPS.empl_num%TYPE) RETURN SALESREPS.name%TYPE IS SELECT name FROM SALESREPS WHERE empl_num = empl_nm;

Надеюсь, вы заметили, что курсоры не просто так определены, а выполняют отбор вполне осмысленной информации. Вспомните наши мучения с SELECT, я не даром там распинался. Но те кто его хорошо знают, то же пусть освежат знания. Итак в определенных (явных) курсорах ключевым звеном является оператор SELECT (ДА ОН ВООБЩЕ КЛЮЧЕВОЙ ЗВЕНО ВСЕХ БД. ). Именно он определяет, что будет возвращать курсор после того как вы его откроете. Итак курсор под номером 1. Это простой не параметризованный курсор возвращающий все содержимое таблицы запроса в данном случае ORDERS. Это самый легкий из курсоров! 🙂 Курсор под номером 2. Это более сложный параметризованный курсор с одним параметром, но их может быть и больше. Он возвращает как видно из условия WHERE одну строку, так как поле order_num ключевое! Обратите внимание при передаче параметра использована конструкция %TYPE. Курсор под номером 3. Чуть более расширенная версия предыдущего, кроме того используется конструкция RETURN — с применением переменной записи на базе таблицы ORDERS. Такая конструкция просто возвратит одну полную запись из таблицы ORDERS при открытии курсора. Такие конструкции курсоров мы рассмотрим после того, как разберемся с процедурами и функциями. Курсор 4. Это то же самое, что и курсор 3, но он применяет конструкцию RETURN для того, чтобы вернуть значение одного поля таблицы SALESREPS, обратите внимание на местоположение оператора RETURN, хотя я в своей практике такие «фортели» с курсорами не использовал! 🙂 Но, тем не менее, в документации такое есть и вам будет полезно посмотреть как это выглядит! Вот собственно, так (пока отвлеченно) определяются курсоры. Можете пока все это переварить, так как дальше на вас обрушится еще большее количество информации. 🙂

Базовый курс SQL. Курсоры

До этого мы с вами рассматривали синтаксис различных SQL-запросов, работающих с данными в базе. К примеру, запрос SELECT извлекает наборы строк, соответствующих установленным условиям. Эти наборы могут иметь ноль, одну и более строк. И они называются результирующими.

Результирующий набор

Результирующий набор — это все строки, выбранные в результате SQL-запроса.

Результат запроса SELECT мы получаем всегда целиком. Но что если есть необходимость получить доступ конкретно к первой строке выборки, или следующей, или даже просмотреть строки в обратном порядке, по нескольку за раз? Все эти возможности предоставляют курсоры.

В разных СУБД спектр применения курсоров различается, но чаще всего реализуется:

• использование курсоров «только для чтения» — данные нельзя будет изменить или удалить
• указание направления чтения данных — вперёд, назад, первая или последняя строка, абсолютное или относительное положение
• разделение столбцов на редактируемые и нередактируемые
• ограничение области видимости — доступ к курсору будет только внутри запроса, который его создал или в любых запросах
• создание копии данных из таблицы с целью сохранения их в неизменном виде до обращения к курсору

Где поддерживаются курсоры

В MySQL курсоры поддерживаются начиная с 5 версии. В PostgreSQL и SQLite курсоры тоже есть, но могут быть различия в синтаксисе. А вот в Microsoft Access возможности, описанные в данной главе, не применимы.

Основной практический смысл использования курсоров — интерактивные приложения, позволяющие пользователям прокручивать какие-либо записи на экране вперёд и назад, просматривать и изменять их.

Как работать с курсорами

Есть несколько этапов, которые можно выделить при работе с курсорами:

1. Создание. Извлечения данных на этом этапе не происходит, а только создаётся текст запроса SELECT и курсору задаются параметры
2. Открытие для получения данных. Здесь данные извлекаются из таблиц
3. Чтение данных из курсора. Можно сделать, когда курсор уже заполнен
4. Закрытие. А также, возможно, освобождение занятых им ресурсов памяти (не для всех СУБД)

Целиком процесс работы с курсором выглядит так:

• когда курсор объявлен, его можно сколько угодно раз открывать и закрывать
• когда курсор открыт, можно сколько угодно раз извлекать из него строки

Создание

Для создания курсоров используется ключевое слово DECLIRE . Как всегда, в зависимости от СУБД, синтаксис команды немного различается. DECLARE назначает курсору имя и запрос SELECT , который определит, какие данные будут храниться внутри. Для примера давайте создадим корсор, который будет служить для внесения недостающих e-mail адресов в карточки студентов:

DECLARE NewCursor CURSOR FOR SELECT * FROM Students WHERE student_emeil IS NULL;

Это версия кода для MySQL, MariaDB, SQLServer и DB2. Ниже представим вариант для PostgreSQL и Oracle:

DECLARE CURSOR NewCursor IS SELECT * FROM Students WHERE student_emeil IS NULL;

NewCursor — это имя курсора — указывается после ключевого слова DECLARE . Следом, за словами FOR или IS , идут условия для выбора строк из БД, переданные с помощью запроса SELECT .

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

Работа с курсорами

Синтаксис команды для открытия курсора очень простой и поддерживается большинством СУБД:

OPEN CURSOR NewCursor

В этот момент выполняется заданный в команде DECLARE запрос, и строки, полученные в результате него, сохраняются (например, в переменной) и в дальнейшем могут быть просмотрены без обращения к БД.
Для доступа к данным используется команда FETCH . Для неё можно указать параметры: какие строки извлекать, откуда, и куда сохранить. Рассмотрим пример получения первой строки курсора в СУБД Oracle:

DECLARE TYPE StudentsCursor IS REF CURSOR RETURN Students%ROWTYPE; DECLARE StudentRecord Students%ROWTYPE BEGIN OPEN StudentsCursor; FETCH StudentsCursor INTO StudentRecord; CLOSE StudentsCursor; END;

FETCH извлекает текущую строку таблицы Students и записывает её в переменную StudentRecord . По умолчанию считывание начинается с первой строки. Чтобы обработать все строки таблицы, воспользуемся циклом:

DECLARE TYPE StudentsCursor IS REF CURSOR RETURN Students%ROWTYPE; DECLARE StudentRecord Students%ROWTYPE BEGIN OPEN StudentsCursor; LOOP FETCH StudentsCursor INTO StudentRecord; EXIT WHEN StudentsCursor%NOTFOUND; . -- Здесь могут быть какие-то действия с текущей строкой END LOOP; CLOSE StudentsCursor; END;

Здесь, так же, как и раньше, производится запись текущей строки в переменную StudentRecord с помощью команды FETCH , но на этот раз, с помощью цикла LOOP , мы обходим все строки таблицы, пока не будет удовлетворено условие StudentsCursor%NOTFOUND . На месте . можно добавить любой код для обработки, который будет применён последовательно к каждой строке.

Ещё один пример работы с курсором для SQL Server:

DECLARE @teacher_id INTEGER(10), @teacher_name CHAR(50), @teacher_surname CHAR(50), @teacher_middle_name CHAR(50), OPEN TeacherCursor FETCH NEXT FROM TeacherCursor INTO @teacher_id, @teacher_name, @teacher_surname, @teacher_middle_name WHILE @@FETCH_STATUS = 0 BEGIN . -- Здесь могут быть какие-то действия с текущей строкой FETCH NEXT FROM TeacherCursor INTO @teacher_id, @teacher_name, @teacher_surname, @teacher_middle_name END CLOSE TeacherCursor

Выборка осуществляется аналогично, командой FETCH , но каждый столбец сохранятеся в отдельную переменную. Строки перебирает цикл WHILE , который будет прерван при условии @@FETCH_STATUS = 0 — когда все строки таблицы будут обработаны. Здесь также опущено, что конкретно делается с данными, т.к. это зависит от того, что нужно пользователю, а задача курсора — только предоставить доступ к строкам таблицы.

Закрытие

После работы с данными, курсор следует закрыть. Чтобы снова обратиться к данному курсору, достаточно будет его открыть с помощью ключевого слова OPEN . Заново объявлять имя и условие не потребуется.

CLOSE NewCursor;

Закрыть курсор можно с помощью слова CLOSE . Для PostgreSQL, Oracle и DB2 этого достаточно. А в SQL Server придётся ещё явно освободить ресурсы, занятые курсором:

CLOSE NewCursor DEALLOCATE CURSOR NewCursor;

Что такое курсор в sql

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

40.7.1. Объявление курсорных переменных

Доступ к курсорам в 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 ), так как к ней не привязан никакой запрос.

40.7.2. Открытие курсора

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

Примечание

Связанные курсорные переменные можно использовать с циклом FOR без явного открытия курсора, как описано в Подразделе 40.7.4.

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 , так что заключать его в апострофы не нужно.

40.7.2.3. Открытие связанного курсора

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;

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

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

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

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

40.7.3.1. FETCH

FETCH [направление ] курсор 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 [направление ] курсор;

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

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

40.7.3.3. UPDATE/DELETE WHERE CURRENT OF

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

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

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

40.7.3.4. CLOSE

CLOSE курсор;

CLOSE закрывает связанный с курсором портал. Используется для того, чтобы освободить ресурсы раньше, чем закончится транзакция, или чтобы освободить курсорную переменную для повторного открытия.

CLOSE curs1;

40.7.3.5. Возврат курсора из функции

Курсоры можно возвращать из функции на 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;

40.7.4. Обработка курсора в цикле

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

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

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

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

Пред.	Наверх	След.
40.6. Управляющие структуры	Начало	40.8. Сообщения и ошибки