Реклама на портале

Введение в базы данных. Создание хранимых процедур в microsoft sql server

22 ответов

В моем опыте написания в основном приложений WinForms Client/Server это простые выводы, к которым я пришел:

Использовать хранимые процедуры:

  • Для любой сложной работы с данными. Если вы собираетесь делать что-то действительно требующее таблиц курсора или temp, это, как правило, самый быстрый способ сделать это в SQL Server.
  • Если вам нужно заблокировать доступ к данным. Если вы не предоставляете доступ к таблице пользователям (или роли или чему-либо еще), вы можете быть уверены, что единственный способ взаимодействия с данными - через создаваемый вами СП.

Использовать специальные запросы:

  • Для CRUD, когда вам не нужно ограничивать доступ к данным (или делаете это по-другому).
  • Для простых поисков. Создание SP для множества критериев поиска - это боль и сложность в обслуживании. Если вы можете создать достаточно быстрый поисковый запрос, используйте это.

В большинстве моих приложений я использовал как SP, так и ad-hoc sql, хотя я считаю, что я использую SP все меньше и меньше, поскольку они в конечном итоге являются кодом, как С#, только сложнее контролировать, тестировать и поддерживать. Я бы рекомендовал использовать ad-hoc sql, если вы не можете найти конкретную причину.

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

Встраивая запросы в ваше приложение, вы тесно связываете себя с вашей моделью данных.

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

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

Как человек с данными, я бы не стал рассматривать работу с базой данных, к которой обращаются через adhoc-запросы, потому что их трудно эффективно настраивать или управлять. Как я могу узнать, что повлияет на изменение схемы? Кроме того, я не думаю, что пользователям следует предоставлять прямой доступ к таблицам базы данных по соображениям безопасности (и я имею в виду не только атаки SQL-инъекций, но также и потому, что это базовый внутренний элемент управления, который не допускает прямых прав и требует от всех пользователей используйте только procs, предназначенные для приложения, чтобы предотвратить возможное мошенничество. Любая финансовая система, которая позволяет напрямую вставлять, обновлять или удалять права на таблицы, имеет огромный риск для мошенничества. Это плохо.).

Базы данных не являются объектно-ориентированными, а код, который кажется хорошим из объектно-ориентированной точки зрения, может быть крайне плохим с точки зрения базы данных.

Наши разработчики сообщают нам, что они рады, что весь наш доступ к базам данных осуществляется через procs, потому что он значительно ускоряет исправление ошибки, зависящей от данных, а затем просто запускает proc в рабочей среде, а не создает новую ветвь кода и перекомпилировать и перезагрузить в производство. Мы требуем, чтобы все наши процессы были в подрывной деятельности, поэтому контроль источника не является проблемой вообще. Если он не находится в Subversion, он будет периодически удаляться dbas, поэтому нет никакого сопротивления использованию Source Control.

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

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

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

Я хотел бы процитировать Tom Kyte от Oracle здесь... Вот его правило о том, где писать код... хотя и немного несвязанный, но хорошо знаю, я думаю.

В нашем приложении есть слой кода, который предоставляет содержимое запроса (а иногда и вызов хранимой процедуры). Это позволяет нам:

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

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

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

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

Выполнение google для хранимой процедуры vs Dynamic Query покажет достойные аргументы в любом случае и, вероятно, лучше для вас принять ваше собственное решение...

Некоторые вещи, о которых нужно подумать: Кому нужны хранимые процедуры, Anyways?

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

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

Я использую ad-hoc для запросов, которые динамически генерируются на основе пользовательского ввода.

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

Если вы используете ad-hoc-запросы, убедитесь, что они параметризированы

Параметрированный SQL или SPROC... не имеет значения с точки зрения производительности... вы можете запросить оптимизацию одного из них.

Для меня последнее оставшееся преимущество SPROC заключается в том, что я могу исключить много прав на управление правами SQL, только предоставляя свои права на вход для выполнения sprocs... если вы используете Parametized SQL, логин, связанный с вашей строкой подключения, имеет намного больше прав (запись любого вида оператора выбора на одну из таблиц, к которым у них есть доступ, например).

Я по-прежнему предпочитаю параметризованный SQL, хотя...

Аргумент производительности sproc является спорным - 3 верхних RDBM используют кэширование плана запросов и некоторое время. Его документально подтвердили... Или еще 1995 год?

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

Если приложение может начинаться с нуля с помощью ORM (приложения с зеленым полем далеки от нескольких!), это отличный выбор, поскольку модель вашего класса управляет вашей моделью БД и экономит время.

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

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

Мой опыт состоит в том, что 90% запросов и/или хранимых процедур вообще не должны записываться (по крайней мере, вручную).

Доступ к данным должен генерироваться как-то автоматически. Вы можете решить, хотите ли вы статически генерировать процедуры во время компиляции или динамически во время выполнения, но если вы хотите добавить столбец в таблицу (свойство объекта), вы должны изменить только один файл.

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

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

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

Введение

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

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

При вызове процедуры она моментально обрабатывается самим сервером без лишних процессов и вмешательства пользователя. После этого можно осуществлять любые удаление, исполнение, изменение. За все это отвечает 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. Хранимые процедуры в таком случае считаются локальными.

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

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

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

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

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

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

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

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

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

В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, 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, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.

Объявление процедуры

CREATE PROCEDURE [({IN|OUT|INOUT } [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC ]

END

Ключевые слова
. IN (Input) – входной параметр
. OUT (Output) – выходной параметр
. INOUT – входной и выходной, а также поле (без параметров)
. DYNAMIC RESULT SET показывает, что процедура может открыть указанное число курсоров, которые останутся открытыми после возврата из процедуры

Примечания
Не рекомендуется использовать много параметров в хранимых процедурах (в первую очередь больших чисел и символьных строк) из-за перегрузки сети и стека. На практике в существующих диалектах Transact-SQL, PL/SQL и Informix наблюдается существенное отличие от стандарта, как в объявлении и использовании параметров, объявлении переменных, так и в вызове подпрограмм. Microsoft рекомендует применять следующую аппроксимацию для оценки размера КЭШа хранимых процедур:
=(максимальное количество одновременно работающих пользователей)*(размер самого большого плана выполнения)*1.25. Определение размера плана выполнения в страницах можно сделать с помощью команды: DBCC MEMUSAGE.

Вызов процедуры

Во многих существующих СУБД вызов хранимых процедур выполняется с помощью оператора:

EXECUTE PROCEDURE [(][)]

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

Пример объявления процедуры

CREATE PROCEDURE Proc1 AS //объявляем процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //объявляем курсор
OPEN Cur1 //открываем курсор
FETCH NEXT FROM Cur1 //считываем данные из курсора
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //закрываем курсор
DEALLOCATE Cur1
EXECUTE Proc1 //запускаем процедуру

Полиморфизм
Две подпрограммы с одним и тем же именем могут быть созданы в одной и той же схеме, если параметры этих двух подпрограмм являются в такое мере отличными друг от друга, чтобы их можно было различать. Для того, чтобы различать две подпрограммы с одним и тем же именем в одной схеме, каждой из них дается альтернативное и уникальное имя (specific name). Такое имя может быть явно указано, когда определяется подпрограмма. При вызове подпрограмм при наличии нескольких одинаковых имен определение нужной подпрограммы осуществляется в несколько шагов:
. Первоначально определяются все процедуры с указанным именем, а если таковых нет, то все функции с заданным именем.
. Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).
. Для них отбираются те, у которых число параметров соответствует числу аргументов вызова. Проверяются указанные типы данных у параметров и их позиции.
. Если осталось более одной подпрограммы, то выбирается та, квалификационное имя которой короче.
На практике в Oracle полиморфизм поддерживается для функций, объявленных только в пакете, DB@ — в разных схема, а в Sybase и MS SQL Server перегрузка запрещена.

Удаление и изменение процедур
Для удаления процедуры используется оператор:

Для изменения процедуры используется оператор:

ALTER PROCEDURE [([{IN|OUT|INOUT }])]
BEGIN [ATOMIC ]

END

Привилегии на выполнение процедур

GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION ]

Системные процедуры
Многие СУБД (включая SQL Server) имеют определенный набор встроенных системных хранимых процедур, которые можно использовать в своих целях.

Когда следует использовать хранимые процедуры и когда я должен использовать представления в SQL Server?

Разрешения позволяют создавать динамические запросы, где мы можем передавать параметры?

Какой из них самый быстрый, и на каком основании он быстрее, чем другой?

Просмотры или хранимые процедуры постоянно сохраняют память?

Что это значит, если кто-то скажет, что представления создают виртуальную таблицу, а процедуры создают таблицу материалов?

Пожалуйста, дайте мне знать о более точках, если они есть.

Solutions Collecting From Web of "В чем разница между хранимой процедурой и представлением?"

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

Хранимая процедура использует параметры для выполнения функции … будь то обновление и вставка данных или возврат отдельных значений или наборов данных.

Создание представлений и хранимых процедур – содержит некоторую информацию от Microsoft о том, когда и почему использовать их.

Скажем, у меня есть две таблицы:

tbl_user Столбцы: .user_id, .user_name, .user_pw

tbl_profile Столбцы: .profile_id, .user_id .profile_description

Поэтому, если я нахожусь в запросе из этих таблиц ALOT … вместо того, чтобы делать соединение в КАЖДОЙ peice sql, я бы определил вид, например:

CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id GO

Поэтому в будущем, если я хочу запросить profile_description по идентификатору пользователя … все, что мне нужно сделать, это

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

Этот код можно использовать в хранимой процедуре, например:

Create procedure dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO

Поэтому позже я могу позвонить

Dbo.getDesc 25

и я получу описание для идентификатора пользователя 25, где 25 – ваш параметр.

Очевидно, что МНОГО больше, но это всего лишь основная идея.

Сначала вам нужно понять, что оба – разные вещи. Хранимые процедуры лучше всего использовать для операторов INSERT-UPDATE-DELETE. и Представления используются для операторов SELECT. и вы должны использовать оба.

В представлениях вы не можете изменять данные.

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

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

Представления могут использоваться в Хранимой процедуре, но Хранимая процедура не может использоваться в Views …!

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

    Представления являются статическими. Подумайте о них как о новых таблицах с определенным макетом, а данные в них создаются «на лету», используя запрос, с которым вы его создали. Как и в любой таблице SQL, вы можете сортировать и фильтровать ее с помощью WHERE , GROUP BY и ORDER BY .

    Это зависит от того, что вы делаете.

    Это зависит от базы данных. Простые представления просто запускают запрос и фильтруют результат. Но такие базы данных, как Oracle, позволяют создать «материализованное» представление, которое в основном представляет собой таблицу, которая автоматически обновляется при изменении базовых данных вида.

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

    Я не понимаю, о чем вы говорите.

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

SQL View – это виртуальная таблица, основанная на запросе SQL SELECT. Представление ссылается на одну или несколько существующих таблиц базы данных или другие представления. Это мгновенный снимок базы данных, тогда как хранимая процедура представляет собой группу операторов Transact-SQL, составленную в единый план выполнения.

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

Представление быстрее, поскольку оно отображает данные из таблиц, на которые ссылается, тогда как процедура хранилища выполняет sql-инструкции.

Проверьте эту статью: Просмотр против хранимых процедур. Именно то, что вы ищете

@Patrick правильно с тем, что он сказал, но, чтобы ответить на ваши другие вопросы, View создаст себя в памяти, и в зависимости от типа Joins, Data и если будет сделано какое-либо агрегирование, это может быть довольно голодный вид.

Хранимые процедуры выполняют всю свою обработку либо с использованием Temp Hash Table, например, # tmpTable1, либо в памяти с помощью @ tmpTable1. В зависимости от того, что вы хотите сказать.

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

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

Махеш не совсем прав, когда он предполагает, что вы не можете изменять данные в представлении. Итак, с точки зрения Патрика

CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id

Я могу обновить данные … в качестве примера я могу сделать любой из этих …

Update vw_user_profile Set profile_description="Manager" where user_id=4

Update tbl_profile Set profile_description="Manager" where user_id=4

Вы не можете ВСТАВИТЬ в это представление, так как не все поля во всей таблице присутствуют, и я предполагаю, что PROFILE_ID является первичным ключом и не может быть NULL. Однако иногда вы можете вставить INSERT в представление …

Я создал представление для существующей таблицы, используя …

Create View Junk as SELECT * from

Insert into junk (Code,name) values ("glyn","Glyn Roberts"), ("Mary","Maryann Roberts")

DELETE from Junk Where ID>4

И INSERT, и DELETE работали в этом случае

Очевидно, вы не можете обновлять какие-либо поля, которые агрегированы или рассчитаны, но любое представление, которое является просто прямым представлением, должно быть обновляемым.

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

В дополнение к приведенным выше комментариям я хотел бы добавить несколько замечаний о Views.

  1. Представления могут использоваться для скрытия сложности. Представьте себе сценарий, в котором 5 человек работают над проектом, но только один из них слишком хорош с базой данных, например сложными объединениями. В таком сценарии он может создавать виды, которые могут быть легко запрошены другими членами команды, поскольку они запрашивают какую-либо одну таблицу.
  2. Безопасность может быть легко реализована Views. Предположим, что мы сотрудник таблицы, который содержит чувствительные столбцы, такие как Зарплата , номер SSN . Эти столбцы не должны отображаться для пользователей, которым не разрешено их просматривать. В этом случае мы можем создать представление, которое будет выбирать столбцы в таблице, не требующие авторизации, такие как имя , возраст и т. Д., Не подвергая уязвимые столбцы (например, о зарплате и т. Д., О которых мы упоминали ранее). Теперь мы можем удалить разрешение для прямого запроса к таблице Employee и просто сохранить разрешение на чтение в представлении. Таким образом, мы можем реализовать безопасность с помощью Views.

Включай в свои процедуры строку - SET NOCOUNT ON:

С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

Transact-SQL

Используй имя схемы с именем объекта:

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

Transact-SQL

SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!

Не используй префикс «sp_» в имени своих хранимых процедур:

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

Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):

Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.