21.Формальные и фактические параметры в процедурах и функциях

Хранимые процедуры – именованный набор команд на транзакт SQL, хранящихся на сервере и представляет самостоятельный объект БД. ХП хранятся в предварительно откомпилированном виде, поэтому эффективность их выполнения выше, чем у обычных запросов. При выполнении ХП процедуры в 1 раз выполняются в 5 шагов следующего алгоритма:

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

2. Компоненты сопоставляются с реальными объектами (таблицы, запросы). Разрешение внешних ссылок.

3. В системной таблице (Sysobjects) заполняется имя процедуры. Тип: Р.А. в системной таблице (Sys comments) в поле text заполняется текст процедуры.

4. Создается предварительный план выполнения запроса, сохраняется в системной таблице Sys PROCEDURES

5. при первом выполнении ХП дерево запроса считывается и окончательно оптимизируется.

При повторном вводе выполняется ранее созданный план процедуры. При повторении вызова выполняется только 5 шаг алгоритма.

Преимущество использования ХП по сравнению с обычными запросами.

• Повышается скорость (из-за откомпилированного вида)

• План выполнения процедуры сохраняется в быстродействующем КЕШе, поэтому при повторном вызове она берется из КЕШа. (Скорость повышается)

• Уменьшается нагрузка на сеть (передача имя процедуры и список, а выполнение происходит только на сервере)

• Использование ХП реализует принцип модульного проектирования, т.к. процедура позволяет разбивать задачи на самостоятельные части более удобные в управлении.

Типы ХП.

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

2. Пользовательские хранятся, как отдельный объект.

3. Глобальные и локальные

Создание хп

Create Proc [edure] имя процедуры [: номер]

[{@ параметр, тип}}[varung] [:default] [output]]

[,…]

[with Recompile/ Encryption/ [with Recompile, Encryption]]

As SQL – Statement …

Varung – если курсор возвращается в виде выходного параметра

Процедура без параметров

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

Процедура с входным параметром

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'

Процедура с выходным параметром

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)))