ГлавнаяРазноеПроцедура sql

Хранимые процедуры SQL: создание и использование. Процедура sql


Что собой представляют хранимые процедуры SQL? Их практическое назначение и применение

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

Хранимые процедуры зачастую выполняются в клиенте программных приложений. Однако это не все, на что горазды SQL.

Немного подробнее о SQL и их предназначении

Многие ИТ-специалисты склонны считать, что хранимые процедуры схожи с элементами языков программирования самого высочайшего уровня. Не берем во внимание MS SQL.  Это подтверждается практикой, они могут работать с однотипными значениями. И одни, и вторые в процессе выполнения соприкасаемы с  базами данных DML, DDL, в том числе и с функциональными возможностями UDF самого пользователя персональной компьютерной техники.

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

Особую популярность эти программные инструментарии получили в период с 2005 года по 2010, именно тогда пользователи получили возможность познакомиться с новым программным продуктом разработчика Майкрасофт  — SQL Server Management Studio. Это программное обеспечение в разы упростило работу с базами данных, а это в свою очередь обусловило комфортабельность и практичность.

Если с появлением MS SQL Server вызывал восторг и многие программисты только о нем и говорили, то сегодня это вполне привычная для пользователя персональной компьютерной техники программка, которая воспринимается им на уровне Excel.

Хранимые процедуры работают в режиме автоматизма, они обрабатываются установленным сервером без вмешательства пользователя ПК. После обработки информацию можно использовать в любом назначении и при этом ее  изменять, удалять, дополнять… И за все эффективные процессы в системе отвечает всего лишь один DDL-оператор,  на которого и приходится вся нагрузка совершения сложнейших задач в сегменте обработки информационных объектов. Эти процессы совершаются с очень быстрой скоростью, таким образом, сервер не успевает нагружаться, а это и есть той самой предпосылкой моментальной пересылки информации большого размера от пользователя компьютера к серверу и обратно.

Такая технология деятельности с электроннымиданными поддерживается не одним языком программирования. К таковым относятся:

  • PL/SQL – составляющая управления БД Oracle;
  • PSQL – составляющая в системах InterBase и Firebird;
  • Transact-SQL.

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

Производительность

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

Используемый в работе модуль SQL можно изменить путем перепрограммирования в любое комфортное для пользователя время. Запуск хранимых предписаний SQL имеет моментальный характер по сравнению со схожими с ними программными элементами, это удобный, практичный и универсальный элемент.

Обеспечение безупречной безопасности использования электронных данных

Инновационный подход к обработке электронной информации обеспечил повышенную степень безопасности ее использования в процессе работы, хранения и обмена.  Доступ посторонних пользователей к хранимым процедурам SQL закрыт. С определенным форматом данных работает только администратор компьютерной техники, тогда как иные участники в обмене информационного потока не могут информацию перехватить и несанкционированно использовать в своих корыстных целях.

Процесс передачи информационных данных

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

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

  • отправка электронных данных через параметры Output;
  • отправка через оператора выбора;
  • отправка через оператора возврата.

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

Весь процесс обработки электронной информации состоит из определенных этапов.

  • Сперва создается EXEC-хранимая процедура в SQL, после чего она перечисляется в программный отдел БД, где процедура творения продолжается самим оператором, так что последующее выполнение создаваемого инструмента зависит от успешности EXEC процесса, который является хранителем имени основного объекта. Во время созидания SQL появляется ее, так называемое, наименование, а далее происходит произведение точных параметров, которые ему присвоены, но это необязательное условие, они могут быть и не присвоены. Ну, а после этой манипуляции, так называемого тела SQL, выполняются иные действия.

Оно может состоять из локальных переменных, которые могут быть локальными и для самой SQL. Если более конкретно рассматривать этот аспект локализации, то получается, что их можно анализировать внутри механизма тела заданной процедуры Microsoft SQL Server.  Следовательно, рассматриваемые инструменты  являются обоюдно локальными.  Чтобы создать локальную процедуру нужно знать как ее имя, так и несколько параметров ее тела.

Идеальный поворот – создание и дальнейшее выполнение процедур с именем схемы воспроизведения в классификаторе. Само тело инструмента может принимать любую огранку, к примеру – табличную, строчную, типичную БД или характерные особенности той же БД.  Однако при этом возникают все же определенные ограничения в количестве создаваемых хранимых процедур, ложном  представлении о создаваемом объекте, невозможности создания триггеров.

  • После создания инструмента переходят к внедрению переменной константы непосредственно в тело созданного объекта. Можно сделать переменные локальными, при этом они будут частью SQL, можно также расположить их в начале тела SQL, либо в любом ином месте в теле самой SQL.

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

Если одна строка содержит  несколько таковых констант, то набор личных данных выполняется единоразово.  Можно также в одном операторе в теле процедуры назначить несколько значений.  Делается это при помощи командных пар, таких, как Select Var = значение, которые разделяются между собой знаком препинания – запятой.

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

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

В выше описанном примере процедура приняла два параметра @State и @City и внутренние переменные @TotalAuthors. А далее происходит автоматический поиск этих авторов и их подсчет, оператор печати, в конце концов, выводит подсчитанное число на экран.

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

Второй метод заключается в применении уже заданного автором порядка, он называется передачей именованных аргументов. Типичная методика может на практике и не срабатывать.  Заданные параметры могут сдвигаться либо принимать значение по умолчанию. В таком случае процедуры SQL воспринимаются как простые параметры. А это означает, что при сдвиге параметров происходит замена параметра UT на СА. Чтобы предотвратить сбой параметров и отнесение их функционала к формату автоматической установки, ИТ-специалисты рекомендуют  размещать  переменные константы ближе к концу создаваемого перечня, а иначе их исполнение является неэффективным и делает работу продолжительной и сложной.

  • СозданнаяSQL работает с возвратом значений, с выходом параметров, выбором одной из нескольких процедур. Предположение возврата значений заключается в присвоении SQL значения  локальной переменной и обеспечении его возвращения.  Следует иметь в виду, что возвращаться может не только переменная, но и постоянная константа. Выполнение SQL и вывода значения возвращаемой константы требует определения переменной и печати, при этом можно использовать как Select @RetValue, так и OutputValue.

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

К примеру, можно задать несколько выходным имен — @TotalNoContract и @TotalAuthors, их преждевременно следует указать в списке параметров. Внутри тела процедуры они присваивают значения. Таким образом, при использовании выходных параметров абонент, делающий вызов, видит значения, которые установлены внутри тела самой процедуры.

Два входных имени задаются с целью увидеть те значения, которые непосредственно и устанавливают хранимые процедуры SQL. Если задано два имени, процедура срабатывает с параметром СА, следовательно, последующие параметры будут являться выходными и передаваться в виде переменных в определенном порядке. Выходное ключевое  слово задается вместе с выходными параметрами. Окончание процедуры выхода означает возвращение выходных параметров и вывод их на окно сообщений.

Выбор одной из нескольких процедур SQL применима для возвращения имеющегося набора определенных значений в виде таблиц с данными к процедуре, которая и выполняет вызов.  К примеру, процедура SQL с выбранными параметрами @AuthID  делает запрос таблицы Авторы, делает это методом фильтрования возвращаемых записей с помощью параметра @AuthId. При этом оператор, именуемый Select, принимает решение, какая информация должна быть возвращена хранимой информации, которая дала запрос на возврат. При этом происходит возвращение либо только одной записи данных, либо ни какой.  Но при этом никакими ограничениями хранимая процедура не наделена. Бывают ситуации, когда на один запрос возврата могут быть возвращены несколько  значений, и все они будут считаться итоговыми правильными.

Подведем итоги

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

Хранимые процедуры выполняются без вмешательства пользователя в процесс. Между сервером и клиентским приложением происходит автоматическая связь, позволяющая избежать передачи информации в больших объемах.  За счет этого происходит снижение нагрузки на сервер SQL, а это выгодно в первую очередь их владельцам.

Если речь идет о внушительных объемах информации, то здесь применимы хранимые процедуры типа T SQL.  Описанные в этой статье свойства хранимых процедур показывают, что  это не простая тема, которая требует подробного изучения и вникания. Она интересна в первую очередь для тех, кто задумал вплотную подойти к основам программирования.

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

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

computerologia.ru

Хранимые процедуры в среде ms sql Server

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

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

В SQL Server имеется несколько типов хранимых процедур.

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

  • Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.

  • Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Триггеры

Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.

Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.

Триггеры – особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.

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

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

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

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

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

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

  • накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=

CREATE TRIGGER имя_триггера

BEFORE | AFTER <триггерное_событие>

ON <имя_таблицы>

[REFERENCING

<список_старых_или_новых_псевдонимов>]

[FOR EACH { ROW | STATEMENT}]

[WHEN(условие_триггера)]

<тело_триггера>

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

Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление "мертвых" блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.

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

studfiles.net

Урок 16. Хранимые процедуры. Просмотр имеющихся процедур и их редактирование.

 Главная → Уроки SQL → Урок 16. Хранимые процедуры. Просмотр имеющихся процедур и их редактирование. Теперь давайте узнаем, как можно посмотреть, какие хранимые процедуры имеются у нас на сервере, и как они выглядят. Для этого познакомимся с двумя операторами:
  • SHOW PROCEDURE STATUS - позволяет просмотреть список имеющихся хранимых процедур. Правда просматривать этот список не очень удобно, т.к. по каждой процедуре выдается информация об имени БД, к которой процедура принадлежит, ее типе, учетной записи, от имени которой была создана процедура, о дате создания и изменения процедуры и т.д. И все-таки, если вам необходимо посмотреть, какие процедуры у вас есть, то стоит воспользоваться этим оператором.
  • SHOW CREATE PROCEDURE имя_процедуры - позволяет получить информацию о конкретной процедуре, в частности просмотреть ее код. Вид для просмотра также не очень удобный, но разобраться можно.
Попробуйте оба оператора в действии, чтобы знать, как это выглядит. А теперь рассмотрим более удобный вариант получения подобной информации. В системной базе данных MySQL есть таблица proc, где и хранится информация о процедурах. Так вот мы может сделать SELECT-запрос к этой таблице. Причем, если мы создадим привычный запрос:

SELECT * FROM mysql.proc//

То получим нечто такое же нечитабельное, как и при использовании операторов SHOW. Поэтому мы будем создавать запросы с условиями. Например, если мы создадим вот такой запрос:

SELECT name FROM mysql.proc//

То получим имена всех процедур всех баз данных, имеющихся на сервере. Нас, например, на данный момент интересуют только процедуры базы данных shop, поэтому изменим запрос:

SELECT name FROM mysql.proc WHERE db='shop'//

Вот теперь мы получили то, что хотели:

Если же мы хотим посмотреть только тело конкретной процедуры (т.е. от begin до end), то мы напишем такой запрос:

SELECT body FROM mysql.proc WHERE name='sum_vendor'//

И увидим вполне читабельный вариант:

Вообще, чтобы извлекать из таблицы proc необходимую вам информацию, надо просто знать, какие столбцы она содержит, а для этого можно воспользоваться знакомым нам с первого урока оператором describe имя_таблицы, в нашем случае describe mysql.proc. Правда, вид у нее тоже не очень читабельный, поэтому приведем здесь названия наиболее востребованных столбцов:

  • db - имя БД, в которую сохранена процедура.
  • name - имя процедуры.
  • param_list - список параметров процедуры.
  • body - тело процедуры.
  • comment - комментарий к хранимой процедуре.
Столбцы db, name и body мы уже использовали. Запрос, извлекающий параметры процедуры sum_vendor составьте самостоятельно. А вот про комментарии к хранимым процедурам мы сейчас поговорим подробнее.

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

Создавать комментарии крайне просто. Для этого сразу после списка параметров, но еще до начала тела хранимой процедуры указываем ключевое слово COMMENT 'здесь комментарий'. Давайте удалим нашу процедуру sum_vendor и создадим новую, с комментарием:

CREATE PROCEDURE sum_vendor(i INT) COMMENT 'Возвращает сумму товара по идентификатору поставщика.' begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

А теперь сделаем запрос к комментарию процедуры:

SELECT comment FROM mysql.proc WHERE name='sum_vendor'//

Вообще-то, чтобы добавить комментарий, вовсе не обязательно было удалять старую процедуру. Можно было отредактировать имеющуюся хранимую процедуру с помощью оператора ALTER PROCEDURE. Давайте посмотрим, как это сделать, на примере процедуры ins_cust из прошлого урока. Эта процедура вводит информацию о новом покупателе в таблицу Покупатели (customers). Давайте добавим комментарий к этой процедуре:

ALTER PROCEDURE ins_cust COMMENT 'Вводит информацию о новом покупателе в таблицу Покупатели.'//

И сделаем запрос к комментарию, чтобы проверить:

SELECT comment FROM mysql.proc WHERE name='ins_cust'//

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

SELECT name, comment FROM mysql.proc WHERE db='shop'//

Ну вот, теперь мы умеем извлекать любую информацию о наших процедурах, что позволит нам ничего не забыть и не запутаться.

Предыдущий урок Вернуться в раздел Следующий урок 

www.site-do.ru

Хранимые процедуры SQL: создание и использование

Компьютеры 23 июля 2016

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

Введение

Многие считают, что они похожи на процедуры различных языков программирования высокого уровня (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название – UDF).

В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность – все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой способ передачи информации набирал популярность в среде программистов. Сегодня же MS SQL Server является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».

При вызове процедуры она моментально обрабатывается самим сервером без лишних процессов и вмешательства пользователя. После этого можно осуществлять любые действия с информацией: удаление, исполнение, изменение. За все это отвечает DDL-оператор, который в одиночку совершает сложнейшие действия по обработке объектов. Причем все это происходит очень быстро, а сервер фактически не нагружается. Такая скорость и производительность позволяют очень быстро передавать большие объемы информации от пользователя на сервер и наоборот.

Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от системы управления базами данных Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact-SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.

Продуктивность

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

Видео по теме

Безопасность

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

Передача данных

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

- передача данных с помощью параметра типа Output;

- передача данных с помощью оператора возврата;

- передача данных с помощью оператора выбора.

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

1. Создание EXEC-хранимой процедуры в SQL

Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC-процесс, который содержит имя самого объекта.

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

Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.

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

Тело процедуры может иметь любой вид из операторов SQL, например, такие как создание таблицы, вставки одного или нескольких строк таблицы, установление типа и характера базы данных и так далее. Тем не менее тело процедуры ограничивает выполнение некоторых операций в нем. Некоторые из важных ограничений перечислены ниже:

- тело не должно создавать какой-либо другой хранимой процедуры;

- тело не должно создать ложное представление об объекте;

- тело не должно создавать никаких триггеров.

2. Установка переменной в тело процедуры

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

Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ​​ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.

Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.

3. Создание хранимой процедуры SQL

В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.

В предыдущем примере процедура принимает два параметра, которые на английском языке условно будут называться @State и @City. Тип данных соответствует типу, определенному в приложении. Тело процедуры имеет внутренние переменные @TotalAuthors (всего авторов), и эта переменная используется для отображения их количества. Далее появляется раздел выбора запроса, который все подсчитывает. Наконец, подсчитанное значение выводится в окне вывода с помощью оператора печати.

Как в SQL выполнить хранимую процедуру

Есть два способа выполнения процедуры. Первый путь показывает, передавая параметры, как разделенный запятыми список выполняется после имени процедуры. Допустим, мы имеем два значения (как в предыдущем примере). Эти значения собираются с помощью переменных параметров процедуры @State и @City. В этом способе передачи параметров важен порядок. Такой метод называется порядковая передача аргументов. Во втором способе параметры уже непосредственно назначены, и в этом случае порядок не важен. Этот второй метод известен как передача именованных аргументов.

Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.

4. Хранимые процедуры SQL Server: способы возврата

Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:

- возврат значения хранимой процедуры;

- выход параметра хранимых процедур;

- выбор одной из хранимых процедур.

4.1 Возврат значений хранимых процедур SQL

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

Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select-оператор, например, Select @RetValue, а также OutputValue.

4.2 Выход параметра хранимых процедур SQL

Ответное значение может быть использовано для возврата одной переменной, что мы и видели в предыдущем примере. Использование параметра Output позволяет процедуре отправить одно или несколько значений переменных для вызывающей стороны. Выходной параметр обозначается как раз-таки этим ключевым словом «Output» при создании процедуры. Если параметр задан в качестве выходного параметра, то объект процедуры должен присвоить ему значение. Хранимые процедуры SQL, примеры которых можно увидеть ниже, в таком случае возвращаются с итоговой информацией.

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

Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.

4.3 Выбор одной из хранимых процедур SQL

Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких-либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.

В заключение

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

Источник: fb.ru

monateka.com