alter procedure sql что это
Изменение хранимой процедуры
В этой статье описывается, как изменить хранимую процедуру в SQL Server с помощью SQL Server Management Studio или Transact-SQL.
Ограничения
Хранимые процедуры Transact-SQL нельзя преобразовать в хранимые процедуры CLR, и наоборот.
Если предыдущее определение процедуры было создано с параметрами WITH ENCRYPTION или WITH RECOMPILE, эти параметры будут включены только в случае, если они указаны в инструкции ALTER PROCEDURE.
Permissions
Необходимо разрешение ALTER PROCEDURE на процедуру.
Использование среды SQL Server Management Studio
Чтобы изменить процедуру в SQL Server Management Studio, выполните следующие действия:
В обозревателе объектов подключитесь к экземпляру Компонент Database Engine и разверните его.
Последовательно разверните узел Базы данных, базу данных, которой принадлежит процедура, и узел Программирование.
Разверните Хранимые процедуры, щелкните правой кнопкой мыши изменяемую процедуру, затем выберите Изменить.
Изменение текста хранимой процедуры.
Для проверки синтаксиса выберите пункт Выполнить анализ в меню Запрос.
Чтобы сохранить изменения определения процедуры, выберите пункт Выполнить в меню Запрос.
Чтобы сохранить обновленное определение процедуры в качестве скрипта Transact-SQL, выберите пункт Сохранить как в меню Файл. Примите предложенное имя файла или замените его новым, затем нажмите кнопку Сохранить.
Проверяйте все данные, вводимые пользователем. Не включайте их в сценарий, не выполнив проверку. Никогда не выполняйте команду, построенную на основании непроверенных пользовательских входных данных. Дополнительные сведения см. в разделе Внедрение кода SQL.
Использование Transact-SQL
Чтобы изменить процедуру с помощью команд T-SQL, выполните следующие действия:
В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine и разверните его.
В меню Файл выберите Создать запрос.
При удалении и повторном создании хранимой процедуры происходит удаление разрешений, которые были явно предоставлены хранимой процедуре. Вместо этого используйте инструкцию ALTER.
В меню Файл выберите Создать запрос.
Чтобы сохранить изменения определения процедуры, выберите пункт Выполнить в меню Запрос.
Чтобы сохранить обновленное определение процедуры в качестве скрипта Transact-SQL, выберите пункт Сохранить как в меню Файл. Примите предложенное имя файла или замените его новым, затем нажмите кнопку Сохранить.
Чтобы выполнить измененную хранимую процедуру, выполните следующий пример.
ALTER PROCEDURE (Transact-SQL)
Modifies a previously created procedure that was created by executing the CREATE PROCEDURE statement in SQL Server.
Transact-SQL Syntax Conventions (Transact-SQL)
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
schema_name
The name of the schema to which the procedure belongs.
procedure_name
The name of the procedure to change. Procedure names must comply with the rules for identifiers.
; number
An existing optional integer that is used to group procedures of the same name so that they can be dropped together by using one DROP PROCEDURE statement.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
@ parameter
A parameter in the procedure. Up to 2,100 parameters can be specified.
[ type_schema_name. ] data_type
Is the data type of the parameter and the schema it belongs to.
For information about data type restrictions, see CREATE PROCEDURE (Transact-SQL).
VARYING
Specifies the result set supported as an output parameter. This parameter is constructed dynamically by the stored procedure and its contents can vary. Applies only to cursor parameters. This option is not valid for CLR procedures.
default
Is a default value for the parameter.
OUT | OUTPUT
Indicates that the parameter is a return parameter.
READONLY
Indicates that the parameter cannot be updated or modified within the body of the procedure. If the parameter type is a table-value type, READONLY must be specified.
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure and the procedure is recompiled at run time.
ENCRYPTION
Applies to: SQL Server ( SQL Server 2008 and later) and Azure SQL Database.
Indicates that the Database Engine will convert the original text of the ALTER PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.
Procedures created with this option cannot be published as part of SQL Server replication.
This option cannot be specified for common language runtime (CLR) stored procedures.
During an upgrade, the Database Engine uses the obfuscated comments stored in sys.sql_modules to re-create procedures.
EXECUTE AS
Specifies the security context under which to execute the stored procedure after it is accessed.
Specifies that stored procedures that are created for replication cannot be executed on the Subscriber. A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and only executed during replication. Parameters cannot be declared if FOR REPLICATION is specified. This option is not valid for CLR procedures. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.
This option is not available in a contained database.
EXTERNAL NAME assembly_name.class_name.method_name
Applies to: SQL Server 2008 and later.
By default, SQL Server cannot execute CLR code. You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL Server until you enable the clr enabled option. To enable the option, use sp_configure.
CLR procedures are not supported in a contained database.
General Remarks
Transact-SQL stored procedures cannot be modified to be CLR stored procedures and vice versa.
ALTER PROCEDURE does not change permissions and does not affect any dependent stored procedures or triggers. However, the current session settings for QUOTED_IDENTIFIER and ANSI_NULLS are included in the stored procedure when it is modified. If the settings are different from those in effect when stored procedure was originally created, the behavior of the stored procedure may change.
If a previous procedure definition was created using WITH ENCRYPTION or WITH RECOMPILE, these options are enabled only if they are included in ALTER PROCEDURE.
For more information about stored procedures, see CREATE PROCEDURE (Transact-SQL).
Security
Permissions
Requires ALTER permission on the procedure or requires membership in the db_ddladmin fixed database role.
Examples
The following example creates the uspVendorAllInfo stored procedure. This procedure returns the names of all the vendors that supply Adventure Works Cycles, the products they supply, their credit ratings, and their availability. After this procedure is created, it is then modified to return a different result set.
The following example alters the uspVendorAllInfo stored procedure. It removes the EXECUTE AS CALLER clause and modifies the body of the procedure to return only those vendors that supply the specified product. The LEFT and CASE functions customize the appearance of the result set.
Хранимые процедуры
— это специальный тип пакета инструкций Transact-SQL, созданный, используя язык SQL и процедурные расширения. Основное различие между пакетом и хранимой процедурой состоит в том, что последняя сохраняется в виде объекта базы данных. Иными словами, хранимые процедуры сохраняются на стороне сервера, чтобы улучшить производительность и постоянство выполнения повторяемых задач.
Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.
При создании хранимой процедуры можно определить необязательный список параметров. Таким образом, процедура будет принимать соответствующие аргументы при каждом ее вызове. Хранимые процедуры могут возвращать значение, содержащее определенную пользователем информацию или, в случае ошибки, соответствующее сообщение об ошибке.
Хранимая процедура предварительно компилируется перед тем, как она сохраняется в виде объекта в базе данных. Предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Это свойство хранимых процедур предоставляет важную выгоду, заключающуюся в устранении (почти во всех случаях) повторных компиляций процедуры и получении соответствующего улучшения производительности. Это свойство хранимых процедур также оказывает положительный эффект на объем данных, участвующих в обмене между системой баз данных и приложениями. В частности, для вызова хранимой процедуры объемом в несколько тысяч байтов может потребоваться меньше, чем 50 байт. Когда множественные пользователи выполняют повторяющиеся задачи с применением хранимых процедур, накопительный эффект такой экономии может быть довольно значительным.
Хранимые процедуры можно также использовать для следующих целей:
управления авторизацией доступа;
для создания журнала логов о действиях с таблицами баз данных.
Использование хранимых процедур предоставляет возможность управления безопасностью на уровне, значительно превышающем уровень безопасности, предоставляемый использованием инструкций GRANT и REVOKE, с помощью которых пользователям предоставляются разные привилегии доступа. Это возможно вследствие того, что авторизация на выполнение хранимой процедуры не зависит от авторизации на модифицирование объектов, содержащихся в данной хранимой процедуре, как это описано в следующем разделе.
Хранимые процедуры, которые создают логи операций записи и/или чтения таблиц, предоставляют дополнительную возможность обеспечения безопасности базы данных. Используя такие процедуры, администратор базы данных может отслеживать модификации, вносимые в базу данных пользователями или прикладными программами.
Создание и исполнение хранимых процедур
Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE, которая имеет следующий синтаксис:
Опция OUTPUT указывает, что параметр процедуры является возвращаемым, и с его помощью можно возвратить значение из хранимой процедуры вызывающей процедуре или системе.
Как уже упоминалось ранее, предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Если же по каким-либо причинам хранимую процедуру требуется компилировать при каждом ее вызове, при объявлении процедуры используется опция WITH RECOMPILE. Использование опции WITH RECOMPILE сводит на нет одно из наиболее важных преимуществ хранимых процедур: улучшение производительности благодаря одной компиляции. Поэтому опцию WITH RECOMPILE следует использовать только при частых изменениях используемых хранимой процедурой объектов базы данных.
Предложение EXECUTE AS определяет контекст безопасности, в котором должна исполняться хранимая процедура после ее вызова. Задавая этот контекст, с помощью Database Engine можно управлять выбором учетных записей пользователей для проверки полномочий доступа к объектам, на которые ссылается данная хранимая процедура.
По умолчанию использовать инструкцию CREATE PROCEDURE могут только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присваивать это право другим пользователям с помощью инструкции GRANT CREATE PROCEDURE.
В примере ниже показано создание простой хранимой процедуры для работы с таблицей Project:
Как говорилось ранее, для разделения двух пакетов используется инструкция GO. Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете. Хранимая процедура IncreaseBudget увеличивает бюджеты для всех проектов на определенное число процентов, определяемое посредством параметра @percent. В процедуре также определяется значение числа процентов по умолчанию (5), которое применяется, если во время выполнения процедуры этот аргумент отсутствует.
Хранимые процедуры могут обращаться к несуществующим таблицам. Это свойство позволяет выполнять отладку кода процедуры, не создавая сначала соответствующие таблицы и даже не подключаясь к конечному серверу.
Локальную временную хранимую процедуру может выполнить только создавший ее пользователь и только в течение соединения с базой данных, в которой она была создана. Глобальную временную процедуру могут выполнять все пользователи, но только до тех пор, пока не завершится последнее соединение, в котором она выполняется (обычно это соединение создателя процедуры).
Жизненный цикл хранимой процедуры состоит из двух этапов: ее создания и ее выполнения. Каждая процедура создается один раз, а выполняется многократно. Хранимая процедура выполняется посредством инструкции EXECUTE пользователем, который является владельцем процедуры или обладает правом EXECUTE для доступа к этой процедуре. Инструкция EXECUTE имеет следующий синтаксис:
За исключением параметра return_status, все параметры инструкции EXECUTE имеют такое же логическое значение, как и одноименные параметры инструкции CREATE PROCEDURE. Параметр return_status определяет целочисленную переменную, в которой сохраняется состояние возврата процедуры. Значение параметру можно присвоить, используя или константу (value), или локальную переменную (@variable). Порядок значений именованных параметров не важен, но значения неименованных параметров должны предоставляться в том порядке, в каком они определены в инструкции CREATE PROCEDURE.
Предложение DEFAULT предоставляет значения по умолчанию для параметра процедуры, которое было указано в определении процедуры. Когда процедура ожидает значение для параметра, для которого не было определено значение по умолчанию и отсутствует параметр, либо указано ключевое слово DEFAULT, то происходит ошибка.
Когда инструкция EXECUTE является первой инструкцией пакета, ключевое слово EXECUTE можно опустить. Тем не менее будет надежнее включать это слово в каждый пакет. Использование инструкции EXECUTE показано в примере ниже:
Инструкция EXECUTE в этом примере выполняет хранимую процедуру IncreaseBudget, которая увеличивает бюджет всех проектов на 10%.
В примере ниже показано создание хранимой процедуры для обработки данных в таблицах Employee и Works_on:
Процедура ModifyEmpId в примере иллюстрирует использование хранимых процедур, как часть процесса обеспечения ссылочной целостности (в данном случае между таблицами Employee и Works_on). Подобную хранимую процедуру можно использовать внутри определения триггера, который собственно и обеспечивает ссылочную целостность.
В примере ниже показано использование в хранимой процедуре предложения OUTPUT:
Данную хранимую процедуру можно запустить на выполнение посредством следующих инструкций:
Эта процедура подсчитывает количество проектов, над которыми занят сотрудник с табельным номером @empId, и присваивает полученное значение параметру ©counter. После удаления всех строк для данного табельного номера из таблиц Employee и Works_on вычисленное значение присваивается переменной @quantityDeleteEmployee.
Значение параметра возвращается вызывающей процедуре только в том случае, если указана опция OUTPUT. В примере выше процедура DeleteEmployee передает вызывающей процедуре параметр @counter, следовательно, хранимая процедура возвращает значение системе. Поэтому параметр @counter необходимо указывать как в опции OUTPUT при объявлении процедуры, так и в инструкции EXECUTE при ее вызове.
Предложение WITH RESULTS SETS инструкции EXECUTE
В SQL Server 2012 для инструкции EXECUTE вводится предложение WITH RESULTS SETS, посредством которого при выполнении определенных условий можно изменять форму результирующего набора хранимой процедуры.
Следующие два примера помогут объяснить это предложение. Первый пример является вводным примером, который показывает, как может выглядеть результат, когда опущено предложение WITH RESULTS SETS:
Результат выполнения хранимой процедуры, вызванной таким способом, будет следующим:
Как можно видеть, запуск хранимой процедуры с использованием предложения WITH RESULT SETS в инструкции EXECUTE позволяет изменить наименования и тип данных столбцов результирующего набора, выдаваемого данной процедурой. Таким образом, эта новая функциональность предоставляет большую гибкость в исполнении хранимых процедур и помещении их результатов в новую таблицу.
Изменение структуры хранимых процедур
Компонент Database Engine также поддерживает инструкцию ALTER PROCEDURE для модификации структуры хранимых процедур. Инструкция ALTER PROCEDURE обычно применяется для изменения инструкций Transact-SQL внутри процедуры. Все параметры инструкции ALTER PROCEDURE имеют такое же значение, как и одноименные параметры инструкции CREATE PROCEDURE. Основной целью использования этой инструкции является избежание переопределения существующих прав хранимой процедуры.
Для удаления одной или группы хранимых процедур используется инструкция DROP PROCEDURE. Удалить хранимую процедуру может только ее владелец или члены предопределенных ролей db_owner и sysadmin.
Хранимые процедуры и среда CLR
SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.
Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure, которая запускается на выполнение инструкцией RECONFIGURE. В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:
Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:
Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.
Используя инструкцию CREATE ASSEMBLY, создать соответствующий выполняемый файл.
Сохранить процедуру в виде объекта сервера, используя инструкцию CREATE PROCEDURE.
Выполнить процедуру, используя инструкцию EXECUTE.
На рисунке ниже показана графическая схема ранее изложенных шагов. Далее приводится более подробное описание этого процесса.
Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код. Далее выполните инструкцию CREATE PROCEDURE, чтобы сохранить выполняемый код в виде объекта базы данных. Наконец, запустите процедуру на выполнение, используя уже знакомую нам инструкцию EXECUTE.
В примере ниже показан исходный код хранимой процедуры на языке C#:
В этой процедуре реализуется запрос для подсчета числа строк в таблице Employee. В директивах using в начале программы указываются пространства имен, требуемые для ее выполнения. Применение этих директив позволяет указывать в исходном коде имена классов без явного указания соответствующих пространств имен. Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure, который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection. Чтобы открыть соединение, применяется метод Open() этого экземпляра. А метод CreateCommand() позволяет обращаться к экземпляру класса SqlCommnd, которому передается нужная SQL-команда.
В следующем фрагменте кода:
используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand. Этот метод возвращает скалярное значение, которое преобразовывается в целочисленный тип данных int и присваивается переменной rows.
Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода. Прежде чем выполнять код в этом примере, необходимо узнать расположение скомпилированного dll-файла (обычно находится в папке Debug проекта).
Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:
В параметре assembly_name указывается имя сборки. В необязательном предложении AUTHORIZATION указывается имя роли в качестве владельца этой сборки. В предложении FROM указывается путь, где находится загружаемая сборка.
Предложение WITH PERMISSION_SET является очень важным предложением инструкции CREATE ASSEMBLY и всегда должно указываться. В нем определяется набор прав доступа, предоставляемых коду сборки. Набор прав SAFE является наиболее ограничивающим. Код сборки, имеющий эти права, не может обращаться к внешним системным ресурсам, таким как файлы. Набор прав EXTERNAL_ACCESS позволяет коду сборки обращаться к определенным внешним системным ресурсам, а набор прав UNSAFE предоставляет неограниченный доступ к ресурсам, как внутри, так и вне системы базы данных.
Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.
Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.
В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:
Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME. Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:
Выполнение процедуры CountEmployees показано в примере ниже:
Инструкция PRINT возвращает текущее количество строк в таблице Employee.