Сегодня будем рассматривать очень много чего интересного, например как запустить уже созданную процедуру, которая принимает параметры, массово, т.е. не только со статическим параметрами, а с параметрами, которые будут меняться, например, на основе какой-нибудь таблицы, как обычная функция, и в этом нам помогут как раз курсоры и циклы, и как это все реализовать сейчас будем смотреть.
Как Вы поняли, курсоры и циклы мы будем рассматривать применимо к конкретной задачи. А какой задачи, сейчас расскажу.
Существует процедура, которая выполняет какие-то действия, которые не может выполнить обычная функция SQL например, расчеты и insert на основе этих расчетов. И Вы ее запускаете, например вот так:
EXEC test_PROCEDURE par1, par2
Другими словами Вы запускаете ее только с теми параметрами, которые были указаны, но если Вам необходимо запустить данную процедуру скажем 100, 200 или еще более раз, то согласитесь это не очень удобно, т.е. долго. Было бы намного проще, если бы мы взяли и запускали процедуру как обычную функцию в запросе select, например:
SELECT my_fun(id) FROM test_table
Другими словами функция отработает на каждую запись таблицы test_table, но как Вы знаете процедуру так использовать нельзя. Но существует способ, который поможет нам осуществить задуманное, точнее даже два способа первый это с использованием курсора и цикла и второй это просто с использованием цикла, но уже без курсора. Оба варианта подразумевают, что мы будем создавать дополнительную процедуру, которую в дальнейшем мы будем запускать.
Примечание! Все примеры будем писать в СУБД MSSql 2008, используя Management Studio. Также все ниже перечисленные действия требуют необходимых знаний в SQL, а точнее в программировании на Transact-SQL. Могу посоветовать для начала ознакомиться со следующим материалом:
И так приступим, и перед тем как писать процедуру, давайте рассмотрим исходные данные нашего примера.
Допустим, есть таблица test_table
В нее необходимо вставлять данные, на основе каких-то расчетов, которые будет выполнять процедура my_proc_test, в данном случае она просто вставляет данные, но на практике Вы можете использовать свою процедуру, которая может выполнять много расчетов, поэтому в нашем случае именно эта процедура не важна, она всего лишь для примера. Ну, давайте создадим ее:
Она просто принимает три параметра и вставляет их в таблицу.
И допустим эту процедуру, нам нужно запустить столько раз, сколько строк в какой-нибудь таблице или представлении (VIEWS) , другими словами запустить ее массово для каждой строки источника.
И для примера создадим такой источник, у нас это будет простая таблица test_table_vrem, а у Вас это может быть, как я уже сказал свой источник, например временная таблица или представление:
Заполним ее тестовыми данными:
И теперь нашу процедуру необходимо запустить для каждой строки, т.е. три раза с разными параметрами. Как Вы понимаете значения этих полей и есть наши параметры, другими словами, если бы мы запускали нашу процедуру вручную, это выглядело вот так:
exec my_proc_test 1, ‘pole1_str1’, ‘pole2_str1’
И так еще три раза, с соответствующими параметрами.
Но нам так не охота, поэтому мы напишем еще одну дополнительную процедуру, которая и будет запускать нашу основную процедуру столько раз, сколько нам нужно.
Первый вариант.
Используем курсор и цикл в процедуре
Перейдем сразу к делу и напишем процедуру (my_proc_test_all), код я как всегда прокомментировал:
И теперь осталось нам ее вызвать и проверить результат:
Как видите, все у нас отработало как надо, другими словами процедура my_proc_test сработала все три раза, а мы всего лишь один раз запустили дополнительную процедуру.
Второй вариант.
Используем только цикл в процедуре
Сразу скажу, что здесь требуется нумерация строк во временной таблице, т.е. каждая строка должна быть пронумерована, например 1, 2, 3 таким полем у нас во временной таблице служит number.
Пишем процедуру my_proc_test_all_v2
И проверяем результат, но для начала очистим нашу таблицу, так как мы же ее только что уже заполнили по средствам процедуры my_proc_test_all:
Как и ожидалось результат такой же, но уже без использования курсоров. Какой вариант использовать решать Вам, первый вариант хорош, тем, что в принципе не нужна нумерация, но как Вы знаете, курсоры работают достаточно долго, если строк в курсоре будет много, а второй вариант хорош тем, что отработает, как мне кажется быстрей, опять же таки, если строк будет много, но нужна нумерация, лично мне нравится вариант с курсором, а вообще решать Вам может Вы сами придумаете что-то более удобное, я всего лишь показал основы того, как можно реализовать поставленную задачу. Удачи!
Вполне может случиться, что ответом на простой запрос клиента будет выборка из сотен тысяч строк, что для большинства клиентов неудобоваримо. В таком случае решением проблемы взаимодействия с клиентами яв-ляется использование курсоров как универсального механизма обмена данными между сервером и клиентом. Курсоры работают с результирующим набором данных (результатом выполнения запроса), давая пользовате-лям дополнительные возможности по обработке данных:
•курсоры позволяют работать со строками таблицы посредством указания их порядкового номера в наборе данных;
•курсоры позволяют реализовать сложные операции изменения данных, например когда для изменения зна-чения столбца требуется многократно обращаться к значениям других столбцов.
Жизненный цикл курсора :
Создание курсора : DECLARE [ INSENSITIVE ] [ SCROLL ] CURSOR FOR FOR
Здесь ключевое слово INSENSITIVE означает, что курсор будет статическим (слепок с данных), в то время как по умолчанию курсор создаётся динамическим (выборка осуществляется каждый раз при обращении к строке). Ключевое слово SCROLL означает, что курсор можно прокручивать в любом направлении, иначе курсор создаётся «последовательным».
Открытие курсора : OPEN [ GLOBAL ] . Курсор, указанный как GLOBAL , не удаляется автома-тически при завершении работы той процедуры или пакета, из которых он был вызван.
Считывание данных : FETCH [[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ] [ GLOBAL ] [ INTO @ variable _ name , …]. SQL Server 2000 позволяет считывать из курсора всего одну строку . Ключевое слово FIRST – возвратить первую строку курсора; LAST – последнюю строку курсора; NEXT – следующую строку за текущей, возвращённая строка становится текущей; PRIOR – предыдущую перед текущей; ABSOLUTE n – возвращает строку по её абсолютному порядковому номеру в курсоре; RELATIVE – через n строк после текущей. Данные столбцов будут сохраняться в каждую из указанных переменных в порядке их перечисления.
Изменение данных : выполняет команда UPDATE с синтаксисом, предназначенным для работы с курсорами.
Удаление данных :выполняет команда DELETE с синтаксисом, предназначенным для работы с курсорами.
Закрытие курсора : CLOSE [ GLOBAL ]
Освобождение курсора : DEALLOCATE [ GLOBAL ]
Пример использования курсора :
DECLARE fo_curs CURSOR STATIC FOR
SELECT name_rus from fo ORDER BY name_rus
DECLARE @name varchar(50)
FETCH FIRST FROM fo_curs INTO @name
FETCH NEXT FROM fo_curs INTO @name
2.7. Обеспечение безопасности и сохранности данных в Microsoft SQL Server. Управление базами данных. Роли. Назначение прав пользователям (GRANT, DENY, REVOKE). Методы и технологии защиты данных в SQL Server.
Система безопасности и администрирование SQL Server . .
Основной задачей СУБД является обеспечение целостности и непротиворечивости данных в рамках выбранной предметной области. Одним из факторов, препятствующих системе решать данную задачу, являются действия пользователей, случайно или умышленно пытающихся разрушить структуру данных или изменить сами данные. Следовательно, СУБД необходимо защищать не только от физических сбоев, но и от неадекватных для реализуемых задач пользователей. Для этого необходимо спроектировать и подключить к базе данных систему безопасности, которая будет препятствовать пользователям выполнять действия, выходящие за рамки их полномочий.
Управление базами данных
Для создания базы данных средствами TSQL используется команда CREATE DATABASE, однако обычно для этой цели используются возможности SQL Server Management Studio. В SQL сервер определено достаточно много операций с базами данных: увеличение (уменьшение) размеров файлов, изменение конфигурации (команда ALTER), присоединение и отсоединение, передача прав владения, изменение имени, просмотр свойств и, наконец, удаление ( DROP DATABASE ).
Как и в большинстве серверов баз данных, в SQL Server существует пользователь, наделенный всеми административными полномочиями — это System Administrator или ‘sa’. После начальной установки сервера пароль sa пуст. Пользователь, создающий новую базу данных, автоматически становится её владельцем (‘dbo’ – Data Base Owner). В момент создания базы определяется и пользователь ‘guest’. Если учётная запись пользователя явно не отображается в пользователя конкретной базы данных, пользователю предоставляется неявный доступ с использованием гостевого имени guest. Обычно guest запрещают.
Пользователь, создавший объект в базе данных, автоматически становится его владельцем, и никто, включая dbo и sa , не могут использовать этот объект, пока владелец не назначит им права на него. Но чтобы пользователь мог создать объект, владелец БД должен сначала ему предоставить соответствующие права.
Роль позволяет объединить пользователей, выполняющих одинаковые функции, для упрощения администрирования. Роли бывают встроенные и пользовательские. Встроенные роли реализуются на уровне сервера и на уровне баз данных. Ниже приведена таблица встроенных ролей для базы данных:
Db_owner. Имеет все права в базе данных
Db_accessadmin. Может добавлять или удалять пользователей
Db_securityadmin. Управляет всеми разрешениями, объектами, ролями и пользователями
Db_ddladmin. Может выполнять все команды DDL, кроме GRANT, DENY, REVOKE
Db_backupoperator. Может выполнять команды архивир. данных
Db_datareader. Может просматр. любые данные в любой таблице
Db_datawriter. Может модифиц. любые данные в любой таблице
Db_denydatareader. Запрещ. просматр. люб. данные в люб. таблиц
Db_denydatawriter. Запрещ модифицир люб данные в люб таблиц
Назначение прав пользователям. Основой системы безопасности SQL Server являются (1) учётные записи (accounts); (2) пользователи (users); (3) роли (roles); (4) группы (groups).
Когда пользователь подключается к SQL Server , действия, которые он может выполнять, определяются правами, выданными ему как пользователю и члену роли. Права выдаются администратором СУБД, владельцем базы данных или владельцем конкретного объекта БД. Права в БД можно разделить на три категории: (1) права на доступ к объектам баз данных; (2) права на выполнение команд TSQL ; (3) неявные права. Сервер позволяет передавать права владения от одного пользователя другому.
Для управления разрешениями пользователя на доступ к объектам базы данных используются следующие команды:
[WITH GRANT OPTION]
– назначение прав пользователям , где
ALL – пользователю предоставляются все возможные разрешения, иначе указать
– права на доступные для пользователя действия, а именно:
SELECT – на просмотр, для столбца таблицы и для таблицы (представления)
INSERT – на добавление, для таблицы (представления) в целом
UPDATE – на изменение, для столбца таблицы и для таблицы (представления)
DELETE – на удаление, для таблицы (представления) в целом
EXECUTE – на выполнение хранимых процедур
REFERENCES – возможность ссылаться на указанный объект (вводить в состав внешнего ключа).
– учётные записи SQL Server , пользователи домена Windows; PUBLIC – для всех пользователей.
WITH GRANT OPTION – позволяет пользователю, которому сейчас предоставляются права, самому назначать права на доступ к объекту другим пользователям.
AS | – участие пользователя в роли, которой предоставлена возможность предоставлять права другим пользователям.
GRANT SELECT ON authors TO public
GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom
GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION
GRANT SELECT ON Plan_Data TO Jack AS Accounting
— Джек не входит в роль Accounting , но кто-нибудь из этой роли может предоставить право
– запрещение доступа пользователям к объектам базы данных. CASCADE отзывает права не только у данного пользователя, но также и у всех, кому он права предоставлял.
Пример (на запрещение выполнения команды TSQL ):
DENY CREATE TABLE TO Jack CASCADE
Команда REVOKE используется для неявного отклонения доступ к объектам базы данных. Синтаксис аналогичен команде DENY. Неявное отклонение подобно запрещению доступа с тем отличием, что оно действует только на том уровне, на котором определено. Пример: пользователю Jack , который является участником роли GoodUsers , предоставлены права на доступ к таблице XFiles . Если при помощи REVOKE для роли GoodUsers отклоняются права на доступ к этой таблице, пользователь Jack всё равно может обращаться к этой таблице, поскольку права для него определены явно. Если же применить REVOKE персонально для него, он потеряет право на доступ к XFiles.
Разрешения, предоставленные роли, наследуются их членами. Если пользователю предоставлен доступ к объекту через членство в одной роли, но запрещён в другой, то конфликт доступа всегда решается в пользу запрещения.
Технологии защиты данных в MS SQL Server
1.Механизм checkpoints – контрольных точек, которые генерируются через
60 с для записи обновлённых страниц на диск (контрольная точка может быть поставлена принудительно командой CHECKPOINT ).
2.Встроенные и внешние механизмы проверки целостности базы данных (запускаются автоматически или, как утилита DBCC – Database Consistency Checker – вручную).
3.Физическое дублирование (если оно разрешено) файлов баз данных средствами операционной системы (включая механизм зеркальных жёстких дисков).
4.Резервирование баз данных и журналов транзакций – путём записи дампа базы данных на устройство резервирования (магнитную ленту или жёсткий диск).
5.Репликация – возможность дублирования информации путём её периодической (в некоторых случаях – синхронной) передачи с одного SQL сервера на другой.
6.Шифрование трафика между клиентом и сервером, а также шифрование кодов, использованных для работы с объектами БД (хранимых процедур, триггеров и др.)
Дается определение курсора. Приводится описание его типов и поведения: статические, динамические, последовательные и ключевые курсоры. Описываются принципы управления курсором: создание и открытие курсора, считывание данных, закрытие курсора. Приводятся примеры программирования курсора.
Понятие курсора
Запрос к реляционной базе данных обычно возвращает несколько рядов (записей) данных, но приложение за один раз обрабатывает лишь одну запись. Даже если оно имеет дело одновременно с несколькими рядами (например, выводит данные в форме электронных таблиц), их количество по-прежнему ограничено. Кроме того, при модификации, удалении или добавлении данных рабочей единицей является ряд. В этой ситуации на первый план выступает концепция курсора , и в таком контексте курсор – указатель на ряд.
Курсор в SQL – это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Если текущий оператор – запрос к базе данных, в памяти сохраняется и строка данных запроса, называемая текущим значением, или текущей строкой курсора . Указанная область в памяти поименована и доступна для прикладных программ.
Обычно курсоры используются для выбора из базы данных некоторого подмножества хранимой в ней информации. В каждый момент времени прикладной программой может быть проверена одна строка курсора . Курсоры часто применяются в операторах SQL, встроенных в написанные на языках процедурного типа прикладные программы. Некоторые из них неявно создаются сервером базы данных, в то время как другие определяются программистами.
В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия :
- создание или объявление курсора ;
- открытие курсора , т.е. наполнение его данными, которые сохраняются в многоуровневой памяти;
- выборка из курсора и изменение с его помощью строк данных;
- закрытие курсора , после чего он становится недоступным для пользовательских программ;
- освобождение курсора , т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память.
В разных реализациях определение курсора может иметь некоторые отличия. Так, например, иногда разработчик должен явным образом освободить выделяемую для курсора память. После освобождения курсора ассоциированная с ним память также освобождается. При этом становится возможным повторное использование его имени. В других реализациях при закрытии курсора освобождение памяти происходит неявным образом. Сразу после восстановления она становится доступной для других операций: открытие другого курсора и т.д.
В некоторых случаях применение курсора неизбежно. Однако по возможности этого следует избегать и работать со стандартными командами обработки данных: SELECT , UPDATE , INSERT , DELETE . Помимо того, что курсоры не позволяют проводить операции изменения над всем объемом данных, скорость выполнения операций обработки данных посредством курсора заметно ниже, чем у стандартных средств SQL.
Реализация курсоров в среде MS SQL Server
SQL Server поддерживает три вида курсоров :
- курсоры SQL применяются в основном внутри триггеров, хранимых процедур и сценариев;
- курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library;
- курсоры клиента реализуются на самом клиенте. Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций.
Различные типы многопользовательских приложений требуют и различных типов организации параллельного доступа к данным. Некоторым приложениям необходим немедленный доступ к информации об изменениях в базе данных. Это характерно для систем резервирования билетов. В других случаях, например, в системах статистической отчетности, важна стабильность данных, ведь если они постоянно модифицируются, программы не смогут эффективно отображать информацию. Различным приложениям нужны разные реализации курсоров .
В среде SQL Server типы курсоров различаются по предоставляемым возможностям. Тип курсора определяется на стадии его создания и не может быть изменен. Некоторые типы курсоров могут обнаруживать изменения , сделанные другими пользователями в строках, включенных в результирующий набор. Однако SQL Server отслеживает изменения таких строк только на стадии обращения к строке и не позволяет модифицировать изменения, когда строка уже считана.
Курсоры делятся на две категории: последовательные и прокручиваемые . Последовательные позволяют выбирать данные только в одном направлении – от начала к концу. Прокручиваемые же курсоры предоставляют большую свободу действий – допускается перемещение в обоих направлениях и переход к произвольной строке результирующего набора курсора .Если программа способна модифицировать данные, на которые указывает курсор , он называется прокручиваемым и модифицируемым. Говоря о курсорах , не следует забывать об изолированности транзакций. Когда один пользователь модифицирует запись, другой читает ее при помощи собственного курсора , более того, он может модифицировать ту же запись, что делает необходимым соблюдение целостности данных.
SQL Server поддерживает курсоры статические , динамические , последовательные и управляемые набором ключей.
В схеме со статическим курсором информация читается из базы данных один раз и хранится в виде моментального снимка (по состоянию на некоторый момент времени), поэтому изменения, внесенные в базу данных другим пользователем, не видны. На время открытия курсора сервер устанавливает блокировку на все строки, включенные в его полный результирующий набор. Статический курсор не изменяется после создания и всегда отображает тот набор данных, который существовал на момент его открытия .
Если другие пользователи изменят в исходной таблице включенные в курсор данные, это никак не повлияет на статический курсор .
В статический курсор внести изменения невозможно, поэтому он всегда открывается в режиме "только для чтения".
Динамический курсор поддерживает данные в "живом" состоянии, но это требует затрат сетевых и программных ресурсов. При использовании динамических курсоров не создается полная копия исходных данных, а выполняется динамическая выборка из исходных таблиц только при обращении пользователя к тем или иным данным. На время выборки сервер блокирует строки, а все изменения , вносимые пользователем в полный результирующий набор курсора , будут видны в курсоре . Однако если другой пользователь внес изменения уже после выборки данных курсором , то они не отразятся в курсоре .
Курсор, управляемый набором ключей , находится посередине между этими крайностями. Записи идентифицируются на момент выборки, и тем самым отслеживаются изменения . Такой тип курсора полезен при реализации прокрутки назад – тогда добавления и удаления рядов не видны, пока информация не обновится, а драйвер выбирает новую версию записи, если в нее были внесены изменения .
Последовательные курсоры не разрешают выполнять выборку данных в обратном направлении. Пользователь может выбирать строки только от начала к концу курсора . Последовательный курсор не хранит набор всех строк. Они считываются из базы данных, как только выбираются в курсоре , что позволяет динамически отражать все изменения , вносимые пользователями в базу данных с помощью команд INSERT , UPDATE , DELETE . В курсоре видно самое последнее состояние данных.
Статические курсоры обеспечивают стабильный взгляд на данные. Они хороши для систем "складирования" информации: приложений для систем отчетности или для статистических и аналитических целей. Кроме того, статический курсор лучше других справляется с выборкой большого количества данных. Напротив, в системах электронных покупок или резервирования билетов необходимо динамическое восприятие обновляемой информации по мере внесения изменений. В таких случаях используется динамический курсор . В этих приложениях объем передаваемых данных, как правило, невелик, а доступ к ним осуществляется на уровне рядов (отдельных записей). Групповой доступ встречается очень редко.
Управление курсором в среде MS SQL Server
Управление курсором реализуется путем выполнения следующих команд:
- DECLARE – создание или объявление курсора ;
- OPEN – открытие курсора , т.е. наполнение его данными;
- FETCH – выборка из курсора и изменение строк данных с помощью курсора;
- CLOSE – закрытие курсора ;
- DEALLOCATE – освобождение курсора , т.е. удаление курсора как объекта.
Объявление курсора
В стандарте SQL для создания курсора предусмотрена следующая команда:
При использовании ключевого слова INSENSITIVE будет создан статический курсор . Изменения данных не разрешаются, кроме того, не отображаются изменения , сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор .
При указании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки . Если этот аргумент опускается, то курсор окажется последовательным , т.е. его просмотр будет возможен только в одном направлении – от начала к концу.
SELECT-оператор задает тело запроса SELECT , с помощью которого определяется результирующий набор строк курсора .
При указании аргумента FOR READ_ONLY создается курсор "только для чтения", и никакие модификации данных не разрешаются. Он отличается от статического , хотя последний также не позволяет менять данные. В качестве курсора "только для чтения" может быть объявлен динамический курсор , что позволит отображать изменения , сделанные другим пользователем.
Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца , во всех столбцах.
В среде MS SQL Server принят следующий синтаксис команды создания курсора :
При использовании ключевого слова LOCAL будет создан локальный курсор , который виден только в пределах создавшего его пакета, триггера, хранимой процедуры или пользовательской функции. По завершении работы пакета, триггера, процедуры или функции курсор неявно уничтожается. Чтобы передать содержимое курсора за пределы создавшей его конструкции, необходимо присвоить его параметру аргумент OUTPUT .
Если указано ключевое слово GLOBAL , создается глобальный курсор ; он существует до закрытия текущего соединения.
При указании FORWARD_ONLY создается последовательный курсор ; выборку данных можно осуществлять только в направлении от первой строки к последней.
При указании SCROLL создается прокручиваемый курсор ; обращаться к данным можно в любом порядке и в любом направлении.
При указании STATIC создается статический курсор .
При указании KEYSET создается ключевой курсор.
При указании DYNAMIC создается динамический курсор .
Если для курсора READ_ONLY указать аргумент FAST_FORWARD , то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC .
В курсоре , созданном с указанием аргумента OPTIMISTIC , запрещается изменение и удаление строк , которые были изменены после открытия курсора .
При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора , если он несовместим с запросом SELECT .
Открытие курсора
Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:
После открытия курсора происходит выполнение связанного с ним оператора SELECT , выходные данные которого сохраняются в многоуровневой памяти.
Выборка данных из курсора
Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:
При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора , которая становится текущей строкой.
При указании LAST возвращается самая последняя строка курсора . Она же становится текущей строкой.
При указании NEXT возвращается строка, находящаяся в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.
Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.
Аргумент ABSOLUTE <номер_строки | @переменная_номера_строки>возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора . Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.
Аргумент RELATIVE <кол_строки | @переменная_кол_строки>возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.
Чтобы открыть глобальный курсор , перед его именем требуется указать ключевое слово GLOBAL . Имя курсора также может быть указано с помощью переменной.
В конструкции INTO @имя_переменной [. n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре , а тип данных переменной – типу данных в столбце курсора . Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран.
Изменение и удаление данных
Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате:
За одну операцию могут быть изменены несколько столбцов текущей строки курсора , но все они должны принадлежать одной таблице.
Для удаления данных посредством курсора используется команда DELETE в следующем формате:
В результате будет удалена строка, установленная текущей в курсоре .
Закрытие курсора
После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки, установленные в процессе его работы. Закрытие может применяться только к открытым курсорам . Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор .
Освобождение курсора
Закрытие курсора необязательно освобождает ассоциированную с ним память. В некоторых реализациях нужно явным образом освободить ее с помощью оператора DEALLOCATE . После освобождения курсора освобождается и память, при этом становится возможным повторное использование имени курсора .
Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS
Функция @@FETCH_STATUS возвращает:
0 , если выборка завершилась успешно;
-1 , если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора ;
-2 , если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.
Пример 13.5. Разработать курсор для вывода списка фирм и клиентов из Москвы.
Пример 13.6. Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В один курсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.
Пример 13.7. Разработать прокручиваемый курсор для клиентов из Москвы. Если номер телефона начинается на 1, удалить клиента с таким номером и в первой записи курсора заменить первую цифру в номере телефона на 4.
Пример 13.8. Использование курсора как выходного параметра процедуры . Процедура возвращает набор данных – список товаров.
Вызов процедуры и вывод на печать данных из выходного курсора осуществляется следующим образом:
