База данных. ЭУМК Базы данных. Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск
 Скачать 2.33 Mb.
|
|
ФИО Группа Стипенди я Иванова АИ42 400 Миронов АИ41 0 Петров АИ41 600 Сидоров АИ41 400 Трофимов АИ42 400 Уткина АИ42 400 В таблице Сотрудники обновить поле Оклад. Данное поле увеличить на процент, который запрашивается. UPDATE Сотрудники SET Оклад = Оклад * (100 + [Введите процент])/100; В таблице Сотрудники обновить поле Оклад у сотрудника, табельный номер которого запрашивается. Поле Оклад увеличивается на процент, который запрашивается. UPDATE Сотрудники SET Оклад = Оклад * (100 + [Введите процент])/100 WHERE [Табельный номер] = [Введите табельный номер]; Даны две таблицы Бензин (Марка, Цена, Общее количество), Продажа (Чек, Марка, Цена продажи, Дата, Продано). Обновить поле Цена продажи в таблице Продажа, присвоить ему значение из поля Цена таблицы Бензин для соответствующих марок бензина. UPDATE Продажа INNER JOIN Бензин ON Бензин.Марка = Продажа.Марка SET Продажа.[Цена продажи] = Бензин.Цена; Запросы на создание таблиц Запрос на создание таблиц аналогичен запросу на добавление, за исключением того, что он создает новую таблицу и сразу же копирует в нее записи. Общий вид запроса: SELECT <список полей> INTO<имя новой таблицы> Примеры. Из таблицы ЗАКАЗЫ сформировать новую таблицу АРХИВ_ЗАКАЗОВ, которая содержит заказы, сделанные до 1 января 2007 года: SELECT ЗАКАЗЫ.* INTO АРХИВ_ЗАКАЗОВ FROM ЗАКАЗЫ WHERE [Дата заказа] <= #01/01/07#; Из таблицы ПОСТАВКА сформировать новую таблицу АРХИВ, в которую отправить записи с датой поступления, совпавшей с запрашиваемой: SELECT ПОСТАВКА.[Индекс_товара], ПОСТАВКА.[Код_поставщика], ПОСТАВКА.[Дата_поступления], ПОСТАВКА.[Количество] INTO АРХИВ FROM ПОСТАВКА WHERE ПОСТАВКА.[Дата_поступления]=[Введите дату поступления]; Использование языка определения данных Команды языка определения данных (Data Definition Language – DDL) представляют собой инструкции SQL, которые позволяют создавать и модифицировать элементы структуры базы данных. Например, используя DDL, можно создавать, удалять таблицы и изменять их структуру, создавать и удалять индексы. Создание таблицы. Оператор создания таблицы имеет следующий вид: CREATE TABLE <имя таблицы> (<имя столбца> <тип данных> [NOT NULL] [,<имя столбца> <тип данных> [NOT NULL]]…) Обязательными операндами оператора являются имя создаваемой таблицы и имя хотя бы одного столбца (поля) с указанием типа данных, хранимых в этом столбце. При создании таблицы для отдельных полей могут указываться некоторые дополнительные правила контроля вводимых в них значений. Например, конструкция NOT NULL (не пустое) служит для определения обязательного поля. Следует отметить, что в коммерческих продуктах определение типов данных не полностью согласуется с требованиями официального стандарта SQL. Это объясняется желанием обеспечить совместимость SQL с другими языками программирования. Стандарт SQL поддерживает следующие типы данных. Строка данных CHARACTER [(длина)] или CHAR [(длина)] Значения данного типа имеют фиксированную длину, которая определяется параметром длина. Этот параметр может принимать значения от 1 до 255 символов. Если вводимая текстовая константа меньше заданной длины, то автоматически строка дополняется справа пробелами. Некоторые реализации языка SQL поддерживают в качестве типа данных строки переменной длины. Этот тип обозначается VARCHAR [(длина)] Этот тип описывает текстовую строку, которая может иметь произвольную длину. Максимальная длина определяется конкретной реализацией языка SQL (например, в ORACLE – до 2000 символов). При вводе текстовой константы, длина которой меньше заданной, не происходит дополнение ее пробелами до заданного максимального значения. Текстовые константы в выражениях SQL заключаются в одиночные кавычки. Если длина строки не указана явно, она полагается равной одному символу во всех случаях. Числовые типы данных. Стандартными числовыми типами данных SQL являются: INTEGER – используется для представления целых чисел в диапазоне от – 231 до +231; SMOLLINT – используется для представления чисел в диапазоне от –2 15 до +2 15 ; DECIMAL (точность [, масштаб]) – десятичное число с фиксированной точкой, точность определяет количество значащих цифр в числе. Масштаб указывает максимальное число цифр справа от точки. NUMERIC (точность[, масштаб]) – десятичное число с фиксированной точкой, такое же, как и DECIMAL; FLOAT [(точность)] – число с плавающей точкой и указанной минимальной точностью; REAL – число такое же, как при типе FLOAT, за исключением определения точности по умолчанию (в зависимости от конкретной реализации SQL); DOUBLE PRECISION – число аналогично REAL, но точность в два раза выше точности REAL. 3. Дата и время. Тип данных, предназначенный для представления даты и времени, является нестандартным. Каждая конкретная СУБД специфически определяет эти типы. Константы типа даты записываются в зависимости от формата, принятого в операционной системе. Например, ’03.05.2006’, ‘12/09/2006’, ’09-nov-2006’, ’07-apr-06’. При создании таблицы возможно задание ограничений. 1. Ограничение Not null может быть установлено для любого поля реляционной таблицы. При наличии ограничения Not null запрещается ввод значений null в это поле. Атрибут null устанавливается по умолчанию. Атрибут Not null означает, что в поле обязательно должна быть внесена информация. 2. Ограничения первичного ключа заключаются в следующем: первичные ключи указываются при создании таблицы, для них обязательно ограничение Not null, которое записывается после определения типа поля. Если ключ составной, для каждого поля надо указывать Not null. 3. Ограничение Unique означает, что все значения в поле уникальны. Однако в отличие от первичного ключа допускается наличие в поле пустых значений (если не установлено Not null). Ограничение записывается после определения типа поля. 4. Значение по умолчанию задается с помощью Default. Оно записывается после определения типа поля, например: Create table имя таблицы (… имя_поля тип_данных Default= значение по умолчанию). Рассмотрим создание таблиц с использованием возможностей различных СУБД. 3.3. Пользовательские процедуры и функции Хранимая процедура (Stored Procedure) — это набор команд, хранимый на сервере и выполняемый как единое целое. Хранимые процедуры являются механизмом, с помощью которого можно создавать подпрограммы, работающие на сервере и управляемые его процессами. Подобные подпрограммы могут быть активизированы вызывающим их приложением. Кроме того, они могут быть вызваны правилами, поддерживающими целостность данных, или триггерами. Хранимые процедуры могут возвращать значения. В процедуре можно выполнять сравнение вводимых пользователем значений с заранее установленной в системе информацией. Хранимые процедуры применяют в работе мощные аппаратные решения SQL Server. Они ориентированы на базы данных и тесно взаимодействуют с оптимизатором SQL Server. Это позволяет получить высокую производительность при обработке данных. В хранимые процедуры можно передавать значения и получать от них результаты работы, причем не обязательно имеющие отношение к рабочей таблице. Хранимая процедура может вычислить результаты в процессе работы. Хранимые процедуры бывают двух типов: обычные и расширенные. Обычные хранимые процедуры представляют собой набор команд на Transact- SQL, в то время как расширенные хранимые процедуры представлены в виде динамических библиотек (DLL). Такие процедуры, в отличие от обычных, имеют префикс xp_. Сервер имеет стандартный набор расширенных процедур, но пользователи могут писать и свои процедуры на любом языке программирования. Главное при этом — использовать интерфейс программирования SQL Server Open Data Services API. Расширенные хранимые процедуры могут находиться только в базе данных Master. Обычные хранимые процедуры также можно разделить на два типа: системные и пользовательские. Системные процедуры — это стандартные процедуры, служащие для работы сервера; пользовательские — любые процедуры, созданные пользователем. В самом общем случае хранимые процедуры обладают следующими преимуществами: Высокая производительность. Является результатом расположения хранимых процедур на сервере. Сервер, как правило, — более мощная машина, поэтому время выполнения процедуры на сервере значительно меньше, чем на рабочей станции. Кроме того, информация из базы данных и хранимая процедура находятся в одной и той же системе, поэтому на передачу записей по сети время практически не затрачивается. Хранимые процедуры имеют непосредственный доступ к базам данных, что делает работу с информацией очень быстрой. Преимущество разработки системы в архитектуре «клиент-сервер». Заключается в возможности раздельного создания программного обеспечения клиента и сервера. Это преимущество является ключевым при разработке, и благодаря ему можно значительно уменьшить время, необходимое для окончания проекта. Код, работающий на сервере, может разрабатываться отдельно от кода клиентской части. При этом компоненты серверной части могут совместно использоваться компонентами стороны клиента. Уровень безопасности. Хранимые процедуры могут выступать в качестве инструмента улучшения безопасности. Можно создать хранимые процедуры, осуществляющие операции добавления, изменения, удаления и отображения списков, и, таким образом, получить контроль над каждым из аспектов доступа к информации. Усиление правил сервера, работающих с данными. Это одна из самых важных причин применения интеллектуального ядра баз данных. Хранимые процедуры позволяют применять правила и другую логику, помогающую контролировать вводимую в систему информацию. Хотя язык SQL определен как непроцедурный, в SQL Server применяются ключевые слова, связанные с управлением ходом выполнения процедур. Такие ключевые слова используются при создании процедур, которые можно сохранять для последующего выполнения. Хранимые процедуры могут быть применены вместо программ, созданных с помощью стандартных языков программирования (например, С или Visual Basic) и выполняющих операции в базе данных SQL Server. Хранимые процедуры компилируются при первом выполнении и сохраняются в системной таблице текущей базы данных. При компилировании они оптимизируются. При этом выбирается самый лучший способ доступа к информации таблицы. Подобная оптимизация учитывает реальное положение данных в таблице, доступные индексы, загрузку таблицы и т. д. Откомпилированные хранимые процедуры могут значительно улучшить производительность системы. Стоит, однако, отметить, что статистика данных с момента создания процедуры до момента ее выполнения может устареть, а индексы могут стать неэффективными. И хотя можно обновить статистику и добавить новые, более эффективные индексы, план выполнения процедуры уже составлен, то есть процедура скомпилирована, и в результате способ доступа к данным может перестать быть эффективным. Поэтому имеется возможность проводить перекомпиляцию процедур при каждом вызове. С другой стороны, на перекомпиляцию каждый раз будет уходить время. Поэтому вопрос об эффективности перекомпиляции процедуры или единовременного составления плана ее выполнения является довольно тонким и должен рассматриваться для каждого конкретного случая отдельно. Хранимые процедуры могут быть выполнены либо на локальной машине, либо на удаленной системе SQL Server. Это дает возможность активизировать процессы на других машинах и работать не только с локальными базами данных, но и с информацией на нескольких серверах. Прикладные программы, написанные на одном из языков высокого уровня, таком как С или Visual Basic .NET, также могут вызывать хранимые процедуры, что обеспечивает оптимальное решение по распределению нагрузки между программным обеспечением клиентской части и SQL-сервера. Для создания хранимой процедуры применяется инструкция Create Procedure. Имя хранимой процедуры может быть длиной до 128 символов, включая символы # и ##. Синтаксис определения процедуры: CREATE PROC[EDURE] имя_процедуры [; число] [{@параметр тип_данных} [VARYING] [= значение_по_умолчанию] [OUTPUT]] [,...n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR REPLICATION] AS <Инструкции_SQL> Рассмотрим параметры этой команды: Имя_процедуры — имя процедуры; должно удовлетворять правилам для идентификаторов: его длина не может превышать 128 символов; для локальных временных процедур перед именем используется знак #, а для глобальных временных процедур — знаки ##; Число — необязательное целое число, используемое для группировки нескольких процедур под одним именем; @параметр тип_данных — список имен параметров процедуры с указанием соответствующего типа данных для каждого; таких параметров может быть до 2100. В качестве значения параметра разрешается передавать NULL. Могут использоваться все типы данных за исключением типов text, ntext и image. В качестве выходного параметра (ключевое слово OUTPUT или VARYING) можно использовать тип данных Cursor. Параметры с типом данных Cursor могут быть только выходными параметрами; VARYING — ключевое слово, определяющее, что в качестве выходного параметра используется результирующий набор (используется только для типа Cursor); OUTPUT — говорит о том, что указанный параметр может быть использован как выходной; значение_по_умолчанию — используется в случае, когда при вызове процедуры параметр пропущен; должно быть константой и может включать символы маски (%, _, [, ], ^) и значение NULL; WITH RECOMPILE — ключевые слова, показывающие, что SQL Server не будет записывать план процедуры в кэш, а будет создавать его каждый раз при выполнении; WITH ENCRYPTION — ключевые слова, показывающие, что SQL Server будет зашифровывать процедуру перед записью в системную таблицу Syscomments. Для того чтобы текст зашифрованных процедур было невозможно восстановить, необходимо после шифрования удалить соответствующие им кортежи из таблицы syscomments; FOR REPLICATION — ключевые слова, показывающие, что эта процедура создается только для репликации. Эта опция несовместима с ключевыми словами WITH RECOMPILE; AS — начало определения текста процедуры; <Инструкции_SQL> — набор допустимых инструкций SQL, ограниченный только максимальным размером хранимой процедуры — 128 Кб. Недопустимыми являются следующие операторы: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, DISK INIT, DISK RESIZE, DROP DATABASE, DROP DEFAULT, DROP PROCEDURE, DROP RULE, DROP TRIGGER, DROP VIEW, RESOTRE DATABASE, RESTORE LOG, RECONFIGURE, UPDATE STATISTICS. Рассмотрим пример хранимой процедуры. Разработаем хранимую процедуру, которая подсчитывает и выводит на экран количество экземпляров книг, которые в настоящий момент находятся в библиотеке: CREATE Procedure Count_Ex1 -- процедура подсчета количества экземпляров книг, -- находящихся в настоящий момент в библиотеке, -- а не на руках у читателей As -- зададим временную локальную переменную Declare @N int Select @N = count(*) from Exemplar Where Yes_No = '1' Select @N GO Поскольку хранимая процедура является полноценным компонентом базы данных, то, как вы уже поняли, создать новую процедуру можно только для текущей базы данных. При работе в SQL Server Query Analyzer установление текущей базы данных выполняется с помощью оператора Use, за которым следует имя базы данных, где должна быть создана хранимая процедура. Выбрать текущую базу данных можно также с помощью раскрывающегося списка. После создания в системе хранимой процедуры SQL Server компилирует ее и проверяет выполняемые подпрограммы. При возникновении каких-либо проблем процедура отвергается. Перед повторной трансляцией ошибки должны быть устранены. Создать хранимую процедуру можно также с помощью SQL Server Enterprise Manager. Хранимые процедуры — это очень мощный инструмент, но максимальной эффективности можно добиться, только сделав их динамическими. Разработчик должен иметь возможность передавать хранимой процедуре значения, с которыми она будет работать, то есть параметры. Ниже приведены основные принципы применения параметров в хранимых процедурах. Для процедуры можно определить один или несколько параметров. Параметры используются в качестве именованных мест хранения данных, точно так же, как переменные в языках программирования, таких как С, Visual Basic .NET. Имя параметра обязательно предваряется символом @. Имена параметров являются локальными в той процедуре, где они определены. Параметры служат для передачи информации процедуре при ее выполнении. Они помешаются в командной строке после имени процедуры. В случае если процедура имеет несколько параметров, они разделяются запятыми. Для определения типа информации, передаваемой в качестве параметра, применяют системные или пользовательские типы данных. В MS SQL SERVER существует множество заранее определенных функций, позволяющих выполнять разнообразные действия. Однако всегда может возникнуть необходимость использовать какие-то специфичные функции. Для этого, начиная с версии 8.0 (2000), появилась возможность описывать пользовательские функции (User Defined Functions, UDF) и хранить их в виде полноценного объекта базы данных, наравне с хранимыми процедурами, представлениями и т. д. Удобство применения функций, определяемых пользователем, очевидно. В отличие от хранимых процедур, функции можно встраивать непосредственно в оператор SELECT, причем использовать их как для получения конкретных значений (в разделе SELECT), так и в качестве источника данных (в разделе FROM). При использовании UDF в качестве источников данных их преимущество перед представлениями заключается в том, что UDF, в отличие от представлений, могут иметь входные параметры, с помощью которых можно влиять на результат работы функции. Функции, определяемые пользователем, могут быть трех видов: скалярные функции, inline-функции и многооператорные функции, возвращающие табличный результат. Рассмотрим все эти типы функций подробнее. Скалярные функции возвращают один скалярный результат. Этот результат может быть любого описанного выше типа, за исключением типов text, ntext, image и timestamp. Это наиболее простой вид функции. Ее синтаксис имеет следующий вид: CREATE FUNCTION [владелец.]имя_функции ([{@имя_параметра [AS] скалярный_тип_данных [= значение_по_умолчанию]} [,... n]]) RETURNS скалярный_тип_данных [WITH SCHEMABINDING | ENCRYPTION | SCHEMABINDING, ENCRYPTION] [AS] BEGIN тело_функции RETURN скалярное_выражение END Параметр ENCRYPTION уже был описан в разделе, посвященном хранимым процедурам; SCHEMABINDING — привязывает функцию к схеме. Это означает, что нельзя будет удалить таблицы или представления, на основе которых строится функция, без удаления или изменения самой функции. Нельзя также изменить структуру этих таблиц, если изменяемая часть используется функцией. Таким образом, эта опция позволяет исключить ситуации, когда функция использует какие-либо таблицы или представления, а кто-то, не зная об этом, удалил или изменил их; RETURNS скалярный_тип_данных — описывает тип данных, который возвращает функция; скалярное_выражение — выражение, которое непосредственно возвращает результат выполнения функции. Оно должно иметь тот же тип, что и тот, что описан после RETURNS; тело_функции — набор инструкций на Transact-SQL. Рассмотрим примеры использования скалярных функций. . Inline-функции - этот вид функций возвращает в качестве результата не скалярное значение, а таблицу, вернее — набор данных. Это может быть очень удобно в тех случаях, когда в разных процедурах, триггерах и т. д. часто выполняется однотипный подзапрос. Тогда, вместо того чтобы везде писать этот запрос, можно создать функцию и в дальнейшем использовать ее. Еще более полезны функции такого типа в тех случаях, когда требуется, чтобы возвращаемая таблица зависела от входных параметров. Как известно, представления не могут иметь параметров, поэтому проблему такого рода могут решить только inline-функции. Особенностью inline-фукций является то, что они могут содержать только один запрос в своем теле. Таким образом, функции этого типа очень напоминают представления, но дополнительно могут иметь входные параметры. Синтаксис inline-функции: CREATE FUNCTION [владелец.]имя_функции ([{@имя_параметра [AS] скалярный_тип_данных [= значение_по_умолчанию]} [,… n]]) RETURNS TABLE [WITH SCHEMABINDING | ENCRYPTION | SCHEMABINDING, ENCRYPTION] [AS] RETURN [(<запрос>)] В определении функции указано, что она будет возвращать таблицу; <запрос> — это тот запрос, результат выполнения которого будет результатом работы функции. Пример Напишем функцию, аналогичную скалярной функции из последнего примера, но возвращающую не только суммирующий результат, но и строки продаж, включающие дату продажи, название книги, цену, количество штук и сумму продажи. Должны выбираться только те продажи, которые попадают в заданный период времени. Зашифруем текст функции, чтобы другие пользователи могли ей воспользоваться, но не могли читать и исправлять ее: CREATE FUNCTION Sales_Period (@datebegin DATETIME, @dateend DATETIME) RETURNS TABLE WITH ENCRYPTION AS RETURN ( SELECT t.title, t.price, s.qty, ord_date, t.price * s.qty as stoim FROM Titles t JOIN Sales s ON t.title_Id = s.Title_ID WHERE ord_date BETWEEN @datebegin and @dateend ) Теперь вызовем эту функцию. Как уже говорилось, вызвать ее можно только в разделе FROM оператора SELECT: SELECT * FROM Sales_Period('01.09.94', '13.09.94') Многооператорные функции, возвращающие табличный результат Первый рассмотренный тип функций позволял использовать сколь угодно много инструкций на Transact-SQL, но возвращал только скалярный результат. Второй тип функций мог возвращать таблицы, но его тело представляет только один запрос. Многооператорные функции, возвращающие табличный результат, позволяют сочетать свойства первых двух функций, то есть могут содержать в теле много инструкций на Transact-SQL и возвращать в качестве результата таблицу. Синтаксис многооператорной функции: CREATE FUNCTION [владелец.]имя_функции ([{@имя_параметра [AS] скалярный_тип_данных [= значение_по_умолчанию]} [,... n]]) RETURNS @имя_переменной_результата TABLE <описание_таблицы> [WITH SCHEMABINDING | ENCRYPTION | SCHEMABINDING, ENCRYPTION] [AS] BEGIN <тело_функции> RETURN END TABLE <описание_таблицы> — описывает структуру возвращаемой таблицы; <описание_таблицы> — содержит перечисление столбцов и ограничений. 3.4. Использование курсоров Запрос к реляционной базе данных обычно возвращает несколько рядов (записей) данных, но приложение за один раз обрабатывает лишь одну запись. Даже если оно имеет дело одновременно с несколькими рядами (например, выводит данные в форме электронных таблиц), их количество по-прежнему ограничено. Кроме того, при модификации, удалении или добавлении данных рабочей единицей является ряд. В этой ситуации на первый план выступает концепция курсора, и в таком контексте курсор – указатель на ряд. Курсор в 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. В курсоре видно самое последнее состояние данных. Статические курсоры обеспечивают стабильный взгляд на данные. Они хороши для систем "складирования" информации: приложений для систем отчетности или для статистических и аналитических целей. Кроме того, статический курсор лучше других справляется с выборкой большого количества данных. Напротив, в системах электронных покупок или резервирования билетов необходимо динамическое восприятие обновляемой информации по мере внесения изменений. В таких случаях используется динамический курсор. В этих приложениях объем передаваемых данных, как правило, невелик, а доступ к ним осуществляется на уровне рядов (отдельных записей). Групповой доступ встречается очень редко. Управление курсором реализуется путем выполнения следующих команд: DECLARE – создание или объявление курсора ; OPEN – открытие курсора, т.е. наполнение его данными; FETCH – выборка из курсора и изменение строк данных с помощью курсора; CLOSE – закрытие курсора ; DEALLOCATE – освобождение курсора, т.е. удаление курсора как объекта. Объявление курсора В стандарте SQL для создания курсора предусмотрена следующая команда: <создание_курсора>::= DECLARE имя_курсора [INSENSITIVE][SCROLL] CURSOR FOR SELECT_оператор [FOR { READ_ONLY | UPDATE [OF имя_столбца[,...n]]}] При использовании ключевого слова INSENSITIVE будет создан статический курсор. Изменения данных не разрешаются, кроме того, не отображаются изменения, сделанные другими пользователями. Если ключевое слово INSENSITIVE отсутствует, создается динамический курсор. При указании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки. Если этот аргумент опускается, то курсор окажется последовательным, т.е. его просмотр будет возможен только в одном направлении – от начала к концу. SELECT-оператор задает тело запроса SELECT, с помощью которого определяется результирующий набор строк курсора. При указании аргумента FOR READ_ONLY создается курсор "только для чтения", и никакие модификации данных не разрешаются. Он отличается от статического, хотя последний также не позволяет менять данные. В качестве курсора "только для чтения" может быть объявлен динамический курсор, что позволит отображать изменения, сделанные другим пользователем. Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах. В среде MS SQL Server принят следующий синтаксис команды создания курсора: <создание_курсора>::= DECLARE имя_курсора CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR SELECT_оператор [FOR UPDATE [OF имя_столбца[,...n]]] При использовании ключевого слова LOCAL будет создан локальный курсор, который виден только в пределах создавшего его пакета, триггера, хранимой процедуры или пользовательской функции. По завершении работы пакета, триггера, процедуры или функции курсор неявно уничтожается. Чтобы передать содержимое курсора за пределы создавшей его конструкции, необходимо присвоить его параметру аргумент OUTPUT. Если указано ключевое слово GLOBAL, создается глобальный курсор ; он существует до закрытия текущего соединения. При указании FORWARD_ONLY создается последовательный курсор ; выборку данных можно осуществлять только в направлении от первой строки к последней. При указании SCROLL создается прокручиваемый курсор ; обращаться к данным можно в любом порядке и в любом направлении. При указании STATIC создается статический курсор. При указании KEYSET создается ключевой курсор. При указании DYNAMIC создается динамический курсор. Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC. В курсоре, созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк, которые были изменены после открытия курсора. При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT. Открытие курсора Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда: OPEN {{[GLOBAL]имя_курсора } |@имя_переменной_курсора} После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти. Выборка данных из курсора Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды: FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {номер_строки | @переменная_номера_строки} | RELATIVE {номер_строки | @переменная_номера_строки}] FROM ]{{[GLOBAL ]имя_курсора }| @имя_переменной_курсора } [INTO @имя_переменной [,...n]] При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора, которая становится текущей строкой. При указании LAST возвращается самая последняя строка курсора. Она же становится текущей строкой. При указании NEXT возвращается строка, находящаяся в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк. Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей. Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру в полном результирующем наборе курсора. Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается. Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей. Чтобы открыть глобальный курсор, перед его именем требуется указать ключевое слово GLOBAL. Имя курсора также может быть указано с помощью переменной. В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре, а тип данных переменной – типу данных в столбце курсора. Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран. Изменение и удаление данных Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате: UPDATE имя_таблицы SET {имя_столбца={ DEFAULT | NULL | выражение}}[,...n] WHERE CURRENT OF {{[GLOBAL] имя_курсора} |@имя_переменной_курсора} За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице. Для удаления данных посредством курсора используется команда DELETE в следующем формате: DELETE имя_таблицы WHERE CURRENT OF {{[GLOBAL] имя_курсора} |@имя_переменной_курсора} В результате будет удалена строка, установленная текущей в курсоре. Закрытие курсора CLOSE {имя_курсора | @имя_переменной_курсора} После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки, установленные в процессе его работы. Закрытие может применяться только к открытым курсорам. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор. |