Вставка, удаление и изменение данных

Изначально целью любой системы управления базами данных является предоставление пользователям удобных и эффективных механизмов управления дан­ными. Любая СУБД предоставляет пользователям инструменты для ввода, изме­нения, удаления и выборки данных. Остальные возможности, такие, как репли­кация, резервное копирование, автоматическое администрирование, перенос данных и другие, являются лишь дополнительными компонентами, обеспечи­вающими более эффективное решение все тех же задач ввода, изменения, уда­ления и выборки данных.

SQL Server 2000 предлагает несколько различных механизмов управления данными. Например, вставка данных может выполняться не только средствами Transact-SQL, но и с помощью утилиты bср.ехе или служб трансформации дан­ных (DTS, Data Transformation Services).

Выборка данных

По большому счету вставка данных в таблицу является лишь подготовительным этапом к их использованию. В основном же работа с данными заключается в их изменении и выборке по различным критериям. Быстрая выборка данных по различным критериям является важнейшей задачей, стоящей перед любой сис­темой управления базами данных. Если система не обеспечивает мощных и про­стых механизмов доступа к данным, то все остальные достоинства системы бу­дут сведены на нет. В принципе, данные можно хранить и в текстовом файле, но поиск нужной информации и ее обработка потребуют очень больших усилий.

SQL Server 2000 предоставляет пользователям гибкие средства доступа к данным. В одном запросе пользователи могут сразу обращаться к множеству разнообразных источников данных, возможно, расположенных на разных серверах сети. За счет использования технологии OLE DB пользователи могут получить доступ не только к реляционным источникам данных, как это было бы во время применения ODBC, но и к нереляционным, таким, как текстовые файлы и элек­тронные таблицы.

Для выборки данных в Transact-SQL существует команда select, которая по­зволяет делать простую выборку всех данных из одной таблицы текущей базы данных и выполнять сложные запросы одновременно к множеству таблиц различных баз данных, расположенных на нескольких серверах сети. В самом простом случае выборка данных производится с помощью команды:

SELECT * FROM table_name

Эта команда выводит данные из всех столбцов для всех строк таблицы, т.е, в результате выполнения запроса возвращается вся информация, содержащаяся в таблице. Однако в большинстве случаев применяются более сложные конструкции, использующие группировку, агрегирование, подзапросы, условия и другие дополнительные механизмы управления запросом.

Хранимые процедуры

Выполнение операций insert, select, update и delete, используемых соответственно для добавления, выборки, изменения и удаления данных, не вызывает особых затруднений. Пользо­ватель может написать запрос непосредственно в окне Query Analyzer и выпол­нить его. Тем не менее основными клиентами баз данных являются приложе­ния, специально созданные для выполнения определенных задач. Эти прило­жения часто реализуют сложные операции, требующие использования множест­ва команд.

Чтобы выполнить такие операции, приложение отсылает на сервер одну или более команд, которые там выполняются. В ответ сервер отправляет клиенту (приложению) результат обработки запроса. Этим результатом может быть как сообщение об успешном завершении выполнения команды, занимающее всего несколько байт, так и огромный массив данных, включающий тысячи строк и занимающий несколько мегабайт. Клиент может обработать полученный резуль­тат и на основе своего полученного результата отослать серверу новый запрос.

В рассмотренной ситуации логика обработки данных реализована на клиенте. Сервер просто принимает набор инструкций и выполняет их. При написании приложения разработчик должен позаботиться о разработке запросов, коррект­но работающих с данными и выполняющих все нужные действия.

Предложенный подход к обработке данных имеет несколько недостатков. Например, если нужно изменить логику обработки данных, то следует изменять исходный код программы, после чего заново компилировать ее и распростра­нять всем пользователям. Кроме того, если одна и та же логика обработки дан­ных используется в нескольких приложениях, то в худшем случае для каждого из этих приложений нужно будет повторять процесс разработки запросов, а в лучшем – переносить код из уже работающего приложения. Также следует обра­тить внимание на сам процесс взаимодействия сервера и клиента. По-видимому, алгоритмы обработки данных будут реализованы в виде набора блоков команд, поочередно отправляемых на сервер. После выполнения блока команд прило­жение получает определенный результат, после обработки которого решается, какой следующий блок и с какими параметрами должен быть выполнен. В не­которых ситуациях обмен между клиентом и сервером наборами команд и ре­зультатами может занимать много времени и генерировать большой сетевой трафик, что отрицательно сказывается на работе приложения в целом и на работе других пользователей сети.

В данном контексте необходимо сказать о безопасности. Для выполнения обработки данных пользователь должен иметь соответствующие права доступа. Предполагается, что эти права будут использованы приложением для доступа к данным. Однако нельзя быть до конца уверенным, что пользователь не сможет обратиться к данным напрямую, например, с помощью Query Analyzer, и выполнить неразрешенные действия. Нельзя быть уверенным и в том, что команды, отправляемые приложением, осуществляют верные действия. Ошибка разработчика при создании запроса может иногда привести к повреждению данных. Кроме того, нельзя не учитывать, что злоумышленник или тот же разработчик способны изменит код запроса для получения несанкционированного доступа к данным или для их повреждения, или даже уничтожения.

Все сказанное выше демонстрирует недостатки подхода к разработке систем, когда логика обработки данных реализуется на клиенте. Описанные проблемы могут быть решены за счет переноса алгоритмов обработки данных на сервер. В этом случае приложение просто сообщает серверу, какой именно набор команд необходимо выполнить. Дополнительно могут быть указаны параметры, которые в зависимости от реализации алгоритма будут влиять на ход выполнения про­цесса обработки данных. При этом приложение сможет получать только конеч­ный результат выполнения. Промежуточные результаты будут обработаны сервером, что позволяет снизить сетевой трафик. Этот набор команд Transact-SQL, сохраненных специальным образом на сервере и выполняемых как одно целое, в терминологии SQL Server 2000 называется хранимой процедурой (stored procedure).

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

Кроме того, использование хранимых процедур также позволяет значительно повысить безопасность данных. Приложение или пользователь получают лишь специальное право на выполнение хранимой процедуры, которая и будет обра­щаться к данным. Доступа же к самим данным пользователь не получает. В хра­нимой процедуре можно реализовать проверки на правильность выполняемых изменений, что обеспечит логическую целостность данных. Также можно реали­зовать проверки на права пользователя выполнять те или иные действия.

В SQL Server 2000 различают:

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

- расширенные хранимые процедуры, представляющие собой динамически подключаемые программы, которые в операционной среде могут играть роль самостоятельного приложения;

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

Использование курсоров

В ответ на запросы пользователей SQL Server 2000 может возвращать сотни тысяч строк общим объемом в десятки мегабайт. Передача такого объема данных по сети одновременно многими пользователями может вызвать значительную загрузку, что отрицательно скажется на работе всех пользователей сети. Кроме того, не каждый клиент имеет достаточный объем памяти, чтобы сохранить все полученные данные. К тому же обычно клиент работает лишь с небольшой ча­стью данных, например с отдельной строкой, а не со всем набором строк, т.е. клиенту не нужен одновременно весь набор данных. Для него было бы предпоч­тительней получать с сервера результат выборки отдельными порциями, что позволило бы снизить требования к мощности компьютера-клиента и уменьшить интенсивность сетевого трафика. Кроме того, часто бывает необходимо иметь возможность обратиться к конкретной строке выборки по ее номеру, од­нако с помощью команды select сделать это довольно трудно.

Механизмом, обеспечивающим хранение результата выборки на сервере и предоставляющим пользователю возможность доступа к любой строке выборки по ее номеру, являются курсоры (cursors). Курсор представляет своего рода окно, накладываемое на результат выборки. Пользователь может работать в каждый момент времени только с одной строкой, но, перемещая окно, он способен по­лучить доступ к любой строке выборки.

Исходный набор данных, к которому обращается пользователь, называется полным набором строк (complete set of rows). В результате выполнения запроса select пользователю возвращается набор данных, называемый результирующим набором (resulting set). Результирующий набор формируется в результате применения к полному набору строк горизонтального и вертикального фильтров. Горизонталь­ная фильтрация выполняется с помощью указания одного или более логических условий в разделе where. Вертикальная же фильтрация подразумевает включение в результирующий набор не всех столбцов исходного набора данных. Горизонталь­ная и вертикальная фильтрации могут использоваться как по отдельности, так и вместе. Курсоры SQL Server 2000 работают с результирующим набором, предлагая пользователям дополнительные средства по его обработке.

По месту хранения и принципам работы курсоры классифицируются следующим образом:

- курсоры Transact-SQL (Transact-SQL Cursors). Создание курсоров этого типа и работа с ними ведется средствами команд Transact-SQL. Эти курсоры соз­даются на сервере. Интенсивное применение может потребовать использования дополнительной оперативной памяти для хранения данных курсоров. Курсоры Transact-SQL могут создаваться и работать в транзакциях, хранимых процедурах и триггерах;

- курсоры API сервера (API Server Cursors). Этот тип курсоров используется приложениями при работе с различными механизмами доступа к данным (ODBC, OLE DB, DB Library и т. д.). Используя соответствующий API, клиент выполняет команду создания курсора. API сервера принимает запрос и создает на сервере курсор Transact-SQL. Работа с этим курсором выполняется средствами API, реализующего все базовые операции с курсорами и, возможно, некоторые дополнительные операции. Как и в случае с курсорами Transact-SQL, при действиях с курсорами API сервера данные хранятся на сервере;

- курсоры клиента (Client Cursors). Этот тип курсоров создается непосредстввенно на клиенте. Сервер обрабатывает отправленный клиентом запрос и возвращает ему результирующий набор. Клиент получает весь результирующий набор и уже сам организует нужные механизмы доступа к данным. Такой подход весьма удобен при работе с небольшим набором данных, так как позволяет повысить производительность за счет уменьшения количества обращений, требующих определенного времени на обработку. Однако при paботе с большими наборами данных каждый из клиентов должен иметь необходимый объем оперативной памяти.

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

1. Последовательные (Forward-only). Этот тип курсоров разрешает только последовательное считывание строк, начиная с первой строки и заканчивая последней. После выполнения команды выборки сервер автоматически перемещает указатель на следующую строку. Сам пользователь не может управлять ходом выборки строк – например, считать предыдущую строку или строку через две после текущей. Последовательные курсоры, хотя и обладают ограниченной функциональностью, работают быстрее прокручиваемых курсоров;

2. Прокручиваемые (Scrollable). В отличие от последовательных курсоров курсоры этого типа позволяют обращаться к произвольной строке результатирующего набора. В распоряжении пользователей имеются средства как последовательного обращения к строкам курсора, так и средства работы со строками по их порядковому номеру в результатирующем наборе. Направление перебора строк может быть не только прямым (от первой строки к последней), но и обратным (от последней к первой). Кроме того, можно в произвольном порядке комбинировать команды последовательного и произвольного обращения к строкам курсора.

Триггеры

Часто разработчикам приходится реализовывать сложные алгоритмы поддержки целостности данных. Использование ог­раничений целостности Primary Key, Foreign Key и других предоставляют разработчикам достаточно эффективные механизмы обеспечения целостности данных. Однако их бывает недостаточно. Например, с помощью упомянутых механизмов нельзя разрешить изменение данных в том случае, если в одном из столбцов находится опреде­ленное значение.

Описанная ситуация является простейшим примером того, какие проверки нередко приходится выполнять перед изменением, удалением или вставкой данных в таблицу. В реальной ситуации применяются гораздо более сложные алгоритмы предварительной проверки данных. Помимо выполнения простых проверок, при модификации данных одной таблицы иногда бывает необходимо соответст­вующим образом модифицировать данные одной или нескольких таблиц. Реше­нием описанной задачи является использование триггеров

Триггеры (triggers) SQL Server 2000 представляют собой набор команд Transact-SQL, выполняемых автоматически при осуществлении тех или иных модифика­ций данных в таблице. Физически триггеры являются ни чем иным, как храни­мыми процедурами специального типа. Каждый триггер связан с конкретной таблицей и запускается сервером автоматически каждый раз, когда пользователи пытаются произвести вставку, изменение или удаление данных. Триггер получа­ет всю информацию о выполняемых пользователем изменениях в таблице. Раз­работчик реализовывает в триггере необходимые проверки и изменения данных в других таблицах базы данных.

Когда пользователь начинает изменение данных, сервер автоматически начинает транзакцию, в которой и выполняется триггер. В теле транзакции разработчик может реализовывать произвольные алгоритмы, которые могут выполнять как проверку, так и изменения данных. В конце концов, работа триггера сводится либо к фиксации, либо к откату транзакции, которая осуществляет изменение данных. Если выполняется откат транзакции, то попытка пользователя изменить данные отменяется. При этом также отменяются все исправления, сделанные самим триггером в различных таблицах (если они выполнялись). При фиксации транзакции производится как фиксирование изменений, выполненных пользо­вателем, так и изменений, сделанных самим триггером.