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

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

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, выполняемых автоматически при осуществлении тех или иных модификаций данных в таблице. Физически триггеры являются ни чем иным, как хранимыми процедурами специального типа. Каждый триггер связан с конкретной таблицей и запускается сервером автоматически каждый раз, когда пользователи пытаются произвести вставку, изменение или удаление данных. Триггер получает всю информацию о выполняемых пользователем изменениях в таблице. Разработчик реализовывает в триггере необходимые проверки и изменения данных в других таблицах базы данных.

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

Статьи к прочтению:

Ошибка в базе данных: не дает редактировать, копировать и удалять таблицу | pHp для чайников

Похожие статьи: