Статья: Примеры решения нестандартных задач на T-SQL в 1С

Нумерация строк таблицы (счетчик строк)

Подсчет нарастающего итога по нужным группировкам

Использование курсоров

Использование временных таблиц для получения нужных индексов в соединениях

Обновление таблицы по собственным данным

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

Использование триггеров

 

 

Нумерация строк таблицы (счетчик строк)

Допустим, есть потребность пронумеровать таблицу, в которой есть документ, номер строки, сотрудник, день и какое-то количество (1 и 2)

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

RecordSet.ВыполнитьИнструкцию("If object_id('tempdb..#tempt') is not null drop table #tempt");
RecordSet.ВыполнитьИнструкцию("Create table #tempt (id int, iddoc char(9), lineno int,  emp char(9), day char(11)
                              , num1 numeric(10,2), num2 numeric(10,2)");

ТекстЗапроса = "
|Insert into #tempt
|Select 0 Ид
|Запрос.Документ
...

|From (
...
|) as Запрос
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

Пронумеровываем строки в служебной колонке id.

ТекстЗапроса = " 
|Declare @i int
|Set @i = 0
|Update #tempt Set id = @i, @i = @i + 1 
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

 

Подсчет нарастающего итога по нужным группировкам    <в начало>

Теперь нам необходимо подсчитать нарастающий итог по количеству 2 в разрезе сотрудника и дня. Результат помещаем во временную таблицу #temptsum.

ТекстЗапроса = "
|Select a.id
|, a.iddoc
|, a.emp
|, a.day
|, a.num1
|, Sum(isnull(b.num2, 0)) + max(a.num2) sumnum2 into #temptsum 
|From #tempt a (nolock)
|Left join #tempt b (nolock) on b.id < a.id and b.emp = a.emp and b.day = a.day
|Group by a.id, a.iddoc, a.emp, a.day, a.day, a.num1
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

 

Использование курсоров    <в начало>

Использование курсоров не приветствуется, но иногда без них обойтись трудно. Например, в случаях списания остатков партий со сложными условиями и расчетами, сложных распределений. При использовании курсоров отмечается падение производительности и повышенная нагрузка на сервер. Но если предлагается выбрать между перетягиванием недообработанных данных на клиента с последующей возней в таблице значений и обработкой данных на сервере с помощью курсора - я выберу последний вариант. Хорошо, что есть возможность использования полноценного T-SQL в 1С 7.7, один из примеров которого вы сможете увидеть ниже.

Теоретические основы можно посмотреть по следующим ссылкам (ссылки даны для SQL 2005, можно переключить):

Курсоры (ядро СУБД)

Курсоры (Transact-SQL)

Мне понравилась статья, где достаточно просто и с примерами рассказывается о курсорах в SQL Server.

Приведу практический пример, пусть с изъянами и не вылизанный, который писался несколько лет назад.

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

#tempra - это таблица движений регистра, которая содержит помимо товара, заявки, адреса и других измерений еще зону сборки и часть заявки (реквизиты регистра). После обработки таблицы (а именно заполнения реквизита "Часть заявки", поле в запросе именуется PZ) останется сделать апдейт таблицы движений и документ будет готов для обработки складом.

TransporterRack - служебная таблица, в которой указано какие стеллажи (Rack) и ячейки (Cell) к какой зоне относятся (ZoneId) и к какой части (первой или второй) заявки будут принадлежать (Part). Гравитация (место сборки больших количеств товара) имеет номер стеллажа '00' и обрабатывается отдельно, делится по ячейкам, а не по стеллажам.

ТекстЗапроса = "
|DECLARE @MaxPart TINYINT
|DECLARE @CountLine INT
|DECLARE @Zone char(9)
|
|if object_id('tempdb..#tempzone') is not null drop table #tempzone
|
|SELECT Zone, NumZone, Count(*) CountLine INTO #tempzone
|FROM #tempra
|GROUP BY Zone, NumZone  
|ORDER BY NumZone
|
|DECLARE Cur Cursor FOR SELECT Zone, CountLine FROM #tempzone
|OPEN Cur
|FETCH NEXT FROM Cur INTO @Zone, @CountLine
|WHILE (@@fetch_status <> -1)
|BEGIN
|
|IF @CountLine > 10
|BEGIN
|
|SET @MaxPart = (SELECT MAX(Pz) FROM #tempra (NOLOCK))
|
|UPDATE #tempra
|SET PZ = @MaxPart + 1
|FROM #tempra T
|LEFT JOIN dbo.TransporterRack TR (NOLOCK) ON T.Zone = TR.ZoneId AND T.Rack = TR.Rack
|WHERE T.Zone = @Zone AND TR.Rack <> '00' AND TR.Part = 1
|
|UPDATE #tempra
|SET PZ = @MaxPart + 1
|FROM #tempra T
|LEFT JOIN dbo.TransporterRack TR (NOLOCK) ON T.Zone = TR.ZoneId AND T.Rack = TR.Rack AND T.Cell = TR.Cell
|WHERE T.Zone = @Zone AND TR.Rack = '00' AND TR.Part = 1
|
|SET @MaxPart = (SELECT MAX(Pz) FROM #tempra (NOLOCK))
|
|UPDATE #tempra
|SET PZ = @MaxPart + 1
|FROM #tempra T
|LEFT JOIN dbo.TransporterRack TR (NOLOCK) ON T.Zone = TR.ZoneId AND T.Rack = TR.Rack
|WHERE T.Zone = @Zone AND TR.Rack <> '00' AND TR.Part = 2
|
|UPDATE #tempra
|SET PZ = @MaxPart + 1
|FROM #tempra T
|LEFT JOIN dbo.TransporterRack TR (NOLOCK) ON T.Zone = TR.ZoneId AND T.Rack = TR.Rack AND T.Cell = TR.Cell
|WHERE T.Zone = @Zone AND TR.Rack = '00' AND TR.Part = 2
|
|END
|ELSE
|BEGIN
|
|SET @MaxPart = (SELECT MAX(Pz) FROM #tempra (NOLOCK))
|
|UPDATE #tempra
|SET PZ = @MaxPart + 1
|WHERE Zone = @Zone AND Rack IS NOT NULL
|
|END
|
|SET @MaxPart = (SELECT MAX(Pz) FROM #tempra (NOLOCK))
|
|UPDATE #tempra
|SET PZ = @MaxPart + 1
|WHERE PZ = 0 AND Zone = @Zone 
|
|FETCH NEXT FROM Cur INTO @Zone, @CountLine
|END
|CLOSE Cur
|DEALLOCATE cur
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

 

Использование временных таблиц для получения нужных индексов в соединениях    <в начало>

Если необходимо соединяться с большой таблицей, не имеющей нужных индексов, то быстрее будет поместить нужный блок данных в свою временную таблицу для дальнейшей обработки. Ниже приведен практический пример апдейта "матрицы" по данным регистра продаж.

//Сбор сведений о продажах, создаем временную таблицу и помещаем в нее данные о продажах за период
    
ТекстЗапроса = "
|if exists (select * from tempdb..sysobjects where id = object_id('tempdb..#TempSale'))
|drop table #TempSale
|create table #TempSale (tov char(9), apt char(9), kol numeric(14,3) primary key clustered (tov, apt))
|";
    
RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

ТекстЗапроса = "
|INSERT INTO #TempSale
|SELECT РозницаУпрОбороты.Товар
|, РозницаУпрОбороты.Подразделение
|, РозницаУпрОбороты.КоличествоРасход
|FROM $РегистрОбороты.РозницаУпр(:ДатаН,
|:ДатаК~,,,
|(Движение IN ($Перечисление.ДвиженияВРознице.Продажа, $Перечисление.ДвиженияВРознице.ПродажаБН)),
|(Товар, Подразделение),
|Количество) AS РозницаУпрОбороты
|";

ДатаК = ТекущаяДата() - 1;
ДатаН = ДобавитьМесяц(ДатаК, -3);    

RecordSet.УстановитьТекстовыйПараметр("ДатаН",    ДатаН);
RecordSet.УстановитьТекстовыйПараметр("ДатаК",    ДатаК);

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

//Обновление данных таблицы ("матрицы")

ТекстЗапроса = "
|UPDATE MatrixCosm 
|SET Sale = CASE WHEN TS.Kol > 0 THEN 1 ELSE 0 END
|FROM MatrixCosm MC
|LEFT JOIN #TempSale AS TS With (NOLOCK) ON TS.Tov = MC.Tov AND TS.Apt = MC.Apt
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

 

Обновление таблицы по собственным данным    <в начало>

У Вас не получится обновить таблицу соединив ее с самой собой. На помощь снова придет временная таблица.

Ниже приведен пример апдейта таблицы справочника (в частности, запись сведений о родителе) с использованием данных самого справочника и таблицы, сформированной из внешнего источника данных (Category). Эта таблица повторяет иерархию справочника и содержит доп. данные для обновления.

ТекстЗапроса = "
|if object_id('tempdb..#tempcat') is not null drop table #tempcat
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

ТекстЗапроса = "
|SELECT SC.Id                    SCId
|, ISNULL(SCP.Id, '     0   ')   ParentId
|, Cat.IsFolder                  IsFolder
|, Cat.Descr                     Descr
|, Cat.IdMenu                    IdMenu into #tempcat
|FROM $Справочник.КатегорииНоменклатуры SC (NOLOCK)
|LEFT JOIN Category Cat (NOLOCK) ON $SC.ИД = Cat.Id
|LEFT JOIN Category CatP (NOLOCK) ON Cat.Parent = CatP.Id
|LEFT JOIN $Справочник.КатегорииНоменклатуры SCP (NOLOCK) ON CatP.Id = $SCP.ИД
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

ТекстЗапроса = "
|UPDATE $Справочник.КатегорииНоменклатуры
|SET ParentId = t.ParentId
|, IsFolder = t.IsFolder
|, Descr = t.Descr
|, $Справочник.КатегорииНоменклатуры.ИДМеню = t.IdMenu
|FROM $Справочник.КатегорииНоменклатуры SC
|LEFT JOIN #tempcat t ON SC.Id = t.SCId
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);
   
ТекстЗапроса = " 
|if object_id('tempdb..#tempcat') is not null drop table #tempcat
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

 

Использование хранимых процедур и функций    <в начало>

Иногда возникает необходимость в запросах использовать функции, а для обработки, вывода данных - процедуры. Теоретические основы можно почитать, например, по ссылке.

Рассмотрим классический пример - перевод из 36-ной системы счисления в 10-ную. Допустим, нам нужно записывать ID новых элементов, хранимых в базе 1С. Для записи нового элемента его ID нужно увеличить на 1.

Тексты запросов проверки существования и создания функции:

Запрос.ВыполнитьИнструкцию("IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Convert36To10')
                            DROP FUNCTION Convert36To10");

ТекстЗапроса = "
|CREATE FUNCTION Convert36To10(@Res36 CHAR(9)) RETURNS int
|AS
|begin
|DECLARE @j INT
|DECLARE @Deci INT
|DECLARE @Arr36 CHAR(36)
|SELECT @Arr36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
|SELECT @Deci = 0
|SELECT @j = 1
|while @j <= LEN(LTRIM(RTRIM(@Res36)))
|begin
|if @j <> 1
|SELECT @Deci = @Deci*36
|SELECT @Deci = @Deci + CHARINDEX(SUBSTRING(LTRIM(RTRIM(@Res36)), @j,1),@Arr36) -1
|SELECT @j = @j+1
|end
|RETURN(@Deci);
|end
|";

Запрос.ВыполнитьИнструкцию(ТекстЗапроса);

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

ТекстЗапроса = "
|declare @rank int,
|@id char(9)
|declare rank_cursor cursor
|for select id
|from " + БазаИсточник + ".dbo." + ТаблицаИсточник + " as t
|where t.id not in (
|select tid
|from " + ВремТаблица + ")
|order by id
|open rank_cursor
|fetch next from rank_cursor into @id
|set @rank = (select maxid from " + БазаПриемник + ".dbo._1suidctl where TYPEID = :ИД)
|if @rank is null select @rank = 0
|set @rank = dbo.Convert36To10(@rank)
|while (@@fetch_status <> -1)
|begin
|select @rank, @id
|set @rank = @rank + 1
|insert into " + ВремТаблица + " values (dbo.sp_tohex(@rank, 6), @id, 1)
|fetch next from rank_cursor into @id
|end
|close rank_cursor
|deallocate rank_cursor  
|";

Запрос.УстановитьТекстовыйПараметр("ИД", СокрЛП(СпрО.Приемник.ИД)); 

Запрос.ВыполнитьИнструкцию(ТекстЗапроса);

 

Использование триггеров    <в начало>

Можно ли в своей базе 1С "поселить" триггеры? Да, конечно можно. Главное - помнить о том, что они существуют.

Теоретические основы с примерами по триггерам можно почитать, например, тут и тут.

Рассмотрим пример изменения данных по остатку в таблице исходя из данных по плановым и фактическим данным (находятся в той же таблице).

ТекстЗапроса = "
|IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Plan_goodsUpdate]'))
|SELECT 1
|ELSE
|SELECT 0
|";

ТЗ = RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

Существует = ТЗ.ПолучитьЗначение(1, 1);

Если Существует = 0 Тогда
    ТекстЗапроса = "
    |CREATE TRIGGER [Plan_goodsUpdate] ON [dbo].[Plan_goods]
    |AFTER UPDATE
    |AS
    |BEGIN
    |UPDATE Plan_Goods with (UPDLOCK, HOLDLOCK)
    |SET Rest_kol = CASE WHEN PlanG.Plan_kol - Ins.Fact_kol > 0 THEN PlanG.Plan_kol - Ins.Fact_kol ELSE 0 END
    |FROM Plan_Goods AS PlanG
    |INNER JOIN Inserted AS Ins ON PlanG.Idd = Ins.Idd
    |END
    |";

    RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);
КонецЕсли;

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

НачалоМесяца = Строка(ДатаГод(НачМесяца(ТекущаяДата()))) + "-" + Прав("0"+Строка(ДатаМесяц(НачМесяца(ТекущаяДата()))),2)
+ "-" + Прав("0"+Строка(ДатаЧисло(НачМесяца(ТекущаяДата()))),2);

RecordSet.ВыполнитьИнструкцию("IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[SaleDelete]'))
                               DROP TRIGGER [dbo].[SaleDelete]");

ТекстЗапроса = "
|CREATE TRIGGER [SaleDelete] ON [dbo].[Sale]
|AFTER DELETE
|AS
|BEGIN
|UPDATE Plan_Goods with (UPDLOCK, HOLDLOCK)
|SET Fact_kol = Fact_kol - Del.Kol
|FROM Plan_Goods AS PlanG
|INNER JOIN (
|SELECT SUM(Kol) Kol
|, Buyer
|, Tov
|FROM Deleted
|GROUP BY Buyer
|, Tov
|) AS Del ON PlanG.Customer_id = Del.Buyer AND PlanG.Goods_id = Del.Tov
|WHERE PERIOD = '" + НачалоМесяца + "'
|END
|";

RecordSet.ВыполнитьИнструкцию(ТекстЗапроса);

Аналогичным образом создаем триггер "AFTER INSERT", а для "AFTER UPDATE" делаем UPDATE дважды в одном запросе: "FROM Deleted" и "FROM Inserted".

 

Продолжение следует...

 

Другие статьи по прямым запросам:

Ускоряем регистрацию объектов в МОД (для SQL)

Проверка дублей строк с помощью 1С++

Аналог ON DUPLICATE KEY UPDATE в MS SQL

Как использовать УРБД в отличающихся конфигурациях

Как написать прямой запрос в 1С (DBF, 1sqlite)

Запросы в 1С к двум базам одновременно (DBF, OLE DB)

Как написать прямой запрос в 1С (SQL) с помощью 1С++

Онлайн резервирование товаров на складе (online reservation)