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

Как уже упоминалось ранее, использование представлений, практически, ничем не отличается от использования обычных таблиц. Тем не менее, представления позволяют больше, чем обычные таблицы. Расмотрим следующий пример. В базе данных имеется таблица, определяющая наличие товаров на складах некоторой фирмы: коды складов, коды товаров, количества товаров. Кроме того, в базе данных находятся накладные, отражающие движение товаров между складами, факты продаж и т.д. Таблица, отражающая состояние складов, является результатом обработки накладных, которые по мере их создания, но не в режиме реального времени, поступают с различных складов на компьютер, хранящий рассматриваемую базу данных. По различным причинам накладные не могут обрабатываться мгновенно и существуют довольно длительные моменты времени, когда таблица состояния складов соответствует только обработанным накладным. Необработанные накладные временно содержат информацию, не проверенную менеджером, контролирующим удаленные на сотни километров склады. Но поскольку, б о льшая часть из непроверенных накладных отражает реальное движение товаров, этой информацией могут пользоваться менеджеры по продажам и закупкам. Поэтому данные о товарах на складах для этих менеджеров необходимо корректировать с учетом необработанных накладных.

Поскольку таблица с накладными довольно большая (особенно в конце месяца) и б о льшая ее часть включает бесполезные для данной задачи обработанные накладные, удобно заранее извлечь из необработанных накладных информацию и поместить ее в отдельную таблицу, отражающую товары, ушедшие и пришедшие на склады. Для записи информации о движении товаров, хранимой в необработанных накладных можно использовать процедуру, похожую на процедуру ЗаписьВКорЗапасы из листинга 21.8.

Листинг 21.8 Процедура заполнения таблицы с информацией из необработанных накладных

1: CREATE PROCEDURE ЗаписьВКорЗапасы

2: /* заполняет таблицу T1N2 товарами из необработанных накладных */

3: AS

4: DECLARE @КодСклада varchar (4)

5: DECLARE @КодТовара varchar (12)

6: DECLARE @КоличТовара int

7: /* объявить курсор */

8: DECLARE Cur CURSOR FOR

9: SELECT КодСклада, КодТовара, SUM(КоличТовара * ПриходРасход)

10: FROM СпецификацииНакладных

11: WHERE ПризнакОбработки = 0

12: GROUP BY КодСклада, КодТовара

13: /* открыть курсор */

14: OPEN Cur

15: /* cчитать данные при помощи курсора */

16: FETCH Cur INTO @КодСклада, @КодТовара, @КоличТовара

17: WHILE @@FETCH_Status = 0

18: BEGIN

19: /* записать данные в таблицу T1N2 */

20: INSERT INTO КорЗапасы (КодСклада, КодТовара, КоличТовара)

21: VALUES (@КодСклада, @КодТовара, @КоличТовара)

22: /* cчитать “следующие” данные при помощи курсора */

23: FETCH Cur INTO @КодСклада, @КодТовара, @КоличТовара

24: END

25: /* закрыть курсор */

26: CLOSE Cur

27: /* удалить курсор из памяти */

28: DEALLOCATE Cur

В строках 4–6 объявляются переменные для считывания данных из курсора Cur, объявленного в строках 8–12. Этот курсор создается для набора, состоящего из кодов складов, кодов товаров и суммарных количеств с учетом приходов (ПриходРасход=1) и расходов (ПриходРасход=1) товаров. Признак обработки (поле ПризнакОбработки) в таблице СпецификацииНакладных, равный значению 0, указывает на то, что накладная не обработана, т.е. ведомость товаров не учитывает движение товара этой накладной.

Запись информации в таблицу T1N2 выполняется, как обычно, в цикле с «продвижением» по записям набора с использованием курсора (строки 17–24). В строках 26 и 28 курсор закрывается и удаляется из памяти.

В листинге 21.9 — код представления Запасы_КорЗапасы, предназначенного для объединения двух таблиц: инвентаризации и учета необработанных накладных.

Листинг 21.9 Представление, объединяющее файлы инвентаризации и учета необработанных накладных

1: CREATE VIEW Запасы_КорЗапасы

2: /* объединение файлов инвентаризации и учета необработанных накладных */

3: AS

4: SELECT КодТовара, КоличТовара, КодСклада

5: FROM Запасы

6: UNION

7: SELECT КодТовара, SUM(КоличТовара), КодСклада

8: FROM КорЗапасы

9: GROUP BY КодТовара, КодСклада

Процедура ВедомостьТоваровСклада (листинг 21.10) формирует ведомость товаров указанного (в аргументе @КодСклада) склада с учетом или без учета необработанных накладных. Во втором случае используется представление Запасы_КорЗапасы (строки 17–23). Таким образом, если учитывать необработанные накладные не нужно, используется только таблица Запасы и Товары (справочник товаров с кодами и наименованиями товаров), в противном случае используется представление Запасы_КорЗапасы.

Листинг 21.10 Процедура формирования ведомости товаров склада

1: CREATE PROCEDURE ВедомостьТоваровСклада

2: /* ведомость товаров на складе без учета (@with_kor = 0) */

3: /* и с учетом (@with_kor < > 0) необработанных накладных */

4: @КодСклада nvarchar(4),

5: @maska nvarchar(10), @LenMaska int, @with_kor int

6: AS

7: IF @with_kor =0

8: /* необработанные накладные не учитываются */

9: SELECT a.НаименТовара, b.КоличТовара

10: FROM Запасы b, Товары a

11: WHERE b.КодТовара =a.КодТовара

12: AND SUBSTRING(a.НаименТовара, 1, @LenMaska)= RTRIM(@maska)

13: AND b.КодСклада =@КодСклада

14: ORDER BY a.НаименТовара

15: ELSE

16: /* необработанные накладные учитываются */

17: SELECT a.НаименТовара, sum(b.КоличТовара)

18: FROM Запасы_КорЗапасы b, t2 a

19: WHERE b.КодТовара =a.КодТовара

20: AND SUBSTRING(a. НаименТовара, 1, @LenMaska)= RTRIM(@maska)

21: AND b. КодСклада =@КодСклада

22: GROUP BY a.НаименТовара

23: ORDER BY 1

В листинге 21.11 представлена процедура СуммыТоваров, также использующая представление Запасы_КорЗапасы. Эта процедура возвращает суммарную инвентаризационную ведомость указанных в списке @SkladList складов для товаров, наименования которых начинаются со строки, совпадающей с маской @maska.

Листинг 21.11 Процедура — ведомость товаров на указанных складах

1: CREATE PROCEDURE СуммыТоваров

2: /* Ведомость товаров на указанных складах */

3: @maska char(20), @with_kor int,

4: @SkladList nvarchar(200), @ilen int

5: AS

6: /* объявления */

7: DECLARE @i smallint

8: DECLARE @ilen1 int

9: DECLARE @LenMaska

10: DECLARE @TempTable TABLE (КодСклада varchar (4) NOT NULL)

11: /* заполнить табличную переменную кодами складов из @SkladList */

12: IF LEN(@SkladList) > 0

13: BEGIN

14: SET @ilen1= LEN(@SkladList)/4

15: SET @i=1

16: WHILE @ilen1 > 0

17: BEGIN

18: INSERT INTO @TempTable (КодСклада)

19: VALUES (SUBSTRING(@SkladList, @i, 4))

20: SET @i=@i+4

21: SET @ilen1=@ilen1-1

22: END

23: END

24: /* для товара с группой @maska сформировать ведомость */

25: SET @LenMaska=LEN(@maska)

26: IF @with_kor=0

27: /* необработанные накладные НЕ учитываются */

28: SELECT a.НаименТовара as 'Наименование',

29: sum(b.КоличТовара) as 'Кол-во', b.КодТовара

30: FROM Товары a, Запасы b

31: WHERE a.КодТовара = b.КодТовара

32: AND SUBSTRING(a. НаименТовара, 1, @LenMaska)= RTRIM(@maska)

33: AND b.C_WHERE IN (SELECT C_WHERE FROM @TempTable)

34: GROUP BY a.НаименТовара, b.КодТовара

35: ORDER BY a.НаименТовара, b.КодТовара

36: ELSE

37: /* необработанные накладные УЧИТЫВАЮТСЯ */

38: SELECT a.НаименТовара as 'Наименование',

39: sum(b.КоличТовара) as 'Кол-во', b.КодТовара

40: FROM Товары a, Запасы_КорЗапасы b

41: WHERE a.КодТовара = b.КодТовара

42: AND SUBSTRING(a.НаименТовара, 1, @LenMaska) = RTRIM(@maska)

43: AND b. КодСклада IN (SELECT КодСклада FROM @TempTable)

44: GROUP BY a.НаименТовара, b.КодТовара

45: ORDER BY a.НаименТовара, b.КодТовара

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