T-SQL, Databases, Development

Как вы уже догадались, речь пойдёт о хранимых процедурах(stored procedures). Когда я начинал работу с БД, не совсем понимал их суть и значимость. Но, спустя некоторое время, от MySQL я стал отказываться именно из-за отсутствия хранимок, пока, наконец, они там не появились. С ростом опыта, я пришёл к мысли, что большинстве приложений логику нужно выносить именно на БД, описывая иногда ОЧЕНЬ больше процедуры. Не всегда удаётся предугадать всё при построении БД. Спустя годы, приходится менять что-то в БД, добавляя новый функционал. И в этот момент очень спасают хранимки. Нет необходимости лопатить и перекомпилировать тонны старых кодов(давай-те ка признаемся сами себе на сколько хорошо мы документироум собственный код и как нелегко бывает вникнуть в его логику спустя месяцы, не говоря о годах). Нужно просто переписать некоторые процедуры, а в программу добавить лишь модуль, реализовывающий новый функционал.

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

Но как и у всего прочего, есть и свои минусы. Не всегда хорошо то, что при изменении лишь одной процедуры, все куски кода на неё ссылающиеся, будут ссылаться уже на эту, ИЗМЕНЁННУЮ процедуру. Но это тема другой беседы

Синтаксис T-SQL  (SQL Server)

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]

<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

Аргументы

schema_name

Имя схемы, которой принадлежит процедура.

procedure_name

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

В имена процедур настоятельно не рекомендуется включать префикс sp_. Этим префиксом в SQL Server обозначаются системные хранимые процедуры..

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

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

; number

Необязательное целое число, используемое для группировки процедур с одним и тем же именем. Все сгруппированные процедуры можно удалить, выполнив одну инструкцию DROP PROCEDURE. Например, в приложении orders можно было бы использовать процедуры с именами orderproc;1, orderproc;2 и т. д. Инструкция DROP PROCEDURE orderproc удалила бы все процедуры из этой группы. Если имя содержит идентификаторы с разделителями, номер не должен быть частью идентификатора; следует выделять при помощи подходящего разделителя только procedure_name.

На пронумерованные хранимые процедуры распространяются следующие ограничения:

· В качестве типов данных для таких процедур нельзя использовать тип xml и пользовательские типы данных среды CLR.

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

@ parameter

Параметр процедуры. В инструкции CREATE PROCEDURE можно объявить один или более параметров. При выполнении процедуры значение каждого из объявленных параметров должно быть указано пользователем, если для параметра не определено значение по умолчанию или значение не задано равным другому параметру. Хранимая процедура может иметь не более 2 100 параметров. Если процедура содержит возвращающие табличное значение параметры, а в вызове отсутствует параметр, передается пустая таблица по умолчанию.

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

Параметры не могут быть объявлены, если указан параметр FOR REPLICATION.

[ type_schema_name. ] data_type

Тип данных параметра и схема, к которой он относится. Все типы данных, которые могут использоваться в качестве параметра хранимой процедуры Transact-SQL. Можно использовать определяемый пользователем табличный тип, чтобы объявить возвращающий табличное значение параметр в качестве параметра хранимой процедуры Transact-SQL. Возвращающие табличное значение параметры можно указать только в качестве входных параметров, и они должны сопровождаться ключевым словом READONLY. Тип данных cursor можно использовать только в качестве выходного параметра. При указании типа данных cursor нужно также указать ключевые слова VARYING и OUTPUT. Выходных параметров типа cursor может быть несколько.

Параметры хранимых процедур CLR не могут иметь тип char, varchar, text, ntext, image, cursor, table и определяемый пользователем тип таблицы. Дополнительные сведения о соответствии между типами среды CLR и системными типами данных SQL Server см. в разделе Сопоставление данных о параметрах CLR. Дополнительные сведения о системных типах данных SQL Server и их синтаксисе см. в разделе Типы данных (Transact-SQL).

Если тип параметра является пользовательским типом данных CLR, то необходимо иметь связанное с этим типом разрешение EXECUTE.

Если аргумент type_schema_name не указан, компонент SQL Server Database Engine ссылается на type_name в следующем порядке:

· Системные типы данных SQL Server.

· Схема по умолчанию текущего пользователя в текущей базе данных.

· Схема dbo текущей базы данных.

В случае пронумерованных хранимых процедур типом данных не может быть тип xml или пользовательский тип данных среды CLR.

VARYING

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

default

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

OUTPUT

Показывает, что аргумент процедуры является выходным. Значение этого аргумента можно получить при помощи инструкции EXECUTE. Используйте выходные аргументы для возврата значений коду, вызвавшему процедуру. Аргументы типов text, ntext и image не могут быть выходными, если процедура не является процедурой CLR. Выходным аргументом с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR. Определяемый пользователем табличный тип не может быть указан в качестве выходного параметра хранимой процедуры.

READONLY

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

RECOMPILE

Показывает, что компонент Database Engine не кэширует план выполнения процедуры и что процедура компилируется во время выполнения. Этот аргумент нельзя использовать, если указан аргумент FOR REPLICATION. Задать аргумент RECOMPILE для хранимой процедуры CLR нельзя.

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

ENCRYPTION

Показывает, что SQL Server выполнит затемнение исходного текста инструкции CREATE PROCEDURE. Результат затемнения не виден непосредственно ни в одном представлении каталога SQL Server. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить затемненный текст. Однако этот текст будет доступен привилегированным пользователям, которые смогут обращаться к системным таблицам либо через порт выделенного административного соединения, либо будут иметь непосредственный доступ к файлам баз данных. Кроме того, пользователи, имеющие право на подключение отладчика к серверному процессу, могут получить дешифрованный текст процедуры из памяти в период выполнения.

Хранимыми процедурами CLR этот аргумент не поддерживается.

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

EXECUTE AS

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

FOR REPLICATION

Указывает, что хранимые процедуры, созданные для репликации, не могут выполняться на подписчике. Хранимая процедура, созданная с аргументом FOR REPLICATION, используется как процедура-фильтр и выполняется только во время репликации. Если указан аргумент FOR REPLICATION, параметры не могут быть объявлены. Указать аргумент FOR REPLICATION для хранимой процедуры CLR нельзя. Аргумент RECOMPILE не учитывается для процедур, созданных с аргументом FOR REPLICATION.

Процедура с аргументом FOR REPLICATION будет иметь в представлении sys.objects и sys.procedures объектный тип RF.

<sql_statement>

Одна или несколько инструкций языка Transact-SQL, которые будут включены в состав процедуры. При этом действуют некоторые ограничения, описанные в разделе «Примечания».

EXTERNAL NAME assembly_name.class_name.method_name

Метод сборки .NET Framework, на который должна ссылаться хранимая процедура CLR. Аргумент class_name должен быть допустимым идентификатором SQL Server и соответствовать существующему в сборке классу. Если имя класса включает названия пространств имен, отделенные точками (.), оно должно быть ограничено при помощи квадратных скобок ([ ]) или двойных кавычек (« «). Указанный метод класса должен быть статическим.

Читайте также:

• Изменения перестроения индексов в SQL Server. (1)
• Выполнение команд T-SQL на удалённом SQL Server (0)
• Быстрый массовый insert данных. (2)
• Ускоряем и оптимизуруем Delete из таблиц (2)
• Пользуемся триггерами(trigger) (0)