Sql server хранимые процедуры. Храни́мая процеду́ра

Цель работы – научиться создавать и использовать хранимые процедуры на сервере БД.

1. Проработка всех примеров, анализ результатов их выполнения в утилите SQL Server Management Studio. Проверка наличия созданных про- цедур в текущей БД.

2. Выполнение всех примеров и заданий по ходу лабораторной работы.

3. Выполнение индивидуальных заданий по вариантам.

Пояснения к выполнению работы

Для освоения программирования хранимых процедур используем при- мер базы данных c названием DB_Books , которая была создана в лабора- торной работе №1. При выполнении примеров и заданий обращайте вни- мание на соответствие названий БД, таблиц и других объектов проекта.

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

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

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

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

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

Создание, изменение хранимых процедур

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

Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:

{ CREATE | ALTER } PROC[ EDURE] имя_процедуры [ ;номер] [ { @имя_параметра тип_данных } [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION] AS sql_оператор [ ... n]

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

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

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

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры. Ключевое слово AS размещается в начале собственно тела хранимой процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

Удаление хранимой процедуры

DROP PROCEDURE { имя_процедуры} [ ,... n]

Выполнение хранимой процедуры

Для выполнения хранимой процедуры используется команда: [ [ EXEC [ UTE] имя_процедуры [ ;номер] [ [ @имя_параметра= ] { значение | @имя_переменной} [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.

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

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

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

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

Использование RETURN в хранимой процедуре

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

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Задание 1.

EXEC Count_Books

Проверьте результат.

Пример создания процедуры c входным параметром:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Задание 2 . Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Pages 100

Проверьте результат.

Пример создания процедуры c входными параметрами:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Задание 3. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Title 100 , "П%"

Проверьте результат.

Пример создания процедуры c входными параметрами и выходным параметром:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Задание 4. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите с помощью набора команд:

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "П%", @q output select @q

Проверьте результат.

Пример создания процедуры c входными параметрами и RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Пушкин А.С." RETURN 1 ELSE RETURN 2

Задание 5. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status

Пример создания процедуры без параметров для увеличения значения ключевого поля в таблице Purchases в 2 раза:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Задание 6. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC update_proc

Пример процедуры с входным параметром для получения всей ин- формации о конкретном авторе:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k

Задание 7.

EXEC select_author "Пушкин А.С." или select_author @k= "Пушкин А.С." или EXEC select_author @k= "Пушкин А.С."

Пример создания процедуры с входным параметром и значением по умолчанию для увеличения значения ключевого поля в таблице Purchases в заданное количество раза (по умолчанию в 2 раза):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

Процедура не возвращает никаких данных.

Задание 8. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

EXEC update_proc 4 или EXEC update_proc @p = 4 или EXEC update_proc --будет использовано значение по умолчанию.

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

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )

Задание 9. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @c2 INT EXEC count_purchases ’01- jun- 2006 ’, ’01- jul- 2006 ’, @c2 OUTPUT SELECT @c2

Варианты заданий к лабораторной работе №4

Общие положения. В утилите SQL Server Management Studio создать новую страницу для кода (кнопка «Создать запрос»). Программно сделать активной созданную БД DB_Books с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые были выполнены во второй лабораторной работе. Причем код SQL запросов нужно изменить таким образом, чтобы в них можно было передавать значения полей, по которым осуществляется поиск.

Например, исходное задание и запрос в лабораторной работе №2:

/*Выбрать из справочника поставщиков (таблица Deliveries) названия ком- паний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) „ОАО МИР“.

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "ОАО МИР"

*/ –В данной работе будет создана процедура:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp

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

EXEC select_name_company "ОАО МИР"

Список заданий

В утилите SQL Server Management Studio создать новую программу. Программно сделать активной индивидуальную БД, созданную в лабораторной работе №1, с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.

Вариант 1

1. Вывести список сотрудников, у которых есть хотя бы один ребенок.

2. Вывести список детей, которым выдали подарки в указанный период.

3. Вывести список родителей, у которых есть несовершеннолетние дети.

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

Вариант 2

1. Вывести список приборов с указанным типом.

2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.

3. Вывести список владельцев приборов и количество их обращений, отсортированный по количеству обращений по убыванию.

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

Вариант 3

2. Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.

3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.

4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.

Вариант 4

1. Вывести список лекарств с указанным показанием к применению.

2. Вывести список дат поставок, по которым продано больше указанного числа одноименного лекарства.

3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и название лекарства по коду поступления больше указанного числа.

Вариант 5

2. Вывести список списанного оборудования по указанной причине.

3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный период.

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

Вариант 6

1. Вывести список блюд с весом больше указанного числа.

2. Вывести список продуктов, в названии которых встречается указанный фрагмент слова.

3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному конечному значению.

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

Вариант 7

1. Вывести список сотрудников с указанной должностью.

3. Вывести дату регистрации, тип документа, ФИО регистратора и название организации для документов, зарегистрированных в указанный период.

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

Вариант 8

1. Вывести список сотрудников с указанной причиной увольнения.

3. Вывести дату регистрации, причину увольнения, ФИО сотрудника для документов, зарегистрированных в указанный период.

Вариант 9

1. Вывести список сотрудников, бравших отпуск указанного типа.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, тип отпуска, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 10

1. Вывести список сотрудников с указанной должностью.

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

3. Вывести дату регистрации, тип документа, ФИО отправителя и название организации для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа меньше определенного значения.

Вариант 11

1. Вывести список сотрудников, назначенных на указанную должность.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, должность, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 12

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированный по количеству обращений по убыванию.

Вариант 13

1. Вывести список оборудования с указанным типом. 2. Вывести список оборудования, которое списал определенный сотрудник.

3. Вывести количество списанного оборудования, сгруппированного по типам оборудования.

4. Вывести информацию о сотрудниках с датой приема на работу больше определенной даты.

Вариант 14

1. Вывести список цветков с указанным типом листа.

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

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

4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.

Вариант 15

1. Вывести список клиентов, заехавших в номера в указанный период.

2. Вывести общую сумму оплат за номера для каждого клиента.

3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных клиентов в номерах определенного типа.

Вариант 16

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированных по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 17

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

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

3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.

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

Вариант 18

1. Вывести список ремонтных работ, выполненных определенным мастером.

2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.

3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.

4. Вывести список мастеров с датой приема на работу в указанном диапазоне.

Вариант 19

1. Вывести список лекарств с определенным показанием.

2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.

3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с указанным номером.

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

Вариант 20

1. Вывести список сотрудников с указанной должностью.

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

3. Вывести дату регистрации, тип документа, ФИО исполнителя и факт исполнения для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа в определенном диапазоне.

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, которое выводится из некоторой нетривиальной логики. Для этого поколения я создаю хранимую процедуру, которая затем запускается триггером всякий раз, когда обновляется строка (или другие исходные данные).

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

В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.

В первой статье данного цикла, опубликованной в № 3’2000 нашего журнала, мы отмечали, что большинство современных серверных СУБД поддерживают представления, триггеры и хранимые процедуры. Представления также поддерживаются и многими настольными СУБД, например Access, dBase, Clipper.

Следует отметить, что триггеры и хранимые процедуры обычно пишутся на языках программирования, представляющих собой процедурные расширения языка SQL. Эти расширения содержат операторы, позволяющие описывать алгоритмы, например do…while, if…then…else, отсутствующие в самом языке SQL (если вы помните, SQL - непроцедурный язык, и на нем можно сформулировать задание, но нельзя описывать алгоритмы его выполнения). В отличие от языка SQL, подчиняющегося стандарту, его процедурные расширения никак не стандартизованы, и разные СУБД используют разные синтаксические конструкции для реализации одних и тех же алгоритмических конструкций, но обсуждение различий в синтаксисе расширений SQL для разных СУБД выходит за рамки данной статьи.

Для иллюстрации того, как можно использовать представления, триггеры и хранимые процедуры, мы выбрали Microsoft SQL Server 7.0 и базу данных NorthWind, входящую в комплект поставки этой СУБД.

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

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

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

Представления

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

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

Ниже перечислены основные характеристики представлений:

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

Для создания представления мы можем использовать SQL-предложение CREATE VIEW, для его модификации - предложение ALTER VIEW, а для удаления его - предложение DROP VIEW.

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

Предложение CREATE VIEW

Синтаксис предложения для создания представления напоминает SQL-предложение SELECT с несколькими дополнительными ключевыми словами. Ниже приведен его упрощенный синтаксис:

CREATE VIEW view_name AS select_statement

Аргумент view_name указывает на имя представления. Ключевое слово , используемое в Microsoft SQL Server, позволяет скрыть исходный текст предложения CREATE VIEW в таблице syscomments.

Ключевое слово AS указывает, какой запрос SELECT реально будет выполняться при обращении к представлению. Обратите внимание на то, что этот запрос не может содержать ключевых слов ORDER BY, COMPUTE или COMPUTE BY, INTO и не может ссылаться на временную таблицу.

Для модификации созданного ранее представления следует использовать предложение ALTER VIEW, кратко описанное в следующем разделе.

Предложение DROP VIEW

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

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

Создание и использование представлений

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

Наилучший способ создать представление - создать запрос SELECT и, проверив его, добавить недостающую часть предложения CREATE VIEW. Давайте рассмотрим исходный текст представления Products by Category в базе данных NorthWind (листинг 1).

Первая строка, выделенная жирным шрифтом, представляет собой то, чем отличается SQL-предложение для создания представления от обычного запроса SELECT, выполняющего работу по выбору данных. Предложение SELECT, содержащееся в этом представлении, выбирает поля из двух таблиц - поле CategoryName из таблицы CATEGORIES и поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued из таблицы PRODUCTS. После этого данные двух таблиц связываются по полю CategoryID, и только те продукты, которые еще имеются на складе (см. критерий после ключевого слова WHERE), включаются в результирующий набор данных. Результат обращения к этому представлению показан на рис. 1 .

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

Убедившись в том, что предложение SELECT возвращает результаты, которые нам нужны, мы добавляем оператор CREATE VIEW и присваиваем создаваемому представлению имя EASTTERR (листинг 3).

Вместо создания текста представления вручную можно использовать визуальные инструменты, обычно входящие в состав СУБД. На рис. 2 показано, как то же самое представление может быть создано с помощью инструмента View Designer, который является составной частью Enterprise Manager, входящего в Microsoft SQL Server.

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

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

• для получения сведений о представлении можно использовать системную хранимую процедуру sp_help. Например, sp_help EastTerr вернет сведения о только что созданном представлении;
• для получения исходного текста представления можно использовать хранимую процедуру sp_helptext;
• для того чтобы найти список таблиц, от которого зависит представление, можно использовать системную хранимую процедуру sp_depends;
• для переименования представления можно использовать системную хранимую процедуру sp_rename.

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

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

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

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

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

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

Для создания, изменения и удаления процедур существуют специальные SQL-предложения - CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Мы рассмотрим их в следующем разделе.

Предложение CREATE PROCEDURE

Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:

CREATE PROC proc_name [ {@parameter data_type} [= default] ] [...] AS sql_statements

Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.

Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.

Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию - это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, и [^]).

Ключевое слово OUTPUT показывает, что это возвращаемый параметр.

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

Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст - в таблице syscomments.

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

Предложение DROP PROCEDURE

Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент - имя удаляемой процедуры.

При удалении хранимой процедуры сведения о ней удаляются из системных таблиц sysobjects и syscomments.

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

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

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1

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

В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:

CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Обратите внимание на то, что мы оставили почти весь текст запроса SELECT нетронутым (он выделен курсивом) и только добавили предложение CREATE PROCEDURE с именем вновь созданной хранимой процедуры (в первой строке), объявление параметра (во второй строке) и ключевое слово AS, указывающее на начало предложений, реально выполняющих действия.

Результат выполнения созданной процедуры в SQL Server Query Analyzer для RegID =2 показан на рис. 3 .

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

В Microsoft SQL Server 7.0 мы также можем использовать системные хранимые процедуры для работы с обычными хранимыми процедурами:

• sp_stored_procedures - показывает список хранимых процедур;
• sp_helptext - показывает исходный текст хранимой процедуры;
• sp_depends - показывает сведения о зависимостях хранимых процедур;
• sp_procoption - устанавливает опции хранимых процедур или устанавливает их;
• sp_recompile - перекомпилирует процедуру в момент ее следующего вызова;
• sp_rename - меняет имя процедуры.

Системные хранимые процедуры

Поскольку мы говорим о Microsoft SQL Server, следует отметить огромное количество системных хранимых процедур, реализованных в нем. Имена системных хранимых процедур начинаются с SP_ или XP_ и хранятся в базе данных master. Выше мы уже описывали некоторые из часто используемых системных хранимых процедур.

Обратите внимание на то, что триггеры не должны возвращать пользователю данные.

В предложении CREATE TRIGGER можно использовать две специальные таблицы. Например, таблицы deleted и inserted имеют ту же структуру, что и таблица, для которой определен триггер, и содержат старое и новое значения записей, измененных пользователем. Например, мы можем использовать следующее SQL-предложение для поиска удаленных записей:

SELECT * FROM deleted

В табл. 3 показано содержимое таблиц deleted и inserted для всех возможных изменений данных.

Для изменения имеющегося триггера следует использовать предложение ALTER TRIGGER. Мы поговорим о нем в следующем разделе.

Для начала нам нужно добавить к таблице два новых поля, в которых будут содержаться эти сведения. Назовем их UpdatedBy (имя менеджера, обновившего запись последним) и UpdatedWhen (время, когда была изменена запись). Затем создадим триггер с именем KeepTrack. Вот его код:

CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID

Как видно из исходного текста триггера, он выполняется после каждой операции INSERT и UPDATE в таблице Customers. Этот триггер будет сохранять имя менеджера (пользователя базы данных) в поле Customers.UpdatedBy и дату и время изменения - в поле Customers.UpdatedWhen. Эти данные извлекаются из временной таблицы inserted.

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

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

Заключение

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

• Представление - это виртуальная таблица, обычно создаваемая как подмножество столбцов одной или нескольких таблиц. Для создания представления применяется предложение CREATE VIEW, для модификации - предложение ALTER VIEW, а для удаления - предложение DROP VIEW.
• Хранимая процедура - это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения - ALTER PROCEDURE, а для удаления - DROP PROCEDURE.
• Tриггер - это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления - предложение DROP TRIGGER.

КомпьютерПресс 12"2000

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

Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.

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

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

Хранимые процедуры можно также использовать для следующих целей:

для создания журнала логов о действиях с таблицами баз данных.

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

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

Создание и исполнение хранимых процедур

Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE , которая имеет следующий синтаксис:

CREATE PROC proc_name [({@param1} type1 [ VARYING] [= default1] )] {, …} AS batch | EXTERNAL NAME method_name Соглашения по синтаксису

Параметр schema_name определяет имя схемы, которая назначается владельцем созданной хранимой процедуры. Параметр proc_name определяет имя хранимой процедуры. Параметр @param1 является параметром процедуры (формальным аргументом), чей тип данных определяется параметром type1. Параметры процедуры являются локальными в пределах процедуры, подобно тому, как локальные переменные являются локальными в пределах пакета. Параметры процедуры - это значения, которые передаются вызывающим объектом процедуре для использования в ней. Параметр default1 определяет значение по умолчанию для соответствующего параметра процедуры. (Значением по умолчанию также может быть NULL.)

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

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

Предложение EXECUTE AS определяет контекст безопасности, в котором должна исполняться хранимая процедура после ее вызова. Задавая этот контекст, с помощью Database Engine можно управлять выбором учетных записей пользователей для проверки полномочий доступа к объектам, на которые ссылается данная хранимая процедура.

По умолчанию использовать инструкцию CREATE PROCEDURE могут только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присваивать это право другим пользователям с помощью инструкции GRANT CREATE PROCEDURE .

В примере ниже показано создание простой хранимой процедуры для работы с таблицей Project:

USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;

Как говорилось ранее, для разделения двух пакетов используется инструкция GO . Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете. Хранимая процедура IncreaseBudget увеличивает бюджеты для всех проектов на определенное число процентов, определяемое посредством параметра @percent. В процедуре также определяется значение числа процентов по умолчанию (5), которое применяется, если во время выполнения процедуры этот аргумент отсутствует.

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

В отличие от основных хранимых процедур, которые всегда сохраняются в текущей базе данных, возможно создание временных хранимых процедур, которые всегда помещаются во временную системную базу данных tempdb. Одним из поводов для создания временных хранимых процедур может быть желание избежать повторяющегося исполнения определенной группы инструкций при соединении с базой данных. Можно создавать локальные или глобальные временные процедуры. Для этого имя локальной процедуры задается с одинарным символом # (#proc_name), а имя глобальной процедуры - с двойным (##proc_name).

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

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

[] [@return_status =] {proc_name | @proc_name_var} {[[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT}.. Соглашения по синтаксису

За исключением параметра return_status, все параметры инструкции EXECUTE имеют такое же логическое значение, как и одноименные параметры инструкции CREATE PROCEDURE. Параметр return_status определяет целочисленную переменную, в которой сохраняется состояние возврата процедуры. Значение параметру можно присвоить, используя или константу (value), или локальную переменную (@variable). Порядок значений именованных параметров не важен, но значения неименованных параметров должны предоставляться в том порядке, в каком они определены в инструкции CREATE PROCEDURE.

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

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

USE SampleDb; EXECUTE IncreaseBudget 10;

Инструкция EXECUTE в этом примере выполняет хранимую процедуру IncreaseBudget, которая увеличивает бюджет всех проектов на 10%.

В примере ниже показано создание хранимой процедуры для обработки данных в таблицах Employee и Works_on:

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

В примере ниже показано использование в хранимой процедуре предложения OUTPUT:

Данную хранимую процедуру можно запустить на выполнение посредством следующих инструкций:

DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N"Удалено сотрудников: " + convert(nvarchar(30), @quantityDeleteEmployee);

Эта процедура подсчитывает количество проектов, над которыми занят сотрудник с табельным номером @empId, и присваивает полученное значение параметру ©counter. После удаления всех строк для данного табельного номера из таблиц Employee и Works_on вычисленное значение присваивается переменной @quantityDeleteEmployee.

Значение параметра возвращается вызывающей процедуре только в том случае, если указана опция OUTPUT. В примере выше процедура DeleteEmployee передает вызывающей процедуре параметр @counter, следовательно, хранимая процедура возвращает значение системе. Поэтому параметр @counter необходимо указывать как в опции OUTPUT при объявлении процедуры, так и в инструкции EXECUTE при ее вызове.

Предложение WITH RESULTS SETS инструкции EXECUTE

В SQL Server 2012 для инструкции EXECUTE вводится предложение WITH RESULTS SETS , посредством которого при выполнении определенных условий можно изменять форму результирующего набора хранимой процедуры.

Следующие два примера помогут объяснить это предложение. Первый пример является вводным примером, который показывает, как может выглядеть результат, когда опущено предложение WITH RESULTS SETS:

Процедура EmployeesInDept - это простая процедура, которая отображает табельные номера и фамилии всех сотрудников, работающих в определенном отделе. Номер отдела является параметром процедуры, и его нужно указать при ее вызове. Выполнение этой процедуры выводит таблицу с двумя столбцами, заголовки которых совпадают с наименованиями соответствующих столбцов таблицы базы данных, т.е. Id и LastName. Чтобы изменить заголовки столбцов результата (а также их тип данных), в SQL Server 2012 применяется новое предложение WITH RESULTS SETS. Применение этого предложения показано в примере ниже:

USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [Фамилия] CHAR(20) NOT NULL));

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

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

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

Компонент Database Engine также поддерживает инструкцию ALTER PROCEDURE для модификации структуры хранимых процедур. Инструкция ALTER PROCEDURE обычно применяется для изменения инструкций Transact-SQL внутри процедуры. Все параметры инструкции ALTER PROCEDURE имеют такое же значение, как и одноименные параметры инструкции CREATE PROCEDURE. Основной целью использования этой инструкции является избежание переопределения существующих прав хранимой процедуры.

Компонент Database Engine поддерживает тип данных CURSOR . Этот тип данных используется для объявления курсоров в хранимых процедурах. Курсор - это конструкция программирования, применяемая для хранения результатов запроса (обычно набора строк) и для предоставления пользователям возможности отображать этот результат построчно.

Для удаления одной или группы хранимых процедур используется инструкция DROP PROCEDURE . Удалить хранимую процедуру может только ее владелец или члены предопределенных ролей db_owner и sysadmin.

Хранимые процедуры и среда CLR

SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.

Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure , которая запускается на выполнение инструкцией RECONFIGURE . В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:

USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:

Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.

Используя инструкцию CREATE ASSEMBLY , создать соответствующий выполняемый файл.

Выполнить процедуру, используя инструкцию EXECUTE.

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

Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код. Далее выполните инструкцию CREATE PROCEDURE, чтобы сохранить выполняемый код в виде объекта базы данных. Наконец, запустите процедуру на выполнение, используя уже знакомую нам инструкцию EXECUTE.

В примере ниже показан исходный код хранимой процедуры на языке C#:

Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures { public static int CountEmployees() { int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "Количество сотрудников" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; } }

В этой процедуре реализуется запрос для подсчета числа строк в таблице Employee. В директивах using в начале программы указываются пространства имен, требуемые для ее выполнения. Применение этих директив позволяет указывать в исходном коде имена классов без явного указания соответствующих пространств имен. Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure , который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection . Чтобы открыть соединение, применяется метод Open() этого экземпляра. А метод CreateCommand() позволяет обращаться к экземпляру класса SqlCommnd , которому передается нужная SQL-команда.

В следующем фрагменте кода:

Cmd.CommandText = "select count(*) as "Количество сотрудников" " + "from Employee";

используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand. Этот метод возвращает скалярное значение, которое преобразовывается в целочисленный тип данных int и присваивается переменной rows.

Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода. Прежде чем выполнять код в этом примере, необходимо узнать расположение скомпилированного dll-файла (обычно находится в папке Debug проекта).

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM {dll_file} Соглашения по синтаксису

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

Предложение WITH PERMISSION_SET является очень важным предложением инструкции CREATE ASSEMBLY и всегда должно указываться. В нем определяется набор прав доступа, предоставляемых коду сборки. Набор прав SAFE является наиболее ограничивающим. Код сборки, имеющий эти права, не может обращаться к внешним системным ресурсам, таким как файлы. Набор прав EXTERNAL_ACCESS позволяет коду сборки обращаться к определенным внешним системным ресурсам, а набор прав UNSAFE предоставляет неограниченный доступ к ресурсам, как внутри, так и вне системы базы данных.

Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.

Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.

В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:

USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees

Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME . Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:

assembly_name.class_name.method_name

assembly_name - указывает имя сборки;

class_name - указывает имя общего класса;

method_name - необязательная часть, указывает имя метода, который задается внутри класса.

Выполнение процедуры CountEmployees показано в примере ниже:

USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Вернет 7

Инструкция PRINT возвращает текущее количество строк в таблице Employee.

Включай в свои процедуры строку - 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», как показано ниже.