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

Цель: формирование практических умений и навыков создания хранимых процедур; применения входных и выходных параметров хранимой процедуры; создания функции.

Закрепить практические умения и навыки работы с операторами манипулирования данными (DML).

Краткие теоретические сведения

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

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

CREATE PROCEDURE|PROC <имя процедуры>

[[<имя параметра><тип данных>[VARYING]

[=<значение по умолчанию>][OUTPUT][,]]

[<имя параметра><тип данных>[VARYING]

[=<значение по умолчанию>][OUTPUT][,]]

...]

[WITH

RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION]

[FOR REPLICATION]

AS

<код>

GO

Объявление параметра требует указания следующей информации:

- имя;

- тип данных;

- значение по умолчанию;

- направление (входной или выходной).

Синтаксис:

@имя_параметра [AS] тип_данных [=умолчание|NULL][VARYING][OUTPUT|OUT]

Имя параметра должно начинаться со знака @, а также недопустимо использование пробелов.

Запуск хранимой процедуры:

EXEC|EXECUTE <имя хранимой процедуры> <входные параметры>

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

Существуют два типа пользовательских функций:

- функции, возвращающие скалярные значения;

- функции, возвращающие таблицы.

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

CREATE FUNCTION <имя функции>

([<@имя_параметра><тип данных>[=<умолчание>][,…n]])

RETURNS {<тип данных>|TABLE}

[WITH {ENCRYPTION|SCHEMABINDING}]

AS

BEGIN

[<операторы функции>]

{RETURN <тип данных, определенный в операторе RETURNS>|

RETURN (<SELECT-выражение>)}

END

Применение функции осуществляется как для системных, а именно имя функции и в скобках входные параметры, если они существуют, иначе просто пустые скобки.

Ход работы

1 Для каждой таблицы создать хранимые процедуры:

− для ввода новой записи, в качестве входных параметров будут выступать вводимые значения;

− для изменения значения по определенному условию, в качестве входных параметров – новое значение и старое для поиска;

− для удаление записей по определенному условию, в качестве входных параметров – значение для поиска соответствующих записей;

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

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

2 Создать функцию, возвращающую значение (например, количество клиентов, средняя стоимость заказов и т.д.).

3 Создать функцию, обобщающую информацию из нескольких столбцов таблицы в одну.

4 Применить хранимые процедуры и функции.

5 Оформить подробный отчет и сделать вывод по выполненной работе.

 

Контрольные вопросы

1 Что представляет хранимая процедура, функция? Назначение.

2 Синтаксис создания хранимой процедуры, функции. Отличия.

3 Виды процедур, функций, используемых в MS SQL Server.

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

5 Назначение входных и выходных параметров.

6 Объявление переменных в хранимой процедуру.

7 Оператор создания хранимой процедуры, функции, использует команды: DDL, DML, DCL.

8 Запуск хранимой, функции. Способы запуска.

9 Что произойдет после выполнения (Compile) команды Create Procedure... или Create Function...?

10 Хранимые процедуры для ввода данных не должна содержать входные параметры. Верно, ли это утверждение?