Рисунок 1. План обработки SQL-выражения.
Что такое хранимые процедуры?
Хранимая процедура - это набор SQL-инструкций, которые под определенным названием хранятся в базе данных в откомпилированном виде, и которые можно использовать в нескольких приложениях. В хранимых процедурах могут быть не только выражения Select, Insert, Update и Delete, но и инкапсулированный процедурный код. Именно откомпилированные и инкапсулированные процедуры могут предоставлять более эффективный и безопасный доступ к данным.Каждый раз, когда вы осуществляете запрос к базе данных, база данных проделывает два действия: компилирует выражение запроса, выполняет его. В процессе компиляции происходит отдельный анализ: определение таблиц и связей между ними, определение используемых индексов и когда объединять и фильтровать набор записей. Например посмотрите на Рисунке 1 план обработки выражения:
Рисунок 1. План обработки SQL-выражения.
Явные преимущества использования хранимых процедур:
Подготовимся к выполнению практических упражнений
Этот SQL крипт поможет вам создать в своем MS SQL Server'е таблицы с данными, необходимые для выполнения практического упражнения: Создайте тестовую базу данных, а в ней, с помощью Query Analyzer'а, создайте таблицы с данными, запустив в нем этот скрипт.Затем в Административной оболочке ColdFusion сервера создайте источник данных для только что созданной базы данных и назовите его 'sqltst'.
Добавляем данные в базу данных с помощью хранимой процедуры
В первом примере мы создам хранимую процедуру, которая будет вставлять новую запись в таблицу 'Books' (Рисунок 2).
Рисунок 2. Таблица 'Books'.
В приведенном выше примере мы использовали атрибут dbvarname для того, чтобы обозначить параметры по их имени, а не по позиции. Иными словами, если мы хотим передавать параметры в хранимую процедуру в той последовательности как они провозглашаются в самой хранимой процедуре, то атрибут dbvarname использовать не нужно, и наоборот.
Атрибут cfsqltype всегда обязателен и указывает тип передаваемого или получаемого параметра. Если вы хотите передать параметр, равный определенному значению, то это значение следует указать в атрибуте value. А если вам нужно передать параметр, равный значению null, то следует указать атрибут null="yes" и значение атрибута value (если оно указано) будет проигнорировано.Для выходного параметра мы снова используем тег <cfprocparam>, указав атрибут type="out", а также имя получаемой переменной через атрибут variable. Если параметр одновременно является входным и выходным, то следует указать type="inout". Чтобы узнать больше о тегах <cfstoredproc> и <cfprocparam>, посмотрите документацию по CFML.
Получаем из базы данных набор записей
Хранимые процедуры могут возвращать не только один параметр, но и целый набор записей. Вот пример процедуры, которая принимает один параметр - примерное название книги, и возвращает информацию о тех книгах, в которых встречается указанное название:CREATE PROCEDURE sp_Search_By_Title (
@arg_Title VARCHAR(255))
as
BEGIN
SELECT
BookID,
Title,
Price,
PublishDate
FROM
Books
WHERE
Title LIKE '%' + @arg_Title + '%';
END;
CFML код, запрашивающий данную процедуру, будет выглядеть так:
На Рисунке 3 вы можете посмотреть результат поиска.
Рисунок 3. Результат поиска, полученный из хранимой процедуры.
Заключение
В этой статье рассмотрены только основные понятия работы с хранимыми процедурами в MS SQL Server. Обратившись к документации по программированию хранимых процедур, вы узнаете, как можно усложнить их, получив взамен больше эффективности и безопасности ваших приложений.
© 2002-2005 г. Вадим Пушкарев
