|
|
Глава 18. Курсоры В этой главе...
SQL отличается от большинства наиболее популярных языков программирования тем, что в нем операции производятся одновременно с данными всех строк таблицы, в то время как процедурные языки обрабатывают данные построчно. Благодаря использованию курсоров в SQL становится возможным выводить, обновлять или же удалять выбранную строку в один прием, упрощая совместное использование SQL с другими языками программирования. По существу, курсор является указателем на определенную строку таблицы. С его помощью можно вывести, обновить или удалить строку, на которую он ссылается. Курсоры незаменимы, если требуется выбрать строки из таблицы, проверить их содержимое, а также выполнить различные операции на основании содержимого полей. Одного только SQL в данном случае недостаточно. С помощью SQL можно извлекать строки, однако для принятия решений на основе содержимого полей лучше использовать процедурные языки. Курсоры позволяют SQL по одной извлекать строки из таблицы и передавать их в процедурный код для обработки. Поместив код SQL в цикл, можно строка за строкой полностью обработать всю таблицу. В случае использования встроенного SQL в общем все выглядит так: EXEC SQL DECLARE CURSOR выражение EXEC SQL OPEN выражение Проверка достижения конца таблицы Программный код Начало цикла Программный код EXEC SQL FETCH Программный код Проверка достижения конца таблицы Конец цикла EXEC SQL CLOSE выражение Программный код В приведенном фрагменте кода командами SQL являются: DECLARE (объявить), OPEN (открыть), FETCH (вызвать) и CLOSE (закрыть). Каждая из этих команд детально рассматривается в этой главе. Совет: Если для выполнения операций с выбранными строками можно обойтись обычными операторами SQL, лучше так и сделать. Используйте для построчной обработки базовый язык лишь в том случае, если обычный SQL не позволяет выполнить необходимые операции. Объявление курсора Чтобы использовать курсор, необходимо сначала объявить о его существовании СУБД. Это делается с помощью оператора DECLARE CURSOR. Фактически она не инициирует никакого действия, а только объявляет имя курсора для СУБД и определяет запрос, с которым будет работать курсор. Синтаксис оператора DECLARE CURSOR имеет следующий вид: DECLARE имя_курсора [<чувствительность>] [<перемещаемость>] CURSOR [<состояние_фиксации>] [<возвращаемость>] FOR выражение_запроса [ORDER BY порядок_сортировки] [FOR разрешение_обновления] ; Примечание: имя курсора однозначно определяет курсор, следовательно, оно должно отличаться от любого другого имени курсора в модуле или программе. Чтобы код был удобочитаемым, курсор следует назвать мнемонически, т.е. чтобы из названия следовало, для чего он предназначен. Название курсора должно быть связано либо с данными, определенными в запросе, либо с операциями, которые с этими данными выполняет программный код. Совет: Чувствительность курсора может быть установлена в состояние SENSITIVE, INSENSITIVE или ASENSITIVE. Перемещаемость курсора может быть в состоянии SCROLL (с прокруткой) или NO SCROLL (без прокрутки). Состояние фиксации курсора может быть либо WITH HOLD (с фиксацией), либо WITHOUT HOLD (без фиксации), а возвращаемость — WITH RETURN (с возвратом) либо WITHOUT RETURN (без возврата). Выражение запроса Выражением запроса может быть любой допустимый оператор SELECT. Оператор SELECT выводит строку, на которую указывает курсор в данный конкретный момент времени. Эти строки представляют собой область действия курсора. Помни: Запрос не будет выполняться сразу же после выполнения оператора DECLARE CURSOR. Выборка данных обязательно начинается с оператора OPEN. Построчная обработка данных осуществляется в цикле с использованием оператора FETCH. Предложение ORDER BY В зависимости от того, что программный код должен делать с данными, иногда требуется обрабатывать выбранные данные в определенном порядке. Сортировку строк перед их обработкой можно выполнять с помощью предложения ORDER BY (упорядочить по). Его синтаксис имеет следующий вид: ORDER BY спецификация_сортировки [ , спецификация_сортировки]... Существует множество спецификаций сортировки, каждая из которых имеет следующий синтаксис: ( имя_столбца ) [ COLLATE BY имя_сопоставления ] [ ASC | DESC ] Для сортировки по столбцу данный столбец должен присутствовать в списке выборки в выражении запроса. Сортировку по столбцам таблицы, не вошедшим в список запроса, выполнить нельзя. Например, требуется произвести операцию, не поддерживаемую SQL, со строками таблицы CUSTOMER. В данном случае можно использовать курсор: DECLARE custl CURSOR FOR SELECT CustID, FirstName,LastName, City, State, Phone FROM CUSTOMER ORDER BY State, LastName, FirstName В этом примере оператор SELECT выводит строки, упорядоченные вначале по штату (State), затем по фамилии (LastName) и по имени (FirstName). Перед тем как перейти к первому заказчику из штата Алабама (AL), выбираются все заказчики из штата Аляска (АК). Затем все записи о заказчиках из Аляски сортируются по их фамилиям (Aaron предшествует Abbot). Там, где фамилии совпадают, сортировка производится по имени (George Aaron предшествует Нету Aaron). Приходилось ли вам когда-нибудь делать 40 копий документа, состоящего из 20 страниц, на копировальном аппарате без сортировщика? Ужас, до чего противная работа! Необходимо организовать место для 20 кучек, которые будут соответствовать 20 страницам документа, и ходить между ними 40 раз туда и обратно, раскладывая 40 копий по этим кучкам. Такой процесс называется сопоставлением. Аналогичный процесс возможен и в SQL. Сопоставление — это набор правил, определяющих порядок сортировки строк данных. Сортировка по алфавитному порядку установлена по умолчанию. Однако можно выбрать отличный от установленного по умолчанию порядок, по которому будут упорядочиваться данные. Для этого следует использовать необязательный оператор COLLATE BY (использовать сопоставление). Любая реализация изначально поддерживает несколько часто встречающихся сопоставлений. Можно выбрать одно из них, указав лишь, как нужно упорядочивать данные — по возрастанию или по убыванию. Для этого в конце предложения нужно добавить ключевое слово ASC или DESC соответственно. В операторе DECLARE CURSOR можно использовать столбец, рассчитанный на основе существующих столбцов таблицы. У такого столбца нет имени, которое может использоваться в предложении ORDER BY. Чтобы иметь возможность сослаться на этот столбец, его имя нужно определить в выражении запроса оператора DECLARE CURSOR. Рассмотрим следующий пример: DECLARE revenue CURSOR FOR SELECT Model, Units, Price, Units * Price AS ExtPrice FROM TRANSDETAIL ORDER BY Model, ExtPrice DESC ; В этом примере нет ни одного оператора COLLATE BY в предложении ORDER BY, таким образом, использовалось сопоставление по умолчанию. В четвертом столбце, ExtPrice (общая цена), находятся данные о совокупной цене изделий определенной модели. В предложении ORDER BY задается вначале сортировка по названию модели Model, затем по общей цене ExtPrice. Сортировка по столбцу ExtPrice производится по убыванию (ключевое слово DESC), т.е. вначале обрабатываются более дорогие транзакции. В предложении ORDER BY по умолчанию используется порядок сортировки по возрастанию. Если в списке спецификаций присутствует сортировка DESC и следующая сортировка также должна производиться по убыванию, для нее следует указать ключевое слово DESC в явном виде. Например, выражение ORDER BY А, В DESC, С, D, E, F эквивалентно такому: ORDER BY A ASC, В DESC, С ASC, D ASC, E ASC, F ASC Разрешение обновления Возможны случаи, когда необходимо обновить либо удалить строки таблицы, выбираемые с помощью курсора. В других случаях требуется гарантированно исключить такое обновление или удаление. SQL предоставляет возможность осуществлять контроль с помощью FOR READ ONLY Для разрешения обновления избранных столбцов, при том, что остальные столбцы должны быть защищены, используйте FOR UPDATE OF имя_столбца [, имя_столбца]... Помни: Естественно, столбцы, внесенные в приведенный список, должны присутствовать в выражении запроса оператора DECLARE CURSOR. Если предложение разрешения (или запрета) обновления отсутствует, по умолчанию данные всех столбцов, указанных в выражении запроса, могут быть обновлены. В этом случае оператор UPDATE может обновить все столбцы строки области действия курсора, а оператор DELETE может удалить такую строку. Чувствительность Выражение запроса в операторе DECLARE CURSOR определяет строки, которые находятся в области действия курсора. Возникает вопрос: что если программный код, расположенный между операторами OPEN и CLOSE, изменяет содержимое некоторых строк так, что они перестают удовлетворять условиям запроса? А если некоторые строки вообще удалены? Продолжает ли команда курсора обрабатывать все строки, которые изначально соответствовали условиям запроса, или же изменяет свое поведение в зависимости от изменений, а может, вообще игнорирует измененные или удаленные строки? Помни: До окончания построчной обработки в результате запроса DECLARE CURSOR данные могут находиться в полном беспорядке. Такие данные недостоверны и противоречивы. Потому рекомендуется сделать курсор нечувствительным к любым изменениям. Для этого в оператор DECLARE CURSOR нужно добавить ключевое слово INSENSITIVE. В течение времени, когда курсор остается открытым, он нечувствителен к изменениям таблицы, так что область действия курсора распространяется на строки, соответствующие исходным условиям выражения запроса. Курсор не может быть нечувствительным и обновляемым в одно и то же время. Нечувствительный курсор должен быть открыт только для чтения. Обычные операторы SQL, такие как UPDATE, INSERT или DELETE, работают с набором строк таблицы базы данных, а возможно, со всеми строками таблицы. Во время работы механизм транзакций SQL защищает ее от вмешательства других команд, работающих одновременно с теми же данными. При использовании же курсора данные более уязвимы. В течение всего времени, пока курсор открыт, данные могут быть изменены другими командами. Если открывается один курсор и начинается обработка таблицы, затем открывается другой курсор, а первый курсор остается активным, действия, производимые вторым курсором, могут повлиять на данные, используемые командой первого курсора. Предположим, составлены следующие запросы: DECLARE Cl CURSOR FOR SELECT * FROM EMPLOYEE ORDER BY Salary ; DECLARE C2 CURSOR FOR SELECT * FROM EMPLOYEE FOR UPDATE OF Salary ; Теперь предположим, что оба курсора открыты и командой курсора С1 последовательно выбрано несколько строк, а в это время данные по зарплате обновлены с помощью С2. При этом может так получиться, что строка, уже выбранная С1, может опять появиться при последующей выборке. Проблемы: Проблемы, возникающие в результате взаимодействий нескольких одновременно открытых курсоров или обыкновенных команд SQL и открытых курсоров, можно избежать только путем локализации транзакций. В противном случае можно нарваться на крупные неприятности. Хорошенько запомните: нельзя работать со множеством открытых курсоров. По умолчанию чувствительность курсора находится в состоянии ASENSITIVE. Значение ASENSITIVE зависит от реализации SQL. В одной реализации оно может быть эквивалентно SENSITIVE, а в другой — INSENSITIVE. Для того чтобы выбрать, какое значение использовать в вашей реализации, прочитайте техническую документацию к вашей системе. Перемещаемость Понятие перемещаемость (scrollability) впервые появилось в SQL-92. В реализациях, поддерживаемых SQL-86 и SQL-89. единственным разрешенным перемещением курсора было последовательное, начиная с первой строки, выбранной выражением запроса, и заканчивая последней строкой. Ключевое слово SCROLL оператора DECLARE CURSOR в SQL-92 (и в SQL:2OO3) позволяет получить доступ к строкам в любом порядке по вашему желанию. Синтаксис оператора FETCH контролирует перемещение курсора. Оператор FETCH мы рассмотрим ниже в этой главе. Открытие курсора Оператор DECLARE CURSOR определяет, какие строки включать в курсор, но фактически не инициирует никакого действия, являясь лишь объявлением. Оператор OPEN активизирует курсор. Она имеет следующий вид: OPEN имя_курсора; Для открытия курсора, приведенного выше в разделе, посвященном предложению ORDER BY, используйте синтаксис: DECLARE revenue CURSOR FOR SELECT Model, Units, Price, Units * Price AS ExtPrice FROM TRANSDETAIL ORDER BY Model, ExtPrice DESC ; OPEN revenue ; Помни: До тех пор пока курсор не открыт, осуществлять выборку строк из курсора невозможно. После открытия курсора значения переменных, содержащиеся в операторе DECLARE CURSOR, становятся фиксированными, это же относится к функциям даты-времени. Рассмотрим следующий пример: DECLARE CURSOR C1 FOR SELECT * FROM ORDERS WHERE ORDERS.Customer= :NAME AND DueDate < CURRENT_DATE; NAME := 'Acme Co' ; //Выражение базового языка OPEN C1; NAME := 'Omega Inc.' ; //Другое выражение базового языка ... UPDATE ORDERS SET DueDate = CURRENT_DATE ; Оператор OPEN фиксирует значение всех переменных, приведенных в объявлении курсора, а также значения всех функций даты-времени. Поэтому второе присваивание имени переменной (NAME := 'Omega Inc.') не влияет на выбранные курсором строки. Это значение NAME будет использоваться при последующем открытии С1. Если даже команда OPEN выполняется до полуночи, а оператор UPDATE выполняется после, значение CURRENT_DATE в операторе UPDATE вычисляется во время выполнения команды OPEN. Это справедливо даже в том случае, если в операторе DECLARE CURSOR нет функций даты-времени. Внутренняя фиксация (для значений даты-времени) Выборка данных из отдельных строк Оператор DECLARE CURSOR определяет название курсора и область его действия, оператор OPEN собирает все строки таблицы, удовлетворяющие выражению запроса DECLARE CURSOR, оператор FETCH осуществляет непосредственную выборку данных. Курсор может указывать на одну строку в области действия курсора, или на область, которая находится перед первой строкой области действия, или же на область, расположенную за последней строкой области действия курсора, или на границу строк. Определить, на что именно указывает курсор, можно, используя предложение ориентации оператора FETCH. Синтаксис Синтаксис оператора FETCH имеет следующий вид: FETCH [[ориентация] FROM] имя_курсора INTO целевая_спвцификация [,целевая_спецификация]... ; Существуют шесть вариантов ориентации:
По умолчанию используется NEXT — единственно возможное значение параметра ориентации в SQL до версии SQL-92. Где бы ни находился курсор, он перемещается на следующую строку в порядке, установленном выражением запроса. Если курсор расположен перед первой записью, он завершает свое выполнение на первую запись. Если он указывает на запись п, то перемещается к записи 77+1. Если курсор указывает на последнюю запись, он прекращает свою работу и в системную переменную SQLSTATE помещается код отсутствия данных. Переменная SQLSTATE, а также остальные средства обработки ошибок в SQL рассматриваются в главе 20. Целевыми спецификациями являются либо базовые переменные, либо параметры, в зависимости от того, определен ли курсор во встроенном SQL или в модуле. Количество и типы целевых спецификаций должны соответствовать количеству и типам столбцов, используемых в выражении запроса оператора DECLARE CURSOR. В случае встроенного SQL, если из строки таблицы выбирается список из пяти значений, в выражении запроса должны фигурировать пять переменных с правильно выбранными типами для хранения этих значений. Ориентация перемещаемого курсора Вследствие того, что в SQL курсор может быть перемещаемым, помимо NEXT можно использовать и другие значения параметра ориентации. После перемещения с ориентацией PRIOR курсор перемещается к предыдущей строке. В случае ориентации FIRST курсор перемещается к первой записи, а в случае ориентации LAST — к последней записи выборки. Задание ориентации в виде числа требует присутствия команд ABSOLUTE и RELATIVE . Например, FETCH ABSOLUTE 7 перемещает курсор на седьмую строку от начала выборки. FETCH RELATIVE 7 перемещает курсор на семь строк ниже его текущей позиции. FETCH RELATIVE 0 не перемещает курсор. Оператор FETCH RELATIVE 1 производит такое же действие, что и FETCH NEXT; FETCH RELATIVE-1 действует таким же образом, что и FETCH PRIOR; FETCH ABSOLUTE 1 выбирает первую запись; FETCH ABSOLUTE 2 — вторую запись и т.д. Аналогично, FETCH ABSOLUTE -1 предоставляет последнюю запись, a FETCH ABSOLUTE -2 — предшествующую последней записи выборки и т.д. Оператор FETCH ABSOLUTE 0 генерирует исключение отсутствия данных, таким же образом действует FETCH ABSOLUTE 17, если в выборке только 16 строк. Оператор FETCH <спецификация_простого_значения> возвращает запись, заданную спецификацией простого значения. Позиционные операторы DELETE и UPDATE Со строками, на которые в данный момент указывает курсор, можно выполнять операции удаления и обновления. Синтаксис оператора DELETE следующий: DELETE FROM имя_таблицы WHERE CURRENT OF имя_курсора ; Если курсор не указывает на строку, генерируется ошибка, и удаление не происходит. Вот синтаксис оператора UPDATE: UPDATE имя_таблицы SET имя_столбца = значение [,имя_столбца = значение]... WHERE CURRENT OF имя_курсора ; Значение, помещаемое в каждый выбранный столбец, должно являться выражением, ключевым словом или ключевым словом DEFAULT. Если при проведении операции обновления возникает какая-либо ошибка, обновление не выполняется. Закрытие курсора Совет: Закончив работу с курсором, тут же закройте его. Курсоры, оставшиеся открытыми после того, как приложение закончило свою работу с ними, как правило, безобидны, но опять-таки не всегда. Кроме того, открытые курсоры используют системные ресурсы. По окончании работы "нечувствительного" курсора и его закрытия достаточно повторно открыть этот курсор, и он сможет работать с измененными данными. |
|
|
| На правах рекламы: |