Ћекции.ќрг


ѕоиск:




 атегории:

јстрономи€
Ѕиологи€
√еографи€
ƒругие €зыки
»нтернет
»нформатика
»стори€
 ультура
Ћитература
Ћогика
ћатематика
ћедицина
ћеханика
ќхрана труда
ѕедагогика
ѕолитика
ѕраво
ѕсихологи€
–елиги€
–иторика
—оциологи€
—порт
—троительство
“ехнологи€
“ранспорт
‘изика
‘илософи€
‘инансы
’ими€
Ёкологи€
Ёкономика
Ёлектроника

 

 

 

 


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




 

—оздание хранимой процедуры осуществл€етс€ вызовом команды CREATE PROCEDURE.

 

ѕри этом можно создавать хранимые процедуры, ссылающиес€ на еще не существующие объекты (таблицы, представлени€ и т.д.). ¬о врем€ создани€ хранимой процедуры выполн€етс€ только проверка синтаксиса. —ама хранима€ процедура не компилируетс€ до первого обращени€ к ней. —сылки на все используемые в хранимой процедуре объекты разрешаютс€ только во врем€ компил€ции. “аким образом, ничто не мешает создать синтаксически корректную хранимую процедуру, ссылающуюс€ на несуществующие таблицы, однако если эти таблицы будут отсутствовать во врем€ выполнени€ хранимой процедуры, она завершитс€ с ошибкой.

 

CREATE { PROC | PROCEDURE } procedure_name

[

{ @parameter data_type }

[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]

] [,...n ]

[ WITH [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS ]]

 

AS

[ BEGIN ] <statements> [ END ]

procedure_name

»м€ новой хранимой процедуры. Bм€ хранимой процедуры не может иметь длину более 128 символов.

 

@ parameter

ѕараметр процедуры. »м€ параметра всегда должно начинатьс€ со знака @. ¬ одной инструкции CREATE PROCEDURE можно объ€вить один или более параметров. ѕри выполнении процедуры значение каждого из объ€вленных параметров должно быть указано пользователем, если дл€ параметра не определено значение по умолчанию или значение не задано равным другому параметру. ’ранима€ процедура может иметь не более 2 100 параметров.

 

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

data_type

“ип данных параметра. ¬се типы данных Transact-SQL могут использоватьс€ в качестве параметра хранимой процедуры. “акже можно использовать определ€емый пользователем табличный тип, чтобы объ€вить входной параметр хранимой процедуры Transact-SQL. ѕараметры табличного типа могут быть только входными и должны сопровождатьс€ ключевым словом READONLY. “ип данных cursor можно использовать только в качестве выходного параметра. ѕри указании типа данных cursor нужно также указать ключевые слова VARYING и OUTPUT. ¬ыходных параметров типа cursor может быть несколько.

VARYING

”казывает результирующий набор, поддерживаемый в качестве выходного параметра. Ётот параметр динамически формируетс€ хранимой процедурой, и его содержимое может различатьс€. ѕримен€етс€ только к параметрам типа cursor.

default

«начение по умолчанию дл€ параметра. ≈сли значение default определено, процедуру можно выполнить без указани€ значени€ соответствующего параметра. «начение по умолчанию должно быть константой или может равн€тьс€ NULL. ≈сли в процедуре используетс€ параметр с ключевым словом LIKE, он может включать символы-шаблоны %, _, [] и [^].

OUTPUT

ѕоказывает, что параметр процедуры €вл€етс€ выходным. «начение этого параметра можно получить после вызова хранимой процедуры. ¬ыходные параметры используютс€ дл€ возврата значений коду, вызвавшему процедуру. ќпредел€емый пользователем табличный тип не может быть указан в качестве выходного параметра хранимой процедуры.

READONLY

”казывает, что параметр не может быть обновлен или изменен в тексте процедуры. ≈сли тип параметра €вл€етс€ определ€емым пользователем табличным типом, ключевое слово READONLY должно быть указано.

RECOMPILE

ѕоказывает, что компонент Database Engine не кэширует план выполнени€ процедуры и что процедура компилируетс€ во врем€ выполнени€.

ENCRYPTION

ѕоказывает, что SQL Server выполнит затемнение исходного текста инструкции CREATE PROCEDURE. –езультат затемнени€ не виден непосредственно ни в одном из представлений каталогов SQL Server. ѕользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст. ќднако этот текст будет доступен привилегированным пользовател€м.

EXECUTE AS

ќпредел€ет контекст безопасности, в котором должна быть выполнена хранима€ процедура, т.е. какую учетную запись компонент Database Engine использует при проверке разрешений на объекты, на которые ссылаетс€ хранима€ процедура. Ёто повышает гибкость и безопасность управлени€ разрешени€ми на цепочки владени€ между пользовательскими хранимыми процедурами и объектами, на которые они ссылаютс€.

ѕользовател€м необходимо будет предоставл€ть только разрешени€ на саму хранимую процедуру, без выдачи €вных разрешений на объекты, на которые он ссылаетс€. “олько пользователь, от имени которого выполн€етс€ модуль, должен будет иметь разрешени€ на объекты, к которым этот модуль обращаетс€.

ƒопустимые значени€ EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }.

CALLER

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

SELF

EXECUTE AS SELF эквивалентно EXECUTE AS user_name, где указанный пользователь - это тот, кто создает или измен€ет модуль.

OWNER

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

'user_name'

”казывает, что инструкции, содержащиес€ в хранимой процедуре, выполн€ютс€ в контексте пользовател€, указываемого аргументом user_name. –азрешени€ на объекты, на которые ссылаетс€ хранима€ процедура, провер€ютс€ дл€ пользовател€ user_name.

ѕользователь user_name должен присутствовать в текущей базе данных и не должен относитьс€ к учетной записи группы. ¬ качестве аргумента user_name нельз€ указывать роль, сертификат, ключ или встроенную учетную запись (например, NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService или NT AUTHORITY\LocalSystem).

<statements>

ќдна или несколько инструкций €зыка Transact-SQL, которые будут включены в состав процедуры.

»нструкцию CREATE PROCEDURE нельз€ объедин€ть с другими инструкци€ми Transact-SQL в одном пакете.

ѕо умолчанию параметры могут принимать значени€ NULL. ≈сли параметр, имеющий значение NULL, используетс€ в инструкции CREATE TABLE или ALTER TABLE при обращении к столбцу, не поддерживающему значени€ NULL, то компонент Database Engine возвращает ошибку. „тобы предотвратить передачу значений NULL столбцу, который их не поддерживает, следует реализовать в процедуре соответствующую логику или передать столбцу значение по умолчанию при помощи ключевого слова DEFAULT инструкции CREATE TABLE или ALTER TABLE.

 ажда€ хранима€ процедура имеет возвращаемое значение типа int. ¬озвращаемое значение можно передать вызывающему коду при помощи конструкции RETURN value. ¬озвращаемое значение можно использовать совместно с выходными параметрами, например, дл€ индикации успешности вызова процедуры.

ѕри создании или изменении хранимой процедуры Transact-SQL компонент Database Engine сохран€ет значени€ SET QUOTED_IDENTIFIER и SET ANSI_NULLS. Ёти первоначальные значени€ используютс€ при выполнении хранимой процедуры. “аким образом, пока хранима€ процедура выполн€етс€, любые значени€ SET QUOTED_IDENTIFIER и SET ANSI_NULLS, задаваемые во врем€ клиентского сеанса, не учитываютс€. ƒругие параметры SET, такие как SET ARITHABORT или SET ANSI_PADDINGS, при создании или изменении хранимой процедуры не сохран€ютс€. ≈сли логика хранимой процедуры зависит от конкретного значени€ параметра, следует включить инструкцию SET в начало процедуры, чтобы гарантировать нужное значение. ≈сли инструкци€ SET выполн€етс€ из хранимой процедуры, устанавливаемое ею значение действует только до завершени€ хранимой процедуры. ѕосле этого оно принимает значение, которое имело место при вызове хранимой процедуры. Ёто позвол€ет клиентам задавать нужные им параметры без вли€ни€ на логику хранимой процедуры.

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

¬ыполнение созданной хранимой процедуры осуществл€етс€ вызовом команды EXEC с указанием хранимой процедуры в качестве аргумента:

EXEC procedure_name

≈сли в хранимой процедуре предусмотрена обработка параметров, можно указать значени€ аргументов.

ѕредоставленное значение должно быть константой или переменной; нельз€ указывать им€ функции в качестве значени€ аргумента. ѕеременные могут быть пользовательскими или системными, например @@spid.

EXEC procedure_name value1, value2...

ѕримеры:

¬ следующих примерах демонстрируетс€ передача значений аргументов хранимой процедуре uspSaleProduct. ѕроцедура ожидает значений дл€ двух входных аргументов: кода продукта и даты.

ѕример 1:

ѕередача констант в качестве значений параметров

EXEC dbo.uspSaleProduct 819, '20050225';

ѕример 2:

ѕередача переменных в качестве значений параметров

DECLARE @ProductID int, @SaleDate datetime;

SET @ProductID = 819;

SET @SaleDate = '20050225';

EXEC dbo.uspSaleProduct @ProductID, @SaleDate;

ѕример 3:

ѕередача имени функции в качестве значений параметров. ƒанный вызов порождает ошибку, поскольку им€ функции нельз€ указывать в качестве значени€ аргумента

EXEC dbo.uspSaleProduct 819, GETDATE();

ѕример 3:

ѕередача результата вызова функции в качестве значени€ параметра, использу€ переменные

DECLARE @SaleDate datetime;

SET @SaleDate = GETDATE();

EXEC dbo.uspSaleProduct 819, @SaleDate;

≈сли необходимо указывать параметры в пор€дке, отличном от определенного в хранимой процедуре, их необходимо именовать, то есть указывать в виде @аргумент = значение. ћожно также опустить аргументы, дл€ которых представлены значени€ по умолчанию. ѕри указании одного аргумента в виде @аргумент = value необходимо предоставить все последующие аргументы тем же способом.

EXEC procedure_name @arg1=value1, @arg2= value2...

ѕри выполнении хранимой процедуры сервер отвергает любые аргументы, не включенные в список аргументов в процессе создани€ процедуры. Ћюбой аргумент, переданный ссылкой (€вно передающей им€ аргумента) не принимаетс€, если им€ аргумента не совпадает.

ѕримеры:

¬ следующих примерах демонстрируетс€ передача значений аргументов хранимой процедуре uspSaleProduct в виде @аргумент = value. ѕроцедура ожидает значений дл€ двух входных аргументов: кода продукта и даты.

ѕример 1:

ѕередача параметров в пор€дке следовани€ в команде CREATE PROCEDURE

EXEC dbo.uspSaleProduct @SaleDate='20050225', @ProductID=819;

ѕример 2:

ѕередача параметров в обратном пор€дке

EXEC dbo.uspSaleProduct @ProductID=819, @SaleDate='20050225';

’от€ разрешаетс€ опустить аргументы, дл€ которых предоставлены значени€ по умолчанию, список аргументов можно лишь подвергнуть усечению (т.е. не передавать значени€ параметров идущих в конце списка параметров и имеющих DEFAULT значение). Ќапример, когда в хранимой процедуре присутствует п€ть аргументов, можно опустить четвертый и п€тый, но нельз€ пропустить четвертый и включить п€тый, если только аргументы не были предоставлены в формате @аргумент = value.

«начение по умолчанию, определенное дл€ аргумента в хранимой процедуре, используетс€, когда:

Ј не указано значение дл€ аргумента при выполнении хранимой процедуры;

Ј в качестве значени€ дл€ аргумента указываетс€ ключевое слово DEFAULT.





ѕоделитьс€ с друзь€ми:


ƒата добавлени€: 2016-11-22; ћы поможем в написании ваших работ!; просмотров: 684 | Ќарушение авторских прав


ѕоиск на сайте:

Ћучшие изречени€:

∆изнь - это то, что с тобой происходит, пока ты строишь планы. © ƒжон Ћеннон
==> читать все изречени€...

652 - | 509 -


© 2015-2024 lektsii.org -  онтакты - ѕоследнее добавление

√ен: 0.019 с.