Ms procedure sql stored procedure: CREATE PROCEDURE (Transact-SQL) — SQL Server

хранимых процедур (ядро базы данных) — SQL Server

  • Статья

Применяется к: SQL Server База данных SQL Azure Azure SQL Управляемый экземпляр Azure Synapse Analytics Analytics Platform System (PDW)

Хранимая процедура в SQL Server — это группа из одного или нескольких операторов Transact-SQL или ссылка на Microsoft Метод общего языка выполнения (CLR) .NET Framework. Процедуры напоминают конструкции в других языках программирования, потому что они могут:

  • Принять входные параметры и вернуть вызывающей программе несколько значений в виде выходных параметров.

  • Содержат операторы программирования, выполняющие операции в базе данных. К ним относятся вызов других процедур.

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

Преимущества использования хранимых процедур

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

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

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

Предложение EXECUTE AS можно указать в операторе CREATE PROCEDURE, чтобы разрешить олицетворение другого пользователя или разрешить пользователям или приложениям выполнять определенные действия с базой данных, не требуя прямых разрешений на базовые объекты и команды. Например, некоторые действия, такие как TRUNCATE TABLE, не имеют предоставляемых разрешений. Чтобы выполнить TRUNCATE TABLE, пользователь должен иметь права ALTER для указанной таблицы. Предоставление пользователю разрешений ALTER для таблицы может быть не идеальным решением, поскольку у пользователя фактически будут разрешения, выходящие далеко за рамки возможности усекать таблицу. Включив оператор TRUNCATE TABLE в модуль и указав, что модуль должен выполняться от имени пользователя, имеющего разрешения на изменение таблицы, вы можете расширить права на усечение таблицы для пользователя, которому вы предоставили разрешения EXECUTE для модуля.

При вызове процедуры по сети виден только вызов для выполнения процедуры. Поэтому злоумышленники не могут видеть имена таблиц и объектов базы данных, встраивать собственные операторы Transact-SQL или искать важные данные.

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

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

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

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

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

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

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

Пользовательская
Пользовательская процедура может быть создана в пользовательской базе данных или во всех системных базах данных, кроме Ресурсной базы данных . Процедура может быть разработана либо на Transact-SQL, либо как ссылка на метод общего языка выполнения (CLR) Microsoft .NET Framework.

Временные
Временные процедуры представляют собой форму определяемых пользователем процедур. Временные процедуры аналогичны постоянным процедурам, за исключением того, что временные процедуры хранятся в база данных tempdb . Существует два типа временных процедур: локальные и глобальные. Они отличаются друг от друга своими именами, видимостью и доступностью. Локальные временные процедуры имеют один знак числа (#) в качестве первого символа своего имени; они видны только текущему пользовательскому соединению и удаляются при закрытии соединения. Глобальные временные процедуры имеют два цифровых знака (##) в качестве первых двух символов их имен; они видны любому пользователю после создания и удаляются в конце последней сессии с помощью процедуры.

Система
Системные процедуры входят в состав SQL Server. Они физически хранятся во внутренней скрытой базе данных Resource и логически появляются в схеме sys каждой системной и определяемой пользователем базы данных. Кроме того, база данных msdb также содержит системные хранимые процедуры в схеме dbo , которые используются для планирования предупреждений и заданий. Поскольку системные процедуры начинаются с префикса sp_ , рекомендуется не использовать этот префикс при именовании определяемых пользователем процедур. Полный список системных процедур см. в разделе Системные хранимые процедуры (Transact-SQL) 9.0015

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

Расширенные определяемые пользователем
Расширенные процедуры позволяют создавать внешние подпрограммы на языке программирования, таком как C. Эти процедуры представляют собой библиотеки DLL, которые экземпляр SQL Server может динамически загружать и запускать.

Примечание

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

Описание задачи Тема
Описывает создание хранимой процедуры. Создать хранимую процедуру
Описывает, как изменить хранимую процедуру. Изменить хранимую процедуру
Описывает, как удалить хранимую процедуру. Удалить хранимую процедуру
Описывает выполнение хранимой процедуры. Выполнение хранимой процедуры
Описывает предоставление разрешений хранимой процедуре. Предоставление разрешений на хранимую процедуру
Описывает, как вернуть данные из хранимой процедуры в приложение. Возврат данных из хранимой процедуры
Описывает, как перекомпилировать хранимую процедуру. Повторная компиляция хранимой процедуры
Описывает, как переименовать хранимую процедуру. Переименование хранимой процедуры
Описывает, как просмотреть определение хранимой процедуры. Просмотр определения хранимой процедуры
Описывает, как просмотреть зависимости хранимой процедуры. Просмотр зависимостей хранимой процедуры
Описывает использование параметров в хранимой процедуре. Параметры

Связанное содержимое

Хранимые процедуры CLR
Отложенное разрешение имен

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

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронная почта

  • Статья

Применяется к: SQL Server База данных SQL Azure Azure SQL Управляемый экземпляр Azure Synapse Analytics Analytics Platform System (PDW)

В этой статье описывается, как изменить хранимую процедуру в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

Ограничения и ограничения

Хранимые процедуры Transact-SQL нельзя преобразовать в хранимые процедуры CLR и наоборот.

Если предыдущее определение процедуры было создано с помощью WITH ENCRYPTION или WITH RECOMPILE, эти параметры включаются, только если они включены в инструкцию ALTER PROCEDURE.

Разрешения

Требуется разрешение ALTER PROCEDURE для процедуры.

Используйте SQL Server Management Studio

Чтобы изменить процедуру в SQL Server Management Studio:

  1. В обозревателе объектов подключитесь к экземпляру компонента Database Engine, а затем разверните этот экземпляр.

  2. Разверните Базы данных , разверните базу данных, к которой принадлежит процедура, а затем разверните Программируемость .

  3. Разверните Хранимые процедуры , щелкните правой кнопкой мыши процедуру, которую нужно изменить, и выберите Изменить .

  4. Изменить текст хранимой процедуры.

  5. Чтобы проверить синтаксис, в меню Query выберите Parse .

  6. Чтобы сохранить изменения определения процедуры, в меню Запрос выберите Выполнить .

  7. Чтобы сохранить обновленное определение процедуры как сценарий Transact-SQL, в меню Файл выберите Сохранить как . Примите имя файла или замените его новым именем, а затем выберите Сохранить .

Важно

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

Использование Transact-SQL

Чтобы изменить процедуру с помощью команд T-SQL:

  1. В Object Explorer подключитесь к экземпляру Database Engine, а затем разверните этот экземпляр.

  2. Расширение Базы данных , разверните базу данных, к которой принадлежит процедура. Или на панели инструментов выберите базу данных из списка доступных баз данных. Для этого примера выберите базу данных AdventureWorks2022 .

  3. В меню Файл выберите Новый запрос .

  4. Скопируйте и вставьте следующий пример в редактор запросов. В примере создается процедура uspVendorAllInfo , которая возвращает имена всех поставщиков в базе данных Adventure Works Cycles, продукты, которые они поставляют, их кредитный рейтинг и их доступность.

    , ЕСЛИ OBJECT_ID ('Purchasing.uspVendorAllInfo', 'P') НЕ НУЛЬ
        ПРОЦЕДУРА УДАЛЕНИЯ Purchasing. uspVendorAllInfo;
    ИДТИ
    СОЗДАЙТЕ ПРОЦЕДУРУ Purchasing.uspVendorAllInfo
    С ВЫПОЛНЕНИЕМ КАК ВЫЗЫВАЮЩИЙ
    КАК
        УСТАНОВИТЬ NOCOUNT;
        ВЫБЕРИТЕ v.Name КАК Поставщик, p.Name КАК «Название продукта»,
          v.CreditRating AS «Рейтинг»,
          Доступность v.ActiveFlag AS
        ОТ Закупки.Поставщик v
        INNER JOIN Purchasing.ProductVendor pv
          ON v.BusinessEntityID = pv.BusinessEntityID
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Производство.Продукт p
          ON pv.ProductID = p.ProductID
        ORDER BY v.Name ASC;
    ИДТИ
     

    Важно

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

  5. В меню Файл выберите Новый запрос .

  6. Скопируйте и вставьте следующий пример в редактор запросов. В примере изменяется процедура uspVendorAllInfo . Предложение EXECUTE AS CALLER удалено, а тело процедуры изменено таким образом, чтобы возвращались только те поставщики, которые поставляют указанный продукт. 9Функции 0293 LEFT и CASE настраивают внешний вид набора результатов.

     ПРОЦЕДУРА ИЗМЕНЕНИЯ Purchasing.uspVendorAllInfo
        @Продукт varchar(25)
    КАК
        УСТАНОВИТЬ NOCOUNT;
        SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Название продукта',
        «Рейтинг» = CASE v.CreditRating
            КОГДА 1 ТОГДА 'Улучшенный'
            КОГДА 2, ТО "Отлично"
            КОГДА 3 ТОГДА «Выше среднего»
            КОГДА 4 ТОГДА 'Среднее'
            КОГДА 5 ТОГДА «Ниже среднего»
            ИНАЧЕ 'Нет рейтинга'
            КОНЕЦ
        , Доступность = CASE v.ActiveFlag
            КОГДА 1 ТОГДА 'Да'
            ИНАЧЕ 'Нет'
            КОНЕЦ
        ОТ Purchasing.Vendor AS v
        INNER JOIN Purchasing.ProductVendor AS pv
          ON v.BusinessEntityID = pv.BusinessEntityID
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Производство. Продукт AS p
          ON pv.ProductID = p.ProductID
        ГДЕ p.Name НРАВИТСЯ @Product
        ORDER BY v.Name ASC;
    ИДТИ
     
  7. Чтобы сохранить изменения определения процедуры, в меню Запрос выберите Выполнить .