Хранимые процедуры: понятие, область применения, достоинства и недостатки

ВЫВОДЫ

В хранимых процедурах можно использовать такие элементы программирования, как параметры, переменные, коды , функции, значения по умолчанию, вложенные процедуры и курсоры. Входные параметры передают процедуре данные, а выходные параметры получают данные от хранимой процедуры. Переменные используются для хранения выходных параметров. Выходные параметры определяют с помощью ключевого слова OUTPUT. Коды возврата используются главным образом для обработки ошибок. Коды возврата передаются с помощью ключевого слова RETURN, после которого следует целое число. При вызове оператора RETURN происходит безусловный выход из программы. Затем код возврата используется в условных операторах для перехода к соответствующей ветви программы. Для обнаружения ошибок базы данных служит функция @@ERROR в с ...

СОДЕРЖАНИЕ

Введение 3
1. Хранимые процедуры – основные понятия 4
2. Создание хранимых процедур 5
3. Изменение хранимых процедур 9
4 Выполнение хранимых процедур 10
5 Некоторые особенности и детали при использовании хранимых процедур 12
6. Расширенные хранимые процедуры 14
7. Ошибки. сообщения об ошибках 17
Выводы 20
Список использованной литературы 21

ВВЕДЕНИЕ

Хранимые процедуры – это процессы, выполнение которых происходит непосредственно на сервере баз данных.
Все хранимые процедуры в базе данных находятся в специально отведенном списке Stored Procedures раздела базы данных Programmability. Следует обратить внимание на перечень системных процедур, используемых при работе SQL-сервера, список которых находятся в группе System Stored Procedures [1].
Для создания новой процедуры выберите команду New Stored Procedures контекстного меню, после чего на экране отобразится вкладка, в которой будет расположен текст шаблона для создания текста процедуры.
Несмотря на ряд ограничений и недостатков, хранимые процедуры обладают рядом преимуществ. Хотя большую часть того, что позволяет хранимая процедура, вы можете сделать при помощи простого кода Transact-SQL, хранимые процедуры имеют множество преимуществ перед прямыми запросами, в том числе:
- перестройка и повторное использование плана выполнения;
- автопараметризация запроса;
- инкапсуляция бизнес-логики;
- обеспечение модульной структуры приложения;
- совместное использование в нескольких приложениях;
- авторизованный и единообразный доступ к объектам базы данных;
- последовательная, безопасная модификация данных;
- уменьшение сетевого трафика;
- поддержка автоматического выполнения при запуске системы.
Именно поэтому и в силу еще ряда причин, такая тема является актуальной и рассмотрена в данном реферате.

CREATE PROC dbo.ex_SEL_ProductAll
AS

SET NOCOUNT ON -- не передавать сообщения о количестве обработанных записей
SELECT F.dtFirstDay,
F.NPech,
F.PlanCalendar,
F.PlanStand,
F.Naliv,
F.Sliv,
F.PercentProduct,
F.NalivDirty,
F.SlivDirty,
F.Shlak
FROM dbo.ex_Product AS F
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Представленный листинг начинается с установки параметров окружения, которые позволяют изменять поведение хранимых процедур. Настройка QUOTED_IDENTIFIER определяет, интерпретировать ли строки в двойных кавычках как идентификаторы объектов (например, таблиц или столбцов). Установка ANSI_NULLS определяет, разрешены ли не-ANSI сравнения с неопределенными (NULL) значениями, что является особенно важным для хранимых процедур, параметрыкоторых могут иметь значения NULL.
Комментарии к хранимой процедуре должны содержать пример вызова процедуры для проверки ее работоспособности. С помощью SQL-команды EXEC осуществляется запуск хранимых процедур, причем весь процесс их выполнения происходит на самом компьютере-сервере. Использование хранимых процедур существенно отражается на производительности компьютеров рабочих станций. Другими словами, данная технология позволяет использовать компьютеры рабочих станций на уровне клавиатурного ввода, а все основные задачи при этом перекладываются на компьютер-сервер.
3. ИЗМЕНЕНИЕ ХРАНИМЫХ ПРОЦЕДУР
Используя команду CREATE PROCEDURE, программист сможет создать хранимые процедуры. Для их изменения можно использовать команду ALTER PROCEDURE. Преимущество ее применения состоит в том, что сохраняются права доступа, тогда как при использовании CREATE PROCEDURE этого не происходит. Ключевое различие между ними в том, что ALTER PROCEDURE требует использования тех же параметров шифрования и пере­компиляции, какие изначально были использованы при ее создании оператором CREATE PROCEDURE. Если вы опустите или измените их значение при выполнении ALTER PROCEDURE, то они будут также опущены или изменены в самом определении процедуры [3].
Процедура может содержать любую допустимую команду Тransact-SQL, кроме следующих: CREATE DEFAULT, CREATE FUNCTION, CREATE PROC, CREATE RULE, CREATE SCHEMA, CREATE TR IGGER, CREATE VI EW, SET SH0WPLAN_TEXT и SET SH0WPLAN_ALL. Эти команды должны находиться в отдельных пакетах команд и поэтому не могут быть частью храни­мой процедуры. Из процедур можно создавать базы данных, таблицы и индексы, но нельзя создавать другие процедуры, стандартные значения, функции, правила, схемы, триггеры или представления.
Это предупреждение появляется вследствие того, что процедуры test2 еще не су­ществует при создании процедуры test. Вы можете его полностью проигнорировать.
4 ВЫПОЛНЕНИЕ ХРАНИМЫХ ПРОЦЕДУР
Процесс выполнения хранимой процедуры возможен, но написать ее имя нужно в пакете ко­манд. Необходимо создавать сам ЕХЕС перед именем хранимой процедуры: EXEC dbo.sp_who.Вызов хранимой процедуры без ключевого слова ЕХЕС должен быть первым в па­кете команд, поэтому, если в дальнейшем перед вызовом процедуры добавятся какие-нибудь строки, ваш код перестанет работать.
Необходимо добавлять префикс имени владельца при вызове процедуры (dbo в предыду­щем примере). Если не указать владельца, сервер установит блокировку компиля­ции на хранимую процедуру, так как он не может сразу найти его в кэше процеду­ры. Эта блокировка снимается, как только владелец найден в кэше, однако это может привести к проблемам в приложениях, требующих высокой производительности. Указание владельца — просто хорошая привычка. Это одна из тех вещей, которую вы можете сделать, чтобы заранее избавить себя от проблем.
Связку I NSERT-EXEC возможно применить с работой расширенных хранимых процедур.
Компиляция плана выполнения и выполнение происходит следующим образом. При первом запуске хранимой процедуры она компилируется, и для нее создается план выполнения. План компилируется не в машинный код и даже не в байт-код, а псевдокомпилируется для ускорения выполнения. Под «псевдокомпилируется» я имею в виду, что определяются ссылки на объекты, выбираются способы объ­единения и индексы и оптимизатор SQL Server строит наиболее эффективный план для выполнения хранимой процедуры. Оптимизатор сравнивает различные пла­ны выполнения и выбирает тот, использование которого будет стоить меньше в терминах полного времени выполнения. Оптимизатор принимает решение, осно­вываясь на множестве параметров, включая оценку количества операций ввода- вывода, связанную с каждым планом, потребность в процессорном времени, тре­буемый объем памяти и т. д.
После создания план выполнения помещается в кэш для дальнейшего исполь­зования. Этот кэш растет и уменьшается по мере необходимости, чтобы хранить планы выполнения хранимых процедур и прямых запросов, выполненных сер­вером. SQL Server поддерживает равновесие между объемами памяти, выделяе­мыми под процедурный кэш и для других целей (например, для кэша данных). Очевидно, что память, используемая для кэширования планов выполнения, не может использоваться для кэширования данных, так что сервер очень осторож­но управляет соотношением объемов памяти процедурного кэша и кэша данных. Кэширование плана выполнения позволяет оптимизатору не перестраивать его при каждом запуске процедуры, что очень сильно повышает производительность.
5 НЕКОТОРЫЕ ОСОБЕННОСТИ И ДЕТАЛИ ПРИ ИСПОЛЬЗОВАНИИ ХРАНИМЫХ ПРОЦЕДУР
Временные процедуры создаются так же, как и временные таблицы: путем добав­ления префикса # создается локальная временная процедура, видимая только в текущем соединении, а добавление префикса ## создает глобальную временную процедуру, доступную для всех соединений. Временные процедуры полезны, когда возникает необходимость объединить преимущества хранимых процедур, такие как повторное использование плана выполнения и расширенные возможности обработки ошибок с преимуществами прямых запросов. Поскольку можно создавать и запускать временные процедуры «налету», то в результате на практике неоходимо применять лучшее из обоих методов. В принципе, sp_executesq 1 может избавить нас от ис­пользования временных процедур, но мы можем их использовать, если нам необ­ходима функциональность большая, чем может предоставить sp_executesq I.
Системные процедуры находятся в базе данных master и имеют префикс sp_. Вы можете выполнять системные хранимые процедуры из любой базы данных, при этом системная процедура выполняется в контексте этой базы данных. Так, на­пример, если процедура ссылается на таблицу sysobjects (которая существует в каждой базе данных), она получит доступ к таблице sysobjects в базе данных, которая была текущей в момент запуска процедуры, а не к таблице из базы данных Master/
Отдельные команды Transact-SQL определяют порядок выполнения команд храни­мой процедуры или пакета команд. Эти команды называются командами управле­ния выполнением: IF... ELSE, WHILE, GOTO, RETURN, WAITFOR, BREAK, CONTINUE и BEG I N...END. Далее обсудим все эти команды, а пока для примера простая храни­мая процедура, которая иллюстрирует использование этих команд (Пример 2)
Пример 2. Команды управления выполнением
USE pubs
IF 0BJECT_ID('dbo. 1 istsales') IS NOT NULL DROP PROC dbo.1istsales
GO
CREATE PROC dbo.1istsales @tntle_id tid=NULL AS
IF (@title_id='/?') GOTO Help -- Here's a basic IF -- Here's one with a BEGIN..END block
IF NOT EXISTS(SELECT * FROM titles WHERE [email protected]_id) BEGIN PRINT 'Invalid title_id'
WAITFOR DELAY '00:00:03' -- Delay 3 secs to view message RETURN -1
END
IF NOT EXISTS(SELECT * FROM sales WHERE [email protected]_id) BEGIN PRINT 'No sales for this title'
WAITFOR DELAY '00:00:03' -- Delay 3 secs to view message RETURN -2
END
DECLARE @qty int. @totalsales int SET @totalsales=0
DECLARE с CURSOR
6. РАСШИРЕННЫЕ ХРАНИМЫЕ ПРОЦЕДУРЫ
Расширенные процедуры — это процедуры, располагающиеся в DLL и функциони­рующие так же, как и обычные процедуры. Они получают параметры и возвраща­ют результаты, используя Open Data Services API SQL Server, и обычно пишутся на С или C++. Они должны находиться в базе данных master и выполняться в ад­ресном пространстве процесса SQL Server. Хотя расширенные хранимые процеду­ры схожи с системными хранимыми процедурами, вызов расширенной хранимой процедуры немного отличается. Расширенные хранимые процедуры не будут ав­томатически найдены в базе данных master и не предполагается их выполнение в контексте текущей базы данных. Чтобы выполнить хранимую процедуру не из базы данных maste г, следует полностью указать ее имя (например, EXEC maste г. dbo. xp_cmdshel 1 dir ). Способ обойти это различие — «обернуть» расширенную хранимую проце­дуру в системную. Это позволяет выполнять ее из любой базы данных, не указы­вая префикс master. Этот способ используется для работы со многими расширен­ными хранимыми процедурами SQL Server. Многие из них «обернуты» в системные хранимые процедуры, которые
предназначены только для того, чтобы сделать вы­зов процедур немного удобнее. На примере 3 показана системная процедура — «обертки» для вызова расширенной хранимой процедуры.
Пример 3. Системные процедуры обычно используются в качестве «оберток» для расширенных процедур
USE master
IF (0BJECT_ID('dbo.sp_hexstring') IS NOT NULL)
DROP PROC dbo.sp_hexstring
GO
CREATE PROC dbo.sp_hexstring @int varchar(10)=NULL, @hexstring > ' varchar(30)=NULL OUT /*
Object: spjiexstring
Description: Return an integer as a hexadecimal string
Usage: sp_hexstring @int=Integer to convert, Phexstring=OUTPUT parm to
receive hex string
Returns: (None)
$Author: Khen $. Email: [email protected] $Revision: 1 $
Example: spjiexstring "23", @myhex OUT Created: 1999-08-02. $Modtime: 1/4/01 8:23p $.
*/
AS
IF (@int IS NULL) OR (@int = '/?') GOTO Help DECLARE @i int. @vb varbinary(30)
SELECT @i=CAST(@int as int). @vb=CAST(@i as varbinary)