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

SQL-Ex blog

В своем коде T-SQL я всегда использую теоретико-множественные операции. Мне говорили, что SQL Server ориентирован на обработку операций этого типа, и они будут выполняться быстрей, чем последовательная обработка. Я знаю, что существуют курсоры, но я не знаю, как их использовать. Можете дать мне несколько примеров курсора? Можете указать мне, когда использовать курсоры? Я предполагаю, что Microsoft включил их в SQL Server намеренно, поэтому они должны иметь свою нишу, где могут быть использованы эффективно.

Решение

• Рассмотрим пример курсора
• Разобьем курсор на компоненты
• Дадим дополнительные примеры курсора
• Проанализируем аргументы за и против использования курсоров

Пример курсора SQL Server

В качестве примера курсора возьмем простой скрипт для создания резервных копий всех баз данных SQL Server, в котором бэкапы создаются в последовательном стиле:

DECLARE @name VARCHAR(50) -- имя базы данных 
DECLARE @path VARCHAR(256) -- путь для файлов бэкапа 
DECLARE @fileName VARCHAR(256) -- имя файла бэкапа 
DECLARE @fileDate VARCHAR(20) -- используется для имени файла 
SET @path = 'C:\Backup\' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 
FETCH NEXT FROM db_cursor INTO @name 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor

Что такое курсор в SQL Server

Курсор SQL Server — это набор логики T-SQL, повторяемой в цикле для заданного количества строк по одной за раз. В курсоре может может выполняться обновление одной строки за раз или административный процесс, подобный последовательному созданию резервных копий баз данных. Курсоры используются при разработке, в административных процессах и процессах ETL.

Как написать курсор в SQL Server

1. Объявите ваши переменные (для имен файлов, имен баз данных, номеров счетов и т.д.), которые вам нужны для реализации логики, и присвойте им начальные значения.
   Эта логика будет меняться в зависимости от задачи.
2. Объявите курсор с конкретным именем (как db_cursor в этом примере), которое вы будете использовать на протяжении всей логики вместе с бизнес-логикой (оператор SELECT) для наполнения курсора требуемыми записями. Имя курсора может быть осмысленным. Сразу после этого следует открытие курсора.
   Эта логика будет меняться в зависимости от задачи.
3. Извлеките запись из курсора, чтобы начать обработку.
   Замечание. Число переменных, объявленных для курсора, число столбцов в операторе SELECT и число переменных в операторе FETCH одинаково. В рассматриваемом примере имеется только одна переменная для извлечения данных из единственного столбца. Однако если должно быть пять элементов данных в курсоре, то необходимо также указать пять переменных в операторе FETCH.
4. Обработка данных уникальна для каждого набора логики. Это может быть вставка, обновление, удаление и т.д. для каждой извлекаемой строки данных. Это самый важный набор логики в данном процессе, который выполняется для каждой строки.
   Эта логика будет меняться в зависимости от задачи.
5. Извлечение следующей записи из курсора, как это делалось на шаге 3, а затем шаг 4 снова повторяется при обработке выбранных данных.
6. По завершению обработки всех данных курсор закрывается.
7. На последнем и важном шаге вам необходимо освободить курсор, т.е. освободить все удерживаемые внутренние ресурсы SQL Server.

-- 1 - Объявление переменных 
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД * 
DECLARE @name VARCHAR(50) -- имя базы данных 
DECLARE @path VARCHAR(256) -- путь в файлам резервных копий 
DECLARE @fileName VARCHAR(256) -- имя файла бэкапа 
DECLARE @fileDate VARCHAR(20) -- используется для имени файла 
-- Инициализация переменных 
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД * 
SET @path = 'C:\Backup\' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
-- 2 - Объявление курсора 
DECLARE db_cursor CURSOR FOR 
-- Наполнить курсор вашей логикой 
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД * 
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 
-- Открыть курсор 
OPEN db_cursor 
-- 3 - Извлечь следующую запись из курсора 
FETCH NEXT FROM db_cursor INTO @name 
-- Проверить состояние курсора 
WHILE @@FETCH_STATUS = 0 
BEGIN 
-- 4 - Начало настраиваемой бизнес-логики 
-- * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД * 
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 
-- 5 - Извлечь следующую запись из курсора 
FETCH NEXT FROM db_cursor INTO @name 
END 
-- 6 - Закрыть курсор 
CLOSE db_cursor 
-- 7 - Освободить ресурсы 
DEALLOCATE db_cursor

Объяснение синтаксиса курсора

• Операторы DECLARE — объявление переменных, используемых в блоке кода.
• Операторы SET\SELECT — инициализация переменных конкретными значениями.
• Оператор DECLARE CURSOR — объявление курсора.
  Замечание: число переменных должно совпадать с числом столбцов в операторе SELECT для оператора DECLARE CURSOR FOR. Это может быть одна или больше переменных, связанных со столбцами в операторе SELECT.
• Оператор OPEN — открывает курсор и наполняет его данными, чтобы начать обработку.
• Операторы FETCH NEXT — Связывает конкретные значения из курсора с переменными в соответствии с оператором DECLARE CURSOR FOR и SELECT
  Замечание. Эта логика используется для начального наполнения до оператора WHILE, а затем повторяется на каждом шаге цикла оператора WHILE.
• Оператор WHILE — условие для начала и продолжения процесса обработки.
• Операторы BEGIN. END — начало и конец блока кода.
  При обработке данных может использоваться множество операторов BEGIN. END.
• Обработка данных — в нашем примере логика заключается в создании резервной копии базы данных по указанному пути и имени файла, но это могут быть любые операторы DML или административные задачи.
• Оператор CLOSE — Освобождает текущие данные и связанные с ними блокировки, но оставляет возможность повторно открыть курсор.
• Оператор DEALLOCATE — уничтожает курсор.

Зачем использовать курсоры в SQL Server

• Итерационный обход данных по одной строке за раз.
• Выполнение процесса в последовательной манере, подобной созданию резервных копий баз данных.
• Обновление данных во множестве таблиц для заданной учетной записи.
• Корректировка данных с помощью предварительно определенного набора данных в качестве записей курсора.

Когда использовать курсоры

• OLTP (оперативная обработка транзакций) — в большинстве сред OLTP логика на основе множества строк (INSERT, UPDATE или DELETE) имеет наибольшее предпочтение для коротких транзакций. Наша команда выполняла стороннее приложение, которое использовало курсоры для любой обработки, что вызывало проблемы, но это случалось нечасто. Обычно логики на основе множеств более чем достаточно, и курсоры редко могут понадобиться.
• Отчеты — для проектирования отчетов курсоры обычно не нужны. Однако наша команда столкнулась с требованиями к отчетности, когда не существовало ссылочной целостности в используемой базе данных, и было необходимо использовать курсор для корректного вычисления отчетных значений. Мы имели подобный опыт при необходимости агрегировать данные для последующих процессов. Подход на основе курсора было быстро разработать и выполнить в приемлемой манере.
• Последовательная обработка — если вам необходимо выполнить процесс в последовательной манере, курсоры являются работоспособным вариантом.
• Административные задачи — многие административные задачи, подобные резервированию баз данных или проверки согласованности баз данных, требуется выполнять в последовательной манере, которая хорошо вписывается в основанную на курсорах логику. Но существуют и другие системные объекты, которые удовлетворяют эту потребность. В некоторых из этих случаев курсоры используются для завершения процесса.
• Большие наборы данных — при больших наборах данных вы можете столкнуться с одним или несколькими из следующих случаев:
  • Логика на основе курсора может не масштабироваться в достаточной мере.
  • Операции на основе множеств с большими объемами данных на сервере с минимальным количеством памяти могут привести к тому, что данные будут выгружаться, отнимая много времени и потенциально вызвая конфликты и проблемы с памятью. В этом случае, решение на базе курсора может оказаться приемлемым.
  • Некоторые инструменты фактически кэшируют данные в файл, поэтому обработка данных в памяти может и не иметь места.
  • Если данные могут быть обработаны на автономном SQL Server, то влияние на производственную среду будет оказываться только на финальной стадии обработки. Все ресурсы автономного сервера могут использоваться для процессов ETL, после чего полученные данные импортируются.
  • SSIS поддерживает пакетную обработку наборов данных, которая может решить общую необходимость разбить большой набор данных на более мелкие и справиться с ними лучше, чем построчным методом на базе курсора.
  • В зависимости от того, как закодирована логика курсора или SSIS, может иметься возможность перезапуска с точки сбоя на основе контрольных точек, или обработки каждой строки при помощи курсора. Однако при подходе на основе множеств это может оказаться недоступным, пока не будет обработан весь набор данных. В таком случае найти строку, которая вызвала сбой, будет более сложным.

  Как избежать курсоров в SQL Server

  • Логика на основе множеств
    • INSERT или SELECT INTO, или INSERT. SELECT для добавления записей в таблицу за одну транзакцию.
    • UPDATE для модификации одной или многих строк в одной транзакции.
    • DELETE или TRUNCATE для удаления записей из таблицы.
    • Ветвящаяся логика MERGE для вставки, удаления или обновления данных на основе критериев.

    Что дальше

    • Когда вам нужно принять решение об обработке данных, определите, где вы можете столкнуться с использованием курсоров. Это может иметь место в вашем приложении или в операционных процессах. Существует много способов решить задачу. Использование курсора может оказаться разумной альтернативой в некоторых случаях. Решать вам.
    • Если вы сталкиваетесь в проблемами при другом способе кодирования, и необходимо сделать что-то быстро, использование курсора может быть надежной альтернативой. Она может привести к более продолжительной обработке данных, но время написания кода может стать значительно быстрей. Если вам требуется одноразовый процесс или процесс, выполняемый в ночное время, это может помочь.
    • Если в вашей среде избегают курсоров, выберите другое надежное решение. Просто убедитесь, что этот процесс не вызовет других проблем. Например, если используется курсор и обрабатываются миллионы строк, не приведет ли это к удалению всех данных из кеша и не спровоцирует ли дальнейшие конфликты? Или при большом наборе данных не будут ли данные сброшены на диск или записаны во временную директорию?
    • Оценивая подход на основе курсора по сравнению с другими альтернативами, проведите честное сравнение методов с точки зрения времени, возможности конфликтов и необходимых ресурсов. Надеюсь, что эти факторы приведут вас к правильному способу.

    Обратные ссылки

    Нет обратных ссылок

    Комментарии

    Показывать комментарии Как список | Древовидной структурой

    Автор не разрешил комментировать эту запись

    SQL-Ex blog

    

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

    Курсоры SQL

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

    • столбцы (или атрибуты) и их типы данных;
    • строки (записи или кортежи);
    • таблицы (коллекция записей).

    Таким образом, курсор служит указателем, который позволяет языку программирования обрабатывать результаты запроса по одной записи за раз. Курсор может перемещаться по всем строкам результата запроса, позиционируясь всякий раз на отдельной строке. Рассмотрим следующий запрос SQL:

    SELECT emp_no, first_name, last_name, birth_date 
    FROM employees 
    WHERE MONTH(birth_date) = MONTH(CURRENT_DATE) 
    AND DAY(birth_date) = DAY(CURRENT_DATE);

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

    Объявление курсора

    Курсоры объявляются во многом подобно переменной. Курсору дается имя, имеются операторы для открытия курсора, извлечения результатов запроса и, наконец, закрытия курсора. Отметим, что различные реализации SQL поддерживают использование курсоров по-разному. Но здесь имеется общее соглашение как должен записываться курсор.

    Мы должны использовать несколько операторов SQL для полной реализации функциональности курсора, т.к. одно лишь объявление курсора недостаточно для извлечения данных из базы данных. Вот 4 основных шага для работы с курсором:

    DECLARE CURSOR: Объявление начинается с задания имени курсору и присвоения ему выражения запроса, которое будет вызываться при открытии курсора.

    OPEN: Оператор open выполняет выражение запроса курсора и подготавливает результат запроса для последующего обхода (FETCH).

    FETCH: Извлекает значения данных в переменные, которые затем могут передаваться базовому языку программирования или другим встроенным операторам SQL.

    CLOSE: Курсор закрывается, и больше не может извлекать результаты запроса.

    DECLARE  
    [SENSITIVE | INSENSITIVE | ASENSITIVE] 
    [SCROLL | NO SCROLL] CURSOR 
    [ WITH HOLD | WITHOUT HOLD] 
    [ WITH RETURN | WITHOUT RETURN] 
    FOR  
    [ ORDER BY ] 
    [ FOR ]>]

    Основной частью объявления курсора является:
    DECLARE FOR

    Необязательная часть, такая как [SENSITIVE | INSENSITIVE | ASENSITIVE] определяет, будет ли курсор чувствителен к изменениям, и будет ли отражать эти изменения в результатах запроса. SENSITIVE означает, что курсор реагирует на изменения, INSENSITIVE означает, что на курсор изменения не влияют, а ASENSITIVE означает, что изменения могут быть видны или не видны в курсоре. Если не указано, то предполагается опция ASENSITIVE.

    Необязательные [SCROLL | NOSCROLL] определяют возможность пролистывания курсора. Если не указано, предполагается опция NOSCROLL.

    Необязательные [ WITH HOLD | WITHOUT HOLD] определяют, сохранять ли курсор или автоматически закрывать его, когда связанная с курсором транзакция фиксируется. Если не указано, поддерживается опция WITHOUT HOLD.

    Необязательные [ WITH RETURN | WITHOUT RETURN] определяют, следует ли возвращать результирующий набор курсора вызывающей стороне, такой как другая подпрограмма SQL или базовый язык. Если не указано, предполагается WITHOUT RETURN.

    Предложение ORDER BY используется для сортировки возвращаемых результатов запроса.

    Опция UPDATE относится к использованию операторов UPDATE или DELETE, связанных со строками, возвращаемыми оператором SELECT в определении курсора. Любая подобная модификация невозможна, если мы указываем опцию READ ONLY. Если не указана, то по умолчанию принимается опция UPDATE.

    Итак, простой курсор может быть объявлен следующим образом:

    DECLARE mycursor CURSOR 
    FOR 
    SELECT emp_no, first_name, last_name, birth_date 
    FROM employees 
    WHERE MONTH(birth_date) = MONTH(CURRENT_DATE) 
    AND DAY(birth_date) = DAY(CURRENT_DATE);

    Курсоры в MySQL

    В MySQL имеется два основных типа курсоров: только на чтение (read-only) и только вперед (forward-only). Эти курсоры могут использоваться в хранимых процедурах MySQL. Они помогают нам итерационно обходить результаты запроса по одной строке за раз и извлекать значения в переменные для последующей обработки. Имеется возможность объявлять более одного курсора и вкладывать их в циклы. Заметим, что курсоры являются курсорами только на чтение, поскольку они для итераций используют временные таблицы. Обычно курсор выполняет запрос при его открытии.

    Одной из проблем, связанных с курсорами в MySQL, является их отрицательное влияние на производительность из-за дополнительных операций ввода/вывода, которые они производят. Это особенно справедливо для больших типов данных, таких как BLOB и TEXT. Т.к. курсоры работают с временными таблицами, эти типы не поддерживаются в таблицах в памяти (in-memory). Следовательно, при работе с этими типами данных MySQL должна создавать временные таблицы на диске, что требует множества операций ввода/вывода, что особенно плохо для таких медленных устройств, которыми являются диски. Это основная причина низкой производительности курсоров.

    MySQL также не поддерживает курсоров на стороне клиента, однако клиентское API может эмулировать их при необходимости. Но тогда это не сильно отличается от извлечения результата в массив на языке программирования типа Java, и манипулирования этим массивом вместо использования курсора.

    Вот пример написания курсора в MySQL:

    CREATE PROCEDURE 'cursor_demo'() 
    BEGIN 
    DECLARE done INT DEFAULT FALSE; 
    DECLARE id INT(11); 
    DECLARE fn varchar(14); 
    DECLARE ln varchar(16); 
    DECLARE bdate date; 
    DECLARE mycursor CURSOR FOR 
    SELECT emp_no, first_name, last_name, birth_date 
    FROM employees 
    WHERE MONTH(birth_date)=MONTH(CURRENT_DATE) 
    AND DAY(birth_date)=DAY(CURRENT_DATE); 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 
    OPEN mycursor; 
    fetch_loop: LOOP 
    FETCH mycursor INTO id, fn, ln, bdate; 
    IF done THEN 
    LEAVE fetch_loop; 
    END IF; 
    SELECT id, fn, ln, bdate; 
    END LOOP; 
    CLOSE mycursor; 
    END

    Вызвать хранимую процедуру можно так:

    mysql> CALL cursor_demo

    Процедура извлекает те строки из таблицы с именем employee, для которых дата рождения совпадает с текущим днем и месяцем, в курсор с именем mycursor и просто печатает их с помощью оператора SELECT.

    Заключение

    Курсоры — это всего лишь указатели на множества записей, возвращаемых запросом SQL. Указатель указывает на одну строку в каждый момент времени и может перемещаться в цикле для извлечения отдельных записей. SQL обычно используется для прямого доступа и создания объектов данных. Курсоры предоставляют технику интерактивного SQL, позволяя выполнять непосредственно SQL-операторы с помощью клиентского приложения. Механизм курсора обеспечивает модель доступа к данным, при котором операторы SQL встраиваются в базовый язык типа С, С++ или Java и т.д.
    Эта статья представляет собой краткий очерк, чтобы начать работать с курсорами. Обратитесь к документации соответствующего сервера баз данных SQL за деталями по конкретной форме реализации.

    Обратные ссылки

    Нет обратных ссылок

    Комментарии

    Показывать комментарии Как список | Древовидной структурой Нет комментариев. Автор не разрешил комментировать эту запись

    Курсоры

    Курсор можно рассматривать как итератор, специфичный для баз данных.

    Запрос исполнился, мы получили какие-то результаты, и теперь собираемся достать множество строк. Все, что обсуждалось до этого (set и select into) позволяло доставать только один результат, а из курсора их можно достать множество.

    Объявление курсора

    Синтаксис

    declare имя -- объявление курсора с именем sensitive|insensitive|asensitive> -- чувствительность курсора [[no] scroll] -- возможность ходить произвольным образом по данным вперед и назад (default - no scroll) cursor select. -- запрос данных, по которым будет ходить курсор [for read only|update [of столбцы]>]; -- права на чтение (дефолтное право) или на обновление (всей таблицы (в частности, этот вариант подходит для удаления) или выбранных столбцов) 

    Чувствительность курсора

    Данные из курсора обрабатываются по одной строке (способа получить более одной строки за один раз нет). Мы напишем цикл, в рамках которого будут выбираться данные из курсора, и возможно будут меняться данные в нашей базе данных. Получаем, что во время таких итераций можно поменять данные самой таблицы, по которой построен текущий курсор. Представим ситуацию, что мы в сделанном курсоре в явной форме написали update на ту таблицу или таблицы, по которым сделан курсор. Что должно произойти в таком случае? В зависимости от желаемого поведения можно выбрать разную чувствительность курсора.

    • [math]\mathtt[/math] — курсор, чувствительный к изменениям. Измененные данные можно будет увидеть в курсоре, если эти данные еще не были пройдены. Иначе, если изменённые данные были просмотрены в прошлом и у нас не scrollable курсор, то они не обновятся. В некотором смысле это итерация по реальным данным, динамическая.
    • [math]\mathtt[/math] — курсор фиксированных данных. Данные зафиксированы на момент открытия курсора. Никакие изменения данных не будут отображены через курсор. В некотором смысле это итерация по временной копии таблицы.
    • [math]\mathtt[/math] — курсор, наиболее предпочтительный для данной СУБД. Связано с различными способами реализациями СУБД: для одних легче реализуем sensitive cursor, а для других — insensitive. Существует два подхода к реализации СУБД: snapshot-based и lock-based, для них либо sensitive, либо insensitive варианты предпочтительны. Ни одна из опций не является дешевой по умолчанию, поэтому и есть значение asensitive: сама СУБД будет решать, какую чувствительность курсора использовать. Для lock-based проще сделать курсор, чувствительный к изменениям, так как для insensitive потребовалось бы дополнительное копирование. Для snapshot-based предпочтительнее insensitive, так как не потребуется специальным образом учитывать обновления.

    Если данные во время использования курсора меняться не будут, то чувствительность курсора не важна.

    Scrollable | No scroll курсоры

    • [math]\mathtt[/math] [math]\mathtt[/math] — по такому курсору можно ходить только по одной записи вперед, как из обычного итератора в Java.
    • [math]\mathtt[/math] — по такому курсору можно ходить не только по одной записи вперед, но и вперед, назад, в начало или конец. Такой курсор больше похож на итератор из языка С++.

    Примеры

    Взятие курсора на чтение для всех студентов, упорядоченных по идентификатору:

    declare SCursor cursor select * from Students order by SId; 

    Взятие курсора с правом на изменение поля SName для студентов группы M3439 в произвольном порядке:

    declare SCursor cursor select * from Students where GId = 'M3439' for update of SName; 

    Открытие и закрытие

    Объявление курсора говорит о намерении получения данных. Реальная выборка в момент объявления не производится. Она происходит в момент открытия курсора (выполнения действия open cursor). Если курсор insensitive, то в момент открытия фиксируются данные, которые попадут в курсор.

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

    Синтаксис

    open курсор; close курсор; 

    Получение данных

    Для получения данных из курсора есть конструкция fetch.

    Синтаксис

    fetch [next|prior|last|first|absolute n|relative n] курсор into переменные; 

    Описание команд

    • [math]\mathtt[/math] — получение следующей записи (доступно по умолчанию, все команды ниже доступны только для scroll курсора)
    • [math]\mathtt[/math] — предыдущая запись
    • [math]\mathtt[/math] — последняя запись
    • [math]\mathtt[/math] — первая запись
    • [math]\mathtt[/math] [math]\mathtt[/math] — получить запись с номером n от начала
    • [math]\mathtt[/math] [math]\mathtt[/math] — запись с номером n, нумерация с текущей позиции (n может быть как положительным (движение в сторону конца), так и отрицательным (в этом случае движение назад))

    Отсутствие данных

    Если данные закончились, либо при помощи команд выше был осуществлен невалидный переход, то случится событие not found.

    Пример

    В данном примере из курсора для студентов мы получим идентификатор, имя и фамилию того студента, на которого сейчас указывает курсор.

    fetch SCursor into Sid, FirstName, LastName; 

    Для получения всех данных из курсора, можно создать цикл, предварительно создав обработчик ошибок на not found снаружи цикла, в котором выйдем из цикла, когда все элементы будут обработаны. Состояние sqlstate 02xxx не является исключительной ситуацией, а просто предупреждение от СУБД, которое зачастую игнорируется. Вопрос производительности зависит от реализации СУБД.

    Позиционированные изменения

    Изменение или удаление ровно одной строчки, которую последней выдал курсор (только в update и delete ).

    Синтаксис

    update таблица set что where current of курсор; -- изменение ровно одной строки, которую последней выдал курсор delete from таблица where current of курсор; -- удаление последней выданной строки 

    Пример

    if FirstName = 'Иван' then delete from Students where current of SCursor; end if; 

    В данном примере мы удалим текущего студента, если его имя Иван.

    Пример применения курсора

    create procedure updateNameStat(name) begin declare cnt int default 0; declare exit handler for sqlstate not found begin insert into Stats (Name, Cnt) values (name, cnt); end; declare SCursor cursor select FirstName from Students; open SCursor; loop declare sname varchar(20); fetch SCursor into sname; if name = sname then set cnt = cnt + 1; end if; end loop; end;  

    Пояснения

    В данном примере объявлен счетчик cnt , по умолчанию проинициализированный 0, курсор SCursor , перебирающий значение столбца FirstName из таблицы Students . Далее в бесконечном цикле (так как мы не имеет возможности написать условие выхода из цикла):

    1. определяем переменную sname , в которой будем хранить имя студента
    2. достаем следующую запись в эту переменную (напоминание: fetch по умолчанию — это fetch next )
    3. проверяем, если текущее имя sname имя соответствует выделенному имени name , то увеличиваем счетчик.

    Повторяем данные операции до тех пор, пока у нас не случится not found , при возникновении которого в обработчике not found обновим данные статистики и (по определению обработчика в sql) выйдем из блока, в котором был объявлен handler — то есть в данном случае выйдем из блока процедуры.

    Обработчик not found может быть только один. Если бы у нас было несколько курсоров, и мы бы захотели знать в обработчике, в каком из курсоров закончились данные, то предварительно вели бы записи в переменные, из каких курсоров планируем достать данные. И в обработчике проверили бы значение переменных.

    Данные пример написан исключительно для демонстрации работы с курсорами. В реальной жизни подобное поведение может быть достигнут проще с использованием select и count .

    Примечания

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

    Особенности курсоров

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

    Внешние курсоры

    Результат запроса к базе данных может быть слишком велик и стать виной нехватки памяти как на стороне клиента, так и на стороны сервера, увеличить нагрузку на сеть, вызывать и другие проблемы. Поэтому наружу данные выдаются в виде внешнего курсора. После исполнения команды select выдается внешний курсор на данные, аналогично внешний курсор выдается при возврате результата из хранимой процедуры.

    Размер выборки

    Может быть указан для внешнего курсора каким-либо образом (в количестве записей или в объеме данных, который выдается за один раз).

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

    • Малый размер выборки — много повторных обращений к серверу базы данных, а так как обычно такое обращение происходит по сети, то взаимодействие не слишком быстрое.
    • Большой размер выборки — требуется много памяти, как со стороны сервера для генерации данных, так и со стороны прикладного приложения.

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

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

    Литература

    • Дейт К. Введение в системы баз данных (главы 4 и 17)
    • Уидом Д., Ульман Д. Основы реляционных баз данных (раздел 7.4)
    • Gulutzan P., Pelzer T. SQL-99 complete, really
      • Chapter 25 – SQL-Invoked Routine
      • Chapter 26 – PSM: Not Just Persistent Stored Modules

      Курсоры (Cursors)

      Курсор – это средство извлечения данных из базы данных Oracle. Курсоры содержат определения столбцов и объектов (таблиц, представлений и т.п.) из которых будут извлекаться данные, а также набор критериев, определяющих какие именно строки должны быть выбраны.

      Пользователю доступны следующие способы выполнения запроса к базе данных:

      • Неявные курсоры — простой оператор SELECT . INTO извлекает одну строку данных непосредственно в переменные локальной программы. Это удобный (и часто наиболее эффективный) способ доступа к данным, использование которого, однако, может приводить к необходимости повторного кодирования оператора SELECT (или похожих операторов) в нескольких местах программы.
      • Явные курсоры — вы можете явно объявить курсов в разделе объявлений (локального блока или пакета). В этом случае курсор можно будет открывать и извлекать данные в одной или нескольких программах, причем возможности контроля будут шире, чем при использовании неявных курсоров.
      • Курсорные переменные — дополнительный уровень гибкости обеспечивают курсорные переменные (объявленные на основе типа REF CURSOR), которые позволяют передавать указатель на результирующее множество, полученное по запросу из одной программы в другую. Любая программа, имеющая доступ к такой переменной, сможет открывать и закрывать курсор, а также выбирать из него данные.
      • Курсорные выражения — появившиеся в версии Oracle 9i выражения CURSOR преобразуют оператор SELECT в указатель (типа REF CURSOR) на результирующее множество и могут использоваться в сочетании с табличными функциями для повышения прозводительности приложений.

      Сравнение явных и неявных курсоров

      В PL/SQL неявные курсоры – это курсоры, которые определяются в момент выполнения.

      DECLARE V_date DATE; BEGIN SELECT order_date INTO v_date FROM orders WHERE order_number = 100; END; 

      Явный курсор – это курсор, который определяется до начала выполнения.

      DECLARE CURSOR curs_get_od IS SELECT order_date FROM orders WHERE order_number = 100; V_date DATE; BEGIN OPEN cure_get_od; FETCH curs_get_od INTO v_date; CLOSE curs_get_od; END; 

      Ключевое преимущество явного курсора заключается в наличии у него атрибутов, облегчающих применение условных операторов.

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

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

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

      Повторное использование курсоров

      Скомпилированная версия курсора может использоваться повторно во избежание расходов на разбор и повторную компиляцию.

      Полный и частичный разбор

      Процесс компиляции нового курсора называется полным разбором. Упрощенно данный процесс может быть представлен четырьмя этапами:

      • Проверка – курсор проверяется на соответствие синтаксическим правилам SQL, также проверяются объекты (таблицы и столбцы), на которые он ссылается.
      • Компиляция – курсор компилируется в исполняемый код и загружается в разделяемый пул сервера баз данных. Для определения местоположения курсора в разделяемом пуле используется его адрес.
      • Вычисление плана выполнения – оптимизатор по стоимости (cost-based optimizer — CBO) Oracle определяет наилучший для данного курсора план выполнения и присоединяет его к курсору.
      • Вычисление хеша – ASCII-значения всех символов курсора складываются и передаются в функцию хеширования. Эта функция рассчитывает значение, по которому курсор легко может быть найден при повторном обращении. Данное значение называется хеш-значением курсора.

      Чтобы определить, может ли планируемый к выполнению курсор воспользоваться уже скомпилированной версией из разделяемого пула, Oracle применяет сложный алгоритм.

      • 1 Рассчитать сумму ASCII – значений всех символов курсора ( исключая переменные связывания).
      • 2 Применить алгоритм хеширования к полученной сумме.
      • 3 Проверить наличие в разделяемом пуле курсора с таким же значением хеша.
      • 4 Если такой курсор найден, он может быть использован повторно.

      Tags: Курсоры, Cursors

      PL/SQL

      tags: Администрирование Oracle DataBase || SQL & PL/SQL

      Исходные коды проекта хранятся на github. Можете заводить Issue и Discussions, при необходимости.
      Чтобы задать вопрос, добавить свои знания, исправить ошибки и неточности, пишите в телеграм чате.

      Читать:

      Куда поставить рупора на ауди 80