Включай в свои процедуры строку - SET NOCOUNT ON:

С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

Transact-SQL

Используй имя схемы с именем объекта:

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

Transact-SQL

SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!

Не используй префикс «sp_» в имени своих хранимых процедур:

Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.

Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):

Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.

Понятие хранимой процедуры

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

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

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

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

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

Хранимые процедуры в среде MS SQL Server

При работе с SQL Server пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами базы данных, а потому каждая из них хранится в конкретной базе данных. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той базы данных, где находится процедура.

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

В SQL Server имеется несколько типов хранимых процедур .

• Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_ , хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
• Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
• Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа # . Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ## . Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение и удаление хранимых процедур

Создание хранимой процедуры предполагает решение следующих задач:

• определение типа создаваемой хранимой процедуры : временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру , назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера;
• планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;
• определение параметров хранимой процедуры . Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами ;
• разработка кода хранимой процедуры . Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур .

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:

<определение_процедуры>::= {CREATE | ALTER } PROC имя_процедуры [;номер] [{@имя_параметра тип_данных } [=default] ][,...n] AS sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_ , # , ## , создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

Для передачи входных и выходных данных в создаваемой хранимой процедуре могут использоваться параметры , имена которых, как и имена локальных переменных, должны начинаться с символа @ . В одной хранимой процедуре можно задать множество параметров , разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры.

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры , годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры , т.е. с указанием ключевого слова OUTPUT .

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

Ключевое слово VARYING применяется совместно с параметром OUTPUT , имеющим тип CURSOR . Оно определяет, что выходным параметром будет результирующее множество.

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

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.

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

Ключевое слово AS размещается в начале собственно тела хранимой процедуры , т.е. набора команд SQL, с помощью которых и будет реализовываться то или иное действие. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры . Выход из хранимой процедуры можно осуществить посредством команды RETURN .

Удаление хранимой процедуры осуществляется командой:

DROP PROCEDURE {имя_процедуры} [,...n]

Выполнение хранимой процедуры

Для выполнения хранимой процедуры используется команда:

[[ EXEC [ UTE] имя_процедуры [;номер] [[@имя_параметра=]{значение | @имя_переменной} |][,...n]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.

Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров , которые были объявлены при создании процедуры с ключевым словом OUTPUT .

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

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

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

Пример 12.1. Процедура без параметров . Разработать процедуру для получения названий и стоимости товаров, приобретенных Ивановым.

CREATE PROC my_proc1 AS SELECT Товар.Название, Товар.Цена*Сделка.Количество AS Стоимость, Клиент.Фамилия FROM Клиент INNER JOIN (Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара) ON Клиент.КодКлиента=Сделка.КодКлиента WHERE Клиент.Фамилия=’Иванов’ Пример 12.1. Процедура для получения названий и стоимости товаров, приобретенных Ивановым.

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

EXEC my_proc1 или my_proc1

Процедура возвращает набор данных.

Пример 12.2. Процедура без параметров . Создать процедуру для уменьшения цены товара первого сорта на 10%.

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

EXEC my_proc2 или my_proc2

Процедура не возвращает никаких данных.

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

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Товар.Название, Товар.Цена*Сделка.Количество AS Стоимость, Клиент.Фамилия FROM Клиент INNER JOIN (Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара) ON Клиент.КодКлиента=Сделка.КодКлиента WHERE Клиент.Фамилия=@k Пример 12.3. Процедура для получения названий и стоимости товаров, которые приобрел заданный клиент.

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

EXEC my_proc3 "Иванов" или my_proc3 @k="Иванов"

Пример 12.4. . Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

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

EXEC my_proc4 "Вафли",0.05 или EXEC my_proc4 @t="Вафли", @p=0.05

Пример 12.5. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

CREATE PROC my_proc5 @t VARCHAR(20)=’Конфеты`, @p FLOAT=0.1 AS UPDATE Товар SET Цена=Цена*(1-@p) WHERE Тип=@t Пример 12.5. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

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

EXEC my_proc5 "Вафли",0.05 или EXEC my_proc5 @t="Вафли", @p=0.05 или EXEC my_proc5 @p=0.05

В этом случае уменьшается цена конфет (значение типа не указано при вызове процедуры и берется по умолчанию).

В последнем случае оба параметра (и тип, и проценты) не указаны при вызове процедуры, их значения берутся по умолчанию.

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

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Товар.Цена*Сделка.Количество) FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара GROUP BY Month(Сделка.Дата) HAVING Month(Сделка.Дата)=@m Пример 12.6. Процедура с входными и выходными параметрами. Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.

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

DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

Этот блок команд позволяет определить стоимость товаров, проданных в январе (входной параметр месяц указан равным 1).

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

Сначала разработаем процедуру для определения фирмы, где работает сотрудник.

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

Затем создадим процедуру, подсчитывающую общее количество товара, который закуплен интересующей нас фирмой.

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Сделка.Количество) FROM Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента GROUP BY Клиент.Фирма HAVING Клиент.Фирма=@firm Пример 12.7. Создание процедуры для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.

Вызов процедуры осуществляется с помощью команды:

DECLARE @k INT EXEC my_proc8 ‘Иванов’,@k OUTPUT SELECT @k

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

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

ШАГ 1 . Создание БД.
Для эксперимента создадим отдельную базу данных.

CREATE DATABASE test_sp_perf
ON (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG ON (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)

ШАГ 2. Создание таблицы.
CREATE TABLE sp_perf_test(column1 int, column2 char(5000))

ШАГ 3. Заполнение таблицы тестовыми строками. В таблицу намеренно добавляются дублирующиеся строки. 10000 строк с номерами от 1 до 10000, и 10000 строк с номерами 50000.

DECLARE @i int
SET @i=1
WHILE(@i<10000)
BEGIN
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,"Test string #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Test string #"+CAST(@i as char(8)))
SET @i= @i+1
END

SELECT COUNT(*) FROM sp_perf_test
GO

ШАГ 4. Создание некластерного индекса. Поскольку план выполнения кэшируется вместе с процедурой, индекс будет при всех вызовах использоваться одинаково.

CREATE NONCLUSTERED INDEX CL_perf_test ON sp_perf_test(column1)
GO

ШАГ 5. Создание хранимой процедуры. Процедура просто выполняет оператор SELECT с условием.

CREATE PROC proc1 (@param int)
AS
SELECT column1, column2 FROM sp_perf_test WHERE column1=@param
GO

ШАГ 6. Запуск хранимой процедуры. При запуске ранимой процедуры специально используется селективный параметр. В результате выполнения процедуры получаем 1 строку. План выполнения показывает на использование некластерного индекса, т.к. запрос селективный и это наилучший способ извлечь строку. Процедура, оптимизированная на выборку одной строки, сохраняется в процедурном кэше.

EXEC proc1 1234
GO

ШАГ 7. Запуск хранимой процедуры с неселективным параметром. В качестве параметра используется значение 50000. Строк с таким значением первого столбца около 10000, соответственно, пользоваться некластерным индексом и операцией bookmark lookup неэффективно, но поскольку откомпилированный код с планом выполнения храниться в процедурном кэше, именно он и будет использоваться. План выполнения показывает это, а так же то, что для 9999 строк выполнялась операция bookmark lookup.

EXEC proc1 50000
GO

ШАГ 8. Выполнение выборки строк с первым полем равным 50000. При выполнении отдельного запроса выполняется оптимизация и компиляция запроса с конкретным значением первого столбца. В результате оптимизатор запросов определяет, что поле много раз дублируется и принимает решение использовать операцию table scan, что в данном случае намного эффективнее, чем использование некластерного индекса.

SELECT column1, column2 FROM sp_perf_test WHERE column1=50000
GO

Таким образом, можно заключить, что использование хранимых процедур не всегда может повысить производительность запросов. Следует крайне внимательно подходить к тем хранимым процедурам, которые работают с результатами с переменным количеством строк и использующие различные планы выполнения.
Можете использовать скрипт для повторения эксперимента на свеем MS SQL сервере.

Храни́мая процеду́ра - объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

Хранимые процедуры похожи на определяемые пользователем функции (UDF). Основное различие заключается в том, что пользовательские функции можно использовать как и любое другое выражение в SQL запросе, в то время как хранимые процедуры должны быть вызваны с помощью функции CALL:

CALL процедура(…)

EXECUTE процедура(…)

Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими хранимыми процедурами, возвращая указатель результирующего множества, либо же приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Хранимые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.

Реализация хранимых процедур варьируется от одной СУБД к другой. Большинство крупных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от СУБД, хранимые процедуры могут быть реализованы на различных языках программирования, таких, как SQL, Java, C или C++. Хранимые процедуры написанные не на SQL могут самостоятельно выполнять SQL-запросы, а могут и не выполнять.

За

Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.

Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.

Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.

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

Против

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

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

Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.

Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Назначение и преимущества хранимых процедур

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

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

Кроме собственно выполнения запроса, хранимые процедуры позволяют также производить вычисления и манипуляцию данными - изменение, удаление, выполнять DDL-операторы (не во всех СУБД!) и вызывать другие хранимые процедуры, выполнять сложную транзакционную логику. Один-единственный оператор позволяет вызвать сложный сценарий, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд и, в особенности, необходимости передачи больших объёмов данных с клиента на сервер.

В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным). В дальнейшем её обработка осуществляется быстрее. В СУБД Oracle выполняется интерпретация хранимого процедурного кода, сохраняемого в словаре данных. Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция (native compilation) хранимого процедурного кода в Си и затем в машинный код целевой машины, после чего при вызове хранимой процедуры происходит прямое выполнение её скомпилированного объектного кода.

Возможности программирования

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

Безопасность

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

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

Снижается вероятность таких действий как «внедрение SQL-кода», поскольку хорошо написанные хранимые процедуры дополнительно проверяют входные параметры перед тем, как передать запрос СУБД.

Реализация хранимых процедур

Хранимые процедуры обычно создаются с помощью языка SQL или конкретной его реализации в выбранной СУБД. Например, для этих целей в СУБД Microsoft SQL Server существует язык Transact-SQL, в Oracle - PL/SQL, в InterBase и Firebird - PSQL, в PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, в IBM DB2 - SQL/PL (англ.), в Informix - SPL. MySQL достаточно близко следует стандарту SQL:2003, её язык похож на SQL/PL.

В некоторых СУБД возможно использование хранимых процедур, написанных на любом языке программирования, способном создавать независимые исполняемые файлы, например, на C++ или Delphi. В терминологии Microsoft SQL Server такие процедуры называются расширенными хранимыми процедурами и являются просто функциями, содержащимися в Win32-DLL. А, например, в Interbase и Firebird для функций, вызываемых из DLL/SO, определено другое название - UDF (User Defined Function). В MS SQL 2005 появилась возможность написания хранимых процедур на любом языке.NET, а от расширенных хранимых процедур в будущем планируется отказаться. СУБД Oracle, в свою очередь, допускает написание хранимых процедур на языке Java. В IBM DB2 написание хранимых процедур и функций на обычных языках программирования является традиционным способом, поддерживаемым с самого начала, а процедурное расширение SQL было добавлено в эту СУБД только в достаточно поздних версиях, после его включения в стандарт ANSI. Также процедуры на Java и С поддерживает Informix.

В СУБД Oracle хранимые процедуры могут объединяться в так называемые пакеты (англ. packages). Пакет состоит из двух частей - спецификации (англ. package specification), в которой указывается определение хранимой процедуры, и тела (англ. package body), где находится её реализация. Таким образом Oracle позволяет отделить интерфейс программного кода от его реализации.

В СУБД IBM DB2 хранимые процедуры можно объединять в модули.

Синтаксис

CREATE PROCEDURE `p2` ()

SQL SECURITY DEFINER

COMMENT "A procedure"

SELECT "Hello World !";

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

4 характеристики хранимой процедуры:

Language: в целях обеспечения переносимости, по умолчанию указан SQL.

Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.

SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.

Comment: в целях документирования, значение по умолчанию - ""

Вызов хранимой процедуры

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , "string parameter" , @parameter_var);

Изменение хранимой процедуры

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

DROP PROCEDURE IF EXISTS p2;

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Параметры

CREATE PROCEDURE proc1 (): пустой список параметров

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.

Синтаксис объявления переменной выглядит так:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Хранимые процедуры SQL представляют собой исполняемый программный модуль, который может храниться в в виде различных объектов. Другими словами, это объект, в котором содержатся SQL-инструкции. Эти хранимые процедуры могут быть выполнены в клиенте прикладных программ, чтобы получить хорошую производительность. Кроме того, такие объекты нередко вызываются из других сценариев или даже из какого-либо другого раздела.

Введение

Многие считают, что они похожи на процедуры различных (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название - UDF).

В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность - все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой набирал популярность в среде программистов. Сегодня же является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».

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

Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact-SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.

Продуктивность

Эти объекты баз данных могут быть запрограммированы различными путями. Это позволяет пользователям выбирать тип используемого способа, который будет наиболее подходящим, что экономит силы и время. Кроме того, процедура сама обрабатывается, что позволяет избежать огромных временных затрат на обмен между сервером и пользователем. Также модуль можно перепрограммировать и изменить в нужное направление в абсолютно любой момент. Особенно стоит отметить скорость, с которой происходит запуск хранимой процедуры SQL: это процесс происходит быстрее иных, схожих с ним, что делает его удобным и универсальным.

Безопасность

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

Передача данных

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

Передача данных с помощью параметра типа Output;

Передача данных с помощью оператора возврата;

Передача данных с помощью оператора выбора.

А теперь разберемся, как же выглядит этот процесс изнутри.

1. Создание EXEC-хранимой процедуры в SQL

Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC-процесс, который содержит имя самого объекта.

При создании процедуры ее название появляется первым, после чего производится один или несколько параметров, присвоенных ему. Параметры могут быть необязательными. После того как параметр(ы), то есть тело процедуры, будут написаны, нужно провести некоторые необходимые операции.

Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.

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

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

Тело не должно создавать какой-либо другой хранимой процедуры;

Тело не должно создать ложное представление об объекте;

Тело не должно создавать никаких триггеров.

2. Установка переменной в тело процедуры

Вы можете сделать переменные локальными для тела процедуры, и тогда они будут находиться исключительно внутри тела процедуры. Хорошей практикой является создание переменных в начале тела хранимой процедуры. Но также вы можете устанавливать переменные в любом месте в теле данного объекта.

Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ​​ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.

Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.

В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.

В предыдущем примере процедура принимает два параметра, которые на английском языке условно будут называться @State и @City. Тип данных соответствует типу, определенному в приложении. Тело процедуры имеет внутренние переменные @TotalAuthors (всего авторов), и эта переменная используется для отображения их количества. Далее появляется раздел выбора запроса, который все подсчитывает. Наконец, подсчитанное значение выводится в окне вывода с помощью оператора печати.

Как в SQL выполнить хранимую процедуру

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

Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.

4. Хранимые процедуры SQL Server: способы возврата

Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:

Возврат значения хранимой процедуры;

Выход параметра хранимых процедур;

Выбор одной из хранимых процедур.

4.1 Возврат значений хранимых процедур SQL

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

Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select-оператор, например, Select @RetValue, а также OutputValue.

4.2 Выход параметра хранимых процедур SQL

Ответное значение может быть использовано для возврата одной переменной, что мы и видели в предыдущем примере. Использование параметра Output позволяет процедуре отправить одно или несколько значений переменных для вызывающей стороны. Выходной параметр обозначается как раз-таки этим ключевым словом «Output» при создании процедуры. Если параметр задан в качестве выходного параметра, то объект процедуры должен присвоить ему значение. Хранимые процедуры SQL, примеры которых можно увидеть ниже, в таком случае возвращаются с итоговой информацией.

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

Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.

4.3 Выбор одной из хранимых процедур SQL

Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких-либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.

В заключение

Хранимая процедура является довольно серьезным программным модулем, возвращающим или передающим, а также устанавливающим необходимые переменные благодаря клиентскому приложению. Поскольку хранимая процедура выполняется на сервере сама, обмена данными в огромных объемах между сервером и клиентским приложением (для некоторых вычислений) можно избежать. Это позволяет снижать нагрузки на сервера SQL, что, конечно же, идет на руку их держателям. Одним из подвидов являются хранимые процедуры T SQL, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.