Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Vladimir_V.
DeColo®es, спасибо за статью, это я читал, но хотелось бы увидеть и менее замороченные варианты :-)
Обычно в поисках "менее замороченных" вариантов, все равно делают подобное по "громоздкости" решение. Ели не "хуже".
27 ноя 12, 08:01    [13533768]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
aleks2, пример интересный!
Счетчиков несколько (Category) и нумерация в каждом году с ноля, но к сожалению, при удалении какого-нибудь документа, номера всех документов поплывут :-).
27 ноя 12, 10:39    [13534489]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
aleks2
Guest
Vladimir_V.
aleks2, пример интересный!
Счетчиков несколько (Category) и нумерация в каждом году с ноля, но к сожалению, при удалении какого-нибудь документа, номера всех документов поплывут :-).

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

2. А так-то, процедура, запускаемая в джобе, и расставляющая нумера документов, хде их нема.
27 ноя 12, 11:28    [13534763]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
aleks2, эт ясно. Просто констатирую, что к сожалению этот вараинт не подходит. Но думаю, что когда-нибудь пригодится.
27 ноя 12, 11:58    [13535035]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Dima T
Member

Откуда:
Сообщений: 15851
Vladimir_V., сделай как-нибудь как самому больше нравится. Когда начнутся проблемы - переделаешь, может вообще не начнутся.
Это поправить никогда не поздно. Может на SQL2012 когда пересядешь, тут проблема микрософтом порешана наконец-то.

Тут такой же вопрос обсуждался.
27 ноя 12, 12:09    [13535158]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Dima T
Может на SQL2012 когда пересядешь, тут проблема микрософтом порешана наконец-то.

Это как, вводом сиквенсов? В общем-то никто не мешает сделать свои доморощенные сиквенсы хоть на MSSQL 2000:
— делаем вспомогательную табличку с полем IDENTITY;
— если нумерация делается по одному документу за раз, то всё просто — вставляем запись во вспомогательную табличку, считываем оттуда айдентити с помощью функции SCOPE_IDENTITY, используем как значение сиквенса;
— если делается нумерация нескольких документов за раз и используется SQL 2005/2008, то вставляем несколько записей во вспомогательную таблицу и одновременно извлекаем их значения во временную таблицу при помощи конструкции OUTPUT. Во временной таблице будет лежать нужное количество значений сиквенса, используем их;
— если делается нумерация нескольких документов за раз и используется SQL 2000, то можно добавить во вспомогательную табличку какой-нибудь GUID, вставлять несколько записей и потом извлекать их по условию WHERE guid_column = @guid_value.
27 ноя 12, 13:40    [13535926]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
Кто-то шнобелевскую получит ))
27 ноя 12, 14:10    [13536276]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
Гость333, я правильно понял Ваше решение? В моем случае для каждого типа документа нужно создавать вспомогательную таблицу и при наступлении нового года еще каждому по таблице и так далее?
27 ноя 12, 14:16    [13536364]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Vladimir_V.,

Вам нужно завести одну таблицу ([Тип документа], [Год], [Текущий номер]) и пользоваться ей для генерации номеров документов.
27 ноя 12, 14:56    [13536896]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
invm, да ладно? Покажете как?
27 ноя 12, 15:02    [13536954]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Vladimir_V.
invm, да ладно? Покажете как?
https://www.sql.ru/forum/actualsearch.aspx?search=%ED%F3%EC%E5%F0%E0%F6%E8%FF+%E4%EE%EA%F3%EC%E5%ED%F2%EE%E2&sin=0&bid=1&a=&ma=0&dt=-1&s=1&so=1
27 ноя 12, 16:13    [13537679]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Vladimir_V.
Гость333, я правильно понял Ваше решение? В моем случае для каждого типа документа нужно создавать вспомогательную таблицу и при наступлении нового года еще каждому по таблице и так далее?

Да, наверное так. На самом деле, я бы не стал так делать :) Я просто показал пример, как можно заменить сиквенсы. Минусы такого подхода (и сиквенсов) в том, что это нетранзакционный подход. В статье DeColores изложен грамотный подход.
27 ноя 12, 16:24    [13537822]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
А в таком способе есть проблемы?
CREATE PROCEDURE Counters
 @ID int,
 @ResultValue int OUTPUT
AS
BEGIN
 SET NOCOUNT ON
 UPDATE SeqNumbers SET @ResultValue=LastValue=LastValue+1 WHERE ID=@ID
END
28 ноя 12, 10:38    [13540828]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
aleks2
Guest
Vladimir_V.
А в таком способе есть проблемы?
CREATE PROCEDURE Counters
 @ID int,
 @ResultValue int OUTPUT
AS
BEGIN
 SET NOCOUNT ON
 UPDATE SeqNumbers SET @ResultValue=LastValue=LastValue+1 WHERE ID=@ID
END

Да. Конкуренция между параллельными вызовами. Нада так
ALTER PROCEDURE dbo.Counters
AS
BEGIN
  SET NOCOUNT ON;

  declare @ret int;

  exec @ret = sp_getapplock @Resource = 'dbo.Counters'
     , @LockMode =  'Exclusive' 
     , @LockOwner = 'Transaction'
     , @LockTimeout = 0;

 if @ret < 0 return @ret;
 
 declare @num int = isnull((select min(DocNum) from TableWithDocs), 0);
  
 -- ну, логику обнуления в начале сезона сами напишите...
  
 UPDATE TableWithDocs SET @num=DocNum=@num+1 WHERE DocNum is null;

 exec @ret = sp_releaseapplock @Resource = 'dbo.Counters', @LockOwner = 'Transaction';
 
 return 0;
END
28 ноя 12, 11:26    [13541195]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
aleks2,

DocNum = min(DocNum) +1

Как это?
28 ноя 12, 13:18    [13542251]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
И насчет того, что в этом примере проблема с конкуренцией между параллельными вызовами:
CREATE PROCEDURE Counters
 @ID int,
 @ResultValue int OUTPUT
AS
BEGIN
 SET NOCOUNT ON
 UPDATE SeqNumbers SET @ResultValue=LastValue=LastValue+1 WHERE ID=@ID
END


Понимать как: "Несколько запросов могут получить одинаковое значение LastValue"?
28 ноя 12, 15:23    [13543519]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Dima T
Member

Откуда:
Сообщений: 15851
Vladimir_V.
И насчет того, что в этом примере проблема с конкуренцией между параллельными вызовами:
CREATE PROCEDURE Counters
 @ID int,
 @ResultValue int OUTPUT
AS
BEGIN
 SET NOCOUNT ON
 UPDATE SeqNumbers SET @ResultValue=LastValue=LastValue+1 WHERE ID=@ID
END


Понимать как: "Несколько запросов могут получить одинаковое значение LastValue"?

Да, так и понимать.

В фоксе такую проблему решают через проверку количества записей которые изменил UPDATE. Если в MS SQL есть что-то подобное, то можно так (синтаксис частично фокспрошный)
do while .T. && зацикливаем
 SET @ResultValue=select LastValue from SeqNumbers where ID=@ID
 UPDATE SeqNumbers SET LastValue=@ResultValue+1 WHERE ID=@ID and LastValue=@ResultValue
 if _tally > 0 && _tally служебная переменная - количество записей измененных UPDATE
   exit && выход из цикла
 endif
enddo

только тут немного допилить надо с обработкой варианта когда нужного @ID вообще нет.
Такое будет работать если у MS SQL есть что-то аналогичное фоксовому _tally
28 ноя 12, 16:50    [13544478]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
aleks2
Guest
Vladimir_V.
aleks2,

DocNum = min(DocNum) +1

Как это?

Ну напиши max?
28 ноя 12, 18:54    [13545398]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
aleks2, всмысле ошибка была или я не понял идеи?
28 ноя 12, 19:21    [13545548]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Dima T
Vladimir_V.
И насчет того, что в этом примере проблема с конкуренцией между параллельными вызовами:
CREATE PROCEDURE Counters
 @ID int,
 @ResultValue int OUTPUT
AS
BEGIN
 SET NOCOUNT ON
 UPDATE SeqNumbers SET @ResultValue=LastValue=LastValue+1 WHERE ID=@ID
END


Понимать как: "Несколько запросов могут получить одинаковое значение LastValue"?

Да, так и понимать.

Шо? И вы можете привести здесь репро?

Vladimir_V.,
Понимайте так, что при более-менее интенсивной многопользовательской работе у вас будет постоянно висеть очередь из блокировок, пытающихся захватить строку в таблице SeqNumbers.
28 ноя 12, 22:04    [13546268]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
Гость333, т.е. двух одинаковых номеров не будет?
Они будут ждать и дождутся или что-то страшнее?
28 ноя 12, 22:17    [13546322]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Vladimir_V.
Гость333, т.е. двух одинаковых номеров не будет?

Проведите эксперимент.
Первоначальное создание объектов БД:
CREATE TABLE dbo.SeqNumbers(ID INT PRIMARY KEY, LastValue INT);
GO
INSERT dbo.SeqNumbers VALUES(1, 0);
INSERT dbo.SeqNumbers VALUES(2, 234);
INSERT dbo.SeqNumbers VALUES(3, 17);
GO
CREATE PROCEDURE dbo.Counters
  @ID INT,
  @ResultValue INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  UPDATE dbo.SeqNumbers SET @ResultValue=LastValue=LastValue+1 WHERE ID=@ID;
END;
GO

В новом окне SSMS запустите запрос получения очередного номера для ID = 2:
DECLARE @ResultValue INT;

BEGIN TRANSACTION;

EXEC dbo.Counters
  @ID = 2,
  @ResultValue = @ResultValue OUTPUT;
  
SELECT @ResultValue;


В другом окне запустите точно такой же запрос. Он будет висеть в ожидании получения блокировки, пока в первом окне транзакция не будет завершена (либо commit, либо rollback). Найдите условия, при которых в обоих окнах будет выведено одинаковое значение номера.

Vladimir_V.
Они будут ждать и дождутся или что-то страшнее?

Всё зависит от. Решите такую задачку: в супермаркете работает одна касса (таблица SeqNumbers). К очереди в кассу подходят покупатели (запросы к SeqNumbers). Кассир обслуживает одного покупателя в среднем за 1,5 минуты. А новые покупатели занимают место в очереди в среднем раз в минуту. Покупатели будут ждать и дождутся, или ... ?
29 ноя 12, 12:50    [13548611]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
Гость333, огромное Вам спасибо за такое наглядное объяснение!

DECLARE @ResultValue INT;

SET @ResultValue = 234
While @ResultValue < 2000000
Begin
  EXEC dbo.Counters
  @ID = 2,
  @ResultValue = @ResultValue OUTPUT;
  Insert into NumbersTable (Number) Values(@ResultValue);
END
29 ноя 12, 20:28    [13552485]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
Гость333, сорвался недописанный ответ ))

1 момент!
29 ноя 12, 20:31    [13552499]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать ежегодно обнуляемую нумерацию для документов?  [new]
Vladimir_V.
Member

Откуда:
Сообщений: 85
Создал я такую таблицу:
CREATE TABLE [dbo].[NumbersTable] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Number] [int] NOT NULL 
) ON [PRIMARY]
GO

И запустил в "SQL Query Analyzer" параллельно 4 следующих цикла (без транзакций):
DECLARE @ResultValue INT;

SET @ResultValue = 234
While @ResultValue < 2000000
Begin
  EXEC dbo.Counters
  @ID = 2,
  @ResultValue = @ResultValue OUTPUT;
  Insert into NumbersTable (Number) Values(@ResultValue);
END

Вставилось почти 2 млн. записей, после чего я выполнил запрос...
SELECT Number, COUNT(*) FROM NumbersTable GROUP BY Number HAVING COUNT(*) > 1

... и он не вернул ни одного повтора!

Why?
29 ноя 12, 20:43    [13552553]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить