SQL-Ex blog
Подобно большинству систем управления реляционными базами данных, MySQL поддерживает использование хранимых процедур, которые могут вызываться по требованию приложениями, управляемыми данными. Каждая хранимая процедура является именованным объектом базы данных, которая содержит процедурный код, состоящий из одного или более операторов SQL. Когда приложение вызывает хранимую процедуру, MySQL выполняет эти операторы и возвращает результаты в приложение.
Процедурный код может содержать широкий ассортимент операторов, включая язык определения данных (DDL) и язык манипуляции данными (DML). Хранимые процедуры также поддерживают использование входных и выходных параметров, делая их исключительно гибким инструментом для инкапсуляции логики операторов.
Хранимые процедуры дают возможность повторно использовать код, когда это необходимо, помогая упростить разработку приложений и уменьшить ошибки в операторах. Разработчикам не придется писать сложные запросы по каждому требованию приложения, и команде QA не потребуется тратить много времени на проверку запросов при тестировании приложений.
Возможность повторно использовать код сокращает также сетевой трафик, поскольку хранимая процедура может вызываться с помощью единственного оператора CALL, и не важно насколько сложен запрос, заложенный в процедуру. Хранимые процедуры могут также обеспечить более высокую степень безопасности, скрывая структуру базы данных и удаляя ad hoc запросы на уровне приложений.
В этой статье я продемонстрирую как создавать и обновлять хранимые процедуры, а также как вызывать их с помощью оператора CALL. Вы узнаете как построить простые и параметризованные процедуры, которые используют входные и выходные параметры. Как и в предыдущих статьях этой серии, я использую редакцию MySQL Community на компьютере с ОС Windows для построения примеров, которые я создавал в MySQL Workbench, графическим интерфейсом пользователя (GUI), идущим вместе с MySQL Community.
Подготовка среды MySQL
Примеры в этой статье используют базу данных travel, которая уже использовалась для предыдущей статьи о представлениях MySQL. Здесь используются те же таблицы и данные для демонстрации работы с хранимыми процедурами. Если вы делали примеры из предыдущей статьи, у вас уже может быть установлена база данных travel на экземпляре MySQL. Если нет, вы можете использовать следующий скрипт для создания базы данных с таблицами:
Таблица airplanes содержит внешний ключ, который ссылается на таблицу manufacturers, поэтому следует создавать таблицы в указанном порядке. После создания таблиц вы можете добавить некоторые примерные данные, чтобы вы могли протестировать вашу хранимую процедуру. Для заполнения таблицы выполните следующий оператор INSERT:
Как и в случае с операторами CREATE TABLE, вы должны выполнять операторы INSERT в указанном порядке, чтобы не нарушалось ограничение внешнего ключа на таблице airplanes.
Создание хранимой процедуры MySQL
Для построения хранимой процедуры в MySQL вы должны использовать оператор CREATE PROCEDURE. Для начала откройте новое окно запроса в Workbench и проверьте, что активна требуемая база данных. (Чтобы активировать базу данных, выполните двойной щелчок на базе данных в навигаторе или выполните оператор USE.) Для этого примера вы будете использовать базу данных travel.
При построении оператора CREATE PROCEDURE вы должны дать имя процедуре и указать код SQL, который вы хотите хранить в базе данных. Код может включать единственный оператор SQL, такой как SELECT или UPDATE, или же это может быть составным оператором. Составной оператор — это оператор, который использует синтаксис BEGIN…END, ограничивающего блок одного или более операторов SQL. Блок может включать разнообразные элементы языка, включая операторы DDL и DML, объявления переменных, вложенные блоки или конструкции управления потоком, такие как циклы и условные операторы.
Большинство хранимых процедур используют составной оператор, даже если они включают только единственный оператор SQL. Например, код в следующем операторе CREATE PROCEDURE включает составной оператор с единственным оператором SELECT:
Пример создает процедуру с именем get_plane_info. Обратите внимание на скобки после имени. Если бы операторы включали входные или выходные параметры, они должны определяться в скобках (это будет обсуждаться ниже). Если вы не включаете параметры, то все равно должны использовать скобки.
Составной оператор определяется синтаксисом BEGIN…END, который заключает единственный оператор SELECT. Сам оператор SELECT соединяет таблицы airplanes и manufacturers, группирует данные по столбцу manufacturer_id в таблице airplanes и вычисляет средние значения wingspan и plane_length для каждого производителя. Оператор также упорядочивает результаты по производителю и выводит для каждого общее число моделей самолетов. (Мы обсудим элементы этого оператора позже в этой серии статей).
Как можно увидеть, создание простой хранимой процедуры весьма несложный процесс. По минимуму вы должны задать имя и тело процедуры. Однако вы без сомнения заметили включение двух операторов DELIMITER, которые окружают определение процедуры.
По умолчанию MySQL использует точку с запятой (;) в качестве разделителя операторов. Это помогает гарантировать, что клиент посылает оператор на сервер целиком, не смешивая его с другими операторами. Однако составной оператор в хранимой процедуре может включать один или более разделителей в добавок к финальному разделителю определения, эти разделители могут вызвать путаницу при передаче оператора CREATE PROCEDURE с клиента на сервер.
Чтобы разрешить эту проблему, MySQL поддерживает использование оператора DELIMITER, который позволяет вам временно изменить разделитель для передачи всего определения процедуры на сервер как единого оператора. В примере выше первый оператор DELIMITER изменяет разделитель на двойной прямой слэш (//), а второй оператор DELIMITER изменяет разделитель обратно на точку с запятой. Временный разделитель затем используется в конце оператора CREATE PROCEDURE (после слова END), но сам оператор SELECT по-прежнему ограничивается разделителем в виде точки с запятой.
Я хочу также отметить, что MySQL Workbench предоставляет инструмент (в форме вкладки) для создания и редактирования хранимых процедур. Этот инструмент похож на тот, который использовался для создания и редактирования представлений. Он предлагает заглушку для построения оператора CREATE PROCEDURE, но предоставляет вам заполнить детали. На рис.1 показана вкладка Stored Procedure, когда она появляется при её первом открытии в Workbench.
Рис.1 Добавление хранимой процедуры с помощью Workbench GUI
Чтобы открыть вкладку Stored Procedure, выберите нужную базу данных в навигаторе, а затем щелкните кнопку создания хранимой процедуры на панели инструментов Workbench. (Кнопка имеет всплывающую подсказку Create a new stored procedure in the active schema in the connected server.) При появлении вкладки Stored Procedure вы можете начинать строить свой оператор. По завершению щелкните Apply. MySQL затем добавит несколько компонент оператора, которые необходимы для создания процедуры. Просмотрите окончательный скрипт, еще раз щелкните Apply, а затем — Finish. Хранимая процедура будет добавлена в соответствующую базу данных.
Вкладка Stored Procedure может оказаться полезной для создания и редактирования хранимой процедуры, поэтому я хочу, чтобы вы о ней знали. Однако я предпочитаю использовать вкладку запроса при работе с хранимой процедурой, поскольку мне кажется, что это проще и экономит время. Поэтому я для примеров в этой статье я буду использовать такой подход.
Проверка созданной новой процедуры
После выполнения оператора CREATE PROCEDURE вы можете проверить, что она была добавлена в базу данных travel, просмотром в навигаторе, как показано на рис.2. (Возможно потребуется обновить навигатор, чтобы увидеть появление новой процедуры.)
Рис.2 Наблюдение хранимой процедуры в навигаторе
Из навигатора вы можете открыть определение процедуры на вкладке Stored Procedure, щелкнув на иконке с изображением гаечного ключа возле имени процедуры. На рис.3 показано определение процедуры в том виде, в котором вы ее создали, с одним отличием. Оно включает предложение DEFINER после ключевого слова CREATE.
Рис.3 Просмотри определения процедуры на вкладке Stored Procedure
Предложение DEFINER указывает, какой аккаунт назначен в качестве создателя процедуры. Поскольку я создал хранимую процедуру под аккаунтом root на моем локальном экземпляре MySQL, это имя добавляется к определению. По умолчанию MySQL использует аккаунт пользователя, который выполнял оператор CREATE PROCEDURE, но вы можете указать отличный аккаунт, если ему предоставлены соответствующие разрешения.
Помимо предложения DEFINER, ваша хранимая процедура должна выглядеть так же, как вы ее создали, за исключением отсутствия операторов DELIMITER или пользовательским разделителем. Однако, если вы обновите определение на вкладке Stored Procedure и щелкните Apply, Workbench добавит эти элементы.
Другим способом проверить создание хранимой процедуры является запрос представления routines в базе данных INFORMATION_SCHEMA:
Оператор включает предложение WHERE, которое ограничивает результаты базой данных travel. Любые процедуры (хранимые процедуры или функции), которые были созданы в базе данных будут возвращаться этим запросом.
Вы можете еще больше ограничить результаты, указав имя процедуры в предложении WHERE, а также какие столбцы следует вернуть. Например, следующий оператор SELECT ограничивает результаты столбцом routine_definition и процедурой get_plane_info в базе данных travel:
Хотя оператор возвращает единственное значение, его все же бывает трудно читать, особенно, если это сложный составной оператор. Для просмотра оператора полностью щелкните правой кнопкой на значении прямо в результатах, а затем — Open Value in Viewer (открыть значение в просмотрщике). Выберите Text, если он еще не выбран. MySQL откроет новое окно, в которое выведет значение, как показано на рис.4.
Рис.4 Проверка тела хранимой процедуры в просмотрщике
Конечно, проверка существования процедуры не скажет вам, что она работает как ожидалось. Поэтому вам следует также выполнить процедуру и посмотреть, какие результаты она вернет (в дополнение к выполнению ее в надлежащем цикле QA). Для этого используйте оператор CALL, который указывает имя процедуры, как показано в следующем примере:
Когда вы вызываете процедуру, MySQL выполняет сохраненный код и возвращает результаты оператора, которые показаны на рис.5.
Рис.5 Просмотр результатов после вызова хранимой процедуры
Как ожидалось, оператор CALL возвращает агрегрованные данные самолетов, группируя их по производителю. Это те же результаты, которые вы получили бы, если выполнили непосредственно оператор SELECT в процедуре. Однако теперь оператор постоянно присутствует в базе данных, устраняя необходимость писать оператор на уровне приложения.
Добавления входного параметра в процедуру
Хранимая процедура get_plane_info из предыдущего раздела продемонстрировала большинство из основных компонентов, которые имеются в хранимых процедурах MySQL. В производственной среде составной оператор будет, скорее всего, более сложным, но этот пример все же обеспечивает большую часть базовых элементов. Тем не менее, одним из наиболее выгодных аспектов хранимой процедуры является ее возможность поддерживать входные и выходные параметры.
В этом разделе я демонстрирую, как добавить входной параметр в определение процедуры. (О выходных параметрах в следующем разделе.) Прежде, чем двинуться дальше, вы должны знать, что не можете просто изменить определение процедуры, как вы могли это сделать с определением таблицы или представления. Вы можете модифицировать характеристики процедуры, но не более. Чтобы внести более существенные обновления, вы должны удалить процедуру, а затем пересоздать её, добавляя любые новые элементы.
Чтобы удалить хранимыю процедуру, вы можете использовать оператор DROP PROCEDURE, как показано в следующем примере:
Предложение IF EXISTS не является обязательным, но оно может помочь избежать необязательных ошибок. После выполнения этого оператора вы сможете убедиться, что процедура была удалена, если опять обратиться к представлению routines в базе данных INFORMATION_SCHEMA:
Теперь оператор должен вернуть пустой результрующий набор, если вы не создавали других хранимых процедур или функций.
- IN. Входной параметр, который передает значение при вызове в код процедуры.
- OUT. Выходной параметр, который передает значение из кода обратно в вызывающее приложение.
- INOUT. Параметр, который может инициализироваться вызываюим приложением, обновляться в процедуре, а затем возвращаться в вызывающее приложение с новым значением.
Определение параметра заключается в круглые скобки и содержит ключевое слово IN, имя параметра тип данных. Я также обновил оператор SELECT, использовав параметр. Он больше не включает предложений GROUP BY и ORDER BY, но включает предложение WHERE, которое сравнивает параметр со столбцом manufacturer. Таким способом вызывающее приложение может указать производителя, на котором основывается запрос.
Оператор CREATE PROCEDURE также включает характеристику COMMENT, которое добавляет комментарий к определению процедуры. Вы можете включить одну или более таких характеристик после определений параметров. Характеристика является одной из нескольких опций, которые могут быть добавлены к определению процедуры. Каждая характеристика влияет на определение процедуры по-разному. Например, эта характеристика добавляет комментарий, но вы можете также использовать характеристики для указания языка процедуры, указать, является ли процедура детерминистической, или определить характер процедуры.
Пр вызове хранимой процедуры, которая принимает входной параметр, вы должны заключить значение параметра в скобки. Если это символьное значение, то нужно заключть его в одинарные кавычки. Например, следующий оператор CALL задает в качестве входного параметра процедуры значение piper:
Когда MySQL выполняет код процедуры, она подставляет значение piper вместо входного параметра in_name, указанного в предложени WHERE. На рис.6 показаны результаты, которые сейчас возвращает хранимая процедура.
Рис.6 Вызов хранимой процедуры с входным параметром
При определении хранимой процедуры вы можете включить несколько параметров IN, разделяя их запятыми. Тогда при вызове процедуры вы задаете значение каждого параметра в скобках, так же разделяя их запятыми. Вы можете также включить параметры OUT или INOUT, наряду с входным параметрами.
Добавление выходных параметров в хранимой процедуре
Давайте теперь посмотрим, как добавить несколько параметров OUT в хранимую процедуру get_plane_info. Выходные параметры обеспечивают механизм возврата одного или более значений обратно в вызывающую программу, а не единственный результирующий набор. В этом примере вы добавите пять выходных параметров, которые будут соответствовать столбцам, указанным в списке SELECT процедуры.
Для добавления параметров вам необходимо опять удалить процедуру, а затем выполнить обновленный оператор CREATE PROCEDURE. Выходные параметры указываются в тех же скобках, что и входные параметры, что показано в следующем скрипте:
Для каждого выходного параметра вы должны указать ключевое слово OUT, имя параметра и тип данных параметра. Кроме того, вы должны добавить предложение INTO после списка SELECT, который возвращает результаты в выходные параметры. Я также удалил алиасы столбцов из списка SELECT, поскольку они больше не нужны.
При вызове хранимой процедуры, которая возвращает выходные параметры, вы можете захватить значения этих параметров, передавая пользовательскую переменную для каждого выходного параметра, которая будет содержать его значение, как показано в следующем операторе CALL:
Оператор CALL указывает beechcraft в качестве значения входного параметра. Затем следует пять пользовательских переменных, которые соответствуют параметрам, указанным в определении хранимой процедуры. Когда оператор CALL выполняется, значения возвращаемых параметров присваиваются переменным.
Конкретный способ, которым вы будете обрабатывать выходные параметры в приложени, будет зависеть от используемого языка программирования. Пока же вы можете проверить, что ваши переменные содержат ожидаемые значения, выполнив оператор SELECT, например, такой:
На рис.7 показан результат, который вернул этот оператор SELECT:
Рис.7 Просмотр значений выходных параметров процедуры для самолетов Beechcraft
На рисунке показаны результаты, когда вы вызываете хранимую процедуру со значением входного параметра beechcraft. Если задать другое значение, например, airbus, ваш оператор SELECT должен вернуть совсем другие результаты, что показано на рис.8.
Рис.8 Просмотр значений выходных параметров процедуры для самолетов airbus
Параметры IN и OUT могут сделать хранимые процедуры значительно более гибкими при поддержке приложений, управляемых данными. Вам могут встретиться ситуации, когда вы захотите использовать параметр INOUT. Например, вы можете создать хранимую процедуру, которая содержит что-то типа счетчика. Вы можете использовать параметр INOUT для установки начального значения счетчика, а затем возвращать новое значение счетчика на основе вывода программы.
Изменение хранимой процедуры в MySQL
MySQL поддерживает оператор ALTER PROCEDURE для обновления характеристик процедуры. Вы не можете использовать этот оператор, чтобы внести любые другие изменения в определение процедуры. Вы ограничены только характеристикам. Например, следующий оператор ALTER PROCEDURE добавляет две характеристики в определение хранимой процедуры, но остальная часть процедуры остается неизменной:
Характеристика READS SQL DATA указывает, что процедура включает операторы, которые читают данные. Этот тип характеристики носит только рекомендательный характер и никак не ограничивает код процедуры. Характеристика SQL SECURITY INVOKER указывает, что процедура должна выполняться в контексте безопасности аккаунта пользователя, который вызывает процедуру, а не под аккаунтом того, кто определял процедуру.
После выполнения оператора ALTER PROCEDURE вы можете проверить, что характеристики были добавлены, просмотром определения процедуры на вкладке Stored Procedure, как показано на рис.9.
Рис.9 Просмотр определения процедуры на вкладке Stored Procedure
Обратите внимание, что оператор CREATE PROCEDURE теперь включает три характеристики: две только что добавленных и исходную характеристику COMMENT, которую вы добавили ранее.
Хранимые процедуры в T-SQL — создание, изменение, удаление
В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.
Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.
Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:
-
; ; ;
- Если Вы хотите освоить язык SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL.
Что такое хранимые процедуры в T-SQL?
Хранимые процедуры – это объекты базы данных, в которых заложен алгоритм в виде набора SQL инструкций. Иными словами, можно сказать, что хранимые процедуры – это программы внутри базы данных. Хранимые процедуры используются для сохранения на сервере повторно используемого кода, например, Вы написали некий алгоритм, последовательный расчет или многошаговую SQL инструкцию, и чтобы каждый раз не выполнять все инструкции, входящие в данный алгоритм, Вы можете оформить его в виде хранимой процедуры. При этом, когда Вы создаете процедуру SQL, сервер компилирует код, а потом, при каждом запуске этой процедуры SQL сервер уже не будет повторно его компилировать.
Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT, например, как функцию уже не получится, т.е. процедуры запускаются отдельно.
В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: INSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).
Хранимая процедура может иметь входные параметры и выходные параметры, она может возвращать табличные данные, может не возвращать ничего, только выполнять заложенные в ней инструкции.
Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL» представлен пример реализации данной возможности в виде хранимой процедуры.
Примеры работы с хранимыми процедурами в Microsoft SQL Server
Исходные данные для примеров
Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express. Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.
Данные есть, теперь давайте переходить к созданию хранимых процедур.
Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE
Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE, после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.
Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT).
В блоке BEGIN…END мы напишем инструкцию добавления данных, а также в завершении процедуры инструкцию SELECT, чтобы хранимая процедура вернула нам табличные данные о товарах в указанной категории с учетом нового, только что добавленного товара. Также в этой хранимой процедуре я добавил обработку входящего параметра, а именно удаление лишних пробелов в начале и в конце текстовой строки с целью исключения ситуаций, когда случайно занесли несколько пробелов.
Вот код данной процедуры (его я также прокомментировал).
Запуск хранимой процедуры на T-SQL – команда EXECUTE
Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам).
Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.
Вот несколько разных, но эквивалентных способов запуска хранимых процедур, в частности нашей тестовой процедуры.
Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE
Внести изменения в алгоритм работы процедуры можно с помощью инструкции ALTER PROCEDURE. Иными словами, для того чтобы изменить уже существующую процедуру, Вам достаточно вместо CREATE PROCEDURE написать ALTER PROCEDURE, а все остальное изменять по необходимости.
Допустим, нам необходимо внести изменения в нашу тестовую процедуру, скажем, параметр @Price, т.е. цену, мы сделаем обязательным, для этого уберём значение по умолчанию, а также представим, что у нас пропала необходимость в получении результирующего набора данных, для этого мы просто уберем инструкцию SELECT из хранимой процедуры.
Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE
В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE.
Например, давайте удалим созданную нами тестовую процедуру.
При удалении хранимых процедур стоит помнить о том, что, если на процедуру будут ссылаться другие процедуры или SQL инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.
Microsoft SQL Server
Хранимые процедуры
В SQL Server процедура представляет собой хранимую программу, в которую вы можете передавать параметры. Он не возвращает значение, как функция. Тем не менее, он может вернуть статус успеха / отказа в процедуру, вызвавшую его.
Синтаксис
- СОЗДАТЬ <ПРОЦЕДУРА | PROC>[имя_схемы.] Имя_процесса
- [@parameter [type_schema_name.] тип данных
- [VARYING] [= по умолчанию] [OUT | ВЫХОД | READONLY]
- , @parameter [type_schema_name.] datatype
- [VARYING] [= по умолчанию] [OUT | ВЫХОД | READONLY]]
- [WITH
] - [ДЛЯ РЕПЛИКАЦИИ]
- КАК
- НАЧАТЬ
- [Declaration_section]
- executable_section
- КОНЕЦ;
Создание и выполнение базовой хранимой процедуры
Использование таблицы « Authors в базе данных библиотеки
Вы можете выполнить процедуру с несколькими различными синтаксисами. Во-первых, вы можете использовать EXECUTE или EXEC
Кроме того, вы можете опустить команду EXEC. Кроме того, вам не нужно указывать, какой параметр вы передаете, когда вы передаете все параметры.
Если вы хотите указать входные параметры в другом порядке, чем то, как они объявлены в процедуре, вы можете указать имя параметра и присвоить значения. Например
нормальный порядок выполнения этой процедуры — сначала указать значение для параметра @ Param1, а затем @ Param2 second. Так что это будет выглядеть примерно так
Но также возможно, что вы можете использовать следующие
в этом случае вы указываете значение для параметра @ param2 first и @ Param1 second. Это означает, что вам не нужно сохранять тот же порядок, что и в процедуре, но вы можете заказать любой заказ по своему усмотрению. но вам нужно указать, к какому параметру вы устанавливаете значение
Доступ к хранимой процедуре из любой базы данных
А также вы можете создать процедуру с префиксом sp_ эти procuedres, как и все системные хранимые процедуры, могут быть выполнены без указания базы данных из-за поведения SQL Server по умолчанию. Когда вы выполняете хранимую процедуру, которая начинается с «sp_», SQL Server сначала ищет процедуру в основной базе данных. Если процедура не найдена в master, она просматривается в активной базе данных. Если у вас есть хранимая процедура, к которой вы хотите получить доступ из всех ваших баз данных, создайте ее в главном и используйте имя, которое включает префикс «sp_».
ЗАПОМНЕННАЯ ПРОЦЕДУРА с параметрами OUT
Хранимые процедуры могут возвращать значения, используя ключевое слово OUTPUT в списке параметров.
Создание хранимой процедуры с одним параметром out
Выполнение хранимой процедуры
Создание хранимой процедуры с несколькими параметрами
Выполнение хранимой процедуры
Сохраненная процедура с If . Else и Insert Into operation
Создать таблицу примеров Employee :
Создает хранимую процедуру, которая проверяет, не являются ли значения, переданные в хранимой процедуре, нулевыми или не пустыми и выполняют операцию вставки в таблице Employee.
Выполнение хранимой процедуры
Динамический SQL в хранимой процедуре
Dynamic SQL позволяет создавать и запускать SQL-запросы во время выполнения. Динамический SQL необходим, когда наши операторы SQL содержат идентификатор, который может меняться в разное время компиляции.
Простой пример динамического SQL:
В приведенном выше sql-запросе мы можем видеть, что мы можем использовать вышеуказанный запрос, определяя значения в @table_name, @col_name, and @col_value во время выполнения. Запрос генерируется во время выполнения и выполняется. Это метод, в котором мы можем создавать целые скрипты в виде строки в переменной и выполнять ее. Мы можем создавать более сложные запросы с использованием динамической концепции SQL и конкатенации. Эта концепция очень эффективна, если вы хотите создать сценарий, который можно использовать в нескольких условиях.
Выполнение хранимой процедуры
Таблица, которую я использовал
Простая петля
Сначала можно получить некоторые данные в таблице temp с именем #systables и #systables число увеличивающихся строк, чтобы мы могли запросить одну запись за раз
Далее мы объявляем некоторые переменные для управления циклом и хранения имени таблицы в этом примере
Теперь мы можем циклично использовать простую. Мы увеличиваем @rn в выражении select но это также может быть отдельный оператор ex set @rn = @rn + 1 который будет зависеть от ваших требований. Мы также используем значение @rn до того, как оно будет увеличено, чтобы выбрать одну запись из #systables . Наконец, мы печатаем имя таблицы.
Хранимые процедуры в SQL server
Использование хранимых процедур позволяет организовать бизнес-логику и логику управления данными на стороне базы данных.
В чем плюсы такого подхода?
Во-первых, SQL Server оптимизирует и компилирует хранимые процедуры, поэтому они выполняются быстро без необходимости повторять эти шаги каждый раз.
Во-вторых, они выполняются на стороне базы данных, а не в коде приложения. Т.е. минимум тратится ресурсов на дополнительные обращения к базе данных.
В-третьих, использование хранимых процедур позволяет сэкономить траффик, так как клиент посылает серверу только запрос, сервер его обрабатывает и возвращает только результат, который обычно значительно меньше, чем полный набор данных.
Создание хранимой процедуры в SQL Server ManagementStudio
Для создания хранимой процедуры требуется выполнить следующие шаги:
выбираем базу данных, переходим на вкладуку «Программирование/Хранимые процедуры»
Создаем хранимую процедуру через контекстное меню:
Видим вот такой код:
Рассмотрим пример создания процедуры. Очищаем все и вставляем в поле следующий код:
После этого нажимаем «Выполнить» (F5) и видим слева нашу хранимую процедуру:
Чтобы запустить на выполнениех хранимую процедуру, достаточно кликнуть по ней правой кнопкой мышки и выбрать пункт «выполнить хранимую процедуру». После чего отобразится вот такое окно, в котором нажимаем «ОК»:
После чего увидим результат выполнения нашей хранимой процедуры:
Вызов нашей хранимой процедуры осуществляется с помощью кода (после чего нажимаем «выполнить» или F5):
Результатом выполнения данного кода будет выбор всех студентов из таблицы Students:
Внутрениие элементы хранимых процедур
Входные параметры хранимой процедуры
У процедуры могут быть различные входные параметры, которые используются в теле процедуры.
Параметры также могут быть выходными — т.е. их значение изменено в процедуре и возвращено в вызывающую сторону.
Использование if
Пример хранимой процедуры с условием if:
В данной процедуре выбираем максимальную сумму премии у студентов, в зависимости от результата выводим нужную строку. Также следует обратить внимание на выражение:
DECLARE применяется д ля определения переменных , после ключевого слова «DECLARE» указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @. В данном примере определяем переменную «@maxAward» с типом данных «money».
В нашем случае результат будет такой:
Циклы в хранимых процедурах SQL Server
Дальше разберем использование циклов. Разберем классический пример: вычисление факториала числа. Используем такой код:
Пояснения к коду: пока переменная @number не будет равна 0, будет продолжаться цикл WHILE. Каждый проход цикла называется итерацией. В каждой итерации будет переустанавливаться значение переменных @factorial и @number.
Результатом выполнения данного кода будет:
Также следует обратить внимание на ключевое слово «PRINT», которое выводит результат нашего кода:
Инструкция OUTPUT
OUTPUT – это инструкция, возвращающая изменившиеся строки в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE.
OUTPUT может помочь в тех случаях, когда нужно проверить или узнать какие именно строки (записи) были добавлены, удалены или изменены, без дополнительных запросов на выборку (SELECT). Данная инструкция позволяет сохранить все изменения в отдельном месте, например, в таблице, благодаря этому мы можем узнать и работать, например, со списком всех идентификаторов, которые были сгенерированы и добавлены в таблицу.
Принцип работы OUTPUT: все изменения, которые производят инструкции INSERT, UPDATE, DELETE и MERGE, фиксируются, условно говоря, во временных таблицах Inserted и Deleted. Они имеют такую же структуру, как и целевая таблица. Для того чтобы посмотреть изменения, нам необходимо в инструкции OUTPUT указать соответствующий префикс и название нужного столбца, примерно так же, как мы это делаем в инструкции SELECT, перечисляя названия столбцов, тем самым мы извлечем данные из этих таблиц.
Преобразование типов данных для переменных
Функция CAST преобразует выражение одного типа к другому и имеет следующую форму:
Пример. Есть такой код:
Преобразуем числовое значение «award».
Результатом данного кода будет:
Также есть функция TRY_CAST для преобразования данных. Функция TRY_CAST пытается преобразовать выражение из одного типа данных в другой тип данных. Если преобразование не удалось, функция вернет NULL. В противном случае вернет преобразованное значение.
Пример, следующий код пытается преобразовать строку «test» к типу float:
Результатом данного кода будет:
Отличие TRY_CAST от CAST заключается в том, что если преобразование не удалось, то TRY_CAST вернет NULL, а CAST вызовет исключение.
Конкатенация строк
Функция CONCAT , которая выполняет конкатенацию, неявно преобразуя типы аргументов к строковому типу данных. Есть следующий код:
Результат данного кода будет следующий:
Данная функция склеивает firstName и lastName в один столбец.
Стандартные функции для работы с датой и временем
Рассмотрим стандартную функцию GETDATE(), которая возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime:
Функция dateadd добавляет к дате некое значение (месяцы, дни, недели, минуты т.д.)
Работа с NULL через ISNULL, NULLIF
В условиях вы можете проверить равно ли какое то выражение через такую конструкцию:
Для замены NULL на какое-то значение используйте функцию ISNULL. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:
Выберем всех студентов из таблицы Students, а у которых значение email NULL, заменим на надпись «неизвестно»:
Результат этого запроса ниже:
Последняя строка поле email было заменено на «неизвестно», т.к. имеет значение NULL.
Рассмотрим другую функцию: NULLIF. Она возвращает нулевое значение, если два указанных выражения равны. Например:
возвращает NULL для первого столбца (4 и 4), потому что два входных значения одинаковы. Второй столбец возвращает первое значение (5), потому что два входных значения различны.