SQL-Ex blog

Курсоры SQL

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

Модель базы данных SQL имеет три основных структуры:

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

Таким образом, главными несовпадениями двух моделей являются:

1. Типы данных атрибутов в базе данных не то же самое, что типы переменных, используемых в языках программирования. Существует много базовых языков, и каждый имеет различные типы данных. Например, типы данных С/С++ и Java различаются, как и типы данных SQL. Следовательно, необходим механизм связывания, чтобы смягчить проблему несовместимости.
   
2. Результат, возвращаемый операторами SELECT, является мультимножеством записей, где каждая запись является коллекцией атрибутов. Базовые языки программирования обычно работают с отдельными значениями данных кортежа, возвращаемого запросом. Таким образом, является существенным, чтобы результат запроса SQL отображался на структуры данных, поддерживаемых языком программирования. Механизм обхода кортежей в цикле необходим для организации итераций по кортежам и значениям их атрибутов.
   
   
   Курсор действует подобно переменной итератора, чтобы обойти кортежи, возвращаемые запросом SQL, и извлечь отдельные значения в пределах каждого кортежа, которые затем могут отображаться на соответствующие типы переменных базового языка программы.
   
   Таким образом, курсор служит указателем, который позволяет языку программирования обрабатывать результаты запроса по одной записи за раз. Курсор может перемещаться по всем строкам результата запроса, позиционируясь всякий раз на отдельной строке. Рассмотрим следующий запрос 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 <выражение запроса SQL>
   
   [ ORDER BY <выражение сортировки>]
   
   [ FOR {READ ONLY | UPDATE [ OF <список столбцов>]}]

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

   DECLARE <имя курсора> FOR <выражение sql-запроса>

   
   Необязательная часть, такая как [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 за деталями по конкретной форме реализации.