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

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

Дата _____________ оценка __________________ подпись преподавателя замечание

Наши рекомендации