Полякова Л.П. Основы SQL. Курс лекций

Подождите немного. Документ загружается.

В базе данных basa1 имеется таблица Город. Из базы данных

basa2 необходимо выполнить действие по увеличению

тарифов для всех городов на 15%. В какой базе данных

должна быть создана процедура и как можно ее выполнить?



Создание процедуры

USE basa2

CREATE PROC my_proc4

UPDATE basa1.dbo.Город

SET basa1.dbo.Город.Тариф=basa1.dbo.Город.Тариф*1.15

Вызов процедуры

USE basa2

EXEC my_proc4



Создание процедуры

USE basa1

CREATE PROC my_proc4

UPDATE Город SET Тариф = Тариф*1.15

Вызов процедуры

USE basa2

EXEC basa1.my_proc4



Создание процедуры

USE basa1

CREATE PROC my_proc4

UPDATE Город SET Тариф = Тариф*1.15

Вызов процедуры

USE basa2

EXEC my_proc4



Создание процедуры

USE basa1

CREATE PROC my_proc4

UPDATE basa1.dbo.Город

SET basa1.dbo.Город.Тариф=basa1.dbo.Город. Тариф*1.15

Вызов процедуры

USE basa2

EXEC basa1.my_proc4

281

Лекция 12 Хранимые процедуры

Курс

282

Основы SQL

Лекция 13. Курсоры: принципы работы

Дается определение курсора. Приводится описание его типов и поведе-

ния: статические, динамические, последовательные и ключевые курсоры.

Описываются принципы управления курсором: создание и открытие курсора,

считывание данных, закрытие курсора. Приводятся примеры программиро-

вания курсора.

Ключевые слова: курсор, действия с курсором, виды курсоров, после-

довательный курсор, прокручиваемый курсор, статический курсор,

динамический курсор, управляемый набором ключей, последова-

тельный курсор, управление курсором, объявление курсора, открытие

курсора, выборка из курсора, изменение данных в курсоре, удаление

данных из курсора, закрытие курсора, освобождение курсора, про-

граммирование курсора, прокручиваемый курсор, курсор как выход-

ной параметр процедуры.

Понятие курсора

Запрос к реляционной базе данных обычно возвращает несколько

рядов (записей) данных, но приложение за один раз обрабатывает лишь

одну запись. Даже если оно имеет дело одновременно с несколькими ряда-

ми (например, выводит данные в форме электронных таблиц), их количе-

ство по-прежнему ограничено. Кроме того, при модификации, удалении

или добавлении данных рабочей единицей является ряд. В этой ситуации

на первый план выступает концепция курсора, и в таком контексте курсор

– указатель на ряд.

Курсор в SQL – это область в памяти базы данных, которая предна-

значена для хранения последнего оператора SQL. Если текущий опера-

тор – запрос к базе данных, в памяти сохраняется и строка данных за-

проса, называемая текущим значением, или текущей строкой курсора.

Указанная область в памяти поименована и доступна для прикладных

программ.

Обычно курсоры используются для выбора из базы данных некоторо-

го подмножества хранимой в ней информации. В каждый момент времени

прикладной программой может быть проверена одна строка курсора. Кур-

соры часто применяются в операторах SQL, встроенных в написанные на

языках процедурного типа прикладные программы. Некоторые из них не-

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

программистами.

283

Лекция 13 Курсоры: принципы работы

В соответствии со стандартом SQL при работе с курсорами можно

выделить следующие основные действия:

• создание или объявление курсора;

• открытие курсора, т.е. наполнение его данными, которые сохраня-

ются в многоуровневой памяти;

• выборка из курсора и изменение с его помощью строк данных;

• закрытие курсора, после чего он становится недоступным для поль-

зовательских программ;

• освобождение курсора, т. е. удаление курсора как объекта, посколь-

ку его закрытие необязательно освобождает ассоциированную с ним

память.

В разных реализациях определение курсора может иметь некоторые

отличия. Так, например, иногда разработчик должен явным образом ос-

вободить выделяемую для курсора память. После освобождения курсора

ассоциированная с ним память также освобождается. При этом становится

возможным повторное использование его имени. В других реализациях

при закрытии курсора освобождение памяти происходит неявным обра-

зом. Сразу после восстановления она становится доступной для других

операций: открытие другого курсора и т.д.

В некоторых случаях применение курсора неизбежно. Однако по

возможности этого следует избегать и работать со стандартными коман-

дами обработки данных: SELECT, UPDATE, INSERT, DELETE. Помимо того,

что курсоры не позволяют проводить операции изменения над всем объ-

емом данных, скорость выполнения операций обработки данных посред-

ством курсора заметно ниже, чем у стандартных средств SQL.

Реализация курсоров в среде MS SQL Server

SQL Server поддерживает три вида курсоров:

• курсоры SQL применяются в основном внутри триггеров, хранимых

процедур и сценариев;

• курсоры сервера действуют на сервере и реализуют программный

интерфейс приложений для ODBC, OLE DB, DB_Library;

• курсоры клиента реализуются на самом клиенте. Они выбирают весь

результирующий набор строк из сервера и сохраняют его локально,

что позволяет ускорить операции обработки данных за счет сниже-

ния потерь времени на выполнение сетевых операций.

Различные типы многопользовательских приложений требуют и раз-

личных типов организации параллельного доступа к данным. Некоторым

приложениям необходим немедленный доступ к информации об измене-

ниях в базе данных. Это характерно для систем резервирования билетов. В

других случаях, например, в системах статистической отчетности, важна

Курс

284

Основы SQL

стабильность данных, ведь если они постоянно модифицируются, про-

граммы не смогут эффективно отображать информацию. Различным при-

ложениям нужны разные реализации курсоров.

В среде SQL Server типы курсоров различаются по предоставляемым

возможностям. Тип курсора определяется на стадии его создания и не

может быть изменен. Некоторые типы курсоров могут обнаруживать из-

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

результирующий набор. Однако SQL Server отслеживает изменения та-

ких строк только на стадии обращения к строке и не позволяет модифи-

цировать изменения, когда строка уже считана.

Курсоры делятся на две категории: последовательные и прокручи-

ваемые. Последовательные позволяют выбирать данные только в одном

направлении – от начала к концу. Прокручиваемые же курсоры предоста-

вляют большую свободу действий – допускается перемещение в обоих

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

курсора. Если программа способна модифицировать данные, на которые

указывает курсор, он называется прокручиваемым и модифицируемым.

Говоря о курсорах, не следует забывать об изолированности транзакций.

Когда один пользователь модифицирует запись, другой читает ее при по-

мощи собственного курсора, более того, он может модифицировать ту же

запись, что делает необходимым соблюдение целостности данных.

SQL Server поддерживает курсоры статические, динамические, пос-

ледовательные и управляемые набором ключей.

В схеме со статическим курсором информация читается из базы дан-

ных один раз и хранится в виде моментального снимка (по состоянию на

некоторый момент времени), поэтому изменения, внесенные в базу дан-

ных другим пользователем, не видны. На время открытия курсора сервер

устанавливает блокировку на все строки, включенные в его полный ре-

зультирующий набор. Статический курсор не изменяется после создания

и всегда отображает тот набор данных, который существовал на момент

его открытия.

Если другие пользователи изменят в исходной таблице включенные

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

В статический курсор внести изменения невозможно, поэтому он

всегда открывается в режиме «только для чтения».

Динамический курсор поддерживает данные в «живом» состоянии,

но это требует затрат сетевых и программных ресурсов. При использова-

нии динамических курсоров не создается полная копия исходных дан-

ных, а выполняется динамическая выборка из исходных таблиц только

при обращении пользователя к тем или иным данным. На время выборки

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

полный результирующий набор курсора, будут видны в курсоре. Однако

285

Лекция 13 Курсоры: принципы работы

если другой пользователь внес изменения уже после выборки данных кур-

сором, то они не отразятся в курсоре.

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

этими крайностями. Записи идентифицируются на момент выборки, и

тем самым отслеживаются изменения. Такой тип курсора полезен при ре-

ализации прокрутки назад – тогда добавления и удаления рядов не видны,

пока информация не обновится, а драйвер выбирает новую версию записи,

если в нее были внесены изменения.

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

в обратном направлении. Пользователь может выбирать строки только от

начала к концу курсора. Последовательный курсор не хранит набор всех

строк. Они считываются из базы данных, как только выбираются в курсо-

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

зователями в базу данных с помощью команд INSERT, UPDATE, DELETE.

В курсоре видно самое последнее состояние данных.

Статические курсоры обеспечивают стабильный взгляд на данные.

Они хороши для систем «складирования» информации: приложений для

систем отчетности или для статистических и аналитических целей. Кроме

того, статический курсор лучше других справляется с выборкой большо-

го количества данных. Напротив, в системах электронных покупок или

резервирования билетов необходимо динамическое восприятие обновля-

емой информации по мере внесения изменений. В таких случаях исполь-

зуется динамический курсор. В этих приложениях объем передаваемых

данных, как правило, невелик, а доступ к ним осуществляется на уровне

рядов (отдельных записей). Групповой доступ встречается очень редко.

Управление курсором в среде MS SQL Server

Управление курсором реализуется путем выполнения следующих

команд:

• DECLARE – создание или объявление курсора;

• OPEN – открытие курсора, т.е. наполнение его данными;

• FETCH – выборка из курсора и изменение строк данных с помощью

курсора;

• CLOSE – закрытие курсора;

• DEALLOCATE – освобождение курсора, т.е. удаление курсора как

объекта.

Курс

286

Основы SQL

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

В стандарте SQL для создания курсора предусмотрена следующая

команда:

<создание_курсора>::=

DECLARE имя_курсора [INTENSITIVE][SCROLL] CURSOR

FOR SELECT_оператор

[FOR { READ_ONLY | UPDATE [OF имя_столбца[,...n]]}]

При использовании ключевого слова INTENSITIVE будет создан

статический курсор. Изменения данных не разрешаются, кроме того, не

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

чевое слово INTENSITIVE отсутствует, создается динамический курсор.

При указании ключевого слова SCROLL созданный курсор можно

прокручивать в любом направлении, что позволяет применять любые

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

последовательным, т. е. его просмотр будет возможен только в одном на-

правлении – от начала к концу.

SELECT-оператор задает тело запроса SELECT, с помощью которого

определяется результирующий набор строк курсора.

При указании аргумента FOR READ_ONLY создается курсор «только

для чтения», и никакие модификации данных не разрешаются. Он отлича-

ется от статического, хотя последний также не позволяет менять данные.

В качестве курсора «только для чтения» может быть объявлен динамиче-

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

пользователем.

Создание курсора с аргументом FOR UPDATE позволяет выполнять в

курсоре изменение данных либо в указанных столбцах, либо, при отсут-

ствии аргумента OF имя_столбца, во всех столбцах.

В среде MS SQL Server принят следующий синтаксис команды соз-

дания курсора:

<создание_курсора>::=

DECLARE имя_курсора CURSOR [LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWAR]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR SELECT_оператор

[FOR UPDATE [OF имя_столбца[,...n]]]

287

Лекция 13 Курсоры: принципы работы

При использовании ключевого слова LOCAL будет создан локальный

курсор, который виден только в пределах создавшего его пакета, тригге-

ра, хранимой процедуры или пользовательской функции. По завершении

работы пакета, триггера, процедуры или функции курсор неявно уничто-

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

конструкции, необходимо присвоить его параметру аргумент OUTPUT.

Если указано ключевое слово GLOBAL, создается глобальный курсор;

он существует до закрытия текущего соединения.

При указании FORWARD_ONLY создается последовательный курсор;

выборку данных можно осуществлять только в направлении от первой

строки к последней.

При указании SCROLL создается прокручиваемый курсор; обра-

щаться к данным можно в любом порядке и в любом направлении.

При указании STATIC создается статический курсор.

При указании KEYSET создается ключевой курсор.

При указании DYNAMIC создается динамический курсор.

Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то

созданный курсор будет оптимизирован для быстрого доступа к данным.

Этот аргумент не может быть использован совместно с аргументами

FORWARD_ONLY и OPTIMISTIC.

В курсоре, созданном с указанием аргумента OPTIMISTIC, запреща-

ется изменение и удаление строк, которые были изменены после откры-

тия курсора.

При указании аргумента TYPE_WARNING сервер будет информировать

пользователя о неявном изменении типа курсора, если он несовместим с

запросом SELECT.

Открытие курсора

Для открытия курсора и наполнения его данными из указанного при

создании курсора запроса SELECT используется следующая команда:

OPEN {{[GLOBAL]имя_курсора }

|@имя_переменной_курсора}

После открытия курсора происходит выполнение связанного с ним

оператора SELECT, выходные данные которого сохраняются в много-

уровневой памяти.

Курс

288

Основы SQL

Выборка данных из курсора

Сразу после открытия курсора можно выбрать его содержимое (ре-

зультат выполнения соответствующего запроса) посредством следующей

команды:

FETCH [[NEXT | PRIOR | FIRST | LAST

| ABSOLUTE {номер_строки

| @переменная_номера_строки}

| RELATIVE {номер_строки | @переменная_номера_строки}]

FROM {{[GLOBAL ]имя_курсора }|

@имя_переменной_курсора }

[INTO @имя_переменной [,...n]

При указании FIRST будет возвращена самая первая строка полного

результирующего набора курсора, которая становится текущей строкой.

При указании LAST возвращается самая последняя строка курсора.

Она же становится текущей строкой.

При указании NEXT возвращается строка, находящаяся в полном ре-

зультирующем наборе сразу же после текущей. Теперь она становится те-

кущей. По умолчанию команда FETCH использует именно этот способ

выборки строк.

Ключевое слово PRIOR возвращает строку, находящуюся перед теку-

щей. Она и становится текущей.

Аргумент

ABSOLUTE {номер_строки | @переменная_номера_строки}

возвращает строку по ее абсолютному порядковому номеру в полном ре-

зультирующем наборе курсора. Номер строки можно задать с помощью

константы или как имя переменной, в которой хранится номер строки.

Переменная должна иметь целочисленный тип данных. Указываются как

положительные, так и отрицательные значения. При указании положи-

тельного значения строка отсчитывается от начала набора, отрицательного

– от конца. Выбранная строка становится текущей. Если указано нулевое

значение, строка не возвращается.

Аргумент

RELATIVE {кол_строки | @переменная_кол_строки}

возвращает строку, находящуюся через указанное количество строк после

текущей. Если указать отрицательное значение числа строк, то будет

возвращена строка, находящаяся за указанное количество строк перед

текущей. При указании нулевого значения возвратится текущая строка.

Возвращенная строка становится текущей.

Чтобы открыть глобальный курсор, перед его именем требуется ука-

зать ключевое слово GLOBAL. Имя курсора также может быть указано с

помощью переменной.

289

Лекция 13 Курсоры: принципы работы

В конструкции INTO @имя_переменной [,...n] задается список

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

столбцов возвращаемой строки. Порядок указания переменных должен

соответствовать порядку столбцов в курсоре, а тип данных переменной –

типу данных в столбце курсора. Если конструкция INTO… не указана, то

поведение команды FETCH будет напоминать поведение команды

SELECT – данные выводятся на экран.

Изменение и удаление данных

Для выполнения изменений с помощью курсора необходимо выпол-

нить команду UPDATE в следующем формате:

UPDATE имя_таблицы SET {имя_столбца={

DEFAULT | NULL | выражение}}[,...n]

WHERE CURRENT OF {{[GLOBAL] имя_курсора}

|@имя_переменной курсора}

За одну операцию могут быть изменены несколько столбцов теку-

щей строки курсора, но все они должны принадлежать одной таблице.

Для удаления данных посредством курсора используется команда

DELETE в следующем формате:

DELETE имя_таблицы

WHERE CURRENT OF {{[GLOBAL] имя_курсора}

|@имя_переменной курсора}

В результате будет удалена строка, установленная текущей в курсоре.

Закрытие курсора

CLOSE {имя_курсора | @имя_переменной_курсора}

После закрытия курсор становится недоступным для пользователей

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

процессе его работы. Закрытие может применяться только к открытым

курсорам. Закрытый, но не освобожденный курсор может быть повторно

открыт. Не допускается закрывать неоткрытый курсор.

Курс

290

Основы SQL