Как работает курсор в sql

IgorSikors.github.io

Cursor это структурная переменная, позволяющая вам обрабатывать данные с несколькими строками. Количество строк зависит от команды запроса данных после нее. В процессе обработки, вы можете манипулировать с Cursor через каждую строку данных. Эта строка данных определена курсором. Перемещая курсор, вы можете получить все данные текущей строки.

Терминология

В PL/SQL имеется множество возможностей выполнения команд SQL, и все они реализованы в программах как курсоры того или иного типа. Прежде чем приступить к их освоению, необходимо познакомиться с методами выборки данных и используемой при этом терминологией.

• Статический SQL. Команда SQL называется статической, если она полностью определяется во время компиляции программы.
• Динамический SQL. Команда SQL называется динамической, если она строится и выполняется на стадии выполнения программы, так что в программном коде нет ее фиксированного объявления. Для динамического выполнения команд SQL могут использоваться программы встроенного пакета DBMS_SQL (имеющегося во всех версиях Oracle) или встроенный динамический SQL.
• Результирующий набор строк. Набор строк с результирующими данными, удовлетворяющими критериям, определяемым командой SQL. Результирующий набор кэшируется в системной глобальной области с целью ускорения чтения и модификации его данных.
• Неявный курсор. При каждом выполнении команды DML (INSERT, UPDATE, MERGE или DELETE) или команды SELECT INTO, возвращающей строку из базы данных прямо
• SELECT FOR UPDATE. Разновидность обычной команды SELECT, устанавливающая блокировку на каждую возвращаемую запросом строку данных. Пользоваться ею следует только в тех случаях, когда нужно «зарезервировать» запрошенные данные, чтобы никто другой не мог изменить их, пока с ними работаете вы.

Типичные операции с запросами и курсорами

Независимо от типа курсора процесс выполнения команд SQL всегда состоит из одних и тех же действий. В одних случаях PL/SQL производит их автоматически, а в других, как, например, при использовании явного курсора, они явно организуются программистом.

• Разбор. Первым шагом при обработке команды SQL должен быть ее разбор (синтаксический анализ), то есть проверка ее корректности и формирование плана выполнения (с применением оптимизации по синтаксису или по стоимости в зависимости от того, какое значение параметра OPTIMIZER_MODE задал администратор базы данных).
• Привязка. Приязкой называется установление соответствия между значениями программы и параметрами команды SQL. Для статического SQL привязка производится ядром PL/SQL. Привязка параметров в динамическом SQL выполняется явно с использованием переменных привязки.
• Открытие. При открытии курсора определяется результирующий набор строк команд SQL, для чего используются переменные привязки. Указатель активной или текущей строки указывает на первую строку результирующего набора. Иногда явное открытие курсора не требуется; ядро PL/SQL выполняет эту операцию автоматически (так происходит в случае применения неявных курсоров и встроенного динамического SQL).
• Выполнение. На этой стадии команда выполняется ядром SQL.
• Выборка. Выборка очередной строки из результирующего набора строк курсора осуществляется командой FETCH. После каждой выборки PL/SQL перемещает указатель на одну строку вперед. Работая с явными курсорами, помните, что и после завершения перебора всех строк можно снова и снова выполнять команду FETCH, но PL/SQL ничего не будет делать (и не станет инициировать исключение) — для выявления этого условия следует использовать атрибуты курсора.
• Закрытие. Операция закрывает курсор и освобождает используемую им память. Закрытый курсор уже не содержит результирующий набор строк. Иногда явное закрытие курсора не требуется, последовательность PL/SQL делает это автоматически (для неявных курсоров и встроенного динамического SQL).

Знакомство с атрибутами курсоров

• %FOUND — TRUE, если успешно выбрана хотя бы одна строка; в противном случае возвращает FALSE
• %NOTFOUND — TRUE, если команда не выбрала ни одной строки; в противном случае возвращает FALSE
• %ROWCOUNT — Количество строк, выбранных из курсора на данный момент времени
• %ISOPEN — TRUE, если курсор открыт; в противном случае возвращает FALSE
• %BULK_ROWCOUNT — Количество измененных записей для каждого элемента исходной коллекции, заданной в команде FORALL
• %BULK_EXCEPTIONS — Информация об исключении для каждого элемента исходной коллекции, заданной в команде FORALL

Выбор между явным и неявным курсорами

Все последние годы знатоки Oracle (включая и авторов данной книги) убежденно доказывали, что для однострочной выборки данных никогда не следует использовать неявные курсоры. Это мотивировалось тем, что неявные курсоры, соответствуя стандарту ISO, всегда выполняют две выборки, из-за чего они уступают по эффективности явным курсорам.

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

• В некоторых случаях явные курсоры эффективнее неявных. Часто выполняемые критические запросы лучше протестировать в обеих формах, чтобы точно выяснить, как лучше выполнять каждый из них в каждом конкретном случае.
• Явными курсорами проще управлять из программы. Например, если строка не найдена, Oracle не инициирует исключение, а просто принудительно завершает выполняемый блок.

Поэтому вместо формулировки «явный или неявный?» лучше спросить: «инкапсулированный или открытый?» И ответ будет таким: всегда инкапсулируйте однострочные запросы, скрывая их за интерфейсом функции (желательно пакетной) и возвращая данные через RETURN.

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

Синтаксис объявления курсора:

Есть 2 вида курсора (Cursor):

Явный курсор

Неявный курсор.

Понятие явный означает, что при использовании нужно написать команду открытия курсора (open), и написать команду закрыть курсор после использования.

При каждом выполнении команды DML (INSERT, UPDATE, MERGE или delete) или команды SELECT INTO, возвращающей строку из базы данных в структуру данных программы, PL/SQL автоматически создает для нее курсор. Курсор этого типа называется неявным, поскольку Oracle автоматически выполняет многие связанные с ним операции, такие как выделение курсора, его открытие, выборку строк и т.д.

Атрибуты явного курсора:

%isopen возвращает значение True если cursor открыт;

%notfound возвращает значение true если отстутствует следующая строка;

%found возвращает значение true если присутствует следующая строка;

SQL Server CURSOR

Summary: in this tutorial, you will learn how to use the SQL Server cursor to process a result set, one row at a time.

SQL works based on set e.g., SELECT statement returns a set of rows which is called a result set. However, sometimes, you may want to process a data set on a row by row basis. This is where cursors come into play.

What is a database cursor

A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.

SQL Server cursor life cycle

These are steps for using a cursor:

First, declare a cursor.

To declare a cursor, you specify its name after the DECLARE keyword with the CURSOR data type and provide a SELECT statement that defines the result set for the cursor.

Next, open and populate the cursor by executing the SELECT statement:

Then, fetch a row from the cursor into one or more variables:

SQL Server provides the @@FETCHSTATUS function that returns the status of the last cursor FETCH statement executed against the cursor; If @@FETCHSTATUS returns 0, meaning the FETCH statement was successful. You can use the WHILE statement to fetch all rows from the cursor as shown in the following code:

After that, close the cursor:

Finally, deallocate the cursor:

SQL Server cursor example

We’ll use the prodution.products table from the sample database to show you how to use a cursor:

First, declare two variables to hold product name and list price, and a cursor to hold the result of a query that retrieves product name and list price from the production.products table:

Next, open the cursor:

Then, fetch each row from the cursor and print out the product name and list price:

After that, close the cursor:

Finally, deallocate the cursor to release it.

The following code snippets put everything together:

Here is the partial output:

In practice, you will rarely use the cursor to process a result set in a row-by-row manner.

In this tutorial, you have learned how to use the SQL Server cursor to process a result set, each row at a time.

Cursors in SQL Server

A SQL cursor is a database object which is used to retrieve data from a result set one row at a time.
A SQL cursor is used when the data needs to be updated row by row.

Why to use Cursors ?

In databases, operations are made on set of rows.
For example, a SELECT statement returns a set of rows which is called a result set and also sometimes the application logic needs to work with one row at a time rather than the entire result set at once.
This can be done using cursors.

Cursor Example :

In this example we will fill the blank data with previous values from a table using a cursor

Here is a table as EmployeeDetails1 where we have two complete data and two empty data in Name column as shown below

Now with the help of cursor, we will fill this blank data with previous given value (i.e. on the data on Name Column will come as “abc” and on the data will come as “pqr”.

Steps to create and use a Cursor.

1.Declaring Cursor :
A cursor is declared for defining SQL statements.

We have declared a cursor as EmployeeCursor for retrieving ID and Name from table EmployeeDetails1 as shown in above image.

2. Opening a Cursor :
A cursor is opened for storing data which are retrieved from result set.

3. Fetching Cursor :
When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

The FETCH_STATUS value is 0 until there are rows.when all rows are fetched then FETCH_STATUS becomes 1.

Convert the @Id column from int to varchar as shown above.

Then we have used UPDATE statement to fill up the blank data with the previous given values.

4. Closing Cursor :
The cursor should be closed explicitly after data manipulation.

5. Deallocating Cursor :
Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.

Курсоры в Mysql.

/*данные о банке */
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 .

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

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 .

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 .

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