Создание процедуры sql: MS SQL Server и T-SQL

Содержание

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

Введение

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

В чем плюсы такого подхода?

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

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

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

Создание хранимой процедуры в SQL Server ManagementStudio

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

выбираем базу данных, переходим на вкладуку «Программирование/Хранимые процедуры»

Создаем хранимую процедуру через контекстное меню:

Видим вот такой код:

Рассмотрим пример создания процедуры. Очищаем все и вставляем в поле следующий код:

CREATE PROCEDURE GetStudents
AS
     SELECT * FROM Students
GO

После этого нажимаем «Выполнить» (F5) и видим слева нашу хранимую процедуру:

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

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

Вызов нашей хранимой процедуры осуществляется с помощью кода (после чего нажимаем «выполнить» или F5):

USE sampledb;
EXEC GetStudents

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

1	[email protected]	Иван	Иванов	г. Рязань, ул. Ленина 54/2	50000,00
2	[email protected]	Петр	Петров	г. Рязань, ул. Ленина 54/3	50000,00
3	[email protected]	Илья	Ильин	г. Рязань, ул. Ленина 54/4	40000,00
4	[email protected]	Иван	Прохоров	г. Рязань, ул. Ленина 57/8	40000,00
5	bak@gmail. com	Борис	Акунин	г. Москва, ул. Лебедева 23/21	60000,00
6	[email protected]	Екатерина	Ларина	г. Шахты, ул. Пражская 4/9	60000,00
7	[email protected]	Елизавета	Бродская	г. Рязань, ул. Ленина 54/2	90000,00

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

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

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

CREATE PROCEDURE proc1
   @s1 nvarchar(128),
   @s2 int
AS
BEGIN
   select @s1 + cast(@s2 as nvarchar)
END
GO

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

exec proc1 @s1='123', @s2 = 0

Параметры также могут быть выходными — т.е. их значение изменено в процедуре и возвращено в вызывающую сторону.

CREATE PROCEDURE proc2
   @s1 nvarchar(128),
   @s2 int,
   @s3 nvarchar OUTPUT
AS
BEGIN
   set @s3 = @s1 + cast(@s2 as nvarchar)
END
GO

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

declare @test nvarchar(max)=''
exec proc1 @s1='123', @s2 = 0, @s3 = @test
print @test

Использование if

Пример хранимой процедуры с условием if:

CREATE PROCEDURE checkMaxAward
AS
BEGIN
	DECLARE @maxAward money
 
SELECT @maxAward = MAX(award) FROM Students
 
IF (@maxAward > 100000) begin
    PRINT 'максимальная сумма премии больше 100000';
end ELSE begin
    PRINT 'максимальная сумма премии меньше 100000';
end
END
GO

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

DECLARE @maxAward money

DECLARE применяется для определения переменных, после ключевого слова «DECLARE» указывается название и тип переменной. При этом название локальной переменной должно начинаться с символа @. В данном примере определяем переменную «@maxAward» с типом данных «money».

В нашем случае результат будет такой:

максимальная сумма премии меньше 100000

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

Дальше разберем использование циклов. Разберем классический пример: вычисление факториала числа. Используем такой код:

DECLARE @number INT, @factorial INT
SET @factorial = 2;
SET @number = 10;
 
WHILE @number > 0
    BEGIN
        SET @factorial = @factorial * @number
        SET @number = @number - 2
    END;
 
PRINT @factorial

Пояснения к коду: пока переменная @number не будет равна 0, будет продолжаться цикл WHILE. Каждый проход цикла называется итерацией. В каждой итерации будет переустанавливаться значение переменных @factorial и @number.

Результатом выполнения данного кода будет:

Также следует обратить внимание на ключевое слово «PRINT», которое выводит результат нашего кода:

Инструкция OUTPUT

OUTPUT – это инструкция, возвращающая изменившиеся строки в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE.

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

Принцип работы OUTPUT: все изменения, которые производят инструкции INSERT, UPDATE, DELETE и MERGE, фиксируются, условно говоря, во временных таблицах Inserted и Deleted. Они имеют такую же структуру, как и целевая таблица. Для того чтобы посмотреть изменения, нам необходимо в инструкции OUTPUT указать соответствующий префикс и название нужного столбца, примерно так же, как мы это делаем в инструкции SELECT, перечисляя названия столбцов, тем самым мы извлечем данные из этих таблиц.

Преобразование типов данных для переменных

Функция CAST преобразует выражение одного типа к другому и имеет следующую форму:

CAST(выражение AS тип_данных)

Пример. Есть такой код:

SELECT 'Средняя премия = '+ CAST(AVG(award) AS CHAR(15)) 
FROM Students;

Преобразуем числовое значение «award».

Результатом данного кода будет:

Средняя премия =   55714.29

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

Пример, следующий код пытается преобразовать строку «test» к типу float:

SELECT   
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL   
    THEN 'Cast failed'  
    ELSE 'Cast succeeded'  
END AS Result;  
GO

Результатом данного кода будет:

Cast failed

Отличие TRY_CAST от CAST заключается в том, что если преобразование не удалось, то TRY_CAST вернет NULL, а CAST вызовет исключение.

Конкатенация строк

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

SELECT  email, CONCAT(firstName, lastName) from Students

Результат данного кода будет следующий:

[email protected]	ИванИванов
[email protected]	ПетрПетров
[email protected]	ИльяИльин
[email protected]	ИванПрохоров
[email protected]	БорисАкунин
[email protected]	ЕкатеринаЛарина
[email protected]	ЕлизаветаБродская

Данная функция склеивает firstName и lastName в один столбец.

Стандартные функции для работы с датой и временем

Рассмотрим стандартную функцию GETDATE(), которая возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime:

SELECT  getdate()

Вернет результат:

2021-11-08 21:19:58.843

Функция dateadd добавляет к дате некое значение (месяцы, дни, недели, минуты т.д.)

select dateadd(day, 1, getdate())

Работа с NULL через ISNULL, NULLIF

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

select * from table1 where a1 is null

Для замены NULL на какое-то значение используйте функцию ISNULL. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:

ISNULL(выражение, значение)

Выберем всех студентов из таблицы Students, а у которых значение email NULL, заменим на надпись «неизвестно»:

SELECT firstName, lastName,
              ISNULL(email, 'неизвестно') AS Email
FROM Students

Результат этого запроса ниже:

Иван	Иванов	[email protected]
Петр	Петров	[email protected]
Илья	Ильин	[email protected]
Иван	Прохоров	[email protected]
Борис	Акунин	[email protected]
Екатерина	Ларина	[email protected]
Елизавета	Бродская	[email protected]
Семен	Зюзин	неизвестно

Последняя строка поле email было заменено на «неизвестно», т.к. имеет значение NULL.

Рассмотрим другую функцию: NULLIF. Она возвращает нулевое значение, если два указанных выражения равны. Например:

SELECT NULLIF (4,4) AS Same, NULLIF (5,7) AS Different;

Результат:

NULL	5

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

Второй столбец возвращает первое значение (5), потому что два входных значения различны.

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

sp_help SP_Name : используется для получения информации о названиях параметров процедуры, их типах и т.д. Эта процедура может быть применена к любому объекту БД (таблица, триггер и т.п.)
sp_helptext SP_Name : используется для получения текста хранимой процедуры

Пример первая функция sp_help. Используем вот такой код:

sp_help Students

Результат выполнения данной функции ниже:

Здесь мы видим подробную информацию о таблице Students.

Пример использования второй функции:

sp_helptext GetStudents

Результат ее выполнения:

СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В 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. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с указанным номером.

Вариант 20

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

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

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

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

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

хранимых процедур SQL Server для начинающих

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

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

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

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

Ее можно легко изменить : Мы можем легко изменить код внутри хранимой процедуры без необходимости перезапуска или развертывания приложения. Например, если запросы T-SQL написаны в приложении и нам нужно изменить логику, мы должны изменить код в приложении и повторно развернуть его. Хранимые процедуры SQL Server устраняют такие проблемы, сохраняя код в базе данных. поэтому, когда мы хотим изменить логику внутри процедуры, мы можем просто сделать это с помощью простого оператора ALTER PROCEDURE.

Уменьшение сетевого трафика: Когда мы используем хранимые процедуры вместо написания запросов T-SQL на уровне приложения, по сети передается только имя процедуры, а не весь код T-SQL.

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

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

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

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

9 9 0003

CREATE TABLE Product

(ProductID INT, ProductName VARCHAR(100))

CREATE TABLE ProductDescription

(ProductID INT, ProductDescription VAR CHAR(800) )

ВСТАВЬТЕ В ЗНАЧЕНИЯ ПРОДУКТА (680, ‘HL Road Frame — Black, 58’)

, (706, HL Road Frame — Red, 58′)

,(707,’Шлем Sport-100, красный’)

ВСТАВЬТЕ В ОПИСАНИЕ ПРОДУКТА ЗНАЧЕНИЯ (680,’Сменное горное колесо для райдера начального уровня’)

,(706, «Прочный сплав с быстросъемной ступицей». )

,(707, «Аэродинамические диски для плавной езды».)

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

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

9 9 0003

СОЗДАТЬ ПРОЦЕДУРУ GetProductDesc

НАЧАТЬ

УСТАНОВИТЬ NOCOUNT ON

ВЫБРАТЬ P.ProductID, P.ProductName, PD.ProductDescription FROM

Продукт P

ВНУТРЕННЕЕ СОЕДИНЕНИЕ ProductDescription PD ON P.ProductID=PD.ProductID

КОНЕЦ

Мы можем использовать «EXEC procedureName» для выполнения хранимых процедур. Когда мы выполняем процедуру GetProductDesc, набор результатов выглядит следующим образом.

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

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

Ниже приведен пример хранимой процедуры, которая принимает параметр.

9 9 0003

СОЗДАТЬ ПРОЦЕДУРУ GetProductDesc_withparameters

(@PID INT)

НАЧАТЬ

SET NOCOUNT ON

SELECT P.ProductID,P.ProductName,PD.ProductDescription FROM

Product P

INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID

WHERE P.ProductID=@PID

END

EXEC GetProductDesc_withparameters 706

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

Создание хранимой процедуры со значениями параметров по умолчанию

Ниже приведен пример хранимой процедуры со значениями параметров по умолчанию.

9 9 0003

СОЗДАТЬ ПРОЦЕДУРУ GetProductDesc_withDefaultparameters

(@PID INT =706)

НАЧАЛО

SET NOCOUNT ON

ВЫБЕРИТЕ P.ProductID, P.ProductName, PD.ProductDescription FROM

Product P

INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID

ГДЕ P.ProductID=@PID

КОНЕЦ

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

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

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

CREATE TABLE Сотрудник (EmpID int identity(1,1),EmpName varchar(500))

9000 2 10

СОЗДАТЬ ПРОЦЕДУРУ ins_NewEmp_with_outputparamaters

(@Ename varchar(50),

@EId int output)

НАЧАТЬ

SET NOCOUNT ON

INSERT INTO Employee (EmpName) VALUES (@Ename)

SELECT @EId= SCOPE_IDENTITY()

КОНЕЦ

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

declare @EmpID INT

EXEC ins_NewEmp_with_outputparamaters ‘Эндрю’, @EmpID OUTPUT

SELECT @EmpID

Создание зашифрованной хранимой процедуры

Мы можем скрыть исходный код в хранимой процедуре, создав процедуру с опцией «ШИФРОВАНИЕ».

Ниже приведен пример зашифрованной хранимой процедуры.

СОЗДАТЬ ПРОЦЕДУРУ GetEmployees

С ШИФРОВАНИЕМ

НАЧАЛО

УСТАНОВИТЬ NOCOUNT ON

ВЫБРАТЬ EmpID,EmpName от сотрудника

КОНЕЦ

Когда мы пытаемся просмотреть код хранимой процедуры SQL Server с помощью sp_helptext, он возвращает «Текст для объекта GetEmployees зашифрован».

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

Создание временной процедуры

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

Эти процедуры создаются в базе данных tempdb .

Локальные временные хранимые процедуры SQL Server : они создаются с префиксом # и доступны только в сеансе, в котором они были созданы. Эта процедура автоматически сбрасывается при закрытии соединения.

Ниже приведен пример создания локальной временной процедуры.

СОЗДАТЬ ПРОЦЕДУРУ #Temp

НАЧАТЬ

ПЕЧАТЬ ‘Локальная временная процедура’

КОНЕЦ

Глобальная временная хранимая процедура SQL Server: Эти процедуры создаются с префиксом ##, и к ним также можно получить доступ в других сеансах. Эта процедура автоматически удаляется при закрытии соединения, которое использовалось для создания процедуры.

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

СОЗДАТЬ ПРОЦЕДУРУ ##TEMP

НАЧАТЬ

ПЕЧАТЬ ‘Глобальная временная процедура’

КОНЕЦ

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

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

9 9 0003

ALTER PROCEDURE GetProductDesc

BEGIN

SET NOCOUNT ON

SELECT P.ProductID, P.ProductName, PD.ProductDescription FROM

Продукт P

ВНУТРЕННЕЕ СОЕДИНЕНИЕ ProductDescription PD ON P.ProductID=PD.ProductID

КОНЕЦ

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

Чтобы переименовать хранимую процедуру с помощью T-SQL, используйте системную хранимую процедуру sp_rename. Ниже приведен пример, в котором процедура «GetProductDesc» переименовывается в новое имя «GetProductDesc_new».

sp_rename ‘GetProductDesc’, ‘GetProductDesc_new’

Вывод

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

Автор
Последние сообщения

Ранга Бабу

Администратор баз данных SQL Server, разработчик с большим опытом администрирования SQL Server, разработки, настройки производительности, мониторинга, высокой доступности и технологий аварийного восстановления

Последние сообщения Ранга Бабу (посмотреть все)

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

Резюме : в этом руководстве вы узнаете, как управлять хранимыми процедурами в SQL Server, включая создание, выполнение, изменение и удаление хранимых процедур.

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

Следующая инструкция SELECT возвращает список продуктов из таблицы products в образце базы данных BikeStores:

 SELECT
наименование товара,
список цен
ОТ
производство.продукция
СОРТИРОВАТЬ ПО
наименование товара;
  Язык кода: SQL (язык структурированных запросов) (sql)

Чтобы создать хранимую процедуру, обертывающую этот запрос, используйте оператор CREATE PROCEDURE следующим образом:

 CREATE PROCEDURE uspProductList
КАК
НАЧИНАТЬ
    ВЫБИРАТЬ
        наименование товара,
        список цен
    ОТ
        производство. продукция
    СОРТИРОВАТЬ ПО
        наименование товара;
КОНЕЦ;
  Язык кода: SQL (язык структурированных запросов) (sql)

В этом синтаксисе:

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

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

Чтобы скомпилировать эту хранимую процедуру, вы выполняете ее как обычный оператор SQL в SQL Server Management Studio, как показано на следующем рисунке:

Если все правильно, вы увидите следующее сообщение:

 Команды выполнены успешно. 
  Язык кода: SQL (язык структурированных запросов) (sql)

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

Вы можете найти хранимую процедуру в обозревателе объектов в разделе Программируемость > Хранимые процедуры , как показано на следующем рисунке:

Иногда вам нужно нажать кнопку Обновить , чтобы вручную обновить объекты базы данных в обозревателе объектов.

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

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

 EXECUTE sp_name;
  Язык кода: SQL (язык структурированных запросов) (sql)

Или

 EXEC sp_name;
  Язык кода: SQL (язык структурированных запросов) (sql)

, где sp_name — имя хранимой процедуры, которую вы хотите выполнить.

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

 EXEC uspProductList;
  Язык кода: SQL (язык структурированных запросов) (sql)

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

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

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

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

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

 ПРОЦЕДУРА ИЗМЕНЕНИЯ uspProductList
    КАК
    НАЧИНАТЬ
        ВЫБИРАТЬ
            наименование товара,
            список цен
        ОТ
            производство.продукция
        СОРТИРОВАТЬ ПО
            список цен
    КОНЕЦ;
  Язык кода: SQL (язык структурированных запросов) (sql)

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

 Команды выполнены успешно.
  Язык кода: SQL (язык структурированных запросов) (sql)

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

 EXEC uspProductList;
  Язык кода: SQL (язык структурированных запросов) (sql)

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

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

Чтобы удалить хранимую процедуру, используйте DROP PROCEDURE или DROP PROC 9060 4 инструкция:

 DROP PROCEDURE sp_name;
  Язык кода: SQL (язык структурированных запросов) (sql)

или

 DROP PROC sp_name;
  Язык кода: SQL (язык структурированных запросов) (sql)

, где sp_name — имя хранимой процедуры, которую вы хотите удалить.

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

Введение

Создание хранимой процедуры в SQL Server ManagementStudio

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

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

Использование if

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

Инструкция OUTPUT

Преобразование типов данных для переменных

Конкатенация строк

Стандартные функции для работы с датой и временем

Работа с NULL через ISNULL, NULLIF

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

хранимых процедур SQL Server для начинающих

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

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

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

Создание хранимой процедуры со значениями параметров по умолчанию

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

Создание зашифрованной хранимой процедуры

Создание временной процедуры

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

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

Вывод

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

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

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

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

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

`Добавить комментарий Отменить ответ`

Рубрики

Рубрики