Особенности использования хранимых процедур
1. Хранимые процедуры позволяют повысить скорость выполнения операций обработки данных, т.к. хранятся в предварительно откомпилированном виде
2. Хранимые процедуры могут состоять из десятков и сотен команд, но для их выполнения достаточно указать имя хранимой процедуры. Это снижает нагрузку на сеть, т.к. уменьшает размер запроса, посылаемого по сети от клиента к серверу.
3. Использование хранимых процедур реализует принцип модульного проектирования, т.к. процедуры позволяют разбивать задачи на самостоятельные, более мелкие и удобные в управлении части.
Типы хранимых процедур.
1. Системные
Sp_ для выполнения различных операций администрирования БД.
2. Пользовательские
Создается пользователем и хранится.
3. Временные
Бывают локальные(#) и глобальные(##)
Создание хранимых процедур.
CREATE proc[edure] proc_name
[@parameter datatype [VARYING][OUTPUT]] [,…n]
[WITH [RECOMPILE/ENCRYPTION/ RECOMPILE,ENCRYPTION]]
AS SQL_statement[… n]
Выполнение хранимых процедур.
EXEC[UTE] proc_name
[[@parameter=] VALUE/@var[OUTPUT][DEFAULT]][…n]
Пример 1Процедура без параметров
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
Выполнение процедуры
EXECUTE au_info_all или EXEC au_info_all
Пример 2Процедура с входными параметрами
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
Выполнение процедуры с параметрами
EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
Пример 3Процедура с выходным параметром
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
Имя параметра не должно совпадать с именем переменной, однако тип и положение переменной должно соответствовать типу и положению параметра
Выполнение процедуры и использование значения выходного параметра
DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
Отчёт по лабораторной работе N 7 студентки Круглова АС группы П-303
Тема: “ Создание хранимых процедур ”
Цель: Научиться создавать и использовать хранимые процедуры с параметрами
Выполнение работы
Эквиваленты хранимых процедур на SQL языке (по заданию преподавателя)
CREATE PROCEDURE dbo.StoredProcedure2
AS
BEGIN
DROP TABLE Оклады
SELECT dbo.Назначения.n, dbo.Должности.min_s INTO Оклады
FROM dbo.Должности INNER JOIN
dbo.Назначения ON dbo.Должности.kod_d = dbo.Назначения.kod_d
WHERE (dbo.Назначения.date_end IS NULL)
END
RETURN
ALTER PROCEDURE pr7z2
AS
BEGIN
UPDATE Назначения SET date_end = GETDATE() WHERE n =
(SELECT TOP 1 n FROM Назначения
WHERE date_end IS NULL
ORDER BY date_b DESC)
END
ALTER PROCEDURE pr7z3
@nst as int
AS
BEGIN
SELECT n FROM Оклады WHERE min_s >
(SELECT min_s FROM Оклады WHERE n = @nst)
END
ALTER PROCEDURE pr7_z4
@np as int
AS
BEGIN
SELECT Фамилия, Имя FROM Сотрудники INNER JOIN Назначения
ON Сотрудники.n = Назначения.n
WHERE (kod_p = @np) and (date_end IS NULL)
(SELECT COUNT(*) as кол FROM Назначения
WHERE (kod_p = @np) and (date_end IS NULL)
GROUP BY kod_p)
END
Дата _____________ оценка __________________ подпись преподавателя замечание