Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Исключить рекурсивный вызов из процедуры  [new]
Svetaahm
Member

Откуда:
Сообщений: 7
+
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
===========================================================================================================================================================================================================================
-- Author:		dgtfgh
-- Create date:	
-- ALTER DATE:
--		28.11.2018 gfhgfh исправление c уровнем рекурсии при поиске пропущеного номера
--		17.09.2018, 13.09.2018 gfjhjhgjbn - номер документа первый незанятый в течение года  
--		12.12.2017
-- Description:	Формирует номер документа по ПУ
-- ===========================================================================================================================================================================================================================
ALTER PROCEDURE [XAF].[DD_Generate_Number_For_Device_Documents]
AS
BEGIN
	SET NOCOUNT, XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
	SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT OFF

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня 
-- =======================================================================================================================
	DECLARE @date datetime;
	-- округляем дату до 00:00 и вычитаем 1 день
	SELECT @Date = DATEADD(day, -1, CONVERT(varchar(8), GETDATE(), 112));

	DELETE XAF.CD_Bookings_Doc_Number
	WHERE D_Date < @Date
-- =======================================================================================================================
	
	DECLARE @DocNumbers TABLE(N_Number INT)
	DECLARE @NextNumber INT, 
			@MaxNextNumber INT 
 
	-- получаем все используеммые номера за текущий год 
	INSERT INTO @DocNumbers
	SELECT
		CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END  -- только коректные номера 
	FROM dbo.DD_Docs dd 
		INNER JOIN dbo.DS_Docum_Types ddt
			ON ddt.LINK = dd.F_Docum_Types
				AND ddt.C_Const IN ('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
	WHERE YEAR(dd.D_Register_Date) = YEAR(GETDATE()) 
	--order by CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END
	-- максимальный из используеммых и забронированных номеров
	SELECT 	@MaxNextNumber = ISNULL(MAX(t.MaxNumber), 0) +1
	--SELECT 	@MaxNextNumber = ISNULL(MAX(t.MaxNumber), 0) +1
	FROM (	SELECT ISNULL(MAX(N_Number), 0) AS MaxNumber FROM @DocNumbers dn 
			UNION
			SELECT ISNULL(MAX(CAST(C_Number AS INT)), 0) FROM XAF.CD_Bookings_Doc_Number 
			WHERE YEAR(D_Date) = YEAR(GETDATE()) 
		) t

	;
	WITH Numbers(Number) AS 
	(
		SELECT 1
        UNION ALL
		SELECT Number + 1 FROM Numbers WHERE Number < @MaxNextNumber
	)
	SELECT TOP 1
			@NextNumber = n.Number 
	FROM Numbers n
		LEFT JOIN @DocNumbers dn 
			ON n.Number = dn.N_Number
		LEFT JOIN XAF.CD_Bookings_Doc_Number bdn
			ON CAST(bdn.C_Number AS INT) = n.Number
			AND YEAR(bdn.D_Date) = YEAR(GETDATE()) 
 WHERE dn.N_Number IS NULL 
		AND bdn.C_Number IS NULL
	OPTION (MAXRECURSION 0)

	INSERT INTO XAF.CD_Bookings_Doc_Number
	SELECT @NextNumber, GETDATE()
	
	SELECT @NextNumber
END



*******************************
Люди добрые помогите решить проблему, без рекурсии пробовал вообще не работает...
Необходимо ускорить процесс выбора свободного номера.
Номера формируются по следующему алгоритму:
1. Очистить таблицу резервных номеров за прошлые сутки
2. Выбрать первый свободный номер в году (не следующий после максимального, а именно первый свободный, если считать начиная с единицы). При этом занятые резервные номера также брать нельзя.
3. Зарезервировать номер в таблице XAF.CD_Bookings_Doc_Number

Сообщение было отредактировано: 3 июл 19, 18:44
3 июл 19, 18:08    [21920154]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
aleks222
Member

Откуда:
Сообщений: 754
Эпический бред.

Сгенерите вы все свои номерки в постоянную таблицу и выбирайте.
3 июл 19, 18:23    [21920171]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
PizzaPizza
Member

Откуда:
Сообщений: 288
Есть несколько вариантов нахождения пропущенных значений.
Рекурсия уж точно тут не нужна как и
-- максимальный из используеммых и забронированных номеров


Например вы можете использовать min()+1 NOT EXISTS ... +1 к вашей выборке, где вы ищите первое пропущенное значение.

WITH u
AS (
    SELECT *
    FROM (
        VALUES (1)
            , (2)
            , (8)
            , (9)
            , (21)
            , (22)
            , (23)
            , (1025)
        ) AS b(nomer)
    )

SELECT min(u.nomer) + 1
FROM u
WHERE NOT EXISTS (
        SELECT *
        FROM u AS u2
        WHERE u2.nomer = u.nomer + 1
        )
4 июл 19, 03:05    [21920396]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1651
Я для подобного такой TABLE VALUE UDF пользуюсь:

CREATE FUNCTION dbo.GenerateRows (@Count INT)
/*
https://yabele.blogspot.com/2017/07/mssql-function-to-generate-empty-rows.html
*/
RETURNS TABLE
AS
RETURN
WITH TenRows AS (
  SELECT * FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(I)
)
SELECT TOP(@Count)
  I = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM
  TenRows T1 CROSS JOIN TenRows T2 CROSS JOIN TenRows T3 CROSS JOIN TenRows T4 CROSS JOIN TenRows T5
/*
SELECT * FROM dbo.GenerateRows(100);
*/
;


А с ней уже EXCLUDE пересечение и забирать MIN значение.

Если 10000 значений мало, добавьте еще сколько надо CROSS JOINов.
4 июл 19, 12:09    [21920635]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
Сруль.
Member

Откуда:
Сообщений: 113
Я не стал разбираться с вашими расчётами.
Если по-большому: таблицу занятых номеров
вы как-то строите? Ведь так?
Постройте вспомогательную таблицу с полем пробелом и
айдентити-инт. Загоните туда 200,000 записей.
При желании-это один инсерт-селект .
Сделайте во вспомог. таблице делит, глядя в таблицу занятых номеров.
Первая запись, что осталась-ваша.
4 июл 19, 15:23    [21920844]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
Svetaahm
Member

Откуда:
Сообщений: 7
Yuri Abele,

Спасибо, вы мне очень помогли!
6 июл 19, 17:09    [21922051]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
Svetaahm
Member

Откуда:
Сообщений: 7
Люди! решила свою проблему вот таким образом:



+
USE [Om*]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [XXX].[DD_GeXXXXXXX]
AS
BEGIN
	SET NOCOUNT, XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
	SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT OFF

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня 
-- =======================================================================================================================
	DECLARE @date datetime;
	-- округляем дату до 00:00 и вычитаем 1 день
	SELECT @Date = DATEADD(day, -1, CONVERT(varchar(8), GETDATE(), 112));

	DELETE XAF.CD_Bookings_Doc_Number
	WHERE D_Date < @Date
-- =======================================================================================================================
	DECLARE @DocNumbers TABLE(C_Number INT)
	DECLARE @NextNumber INT
	DECLARE @MaxNextNumber INT 

 INSERT INTO @DocNumbers
	-- получаем все используеммые номера за текущий год 
	     SELECT df.C_Number
			 FROM

			  (SELECT (V1_100.RN_100-1)*100 +V2_100.RN_100 AS C_Number
              FROM 
              (
                       SELECT (V1.RN - 1)*10 + V2.RN AS RN_100 
                       FROM
                       (
                       SELECT 1 AS RN UNION ALL 
                       SELECT 2 AS RN UNION ALL 
                       SELECT 3 AS RN UNION ALL
                       SELECT 4 AS RN UNION ALL 
                       SELECT 5 AS RN UNION ALL 
                       SELECT 6 AS RN UNION ALL
                       SELECT 7 AS RN UNION ALL 
                       SELECT 8 AS RN UNION ALL 
                       SELECT 9 AS RN UNION ALL 
                       SELECT 10 AS RN
                       ) V1
                       CROSS JOIN
                       (SELECT 1 AS RN UNION ALL 
                       SELECT 2 AS RN UNION ALL 
                       SELECT 3 AS RN UNION ALL
                       SELECT 4 AS RN UNION ALL 
                       SELECT 5 AS RN UNION ALL 
                       SELECT 6 AS RN UNION ALL
                       SELECT 7 AS RN UNION ALL 
                       SELECT 8 AS RN UNION ALL 
                       SELECT 9 AS RN UNION ALL 
                       SELECT 10 AS RN) V2
                ) V1_100
                CROSS JOIN
                (
              SELECT (V1.RN - 1)*10 +V2.RN AS RN_100
              FROM
              (
			  SELECT 1 AS RN UNION ALL 
              SELECT 2 AS RN UNION ALL 
              SELECT 3 AS RN UNION ALL
              SELECT 4 AS RN UNION ALL 
              SELECT 5 AS RN UNION ALL 
              SELECT 6 AS RN UNION ALL
              SELECT 7 AS RN UNION ALL 
              SELECT 8 AS RN UNION ALL 
              SELECT 9 AS RN UNION ALL 
              SELECT 10 AS RN
			  ) V1

              CROSS JOIN 
              (
			  SELECT 1 AS RN UNION ALL 
              SELECT 2 AS RN UNION ALL 
              SELECT 3 AS RN UNION ALL
              SELECT 4 AS RN UNION ALL 
              SELECT 5 AS RN UNION ALL 
              SELECT 6 AS RN UNION ALL
              SELECT 7 AS RN UNION ALL 
              SELECT 8 AS RN UNION ALL 
              SELECT 9 AS RN UNION ALL 
              SELECT 10 AS RN
			  ) V2
              ) V2_100
       ) AS DF
 LEFT JOIN 
 (select distinct C_number,C_number2 from
(
SELECT 
dd.C_NUMBER,  dd.N_Number
,case when dd.C_Number is null or  dd.C_Number = ' '   then 'a' else 'b' end C_number1
,CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END C_number2 -- только коректные номера 
FROM dbo.DD_Docs dd
INNER JOIN dbo.DS_Docum_Types ddt ON ddt.LINK = dd.F_Docum_Types
where ddt.C_Const IN 
('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
and YEAR(dd.D_Register_Date) = YEAR(GETDATE())  ) t
where C_number not in  ('a','БДИ') 

) AS FD
ON DF.C_Number=FD.C_NUMBER
WHERE FD.C_NUMBER IS NULL

	INSERT INTO XAF.CD_Bookings_Doc_Number
	SELECT min(C_Number), GETDATE()  from @DocNumbers	
    SELECT C_Number from XAF.CD_Bookings_Doc_Number
END


Только проблема в том, что в таблицу XAF.CD_Bookings_Doc_Number записывается один и тот же номер, а нужно чтоб следующий из не занятых номеров. Что нужно и куда дописать, чтоб сравнирь @DocNumbers и XAF.CD_Bookings_Doc_Number, если в XAF.CD_Bookings_Doc_Number есть минимальный номер, то записать в таблицу следующий из @DocNumbers

Сообщение было отредактировано: 7 июл 19, 19:08
7 июл 19, 15:10    [21922243]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
aleks222
Member

Откуда:
Сообщений: 754
Зачем весь этот бред?

USE [Om*]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [XXX].[DD_GeXXXXXXX]
AS
BEGIN

SET NOCOUNT;

declare @now date = GETDATE();

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня 
-- =======================================================================================================================
DELETE XAF.CD_Bookings_Doc_Number WHERE D_Date < DATEADD(day, -1, @now );
-- =======================================================================================================================

declare @yearStart date = dateadd( day, 1 - day(@now), dateadd( month, 1 - month(@now), @now ) );

declare @UsedNumbers table( C_number int primary key );

with rn as ( SELECT dd.C_NUMBER
                  , CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END C_number2 -- только коректные номера 
               FROM  dbo.DD_Docs as dd
                     INNER JOIN dbo.DS_Docum_Types as ddt ON ddt.LINK = dd.F_Docum_Types
                     where ddt.C_Const IN ('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
                           and @yearStart <= dd.D_Register_Date and dd.D_Register_Date < dateadd( year, 1, @yearStart )
						   and dd.C_number not in ('a','БДИ')
            ) 
  insert @UsedNumbers
	select C_number2 from rn;

insert XAF.CD_Bookings_Doc_Number
  SELECT min(C_Number) + 1, GETDATE() 
    from @UsedNumbers as t1
	where not exists( select * from @UsedNumbers as t2 where t2.C_number = t1.C_number + 1 )
	      and not exists( select * from XAF.CD_Bookings_Doc_Number as t2 where t2.C_number = t1.C_number + 1 )
;

SELECT C_Number from XAF.CD_Bookings_Doc_Number;

END
7 июл 19, 16:12    [21922252]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
aleks222
Member

Откуда:
Сообщений: 754
Впрочем, так будет православнее

ALTER PROCEDURE [XXX].[DD_GeXXXXXXX]
AS
BEGIN

SET NOCOUNT;

declare @now date = GETDATE();

-- =======================================================================================================================
-- очистка бронированных номеров старше 1 дня 
-- =======================================================================================================================
DELETE XAF.CD_Bookings_Doc_Number WHERE D_Date < DATEADD(day, -1, @now );
-- =======================================================================================================================

declare @yearStart date = dateadd( month, 1 - month(@now), dateadd( day, 1 - day(@now), @now ) );

declare @UsedNumbers table( C_number int primary key );

begin transaction 

with rn as ( SELECT dd.C_NUMBER
                  , CASE WHEN (ISNUMERIC(dd.C_Number) = 1 AND dd.C_Number NOT LIKE '%[.,]%') THEN CAST(dd.C_Number AS INT) END C_number2 -- только коректные номера 
               FROM  dbo.DD_Docs as dd
                     INNER JOIN dbo.DS_Docum_Types as ddt ON ddt.LINK = dd.F_Docum_Types
                     where ddt.C_Const IN ('DST_ToleranceFirst', 'DST_ToleranceNext', 'DST_Checkup', 'DST_Return', 'DST_Survey', 'DST_Survey_TES', 'DST_Checkup_TES', 'DST_Return_TES', 'DST_ControlCheckup','DST_AnnulateAct','DST_TU_Giving', 'DST_UU_Matching')
                           and @yearStart <= dd.D_Register_Date and dd.D_Register_Date < dateadd( year, 1, @yearStart )
						   and dd.C_number not in ('a','БДИ')
            ) 
  insert @UsedNumbers
	select C_number2 from rn
	union all
	select C_number2 from XAF.CD_Bookings_Doc_Number
	;

insert XAF.CD_Bookings_Doc_Number
  SELECT min(C_Number) + 1, GETDATE() 
    from @UsedNumbers as t1
	where not exists( select * from @UsedNumbers as t2 where t2.C_number = t1.C_number + 1 )
;

commit transaction;

SELECT C_Number from XAF.CD_Bookings_Doc_Number;

END
7 июл 19, 16:16    [21922255]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
Svetaahm
Member

Откуда:
Сообщений: 7
Увы нет! min(C_Number) + 1 -- выбирает как в математике +1.
например: первый свободный номер в базе 3275, первый раз программа его выбирает и записывает в таблицу, второй раз программа выбирает 3276, но этот номер занят....
7 июл 19, 16:46    [21922265]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
Svetaahm
Member

Откуда:
Сообщений: 7
По аналогии я так пробовала, чуть по другому у меня код выглядел...
7 июл 19, 16:47    [21922266]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
Svetaahm
Member

Откуда:
Сообщений: 7
aleks222,Спасибо за помощь, но ваш запрос не работает, как бы я его не видоизменяла.
Пишет!
(0 row(s) affected)
Msg 2627, Level 14, State 2, Line 36
Violation of PRIMARY KEY constraint 'PK__#ABAE134__2F653DEB25F6E8D6'. Cannot insert duplicate key in object 'dbo.@UsedNumbers'. The duplicate key value is (92).
7 июл 19, 17:04    [21922269]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
aleks222
Member

Откуда:
Сообщений: 754
 insert @UsedNumbers
	select C_number2 from rn
	union
	select C_number2 from XAF.CD_Bookings_Doc_Number
	;


только это как-то неаккуратненько.

ЗЫ. Желательно понять, а не повторить.
7 июл 19, 17:17    [21922271]     Ответить | Цитировать Сообщить модератору
 Re: Исключить рекурсивный вызов из процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 8838
Svetaahm
запрос не работает, как бы я его не видоизменяла.
Пишет!
(0 row(s) affected)
Msg 2627, Level 14, State 2, Line 36
Violation of PRIMARY KEY constraint 'PK__#ABAE134__2F653DEB25F6E8D6'. Cannot insert duplicate key in object 'dbo.@UsedNumbers'. The duplicate key value is (92).
Замените uniuon all на union

ЗЫ: Если данная процедура вызывается одновременно в нескольких сессиях - рискуете бронировать один и тот же номер несколько раз.
7 июл 19, 17:25    [21922273]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить