Метод указания AIS
.pdf
В операторе RETURN int – это целочисленное значение. Как мы видели раньше, возврат значений чаще всего используется для определения статуса выполнения хранимой процедуры. При этом 0 указывает на успешное завершение выполнения, а любое другое число указывает на ошибку. Ошибки могут быть проанализированы с помощью глобальной переменной @@ERROR, которая возвращает статус выполнения последней команды Transact-SQL: 0 указывает на успешное выполнение, а ненулевое значение указывает, что имела место ошибка.
Совет. Строка сообщения об ошибках SQL Server хранится в главной базе данных в таблице sysmessage. Вы можете добавить свои собственные сообщения об ошибках в эту таблицу, воспользовавшись системной процедурой sp_addmessage, а затем применить функцию RAISERROR для генерирования характерных для базы данных, или даже для приложения, ошибок.
Создайте хранимую процедуру с возвращаемым значением
1. Введите
КАТУ ИМ СЕЙФУЛЛИНА
К.Т.Н. А.С.
2.
Нажмите кнопку ExecuteСУБДQuery (Выполнить запрос) в панели инструментов анализатора запросов
Query Analyzer. Query Analyzer создаст хранимую процедуру.
3.Нажмите кнопку New Query (Новый запрос) в панели инструментов анализатора запросов
Query Analyzer. Query Analyzer отобразит новое окно Query (Запрос).
4.Введите следующие операторы на вкладке Editor (Редактор):
161
5.Нажмите кнопку Execute Query (Выполнить запрос)
в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит хранимую процедуру и отобразит результаты. Во второй панели сетки отображается 0, что указывает на успешное выполнение команды.
Закройте окно Query (Запрос), без сохранения изменений.
КАТУ ИМ.С.СЕЙФУЛЛИНА
К.Т.Н. ОМАРБЕКОВА А.С.
СУБД
162
22 Курсоры в Transact-SQL
Задание
1.Перечислить характеристики курсоров
2.Типы курсоров
3.Синтаксис DECLARE CURSOR.
4.Команды для работы с курсорами
5.Создайте курсор для столбца KODSPEC таблицы kontengent и сохраните значение в переменную
@s
6.Выведите фамилию последнего и предпоследнего абитуриента таблицы kontengent
7.Вывести в цикле 10 фамилий и имен таблицы kontengent, начиная с пятой строки
8.В таблице lichkart установите значение столбца noudost ‘не определено’ для всех строк
9.Описан курсор:
declare Kursor Cursor local
static
for select fio from kontengent
Определите количество строк в курсоре;
10. Определите переменную @@FETCH_STATUS. Поясните результат.
КАТУКурсор – это особыйИМвременный.объектСSQL,.СЕЙФУЛЛИНАпредназначенный для использования в программах и хранимых процедурах. С его помощью можно в цикле пройти по результирующему набору строк
запроса, по отдельности считывая и обрабатывая каждую его строку. В хранимых процедурах с помощью курсоров можно выполнять сложные вычисления, которые трудно выразить с помощью синтаксиса инструкции SELECT.
КОдним.Тиз.характерныхН. ОМАРБЕКОВАсвойств реляционных баз данных является то, что действия выполняютсяА.Снад . множествами строк. Множество может быть пустым, либо содержать только одну строку, но все равно
оно считается множеством. Это необходимое и полезное свойство для реляционных операций, но оно порой может быть не слишком удобным для приложений.
Например, поскольку нет возможностиСУБДуказать на определенную строку во множестве, представление пользователю строк по одной за раз может вызвать затруднения. Даже несмотря на то, что предоставляемые Transact-SQL расширения к стандартному языку SQL позволяют реализовать гораздо большие возможности для программирования, тем не менее остаются операции, которые затруднительно, трудоемко или даже вообще невозможно выполнить на основе принципов работы с множествами.
Чтобы справиться с подобными ситуациями, в SQL предусмотрены курсоры. Курсор представляет собой объект, который указывает на определенную строку во множестве. В зависимости от сути созданного вами курсора, вы можете перемещать курсор внутри множества и модифицировать или удалять данные.
Понятие о курсорах
Microsoft SQL Server реально поддерживает два различных типа курсоров: курсоры Transact-SQL и курсоры API (курсоры программного интерфейса приложений). Курсоры API создаются внутри приложения, использующего объекты Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC или DBLibrary. Каждое из этих API поддерживает несколько отличающиеся функциональные возможности и использует различный синтаксис. Здесь мы не будем подробно обсуждать курсоры API; если вы
163
планируете использовать их, обратитесь к соответствующей документации на API и языку программирования, который вы собираетесь применить.
Курсоры Transact-SQL создаются с помощью команды DECLARE CURSOR. Как объект курсора, так и множество строк, на которое он указывает, должны существовать на сервере. Подобные курсоры называются серверными курсорами. Если вы используете серверный курсор из приложения, соединенного с SQL Server через сеть, каждая операция с курсором требует двустороннего сетевого взаимодействия. Библиотеки API-курсоров, поддерживающие серверные курсоры, поддерживают также клиентский курсор, который существует в клиентской системе и кэширует строки, которые он обрабатывает на клиенте.
Множество строк, на которое указывает курсор, определяется с помощью команды SELECT. При создании курсора Transact-SQL на команду SELECT накладываются несколько ограничений:
команда SELECT не может возвращать несколько результирующих множеств;
команда SELECT не может содержать фразу INTO для создания новой таблицы;
команда SELECT не может содержать фразу COMPUTE или COMPUTE BY, используемые для агрегирования результатов. (Однако, она может содержать функции агрегирования, например, AVG.)
КАТУХарактеристикиИМкурсоров .С.СЕЙФУЛЛИНА Transact-SQL поддерживает несколько различных типов курсоров. Выяснение различных характеристик
каждого из курсоров является довольно утомительной задачей, но ее можно облегчить, если принять во внимание для каждого типа курсора три более или менее независимых характеристики: способность
отражать изменения в исходных данных, способность осуществлять прокрутку во множестве строк, а Ктакже.Тспособность.Н.модифицироватьОМАРБЕКОВАмножество строк. А.С.
Отражение изменений
Способность курсора отражатьСУБДизменения в данных называется чувствительностью курсора. Предположим, что вы создали курсор для оператора:
SELECT * FROM kontengent
WHERE Left(fio, 1) = 'А'
База данных student вернет строки, как показано на рисунке.
Прокрутка
Второй характеристикой курсора является способность осуществления прокрутки как вперед, так и назад, либо только вперед. Здесь имеет место извечная для программирования дилемма: скорость
164
против гибкости. Последовательные курсоры (forward-only) работают значительно быстрее, но имеют меньшую гибкость.
Обновление
Последней характеристикой, используемой для классификации курсоров, является возможность обновления строк курсором. Опять же, курсоры "только чтение" обычно более производительны, но имеют меньшую гибкость.
Типы курсоров
Transact-SQL поддерживает четыре различных типа курсоров: статические, ключевые, динамические и курсоры быстрого доступа, или "пожарные" (firehose). Каждый тип курсора хранит различные данные относительно строк, на которые он указывает, кроме того, каждому типу курсора свойственны различные сочетания характеристик, рассмотренных в предыдущем разделе.
Статические курсоры
Статический курсор делает как бы моментальный снимок данных, задаваемых оператором SELECT, и хранит их в базе данных tempdb. Он "не чувствует" изменений в структуре или в значениях данных, а
КАТУпоскольку любые модификацииИМбудут.Сотражены.СЕЙФУЛЛИНАтолько в копии, этот курсор всегда открывается в режиме "только чтение". Статические курсоры, однако, могут быть объявлены как последовательные
или как прокручиваемые.
Ключевые курсоры
ККлючевой.Т.Нкурсор.копируетОМАРБЕКОВАв базу tempdb только те столбцы, которые уникально идентифицируютА.С. каждую строку. Чтобы иметь возможность объявить ключевой курсор, каждая таблица, входящая в
определение оператора SELECT, должна иметь уникальный индекс, который задает копируемый набор
– ключ.
Ключевые курсоры могут бытьСУБДкак модифицируемыми, так и иметь режим "только для чтения". Они также могут быть прокручиваемыми или последовательными.
Членство в ключевом курсоре фиксируется на момент объявления курсора. Если в процессе открытого состояния курсора добавляется строка, удовлетворяющая условию отбора, она не будет добавлена во множество. В нашем предыдущем примере, где в качестве условия отбора использовалось LEFT(fio, 1) = 'A', новая строка со значением поля fio 'Ахметова' не будет добавлена к строкам, относящимся к области действия курсора.
Аналогично, если изменение вносится в строку, которая после этого не будет удовлетворять условию членства во множестве строка все же останется членом множества. Даже если строка удаляется, она попрежнему остается членом множества, но SQL Server возвращает NULL для всех значений столбцов.
Совет. Как мы увидим в следующем разделе, создание курсора и открытие курсора являются разными операциями. Чтобы обновить содержимое ключевого курсора, можно закрыть и повторно открыть его.
Динамические курсоры
Динамический курсор ведет себя так, как если бы при каждом обращении к строке повторно выполнялся оператор SELECT. (На самом деле все происходит несколько иначе, но подобное
165
представление позволяет лучше судить о работе динамических курсоров.) Динамические курсоры отражают изменения, связанные как с членством, так и со значениями исходных данных, независимо от того, сделаны ли эти изменения внутри курсора, либо внесены другим пользователем.
Для динамических курсоров действует одно ограничение: используемый для определения курсора оператор SELECT может содержать фразу ORDER BY только в том случае, если имеется индекс, включающий в себя столбцы, используемые в фразе ORDER BY. Если вы объявляете ключевой курсор с использованием фразы ORDER BY, не оперирующей индексом, SQL Server преобразует курсор в ключевой.
Курсоры быстрого доступа
SQL Server поддерживает специальную оптимизированную форму не прокручиваемого курсора, допускающего только чтение. Этот вид курсора объявляется с использованием ключевого слова FAST_FORWARD, и чаще всего его называют "пожарным" курсором (firehose).
"Пожарные" курсоры очень эффективны, но при их использовании имеются два важных ограничения. Во-первых, если в операторе определения SELECT курсора вы использовали столбцы с типом данных text, ntext или image, а также фразу TOP, SQL Server преобразует курсор в ключевой.
КАТУВо-вторых, если операторИМSELECT,.Скоторый.СЕЙФУЛЛИНАвы использовали для определения курсора, содержит таблицы, имеющие триггеры, и таблицы, не имеющие триггеров, курсор преобразуется в статический.
Триггеры представляют собой сценарии Transact-SQL, которые автоматически исполняются сервером при выполнении для таблицы операторов Data Manipulation Language (DML).
КИспользование.Т.Нкурсоров. ОМАРБЕКОВА А.С.
Использование курсоров подобно использованию локальных переменных – вы объявляете их, устанавливаете значение, а затем используете. Однако, в отличие от локальных переменных, которые автоматически уничтожаются при выходе за пределы области действия, вы должны явным образом освобождать используемыеСУБДкурсором строки, а затем уничтожать курсор.
Первым шагом в использовании курсора является его создание. Курсоры Transact-SQL создаются с помощью оператора DECLARE CURSOR.
Оператор DECLARE CURSOR имеет следующий синтаксис:
DECLARE имя_курсора CURSOR [видимость] [прокрутка] [тип] [блокировка]
[TYPE_WARNING] FOR оператор_выборки
[FOR UPDATE [OF имена_столбцов]]
Обратите внимание, что все параметры, определяющие характеристики курсора, – видимость, тип и т.д.
– являются необязательными. Значениями по умолчанию для этих параметров являются сложными и
166
могут указывать, либо не указывать способы взаимодействия с исходными записями или представлениями, а также опции работы с базой данных. Чтобы сделать восприятие оператора долее удобным, лучше явно задавать все необходимые вам параметры. При этом вы будете точно знать, что получите.
Видимость курсора определяется с помощью ключевых слов LOCAL или GLOBAL, которые имеют тот же эффект, что и ключевые слова @local_table или @@global_table при объявлении временных таблиц.
Совет. SQL Server будет закрывать и освобождать локальный курсор при выходе за пределы его области действия (видимости), но лучше всегда делать это явно.
Параметр прокрутка допускает использование ключевых слов FORWARD_ONLY и SCROLL, которые задают, соответственно, возможность перемещения только от начала к концу, или в любом направлении.
Параметр тип определяет тип создаваемого курсора. Здесь допустимы ключевые слова STATIC, KEYSET, DYNAMIC и FAST_FORWARD. Параметр типа FAST_FORWARD и параметр прокрутки
FORWARD_ONLY являются взаимно исключающими.
Параметр блокировка определяет, могут ли строки модифицироваться курсором, и если да, то могут ли
КАТУих модифицироватьИМдругие пользователи.С. Если.СЕЙФУЛЛИНАиспользуется ключевое слово READ ONLY, курсор не может вносить никаких изменений в исходные данные. Тем не менее, другие пользователи могут
модифицировать данные, либо это можете делать вы сами с помощью оператора UPDATE. Если в качестве параметра блокировка задано SCROLL_LOCKS, обновления могут быть выполнены только курсором. Все другие операторы UPDATE, как внутри этого же пакета, так и предоставляемые другими
пользователями, выполняться не будут. КПоследняя.Т.Нопция.блокировкиОМАРБЕКОВА, OPTIMISTIC, позволяет осуществлять обновления строкАкак внутри.С.
курсора, так и вне его. Это наиболее гибкая опция, но при этом всегда существует вероятность, что
модификация, выполненная курсором, окончится неудачей, если строка была изменена после ее чтения курсором. СУБД
Параметр TYPE_WARNING предписывает SQL Server отправлять предупреждающее сообщение клиенту, если тип курсора преобразуется от заданного к другому типу. Это возможно, если вы объявляете курсор, который не поддерживает заданный оператор SELECT.
Параметр оператор_выборки, указываемый в фразе FOR, является обязательным. Он задает строки, которые будут включены во множество курсора.
Фраза FOR UPDATE является необязательной. По умолчанию курсоры являются модифицируемыми, если не задан параметр READ_ONLY, однако и в этом случае лучше все-таки использовать эту фразу, чтобы быть уверенным в полученном результате. Вы можете использовать раздел OF имена_столбцов, чтобы указать определенные строки, для которых вы допускаете модификацию. Если вы опускаете раздел OF имена_столбцов, модификация может быть выполнена для всех столбцов, указанных в операторе SELECT.
Курсорные переменные
Transact-SQL позволяет объявлять переменные типа CURSOR. В этом случае стандартный синтаксис DECLARE не создает курсор; вы должны явно установить переменную для курсора с помощью ключевого слова SET.
167
DECLARE myCursor CURSOR
LOCAL
FAST_FORWARD
FOR SELECT fio FROM kontengent
DECLARE @myCursorVariable CURSOR
SET @myCursorVariable = myCursor
Этот синтаксис полезен, если вы хотите создать переменные, которые могут быть назначены различным курсорам. Это может потребоваться, если вы создаете обобщенную процедуру для работы с различными результирующими множествами.
Вы можете объявить курсорную переменную, а затем использовать ее для непосредственного создания курсора.
DECLARE @myCursorVariable CURSOR
SET @myCursorVariable = CURSOR
LOCAL FAST_FORWARD FOR SELECT fio FROM kontengent
При использовании такого синтаксиса курсор не имеет идентификатора, и ссылка на него может быть осуществлена только через переменную.
КАТУОткрытие курсораИМ.С.СЕЙФУЛЛИНА
Объявление курсора создает объект курсора, но не создает набор записей, которыми курсор будет манипулировать (множество курсора). Множество курсора не создается, пока вы не откроете курсор.
После достаточно сложного синтаксиса оператора DECLARE CURSOR, синтаксис оператора кажется Квполне.Тпрозрачным.Н.: ОМАРБЕКОВА А.С.
OPEN [GLOBAL] курсор_или_переменная
Ключевое слово GLOBALСУБДпомогает избежать конфликтов: если курсор, объявленный с ключевым словом LOCAL, и курсор, объявленный с ключевым словом GLOBAL, имеют одинаковый идентификатор, ссылки на курсор будут по умолчанию отнесены к локальному курсору, если вы не использовали ключевое слово GLOBAL. Как и в других подобных случаях, лучше явно указывать ключевое слово, если вы открываете глобальный курсор.
Закрытие курсора
Закончив использование курсора, вы должны его закрыть. Оператор CLOSE освобождает ресурсы, используемые для обслуживания множества курсора, а также освобождает все блокировки, наложенные на строки, если вы использовали параметр SCROLLOCKS в операторе DECLARE. Синтаксис команды CLOSE почти идентичен синтаксису оператора OPEN – меняется только ключевое слово:
CLOSE [GLOBAL] курсор_или_переменная
Освобождение курсора
Последним оператором в последовательности действий, связанных с созданием курсора, является оператор DEALLOCATE. Синтаксис его также прост:
168
DEALLOCATE [GLOBAL] курсор_или_переменная
Однако здесь есть одна тонкость: оператор DEALLOCATE удаляет идентификатор или курсорную переменную, но он не обязательно удаляет сам курсор. Сам курсор не удаляется до тех пор, пока все ссылающиеся на него идентификаторы будут либо освобождены, либо перестанут действовать (при выходе за пределы области действия). Рассмотрим следующие примеры:
--Создание курсора
DECLARE myCursor CURSOR KEYSET
READ_ONLY
FOR SELECT * FROM kontengent
--Создание курсорной переменной
DECLARE @cursorVariable CURSOR
--Создание множества записей курсора
OPEN myCursor
--Назначение переменной курсору
SET @cursorVariable = myCursor
--Освобождение курсора
КАТУDEALLOCATE myCursorИМ.С.СЕЙФУЛЛИНА
После освобождения курсора идентификатор myCursor больше не ассоциируется с множеством курсора, но поскольку на множество курсора еще ссылается переменная @cursorVariable, курсор и множество курсора не освобождаются. Если вы явно не освободите также и курсорную переменную, курсор и
множество курсора будут существовать, пока переменная не утратит свое действие. К.ТМанипулирование.Н.строкамиОМАРБЕКОВАс помощью курсора А.С.
Курсоры сами по себе не вызвали бы никакого интереса, если бы вы не могли осуществлять с их помощью определенные действияСУБД. Transact-SQL поддерживает три различные команды для работы с курсорами: FETCH, UPDATE и DELETE.
Команда FETCH извлекает указанную строку из множества строк курсора. В своем простейшем варианте команда FETCH имеет следующий синтаксис:
FETCH курсор_или_переменная
В этом формате записи возвращается строка в позиции курсора (текущая строка).
Используйте простую команду FETCH
1. Введите
169
2.Нажмите кнопку Execute Query (Выполнить запрос) в панели инструментов анализатора запросов Query Analyzer.
Query Analyzer выполнит запрос.
КАТУ ИМ СЕЙФУЛЛИНА
К.Т.Н. А.С.
Совет. Возможно, вы обратили внимание, что этот сценарий выполняется дольше, чем соответствующий операторСУБДSELECT. Дело в том, что создание и открытие курсора требует дополнительного времени. Никогда не используйте курсор, если для выполнения задачи достаточно оператора SELECT.
Команда FETCH может не только возвращать строку непосредственно, но и позволяет сохранять значения из возвращенного столбца в переменных. Чтобы сохранить результаты выполнения команды FETCH в переменной, используйте следующий синтаксис:
FETCH курсор_или_переменная
INTO список_переменных
Список_переменных есть перечень разделяемых запятыми идентификаторов переменных. Перед выполнением команды FETCH вы должны объявить переменные. Список_переменных должен содержать переменную для каждого столбца, фигурирующего в операторе SELECT, который определяет курсор. Тип данных переменной должен либо совпадать, либо быть совместимым с типом данных столбца.
Выберите строки с записью ее в переменные
1. Введите
170