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

ХП -- набор инструкций T-SQL, выполняющийся как единое целое.

ХП обладают рядом преимуществ:

1) явл. объектами БД, размещённые в файле БД и перемещаемые вместе с функциями в случае репликации БД;

2) позволяют передавать данные процедуре для их обработки и принимать обратно от процедуры в данные и сформированный код;

3) ХП представляются в оптимизированной форме, след., выполняются быстрее, чем сценарий (пакет операторов T-SQL, которые хранятся в текстовых файлах);

4) ХП предоставляют 2 метода взаимодействия с внешними процессами:

-- через параметры; -- через возвращаемое значение.

Параметры – спец. тип локальных переменных, которые объявлены как часть ХП. Могут быть входными и выходными.

Типы:

1) системные – хранятся в главной БД (имя начинается с sp_, около 100);

2) пользовательские.

Системные:

1) Задачи администрирования:

Sp_addlogin – добавляет id учётной записи.

Sp_add_jobshedule – расписание заданий, например, резервное копирование БД.

2) Для управления объектами БД:

Sp_rename – переименование объектов БД.

Sp_renamedb – переименование БД.

3) Предоставление информации о текущем статусе системы.

Sp_who – текущие пользователи и процессы.

Sp_cursor_list – список текущих курсоров для данного соединения.

Sp_helpdb –список всех текущих БД на сервере.

29. Создание, удаление, изменение хранимой процедуры .

procedure_name С помощью этого параметра указывается имя, которое будет иметь хранимая процедура

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

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

data_type Этот параметр определяет тип данных, который будет иметь соответствую­щий параметр ХП.

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

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

OUTPUT Это ключевое слово приводится, когда необх. возвратить измененное значение параметра ХП.

WITH После этого ключевого слова приводятся дополнительные опции хранимой процедуры.

1) RECOMPILE. Использование этого параметра предписывает не выполнять кэширование плана выполнения хранимой процедуры. То есть всякий раз при вызове процедуры будет происходить генерирование плана обработки запроса.

2) ENCRYPTION. При указании данного параметра происходит шифрование кода процедуры в таблице syscomments, где хранится исходный текст процедуры.

3) FOR REPLICATION. Этот параметр применяется только при выполнении ре­пликации хранимых процедур. Указанный тип репликации позволяет пере­давать от издателя к подписчикам не весь набор выполненных изменений, и лишь вызов хранимой процедуры со значениями всех входных парамет­ров. Таким образом можно резко снизить объем сетевого трафика. Хра­нимая процедура, созданная на подписчике с использованием FOR REPLICATION, не может быть вызвана пользователем. Ее запуск разрешен только подсистеме репликации.

AS После этого ключевого слова начинается тело хранимой процедуры, которое содержит набор команд Transact-SQL.

sql_statement [...n]

Этот параметр подразумевает указание собственно команд Transact-SQL, из которых и формируется тело хранимой процедуры.

Изменение процедуры: ALTER PROCEDURE.

Удаление процедуры: DROP PROCEDURE.

Функции: типы функций.

Пользовательские и встроенные

Встроенные функции:

1) функции просмотра конфигурации (@@VERSION, @@OPTIONS, @@LANGUAGE, @@MAX_CONNECTIONS, @@SERVERNAME);

2) функции для работы с курсорами (@@CURSOR_ROWS, @@FETCH_STATUS, @@CURSOR_STATUS);

3) функции работы с датой и временем (SYSDATETIME, GETDATE, DAY, MONTH, YEAR);

4) математические функции (ABS, SIN,SQRT);

5) функции метаданных (TYPE_NAME, FILE_NAME, KEY_NAME, DATABASEPROPERTY);

6) функции подсистемы безопасности (@@ERROR, HOST_ID, @@IDENTITY);

7) строковые функции (STR, UPPER, LOWER, REPLACE);

8) системные функции(@@ERROR, HOST_ID, @@IDENTITY);

9) статистические функции (@@CONNECTIONS, @@PACK_RECEIVED);

10) функции для работы с типами данных image, text и ntext (PATINDEX, TEXTPTR, TEXTVALID).

Все встроенные функции, как правило, имеют имена, начинающиеся с @@.