Microsoft SQL Server предоставляет механизм хранимых процедур для упрощения процесса разработки базы данных путем группировки операторов Transact-SQL в управляемые блоки. Хранимые процедуры ценятся большинством разработчиков SQL Server, которые считают, что преимущества в эффективности и безопасности, которые они получают, вполне стоят первоначальных затрат времени.
Преимущества использования хранимых процедур
Почему разработчик должен использовать хранимые процедуры? Вот основные преимущества этой технологии:
- Прекомпилированное выполнение: SQL Server компилирует каждую хранимую процедуру один раз, а затем повторно использует план выполнения. Это приводит к значительному повышению производительности при многократном вызове хранимых процедур.
- Сокращение трафика клиент / сервер: если пропускная способность сети является проблемой в вашей среде, вы будете рады узнать, что хранимые процедуры могут сократить длинные запросы SQL до одной строки, передаваемой по проводам.
- Эффективное повторное использование кода и программной абстракции: хранимые процедуры могут использоваться несколькими пользователями и клиентскими программами. Если вы используете их запланированным образом, вы обнаружите, что цикл разработки занимает меньше времени.
- Расширенные элементы управления безопасностью: вы можете предоставить пользователям разрешение на выполнение хранимой процедуры независимо от базовых разрешений таблицы.
Хранимые процедуры похожи на пользовательские функции, но есть небольшие различия.
Состав
Хранимые процедуры похожи на конструкции, встречающиеся в других языках программирования. Они принимают данные в виде входных параметров, которые указываются во время выполнения. Эти входные параметры (если они реализованы) используются при выполнении ряда операторов, которые дают некоторый результат. Этот результат возвращается в вызывающую среду с помощью набора записей, выходных параметров и кода возврата. Это может звучать как глоток, но вы обнаружите, что хранимые процедуры на самом деле довольно просты.
пример
Давайте посмотрим на практический пример, связанный с таблицей с именем инвентарь, показанной внизу этой страницы. Эта информация обновляется в режиме реального времени, и менеджеры склада постоянно проверяют уровни продуктов, хранящихся на их складе и доступных для отправки. В прошлом каждый менеджер выполнял запросы, подобные следующим:
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'
Это привело к неэффективной производительности на SQL Server. Каждый раз, когда менеджер хранилища выполнял запрос, сервер базы данных был вынужден перекомпилировать запрос и выполнить его с нуля. Также требуется, чтобы менеджер хранилища знал SQL и имел соответствующие разрешения для доступа к информации таблицы.
Вместо этого процесс может быть упрощен с помощью хранимой процедуры. Вот код для процедуры sp_GetInventory, которая извлекает уровни запасов для данного склада.
CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location
Затем менеджер склада во Флориде может получить доступ к уровням запасов, выполнив команду:
EXECUTE sp_GetInventory 'FL'
Менеджер склада в Нью-Йорке может использовать ту же хранимую процедуру для доступа к инвентарю этой области:
EXECUTE sp_GetInventory 'NY'
Конечно, это простой пример, но преимущества абстракции можно увидеть здесь. Менеджер склада не должен понимать SQL или внутреннюю работу процедуры. С точки зрения производительности хранимая процедура творит чудеса. SQL Server создает план выполнения один раз, а затем повторно использует его, подключая соответствующие параметры во время выполнения.
Теперь, когда вы узнали о преимуществах хранимых процедур, используйте их. Попробуйте несколько примеров и оцените достигнутые улучшения производительности — вы будете поражены!
Инвентарный стол
| Я БЫ | Товар | Склад | Количество |
| 142 | зеленая фасоль | Нью-Йорк | 100 |
| 214 | Горох | Флорида | 200 |
| 825 | Кукуруза | Нью-Йорк | 140 |
| 512 | лимская фасоль | Нью-Йорк | 180 |
| 491 | помидоры | Флорида | 80 |
| 379 | Арбуз | Флорида | 85 |