Процедура sql: Урок 15. Хранимые процедуры. Создание, использование и удаление.

SQL Хранимая процедура для SQL Server

Что такое хранимая процедура?

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

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

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

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

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

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

Демо база данных

Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:

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

Следующая инструкция SQL создает хранимую процедуру с именем «SelectAllCustomers» , которая выбирает все записи из таблицы «Customers»:

Пример

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Выполните описанную выше хранимую процедуру следующим образом:

Пример

EXEC SelectAllCustomers;

Хранимая процедура с одним параметром

Следующая инструкция SQL создает хранимую процедуру, которая выбирает клиентов из определенного города из таблицы «Customers»:

Пример

CREATE PROCEDURE SelectAllCustomers
@City nvarchar(30)
AS
SELECT * FROM Customers WHERE
City = @City
GO;

Выполните описанную выше хранимую процедуру следующим образом:

Пример

EXEC SelectAllCustomers @City = «London»;

Хранимая процедура с несколькими параметрами

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

Следующая инструкция SQL создает хранимую процедуру,
которая выбирает клиентов из определенного города с определенным почтовым кодом из таблицы «Customers»:

Пример

CREATE PROCEDURE SelectAllCustomers
@City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE
City = @City AND PostalCode = @PostalCode
GO;

Выполните описанную выше хранимую процедуру следующим образом:

Пример

EXEC SelectAllCustomers @City = «London», @PostalCode = «WA1 1DP»;

Transact-SQL | Хранимые процедуры

86

Работа с базами данных в .NET Framework — SQL Server 2012 — Хранимые процедуры

Исходники баз данных

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

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

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

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

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

• управления авторизацией доступа;

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

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

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

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

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

CREATE PROC[EDURE] [schema_name.]proc_name
    [({@param1} type1 [ VARYING] [= default1] [OUTPUT])] {, …}
    [WITH {RECOMPILE | ENCRYPTION | EXECUTE AS 'user_name'}]
    [FOR REPLICATION]
    AS batch | EXTERNAL NAME method_name


Соглашения по синтаксису

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

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

Как уже упоминалось ранее, предварительно компилированная фо

СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В MICROSOFT SQL SERVER

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

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

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

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

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

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

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

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 'ОАО МИР'

Сохранить файл программы с названием ФамилияСтудента_ЛАб_4.
В SQL Server Management Studio в разделе хранимых процедур БД
DB_Books проверить наличие процедур.

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

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

Сохранить файл программы с названием Фамилия Студента_Лаб_4_№варианта. В SQL Server Management Studio в разделе хранимых процедур индивидуальной БД проверить наличие процедур.

Вариант 1

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

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

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

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

Вариант 2

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

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

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

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

Вариант 3

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

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

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

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

Вариант 4

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

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

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

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

Вариант 5

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

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

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

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

Вариант 6

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

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

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

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

Вариант 7

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

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

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

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

Вариант 8

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

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

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

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

Вариант 9

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

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

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

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

Вариант 10

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

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

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

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

Вариант 11

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

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

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

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

Вариант 12

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

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

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

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

Вариант 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. Вывести список зарегистрированных документов с указанным типом
документа или с кодом документа в определенном диапазоне.

Назад: СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В MICROSOFT SQL SERVER

Передача табличных данных из хранимой процедуры / Хабр

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

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

create procedure Proc1
as
begin
	select 1 p1, 'b' p2
end

1 Метод

Один из самых простых методов. Используем конструкцию insert ... exec ...

if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(p1 int, p2 varchar(max))
insert #t1
exec Proc1
select * from #t1

Плюсы и минусы:

• Передаваемые поля перечисляются 2 раза (это внутренний select, внешнее создание таблицы и insert). Плюс перечисление полей происходит при каждом новом аналогичном вызове. (Я добавляю данный критерий, т.к. при большом количестве правок и множестве мест вызова процедуры, процесс изменения выводимых данных становится очень трудоемким)
• Имеет серьезное ограничение – мы можем получить только одну таблицу
• Для работы процедуры в режиме простого вывода не требуются дополнительные действия, достаточно запустить exec Proc1 без insert

2 Метод

С помощью записи в ранее созданную таблицу. Здесь придется добавлять insert в процедуру:

create procedure Proc1
as
begin
	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
end

По сути мы перенесли строку insert внутрь процедуры.

if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(p1 int, p2 varchar(max))
exec Proc1
select * from #t1

Плюсы и минусы:

• Передаваемые поля перечисляются 2 раза, и еще по одному перечислению на каждое новое использование
• Для работы процедуры в режиме простого вывода потребуется либо писать отдельную процедуру, выводящую принятые от Proc1 таблицы, либо определять, когда их выводить внутри Proc1. Например, по признаку не существования таблицы для вставки:

alter procedure Proc1
as
begin
	declare @show bit
	if object_id(N'tempdb..#t1',N'U') is null 
	begin
		set @show = 1
		create table #t1(p1 int, p2 varchar(max))
	end

	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
	
	if (@show = 1)
	begin
		select * from #t1
	end
end

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

3 Метод

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

create type tt1 as table(p1 int, p2 varchar(max))
go
create procedure Proc1
as
begin
	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
end
go
-- используем:
declare @t1 tt1
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
select *
into #t1
from @t1

exec Proc1
select * from #t1

Плюсы и минусы:

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

4 Метод

Усложнение третьего метода, позволяющее создавать таблицу с ограничениями и индексами. В отличии от предыдущего работает под Microsoft SQL Server 2005.

create procedure Proc1
as
begin
	insert #t1(p1, p2) 
	select 1 p1, 'b' p2
end
go
create procedure Proc1_AlterTable
as
begin
	alter table #t1 add p1 int, p2 varchar(max)
	alter table #t1 drop column delmy
end
go
-- используем:
if object_id(N'tempdb..#t1',N'U') is not null drop table #t1
create table #t1(delmy int)
exec Proc1_AlterTable
exec Proc1
select * from #t1

Однако обычно временная колонка delmy не используется, вместо неё таблица создается просто с одним первым столбцом (здесь с p1).

Плюсы и минусы:

• Передаваемые поля перечисляются 2 раза, при этом каждое новое использование не добавляет сложности
• Для непосредственного вывода результата также требуются дополнительные действия
• Неожиданно обнаружилось, что иногда, по непонятным причинам, возникают блокировки на конструкции alter table #t1, и процесс ожидает полного завершения Proc1 (не Proc1_AlterTable!) параллельного запроса. Если кто-нибудь знает, с чем это связанно — поделитесь, буду рад услышать:)

5 Метод

Этот метод использует предварительно созданные процедуры. Он основан на включении динамического SQL-запроса в запускаемую процедуру. Однако является достаточно простым в использовании.

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

1. В начало процедуры включить строки:

if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin

2. Все выводящие select’ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect). Если процедура не создает результирующего набора, то действие не требуется
3. В конец процедуры включить строку:

exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке их создания после запуска util.InclusionBegin

Для нашего примера мы получаем:

create procedure Proc1
as
begin
	if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
	exec util.InclusionBegin

		select 1 p1, 'b' p2
	into #Output1

	exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке их создания после запуска util.InclusionBegin
end

Запуск осуществляется так:

if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max))
exec util.InclusionRun'

select * from #InclusionOutput1

', 1, '#InclusionOutput'
exec Proc1

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

Функции util.InclusionRun передаются 3 параметра:

• @sql – SQL-скрипт, который выполниться внутри вызываемой процедуры
• @notShowOutput – если = 1, то блокировать вывод таблиц, начинающихся с #Output
• @replaceableTableName – (по умолчанию = '#Output') задать префикс в имени таблиц используемых в @sql, для замены его на соответствующую #Output* таблицу в скрипте. Например, если задать #InclusionOutput, и в процедуре созданы две таблицы #Output55 и #Output0A, то в @sql можно обратиться к #Output55 как к #InclusionOutput1, а к #Output0A как к #InclusionOutput2

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

Нюансы использования:

• Накладывает ограничения на использование инструкции return в процедуре, т.к. перед ней необходим запуск util.InclusionEnd
• Выводящие результат select’ы из запускаемых процедур выводят результат раньше, чем даже те #Output-таблицы, которые были созданы до их вызова (это логично, т.к. вывод происходит только в util.InclusionEnd)

Плюсы и минусы:

• Передаваемые поля перечисляются один раз, при этом каждое новое использование не добавляет сложности
• Для непосредственного вывода результата не требуется никаких действий
• Необходимо помнить и учитывать нюансы использования
• Из-за дополнительных процедур выполняется больше инструкций, что может снизить быстродействие при частых вызовах (я думаю, что при запуске реже одного раза в секунду этим можно пренебречь)
• Возможно, может усложнить понимание кода для сотрудников не знакомых с данным методом: процедура приобретает два exec-вызова и неочевидность того, что все #Output-таблицы будут выведены
• Позволяет легко организовать модульное тестирование без внешних инструментов

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

Скрытый текстКод:

if object_id('dbo.TestInclusion') is not null drop procedure dbo.TestInclusion
go
create procedure dbo.TestInclusion 
        @i int
as
begin
        if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
        exec util.InclusionBegin 

        if object_id('tempdb..#tmp2', 'U') is not null drop table #tmp2 
        select @i myI
        into #tmp2
        
        if object_id('tempdb..#tmp3', 'U') is not null drop table #tmp3
        select @i + 1 myI
        into #tmp3

        select *
        into #Output0 --На вывод (выводится в util.InclusionEnd)
        from #tmp2
        union all
        select *
        from #tmp3

        select 'процедура TestInclusion' alt
        into #OutputQwerty --На вывод (выводится в util.InclusionEnd)
        
        exec util.InclusionEnd --выводит все таблицы начинающиеся с #Output в порядке из создания после запуска util.InclusionBegin 
end
go
set nocount on
set ansi_warnings off
if object_id('tempdb..#ttInclusionParameters', 'U') is not null drop table #ttInclusionParameters
go
select 'Тест 1: запуск TestInclusion. Ниже должен быть вывод таблицы с одной колонкой myI и двумя строками: 2 и 3. И таблица с 1 строкой: "процедура TestInclusion"'
exec dbo.TestInclusion 2
go
select 'Тест 2: тест TestInclusion. Ниже должен быть вывод таблицы с одной колонкой testSum и одной строкой: 5'
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int, val varchar(max))
exec util.InclusionRun '

        select sum(myI) testSum
        from #InclusionOutput1
        
', 1, '#InclusionOutput'
exec dbo.TestInclusion 2

Результат:

-----------------------------------------------------------------------------------------------------------------------------------------------------------
Тест 1: запуск TestInclusion. Ниже должен быть вывод таблицы с одной колонкой myI и двумя строками: 2 и 3. И таблица с 1 строкой: "процедура TestInclusion"

myI
-----------
2
3

alt
-----------------------
процедура TestInclusion


------------------------------------------------------------------------------------------------------
Тест 2: тест TestInclusion. Ниже должен быть вывод таблицы с одной колонкой testSum и одной строкой: 5

testSum
-----------
5

Сами функции:

Скрытый текст

if not exists(select top 1 null from sys.schemas where name = 'util')
begin
	exec ('create schema util')
end
go
alter procedure util.InclusionBegin
as
begin
/*
	Инструкция для использования:
	1. Обработка процедуры данные которой необходимо использовать
	1.1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin 
	1.1. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect)
	1.2. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке из создания после запуска util.InclusionBegin
	2. В месте, где вызывается обработанная процедура, непосредственно до её запуска включить строки (иначе процедура будет просто выводить все #Output* таблицы):
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int)
exec util.InclusionRun('<sql скрипт который выполнится внутри вызываемой процедуры перед её завершением>')
	Дополнительно см. коментарии внутри util.InclusionRun
*/
	set nocount on
	set ansi_warnings off
	declare @lvl int
	
	if object_id('tempdb..#ttInclusionParameters', 'U') is not null
	begin
		select @lvl = max(lvl)
		from #ttInclusionParameters
		
		--Добавляем null задание, для предотвращения запуска скрипта во вложенных процедурах с данным механизмом
		if (@lvl is not null)
		begin
			insert #ttInclusionParameters(lvl, pr)
			select @lvl+1 lvl, null pr
		end
	end

	if object_id('tempdb..#ttInclusion', 'U') is not null
	begin
		--запоминаем все уже существующие таблицы #Output, чтобы в util.InclusionEnd не выводить их
		insert #ttInclusion(lvl, i)
		select isnull(@lvl, 0), so.object_id i
		from tempdb.sys.objects so
		where so.type = 'U'
			and so.name like '#[^#]%'
			and object_id('tempdb..' + so.name, 'U') is not null
			and not exists (select top 1 null from #ttInclusion where i = so.object_id)
	end
	
end
GO

go
alter procedure util.InclusionEnd
as
begin
/*
	Инструкция для использования:
	1. Обработка процедуры данные которой необходимо использовать
	1.1. В начало процедуры включить строки:
if object_id('tempdb..#ttInclusion', 'U') is null create table #ttInclusion(lvl int, i int)
exec util.InclusionBegin 
	1.1. Все выводящие select'ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output, into #Output5, into #OutputMySelect)
	1.2. В конец процедуры включить строку:
exec util.InclusionEnd --выводит все таблицы, начинающиеся с #Output, в порядке из создания после запуска util.InclusionBegin
	2. В месте, где вызывается обработанная процедура, непосредственно до её запуска включить строки (иначе процедура будет просто выводить все #Output* таблицы):
if object_id('tempdb..#ttInclusionParameters', 'U') is null create table #ttInclusionParameters(lvl int, pr int)
exec util.InclusionRun('<sql скрипт который выполнится внутри вызываемой процедуры перед её завершением>')
	Дополнительно см. коментарии внутри util.InclusionRun
*/
	set nocount on
	set ansi_warnings off
	----------------------------------------------------------------------------------------------------
	--считываем параметры
	declare @lvl int
		, @p0 varchar(max) --(@sql) sql скрипт который необходимо выполнить
		, @p1 varchar(max) --(@notShowOutput) если равно '1' хотя бы у одного из существующих вложенности заданий, то НЕ выводим #Output, иначе селектим их
		, @p2 varchar(max) --(@replaceableTableName) заменяемый префекс таблицы
	
	if object_id('tempdb..#ttInclusionParameters', 'U') is not null
	begin
		--считываем глобальные параметры
		select	@p1 = max(val)
		from #ttInclusionParameters
		where pr = 1
		
		--находим уровень на котором наше задание (max(lvl) - это уровень с null который мы добавили в util.InclusionBegin)
		select @lvl = max(lvl) - 1
		from #ttInclusionParameters
		
		if @lvl is not null
		begin
			--считываем
			select	@p0 = max(case when pr = 0 then val end)
				,	@p2 = max(case when pr = 2 then val end)
			from #ttInclusionParameters
			where lvl = @lvl 
			having max(pr) is not null
			
			--удаляем задание на скрипт, а если его нет, то только null-задание 
			delete #ttInclusionParameters
			where lvl >= @lvl and (lvl > @lvl or @p0 is not null)
		end
	end
	
	----------------------------------------------------------------------------------------------------
	--выбираем все созданные таблицы #Output
	if object_id('tempdb..#InclusionOutputs', 'U') is not null drop table #InclusionOutputs
	create table #InclusionOutputs(i int, tableName varchar(max), num int)
	
	if object_id('tempdb..#ttInclusion', 'U') is not null
	begin
		insert #InclusionOutputs(i, tableName, num)
		select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num
		from tempdb.sys.objects so
		where so.type = 'U'
			and so.name like '#[^#]%'
			and object_id('tempdb..' + so.name, 'U') is not null
			and so.name like '#Output%'
			and not exists (select top 1 null from #ttInclusion where i = so.object_id and lvl <= isnull(@lvl, lvl))
		
		--очищаем список созданных таблиц, которые принадлежат обрабатываемому уровню
		delete #ttInclusion 
		where lvl <= @lvl
	end
	else
	begin
		insert #InclusionOutputs(i, tableName, num)
		select so.object_id i, left(so.name, charindex('_', so.name)-1) tableName, row_number() over (order by so.create_date) num
		from tempdb.sys.objects so
		where so.type = 'U'
			and so.name like '#[^#]%'
			and object_id('tempdb..' + so.name, 'U') is not null
			and so.name like '#Output%'
	end
	
	----------------------------------------------------------------------------------------------------
	--Выполнение заданий (если его не было - вывод всех #Output)
	declare @srcsql varchar(max)
	
	--Выполняем заданный скрипт в util.InclusionRun
	if (@p0 is not null and @p0 <> '')
	begin
		--заменяем псевдонимы @replaceableTableName
		if (@p2 is not null and @p2 <> '')
		begin
			select @p0 = replace(@p0, @p2 + cast(num as varchar(10)), replace(tableName, '#', '#<tokenAfterReplace>'))
			from #InclusionOutputs
			order by num desc
			
			select @p0 = replace(@p0, '<tokenAfterReplace>', '')
		end
		
		--добавляем в скрипт
		select @srcsql = isnull(@srcsql + ' ' + char(13), '')
			+ @p0 + ' ' + char(13)
	end
	
	--Выводим созданные #Output таблицы
	if (@p1 is null or @p1 <> '1') --если равно 1, то не выполняем!
	begin
		--отступ от прошлого скрипта
		select @srcsql = isnull(@srcsql + ' ' + char(13), '')

		--добавляем в скрипт
		select @srcsql = isnull(@srcsql + ' ', '') +
			'select * from ' + tableName
		from #InclusionOutputs
		order by num asc
	end
	
	if (@srcsql is not null)
	begin
		exec (@srcsql)
	end
	
end
go
alter procedure util.InclusionRun 
	@sql varchar(max), --sql скрипт который выполниться внутри вызываемой процедуры (содержащей util.InclusionEnd)
	@notShowOutput bit, --если = 1, то блокировать вывод таблиц начинающихся с #Output
	@replaceableTableName varchar(100) = '#Output' -- задать префикс в имени таблиц используемых в @sql, для замены его на соответствующую #Output* таблицу в скрипте. 
		-- Например, если задать #InclusionOutput, и в процедуре созданы две таблицы #Output55 и #Output0A, 
		-- то в @sql можно обратиться к #Output55 как к #InclusionOutput1, а к #Output0A как к #InclusionOutput2	
as
begin
	set nocount on
	set ansi_warnings off

	if object_id('tempdb..#ttInclusionParameters', 'U') is null 
	begin
		print 'Процедура util.InclusionRun не выполнена, т.к. для неё не созданна таблица #ttInclusionParameters!  '
		return
	end
	
	declare @lvl int
	select @lvl = isnull(max(lvl), 0) + 1
	from #ttInclusionParameters
	
	insert #ttInclusionParameters(lvl, pr, val)
	select @lvl, 0, @sql
	union all
	select @lvl, 1, '1'	where @notShowOutput = 1
	union all
	select @lvl, 2, @replaceableTableName
	
end

Другие методы

Можно воспользоваться передачей параметра из функции (OUTPUT) и на основе его значения восстановить таблицу. Например, можно передать курсор или XML.
На эту тему существует статья.
Использовать курсор для этой задачи я не вижу смысла, только если изначально требуется именно курсор. А вот XML выглядит перспективным. Здесь очень интересные результаты тестов на производительность.
Интересно услышать какие вы используете способы упрощения этой задачи 🙂

UPD 31.03.2014: Скорректировал пост по идеям из комментариев

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

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

Введение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В заключение

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

Microsoft SQL Server 2005. Хранимые процедуры (Урок 9)

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

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

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

Как серверные программы хранимые процедуры имеют ряд преимуществ.

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

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

Хранимые процедуры управляются посредством инструкций языка определения данных (DDL) CREATE, ALTER и DROP.

Общий синтаксис T-SQL кода для создания хранимой процедуры имеет следующий вид:

CREATE PROC | PROCEDURE <procedure_name>

[ <@parameter> <data_type> [ = <default> ] [ OUT | OUTPUT ] ] [ ,…n ]

AS

[ BEGIN ] <sql_statements> [ END ]

<procedure_option> ::=

[ ENCRYPTION ]

[ RECOMPILE ]

[ EXECUTE_AS_Clause ]

Структура этого оператора соответствует основному синтаксису CREATE <Object Туре> <Object Name>, лежащему в основе любого оператора CREATE. Единственная отличительная особенность состоит в том, что в нем допускается использовать ключевое слово PROCEDURE или PROC. Оба эти варианта являются допустимыми: PROC является лишь сокращением от PROCEDURE.

Каждая процедура должна иметь уникальное в рамках базы данных имя (procedure_name), соответствующее правилам для идентификаторов объектов.

Процедуры могут иметь любое число входных параметров (@parametr) заданного типа данных (data_type), которые используются внутри процедуры как локальные переменные. При выполнении процедуры для каждого из объявленных формальных параметров должны быть переданы фактические значения. Или же для входного параметра может быть определено значение по умолчанию (default), которое должно быть константой или равняться NULL. В этом случае процедуру можно выполнить без указания значения соответствующего аргумента. Применение входных параметров необязательно.

Можно также указать выходные параметры (помеченные как OUTPUT), позволяющие хранимой процедуре вернуть одно или несколько скалярных значений в подпрограмму, из которой она была вызвана. При создании процедур можно задать три параметра. При создании процедуры с параметром ENCRYPTION SQL Server шифрует определение процедуры. При задании параметра RECOMPILE SQL Server перекомпилирует хранимую процедуру при каждом ее запуске. Параметр EXECUTE AS определяет контекст безопасности для процедуры.

В конце определения хранимой процедуры вслед за ключевым словом AS должно быть приведено непосредственно тело процедуры (sql_statements) в виде кода из одной или нескольких инструкций языка T-SQL.

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

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

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

CREATE PROCEDURE spr_getOrders

AS

SELECT IdOrd, IdCust, OrdDate

FROM [Order]

WHERE (OrdDate >= ‘01.01.2010’)

RETURN

Чтобы протестировать новую процедуру, откройте новый запрос SQL Server и выполните следующий код.

EXEC spr_getOrders

Команда EXECUTE или сокращенно EXEC выполняет указанную хранимую процедуру.

В данном случае хранимая процедура вернет все строки из таблицы Order, в которых значение поля OrdDate больше 1 января 2010 года, в соответствии с содержащимся в нем запросом на выборку.

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

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

@parameter_name [AS] datatype [= default|NULL]

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

Значительные различия между объявлениями параметров хранимых процедур и объявлениями переменных начинают впервые обнаруживаться, когда дело касается значений, заданных по умолчанию. Прежде всего, при инициализации переменным всегда присваиваются NULL-значения, а на параметры это правило не распространяется. В действительности, если в объявлении параметра не предусмотрено заданное по умолчанию значение, то подразумевается, что этот параметр должен быть обязательным и что при вызове хранимой процедуры должно быть указано его начальное значение. Чтобы задать предусмотренное по умолчанию значение, необходимо добавить знак равенства (=) после обозначения типа данных, а затем указать применяемое по умолчанию значение. Благодаря этому пользователи получают возможность при вызове хранимой процедуры принимать решение о том, следует ли задать другое значение параметра или воспользоваться значением, предусмотренным по умолчанию.

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

ALTER PROCEDURE [dbo].[spr_getOrders]

@dateBegin datetime,

@dateEnd datetime

AS

SELECT IdOrd, IdCust, OrdDate

FROM [Order]

WHERE (OrdDate BETWEEN @dateBegin AND @dateEnd)

RETURN

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

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

EXEC spr_getOrders ‘01.01.2010’, ‘01.07.2010’

EXEC spr_getOrders
@dateBegin = ‘01.01.2010’,

@dateEnd = ‘01.07.2010’

EXEC spr_getOrders ‘01.01.2010’, @dateEnd = ‘01.07.2010’

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

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

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

CREATE PROCEDURE spr_addProduct

@Description nvarchar(100),

@InStock int = 0,

@IdProd int OUT

AS

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN

Пример вызова:

DECLARE @IdProd int

EXEC spr_addProduct
@Description = N’Новый товар’,

@IdProd = @IdProd OUTPUT

SELECT @IdProd as N’@IdProd’

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

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

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

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

RETURN [<Целое число>]

Обратите внимание на то, что возвращаемое значение должно быть обязательно целочисленным.

Возвращаемые значения предназначены исключительно для указания на успешное или неудачное завершение хранимой процедуры и позволяют даже обозначить степень или характер успеха или неудачи. Использование возвращаемого значения для возврата фактических данных, таких как идентификационное значение или данные о количестве строк, затронутых хранимой процедурой, рассматривается как недопустимая практика программирования. Возвращаемое значение 0 указывает на успешное выполнение процедуры и установлено по умолчанию. Компания Microsoft зарезервировала значения от -99 до -1 для служебного пользования. Разработчикам для возвращения состояния ошибки пользователю рекомендуется использовать значения -100 и меньше.

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

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

ALTER PROCEDURE [dbo].[spr_addProduct]

@Description nvarchar(100),

@InStock int = 0,

@IdProd int OUT

AS

IF EXISTS(SELECT * FROM Product WHERE [Description] = @Description)

RETURN -100

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN 0

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

EXEC @локальная_переменная = имя_хранимой_процедуры;

DECLARE @return_value int,

@IdProd int

EXEC @return_value = spr_addProduct

@Description = N’Новый товар’,

@IdProd = @IdProd OUTPUT

IF @return_value = 0

BEGIN

PRINT ‘Товар успешно добавлен’
SELECT @IdProd as N’@IdProd’

END

ELSE

BEGIN

PRINT ‘При добавлении товара произошла ошибка’

SELECT ‘Return Value’ = @return_value

END

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

• Возврат списка всех заказов содержащих заданный товар (по IdProd).
• Определение количества клиентов, не имеющих ни одного заказа. Результат должен возвращаться через выходной параметр.
• Удаление из базы данных информации об определенном клиенте (по IdCust). Если с данным клиентом имеются связанные записи (заказы) удаление должно быть отменено. Возвращаемое значение должно определять успешность выполнения операции.

&nbsp

В общем, это все чем хотел поделиться на тему SQL. Лично мне этот курс очень понравился, сам обучался по нему. На удивление, знание SQL мне очень даже пригодилось. Удачи в дальнейшем освоении этого языка, учиться еще есть чему! =)

Еще записи по теме

Оптимизация хранимых процедур в SQL Server / Хабр

Доброго дня, хабрачеловек. Сегодня я бы хотел обсудить с вами тему хранимых процедур в SQL Server 2000-2005. В последнее время их написание занимало львиную долю моего времени на работе и чего уж тут скрывать – по окончанию работы с этим делом осталось достаточно информации, которой с удовольствием поделюсь с тобой %пользовательимя%.
Знания, которыми я собираюсь поделиться, к сожалению,(или к счастью) не добыты мной эмперически, а являются, в большей степени, вольным переводом некоторых статей из буржуйских интернетов.
Итак, как можно понять из названия речь пойдет об оптимизации. Сразу оговорюсь, что все действия, которые я сейчас буду описывать, действительно дают существенный(некоторые больший, некоторые меньший) прирост производительности.
Данная статья не претендует на полное раскрытие темы оптимизации, скорее это собрание практик, которые я применяю в своей работе и могу ручаться за их эффективность. Поехали!

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

CREATE PROC dbo.ProcName

AS

SET NOCOUNT ON;

--Здесь код процедуры

SELECT column1 FROM dbo.TblTable1

--Перключение SET NOCOUNT в исходное состояние

SET NOCOUNT OFF;

GO


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

SELECT * FROM dbo.MyTable --Вот так делать хорошо

-- Вместо

SELECT * FROM MyTable --А так делать плохо

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

EXEC dbo.MyProc --Опять же хорошо

--Вместо

EXEC MyProc --Плохо!

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

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

IF EXISTS (SELECT 1 FROM sysobjects

WHERE name = 'MyTable' AND type = 'U')


5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server’ом появился более правильный и удобный способ решения этой проблемы:

BEGIN TRY

--код

END TRY

BEGIN CATCH

--код отлова ошибки

END CATCH


Заключение

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

P.S.

Мой первый пост, не судите строго.

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

Что такое хранимая процедура?

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

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

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

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

СОЗДАТЬ ПРОЦЕДУРУ имя_процедуры
AS
sql_statement
ИДТИ;

Выполнить хранимую процедуру

Демо-база данных

Ниже представлен выбор из таблицы «Клиенты» в образце базы данных Northwind:

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

Следующий оператор SQL создает хранимую процедуру с именем «SelectAllCustomers».
который выбирает все записи из таблицы «Клиенты»:

Пример

СОЗДАТЬ ПРОЦЕДУРУ SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Выполните указанную выше хранимую процедуру следующим образом:

Пример

EXEC SelectAllCustomers;

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

Следующий оператор SQL создает хранимую процедуру
который выбирает клиентов из определенного города из таблицы «Клиенты»:

Пример

СОЗДАТЬ ПРОЦЕДУРУ SelectAllCustomers
@City nvarchar (30)
AS
ВЫБРАТЬ * ОТ клиентов ГДЕ
City = @City
GO;

Выполните указанную выше хранимую процедуру следующим образом:

Пример

EXEC SelectAllCustomers @City = ‘Лондон’;

Сохраненная процедура с несколькими параметрами

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

Следующий оператор SQL создает хранимую процедуру
который выбирает клиентов из определенного города с определенным почтовым кодом из таблицы «Клиенты»:

Пример

СОЗДАТЬ ПРОЦЕДУРУ SelectAllCustomers
@City nvarchar (30), @PostalCode nvarchar (10)
AS
SELECT * FROM Customers WHERE
Город = @City AND PostalCode = @PostalCode
GO;

Выполните указанную выше хранимую процедуру следующим образом:

Пример

EXEC SelectAllCustomers @City = ‘Лондон’, @PostalCode = ‘WA1 1DP’;

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

Sql — Примеры запросов

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

1-) Вызов хранимой процедуры для сложения двух чисел с входными выходными параметрами Пример

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

Создать процедуру AddTwoNumber (@ p1 int, @ p2 int, @ Result int output)
как
Начать
Установите @Result = @ p1 + @ p2
Конец

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

Объявить @r int
Выполнить AddTwoNumber 20,25, @ r output
Выберите @r в качестве результата

Результат кода: 45

Объявить @r int
Выполнить AddTwoNumber 5,7, @ r output
Выберите @r в качестве результата

Результат кода: 12

2-) Сохраненная процедура, которая выбирает случайное число из двух чисел, введенных в качестве параметров в Sql

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

Создать процедуру Sp_Random_Value
@first int,
@ второй int,
@result int вывод
Как
Начать
Установите @result = Floor (RAND () * (@ second- @ first)) + @ first
Конец

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

Объявить @r int
Выполнить Sp_Random_Value 20,30, @ r output
Выберите @r

Каждый раз, когда вы запускаете код, вы будете получать разные значения от 20 до 30
Получил результат: 23

Объявить @r int
Выполнить Sp_Random_Value 20,30, @ r output
Select @r

Результат: 20

Объявить @r int
Выполнить Sp_Random_Value 20,30, @ r output
Выберите @r

3-) Сгенерировать уникальные случайные числа в Sql с помощью хранимой процедуры

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

Создать процедуру randomGenerate (@unit int, @ min int, @ max int)
как
Начать
Объявить таблицу @numbers (число int)
Объявить @i int = 0
Объявить @number int
в то время как (@ i & lt; @unit)
Начать
Установите @number = floor (rand () * (@ max- @ min + 1)) + @ min
если (не существует (выберите * из @numbers, где number = @number))
начать
вставить в значения @numbers (@number)
Установите @i = @i + 1
конец
конец
Выберите * из @ номеров в порядке 1
Конец

После того, как вы запустите код выше, чем yo Вы запускаете код ниже.

Выполнить randomGenerate 5,20,30

Результат: Список 5 чисел от 20 до 30

Выполнить randomGenerate 6,0,49

Результат: Список 6 чисел от 0 до 49

Результат: Список 10 номеров от 50 до 100

4-) Вычисление факториала заданного числа с помощью хранимой процедуры

В этом руководстве мы напишем «Хранимая процедура» вычисляет факториал числа.Это указано ниже.

Создать коэффициент процедуры (@number int)
как начало
Объявить @i int = 1, @ result int = 1
а (@ i & lt; = @ число)
Начать
Установите @result = @result * @i
Установить @i + = 1
Конец
Выберите @result
Конец

Вы должны выполнить приведенный выше код и получить успешное сообщение.Затем, запустив следующий код, вы можете вычислить факториал нужного числа.

Результат : 120

Результат : 3628800

5-) Создание хранимой процедуры. Вычислить степень числа в Sql

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

Создать процедуру myPower (@num int, @ pow int, @result int output) Как
Начать
Объявить @i int = 0;
Установите @result = 1
в то время как (@ i & lt; @pow)
Начать
Установить @result = @result * @num
Установить @i + = 1
Конец
Конец

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

Объявить @result int
Выполнить myPower 3,4, @ результат вывода
Выберите @result

Результат: 81

Объявить @result int
Выполнить myPower 2,5, @ результат вывода
Выберите @result

Результат: 32

Объявить @result int
Выполнить myPower 5,3, @ результат вывода
Выберите @result

Result: 125

6-) Нахождение суммы трех Числа с хранимой процедурой

В этом учебном веб-сайте Уильям создает процедуру, которая суммирует три числа.Номера будут записаны параметром. Давай сделаем это.

Создать процедуру sumThree
@ n1 int,
@ n2 int,
@ n3 int,
@result int вывод как
Начать
Установите @result = @ n1 + @ n2 + @ n3
Конец

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

Результат : 24

Результат : 34

7) Сохраненная процедура для поиска простого числа в Sql

Мы напишем процедуру, которая находит данное число простым. Процедура получает целочисленный параметр и выходной битовый параметр. Если значение параметра результата равно 0 (ноль), то записанное число является простым, если значение параметра результата равно 1 (единице), то записанное число не является простым.

Создайте процедуру sp_isPrime (@number int, @ выходной бит результата) как
Начать
Установите @result = 1
Объявить @i int = 2
Пока (@ i & lt; @number)
Начать
если (@number% @i = 0)
Начать
Установите @result = 0
сломать
Конец
Установить @i + = 1
Конец
вернуть @result
Конец

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

Пример 1

Объявить бит @result
Выполнить процедуру sp_isPrime 11, @ вывод результата
Выберите @result

Result : 1 (Prime)

Пример 2

Объявить бит @result
Выполнить процедуру sp_isPrime 9, @ вывод результата
Выберите @result

Result : 0 (non-Prime)

8) Сохраненная процедура для разделения Числа и получить разделенное значение и оставшееся значение

В этом посте мы напишем хранимую процедуру.Мы разделим числа, чем получим разделенное значение и оставшееся значение на выходной параметр.

После запуска кода выше мы получим успешное сообщение, чем мы можем запустить коды ниже

Ex1

Объявить @d int, @ r int
Выполнить процедуру sp_Devide 34,7, выход @ d, выход @ r
Выберите деление @d, оставшееся @ r

Результат

Ex2

Объявить @d int, @ r int
Выполнить процедуру sp_Devide 25,4, выход @ d, выход @ r
Выберите @d Division, @ r Осталось

.

Хранимая процедура в SQL Server

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

1. Что такое хранимая процедура в SQL?
2. Почему мы используем SET NOCOUNT ON в хранимой процедуре?
3. Сколько существует типов хранимых процедур?
4. Как писать комментарии в SQL Server?
5. Каковы соглашения об именах хранимых процедур?
6. Как создать хранимую процедуру для выбора данных из вкладки базы данных с помощью запроса SELECT SQL?
7. Как выполнять хранимые процедуры в SQL Server?
8. Что такое параметры в хранимых процедурах?
9. Как создать параметры в хранимой процедуре запроса SELECT, которая возвращает записи в соответствии с переданным параметром?
10. Как создать хранимую процедуру на основе запроса INSERT?
11. Как создать хранимую процедуру на основе запроса UPDATE?
12. Как создать хранимую процедуру для удаления записей с помощью запроса DELETE?

Что такое хранимая процедура?

Хранимая процедура SQL (SP) — это набор операторов SQL и логики команд sql, которые компилируются и хранятся в базе данных.Сохраненные процедуры в SQL позволяют нам создавать SQL-запросы, которые будут храниться и выполняться на сервере. Сохраненные процедуры также можно кэшировать и использовать повторно. Основная цель хранимых процедур — скрыть прямые SQL-запросы от кода и повысить производительность таких операций с базой данных, как выбор, обновление и удаление данных.

Почему мы используем SET NOCOUNT ON в хранимой процедуре?

Пока мы устанавливаем SET NOCOUNT ON, это означает, что нет сообщений, показывающих количество затронутых строк.

NOCOUNT означает не считать, что ON.

Теперь вы узнаете, что произошло, когда SET NOCOUNT OFF.

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

В SQL Server доступны два типа хранимых процедур:

1. Пользовательские хранимые процедуры
2. Системные хранимые процедуры

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

Пользовательские хранимые процедуры создаются разработчиками или администраторами баз данных.Эти SP содержат еще один оператор SQL для выбора, обновления или удаления записей из таблиц базы данных. Определяемая пользователем хранимая процедура может принимать входные параметры и возвращать выходные параметры. Определяемая пользователем хранимая процедура представляет собой смесь команд DDL (язык определения данных) и DML (язык манипулирования данными).

Пользовательские SP подразделяются на два типа:

Хранимые процедуры T-SQL: T-SQL (Transact SQL) SP получают и возвращают параметры.Эти SP обрабатывают запросы Insert, Update и Delete с параметрами или без них и возвращают данные строк в качестве вывода. Это один из наиболее распространенных способов написания SP в SQL Server.

Хранимые процедуры CLR: CLR (Common Language Runtime) SP написаны на языке программирования на основе CLR, таком как C # или VB.NET, и выполняются .NET Framework.

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

Системные хранимые процедуры создаются и выполняются SQL Server для административных действий сервера.Разработчики обычно не вмешиваются в системные SP.

Вход в базу данных SQL Server

Давайте войдем в нашу базу данных SQL Server, чтобы мы могли достичь следующего:

• Как создать хранимую процедуру на основе SELECT QUERY, которая возвращает все записи?
• Как создать хранимую процедуру SELECT QUERY на основе ПАРАМЕТРОВ, которая возвращает записи на основе параметров?
• Как создать хранимую процедуру на основе запроса INSERT?
• Как создать хранимую процедуру на основе запроса UPDATE?
• Как создать хранимую процедуру на основе запроса DELETE?

Войдите в SQL SERVER с вашим именем сервера, логином и паролем.

Переключитесь на свою базу данных. Моя база данных называется MBKTest.

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

Пустой шаблон, созданный SQL Server для SP, выглядит следующим образом. SQL-команда CREATE PROCEDURE используется для создания процедуры, за которой следует имя SP и его параметры. Области BEGIN и END используются для определения запроса операции.Здесь вы будете писать запросы на выбор, обновление, вставку или удаление.

12. НАБОР ANSI_NULLS000
17. СОЗДАТЬ ПРОЦЕДУРУ <имя_процедуры, sysname, имя_процедуры>
19. <@ Param1, sysname, @ p1> = , > <@ Param2 Datatype_For_Param2`` int> =
20. AS
21. BEGIN
24. SET NOCOUNT ON;
27. SELECT <@ Param1, sysname, @ p1>, <@ Param2, sysname, @ p2>
28. END
29. GO

Как писать комментарии в SQL SERVER?

Вы можете комментировать в sql server следующими способами:

1. — (два дефиса / тире) для одной строки комментария.
2. начать с / * ……. заканчиваться * / для многострочных комментариев.

Каковы правила именования хранимых процедур?

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

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

1. sp
2. stp
3. stp_
4. udstp
5. udstp_

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

Создание таблицы базы данных

Раньше мы могли создавать и выполнять любые SP, нам нужна таблица базы данных. Я создаю таблицу базы данных с именем «tblMembers», используя следующий запрос SQL, и выполняю его на сервере. Как видите, в моей таблице 4 столбца, где первый столбец является столбцом идентификации. После создания таблицы откройте ее в SSMS и добавьте данные, вручную введя данные в таблицу.

1. ИСПОЛЬЗОВАТЬ [MBKTest]
2. GO
4. / ****** Объект: Таблица [dbo]. [TblMembers] Дата сценария: 18 ноября 17, сб 18:47:55 *** *** /
5. SET ANSI_NULLS ON
6. GO
8. SET QUOTED_IDENTIFIER ON
9. GO
11. SET ANSI_PADDING ON
12. GO
14. CREATE TABLE [
16. CREATE TABLE [таблица элементов] ] [Int] IDENTITY (1,1) NOT NULL,
17. [MemberName] [varchar] (50) NULL,
18. [MemberCity] [varchar] (25) NULL,
19. [MemberPhone] [varchar] (15) NULL
20. )
22. GO
24. SET ANSI_PADDING OFF
25. GO

Как создать хранимую процедуру SELECT?

Щелкните свою базу данных и разверните элемент «Программируемость» и щелкните правой кнопкой мыши «Хранимые процедуры» или нажмите CTRL + N, чтобы открыть новое окно запроса.В области запроса между BEGIN и END введите оператор SELECT, чтобы выбрать записи из таблицы. См. Оператор Select в приведенном ниже коде.

1. УСТАНОВИТЬ ANSI_NULLS ON
2. GO
3. УСТАНОВИТЬ QUOTED_IDENTIFIER ON
4. GO
11. 000
12. 00050006
13. CREATE PROCEDURE
16. Выберите * из tblMembers
17. END
18. GO

Теперь нажмите F5 или нажмите кнопку Execute, чтобы выполнить SP.

Вы должны увидеть сообщение «Команда (и) успешно выполнена».

Теперь перейдите к Программируемости -> Хранимые процедуры, щелкните правой кнопкой мыши и выберите Обновить.

На следующем изображении вы можете видеть, что создается новый SP с именем stpGetAllMembers.

Выполнение хранимых процедур в SQL Server

В пользовательском интерфейсе ниже щелкните правой кнопкой мыши имя SP и выберите «Выполнить хранимую процедуру»… выполнить ИП. Отсюда вы также можете изменить существующий SP.

Кроме того, вы также можете выполнить SP из окна запроса.

Чтобы запустить хранимую процедуру в SQL Server Management Studio, переключитесь в окно запроса или CTRL + N, чтобы открыть новое окно запроса, и введите следующую команду.

• Синтаксис — EXEC <имя хранимой процедуры>
• Пример — EXEC stpGetAllMembers

Теперь мы запускаем нашу хранимую процедуру под названием stpGetAllMembers.Результат выглядит следующим образом:

ВЫХОД

Что такое параметры в хранимых процедурах?

Параметры в SP используются для передачи входных значений и возврата выходных значений. Есть два типа параметров:

1. Входные параметры — передача значений в хранимую процедуру.
2. Выходные параметры — возвращаемые значения из хранимой процедуры.

Как создать СП запроса SELECT с параметрами?

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

Вот обновленный SP с параметром @CityName.

1. УСТАНОВИТЬ ANSI_NULLS ВКЛ
2. GO
3. УСТАНОВИТЬ QUOTED_IDENTIFIER ВКЛ
4. GO
10. СОЗДАТЬ ПРОЦЕДУРУ ПРОЦЕДУРЫ
12. 0005

0005

0005

15. УСТАНОВИТЬ СЧЕТ НЕТ;
17. Выберите * From tblMembers
18. , где MemberCity, например, ‘%’ + @ CityName + ‘%’
20. END
21. GO

Выполнить.

Чтобы запустить этот SP, введите следующую команду в инструменте запросов SQL:

EXEC GetMemberByCityName @CityName = ‘mal’

ИЛИ из пользовательского интерфейса запустите SP и введите следующие данные.

Код для выполнения выглядит следующим образом:

1. USE [MBKTest]
2. GO
4. DECLARE @return_value int
6. EXEC @return_value.[GetMemberByCityName]
7. @CityName = N’mal ‘
9. SELECT’ Возвращаемое значение ‘= @return_value
11. GO

OUTPUT

INSERT для создания сохраненной процедуры 900S для создания хранимой процедуры 900S ?

Мы можем использовать запрос INSERT INTO SQL для вставки данных в таблицу. Следующий оператор SQL создает INSERT SP с тремя параметрами.

1. УСТАНОВИТЬ ANSI_NULLS ON
2. GO
3. УСТАНОВИТЬ QUOTED_IDENTIFIER ON
4. GO
10. CREATE PROCEDURE stpumber6emberity 9Number5000 @ varMember 9Number5000 @ varMember
11. @MemberPhone varchar (15)
13. AS
14. НАЧАТЬ
17. УСТАНОВИТЬ NOCOUNT ON;
19. Вставить в tblMembers (MemberName, MemberCity, MemberPhone)
20. Значения (@ MemberName, @ MemberCity, @MemberPhone)
22. END
23. GO

Щелкните правой кнопкой мыши по сохраненной процедуре в проводнике объектов и выберите «Обновить».

Передайте значение параметра в диалоговом окне «Выполнить». Примерно так:

Следующий код можно использовать для выполнения этого SP в SSMS.

1. ИСПОЛЬЗУЙТЕ [MBKTest]
2. GO
4. DECLARE @return_value int
6. EXEC @return_value = [dbo]. [StpInsertMember]
7. @MemberNameh ‘NewYork’,
8. @MemberPhone = N’9999945121 ‘
9. SELECT’ Return Value ‘= @return_value
10. GO

OUTPUT

Member В окне запроса вы можете проверить наличие новой записи Имя «Махеш Чанд» добавлено в таблицу.

Вы также можете запустить тот же SP в коде.

EXEC stpInsertMember @MemberName = ‘Сухана и Ашиш Калла’, @MemberCity = ‘Мумбаи’, @MemberPhone = N’

92774xxx ‘

Вы можете проверить 9UTP. & Ашиш Калла »успешно добавлена.

Как создать хранимую процедуру на основе запроса UPDATE?

Давайте создадим новый SP, который будет обновлять записи таблицы на основе столбца идентификатора участника.ID передается как входной параметр. Вот новый SP, который использует команду UPDATE..SET..WHERE.

1. УСТАНОВИТЬ ANSI_NULLS ON
2. GO
3. УСТАНОВИТЬ QUOTED_IDENTIFIER ON
4. GO
10. СОЗДАТЬ ПРОЦЕДУРУ
11. , номер 9BydateMan 9Member6000, номер 9MemberUpdateM6 MemberCity varchar (25),
12. @MemberPhone varchar (15)
14. AS
15. НАЧАТЬ
18. УСТАНОВИТЬ NOCOUNT ON;
20. ОБНОВЛЕНИЕ tblMembers
21. Установить MemberName = @MemberName,
22. MemberCity = @MemberCity,
23. MemberPhone = @MemberPhone
24. Где MemberID = @MemberID
25. END

, сохраненный в процедуре GO

26. END

Обозреватель объектов и выберите Обновить.Вы увидите, что SP создан.

Теперь щелкните правой кнопкой мыши имя SP и выберите «Выполнить хранимую процедуру…». Введите входные значения и выполните.

Мы можем использовать следующую команду в SSMS.

1. ИСПОЛЬЗУЙТЕ [MBKTest]
2. GO
4. DECLARE @return_value int
6. EXEC @return_value = [dbo]. [StpUpdateMemberByID] @

Member60005 @Member60005 @Member6Name ,

7. @MemberCity = N’Mumbai ‘,
8. @MemberPhone = N’
9. 2541xxxx’
11. SELECT «Возвращаемое значение» = @return_value
13. GO

EXEC stpdateMemberpdateMember «Биканер», «

564xxx»

Результаты должны показать вам обновленные значения.

Как создать хранимую процедуру на основе запроса DELETE?

Давайте создадим SP, который будет удалять записи. Новый SP использует команду DELETE и удаляет все записи, соответствующие указанному идентификатору участника.

1. НАБОР ANSI_NULLS ПО
3. GO SET QUOTED_IDENTIFIER ON
5. GO
10. CREATE ПРОЦЕДУРА stpDeleteMemberByMemberID

@MemberID INT

13. КАК НАЧАТЬ
16. SET NOCOUNT ON;
18. Удалить из tblMembers
19. , где MemberId = @MemberID
21. END
22. GO

Выполнить.

Щелкните правой кнопкой мыши «Хранимые процедуры» в проводнике объектов и выберите «Обновить».

ЗАПУСТИТЬ хранимую процедуру BY UI

Теперь снова щелкните правой кнопкой мыши по хранимой процедуре и выберите «Выполнить хранимую процедуру…»

Как вы можете видеть на изображении, я передал значение параметра @MemberID = 4.

ВЫПОЛНИТЬ УДАЛЕНИЕ хранимой процедуры ВРУЧНУЮ (КОДИРОВКА)

EXEC stpDeleteMemberByMemberID 2

OUTPUT

Запись успешно удалена.

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

.

No related posts.