Создание и работа с хранимыми процедурами

Для реализации клиентского приложения, работающего с базой данных «Реализация товаров» и отражающего учет продажи товаров разных типов в некотором супермаркете, необходимо создать две хранимые процедуры с параметрами, которые реализуют следующие функции:

Рис. 17. Результат выполнения запроса

- формирует список товаров с их реквизитами (название товара, цена приобретения, цена реализации) для заданного вида товаров,

- формирует ведомость прибыли от реализации каждого товара заданного вида за определенный период.

Для создания хранимых процедур можно использовать либо язык T-SQL, либо средство созданияManagement Studio.

Создание процедуры proc_goods с входным параметром (вид товара) и значениями по умолчанию для формирования списка товаров с их реквизитами на языке T-SQL выглядит следующим образом:

CREATE PROCEDURE [dbo].[proc_goods]

@par_name_type_goods varchar(50) ='мебель'

AS

SELECT dbo.Goods.name_goods as товар,

dbo.Goods.price_acq as цена_приобр,

dbo.Goods.price_rel as цена_реал

FROM dbo.Goods, dbo.Type_goods

Where dbo.Goods.id_type_goods = dbo.Type_goods.id_type_goods and

dbo.Type_goods.name_type_goods=@par_name_type_goods

Создание процедуры proc_real_period с входными параметрами (вид товара, даты периода) и значениями по умолчанию для формирования ведомости прибыли от реализации каждого товара заданного вида за определенный период на языке T-SQL выглядит следующим образом:

CREATE PROCEDURE [dbo].[proc_real_period]

@par_name_type_goods varchar(50)='мебель',

@date_begin varchar(20)='01.01.2008',

@date_end varchar(20)='31.12.2008'

AS

SELECT dbo.Type_goods.name_type_goods as тип,

dbo.Goods.name_goods as товар,

sum((dbo.Goods.price_rel-

dbo.Goods.price_acq)*dbo.Realization.amount) AS Прибыль

FROM dbo.Goods, dbo.Type_goods, dbo.Realization

WHERE (dbo.Realization.date_real >= @date_begin) AND

(dbo.Realization.date_real <= @date_end) and

dbo.Type_goods.name_type_goods=@par_name_type_goods and

dbo.Goods.id_goods = dbo.Realization.id_goods and

dbo.Goods.id_type_goods = dbo.Type_goods.id_type_goods

GROUP BY dbo.Type_goods.name_type_goods, dbo.Goods.name_goods

Заметим, что в данных процедурах для полей под SELECTом применены псевдонимы на русском языке, что связано с дальнейшим их использованием конечным пользователем при работе с клиент-серверным приложением.

Рассмотрим, как использовать Management Studio для создания хранимых процедур. Заметим, что при создании хранимой процедуры с помощью Management Studio, тем не менее необходимо знать синтаксис операторов языка T-SQL.

В качестве примера создадим процедуру proc_real_period. Для этого (рис. 18)следует открыть список хранимых процедур для выбранной базы данных в Обозревателе объектов, щелкнуть правой кнопкой мыши на объекте Хранимые процедуры и выбрать пункт контекстного меню Создать хранимую процедуру (New Stored Procedure). После этого справа откроется окно (с шаблоном) для ввода текста хранимой процедуры, в котором надо убрать лишние строки (например, комментарии) и ввести код процедуры, строго соблюдая синтаксис языка T-SQL, как это изображено на рисунке 19.

Рис. 18. Вызов функции для создания процедуры

Рис. 19. Окно для ввода текста процедуры

Кнопка Management Studio служит для проверки синтаксиса созданной процедуры и помещения ее в список пользовательских хранимых процедур текущей базы данных. Для того, чтобы увидеть вновь созданную процедуру в этом списке необходимо воспользоваться пунктом Обновить контекстного меню.

Для проверки корректности работы хранимой процедуры надо щелкнуть правой кнопкой мыши на имени этой процедуры и выбрать из контекстного меню пункт Выполнить хранимую процедуру (рис. 20). ТогдаManagement Studio предоставит окно для ввода значений параметров (рис. 21). После нажатия на кнопку «Ок» Management Studio выполнит процедуру и предоставит результаты в специальном окне, пример которого приведен на рис. 22.

Рис. 20. Контекстное меню

Рис. 21. Окно для ввода значений параметров процедуры

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

В Management Studio есть также возможность редактирования хранимой процедуры. Для этого надо щелкнуть правой кнопкой мыши на имени этой процедуры и выбрать из контекстного меню пункт Изменить (рис. 20).

Дата добавления: 2015-07-08