Цель: формирование практических умений и навыков создания хранимых процедур; применения входных и выходных параметров хранимой процедуры; создания функции.
Закрепить практические умения и навыки работы с операторами манипулирования данными (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 Хранимые процедуры для ввода данных не должна содержать входные параметры. Верно, ли это утверждение?