Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Незапускает рекурсивная процедура / 4 сообщений из 4, страница 1 из 1
02.07.2018, 17:19
    #39668460
Jonnik
Jonnik
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Незапускает рекурсивная процедура
У меня есть три таблички.
В одной описание деталей.
В другой история закупочных цен детали.
И третья список используемых материалов для производства этой детали.
Есть вложенность т.е. одна деталь может в ходить в состав другой детали и так далее. Вложенность не большая, максимуим 5 уровней.
Мне нужно было посчитать объем материалов необходимых для производства конкретной детали. Т.е. разложить на материалы из чего она состоит.

Получается нужна рекурсивная функция. Но тут есть сложности в Mysql с рекурсией.

Я создал функцию FGetListDetails в которую я передаю ID детали и сколько их надо. А она запускает процедуру PGetListDetails, потому как рекурсивно функцию нельзя вызывать, только процедуру.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
DELIMITER $$

USE `zmk`$$

DROP FUNCTION IF EXISTS `FGetListDetails`$$

CREATE DEFINER=`root`@`%` FUNCTION `FGetListDetails`(TIDDetail INT, NumberDetais DOUBLE) RETURNS INT(11)
BEGIN
    
  DECLARE CountDetais INT;    
    
  CALL PGetListDetails(TIDDetail, NumberDetais);
       
  SET CountDetais  = 0;
    
  RETURN CountDetais ;
 END$$

DELIMITER ;



Процедура PGetListDetails проверяет, что если есть закупочная цена, то я записываю эту деталь в таблицу ListDetailsForReserve, затем получаю список материалов нужных для этой детали и используя курсор запускаю процедуру PGetListDetails для каждого материала. Получается рекурсия.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
DELIMITER $$

USE `zmk`$$

DROP PROCEDURE IF EXISTS `PGetListDetails`$$

CREATE DEFINER=`root`@`%` PROCEDURE `PGetListDetails`(TIDDetail INT, NumberDetais DOUBLE)
BEGIN
    
    DECLARE Done INT DEFAULT 0;    
    DECLARE CountDetais INT;
    DECLARE ExistPurchaserPrice INT;
    DECLARE PurchaserPrice, TNumberDetais DOUBLE;
    DECLARE IDMaterial INT;   
    
    DECLARE BankCursor CURSOR FOR SELECT 
    IMD.IDMaterial, (IMD.Number * NumberDetais) AS TNumberDetais 
    FROM ItemsMaterialsDetails IMD 
    WHERE IMD.IDDetail=TIDDetail;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done=1;
    
    SET CountDetais  = 0;
    SET ExistPurchaserPrice  = 0;
    SET PurchaserPrice  = 0;
    
    SET ExistPurchaserPrice = (SELECT COUNT(IPD.ID) FROM ItemsPriceDetails IPD WHERE IPD.IDDetail=TIDDetail);
    
    SET PurchaserPrice = (SELECT IF((SELECT COUNT(IPD.ID) FROM ItemsPriceDetails IPD WHERE IPD.IDDetail=TIDDetail)>0, 
    (SELECT IPD.Price FROM ItemsPriceDetails IPD WHERE IPD.IDDetail=TIDDetail ORDER BY IPD.DatePrice DESC LIMIT 1), 0)); 
     
    IF ExistPurchaserPrice>0 THEN
	INSERT INTO ListDetailsForReserve(IDDetail, Number, Price) 
        VALUES (TIDDetail, NumberDetais, PurchaserPrice);
    END IF;
    
    OPEN BankCursor;
	WHILE Done = 0 DO
	  FETCH BankCursor INTO IDMaterial, TNumberDetais;
	  CALL PGetListDetails(IDMaterial, TNumberDetais);
	END WHILE;
    CLOSE BankCursor; 
    
    
	   
  #  SET NumberHours = FORMAT(NumberHours, 2);
    
   # RETURN IFNULL(NumberHours, 0);
    END$$

DELIMITER ;



В итоге на выходе я должен получить в таблице ListDetailsForReserve список необходимых материалов.

Вот так я запускал этот весь маханизм для теста
Код: plsql
1.
2.
3.
4.
5.
6.
7.
DROP TEMPORARY TABLE IF EXISTS ListDetailsForReserve ; 
CREATE TEMPORARY TABLE ListDetailsForReserve(IDDetail INT(11), Number DOUBLE NOT NULL DEFAULT "0", 
Price DOUBLE NOT NULL DEFAULT "0", KEY IDDetail (IDDetail) ) ;

SELECT FGetListDetails(6132, 2);

SELECT * FROM ListDetailsForReserve ;



По факту сначала разобрался с проблемай нехватки стека Thread stack overrun и thread_stack=4M, Но после этого появилась эта ошибка
Код: sql
1.
2.
Error Code: 1456
Recursive limit 255 (as set by the max_sp_recursion_depth variable) was exceeded for routine PGetListDetails



Как я только не менял SET SESSION max_sp_recursion_depth=500; Но сделать этот параметр выше 255 невозможно.
А по сути у меня нет такой вложенности. У меня максиму вложенность на 5 уровней. Не знаю зачем там такая нужна. Может где ошибка есть. Я думал, что может цикл не завершается. Но вроде нет. Может если курсор сразу пустой, то может он не звершается.

Вызывал еще процедуру напрямую так
Код: sql
1.
 CALL PGetListDetails(6132, 2);



В этот раз в таблицу записывается, только пару записей и снова таже ошибка
Код: sql
1.
2.
Error Code: 1456
Recursive limit 255 (as set by the max_sp_recursion_depth variable) was exceeded for routine PGetListDetails



Проверял это на Хостинге, и на двух своих серверах. Везде одна и таже ошибка. В чем может быть дело ?
Версии серверов 5.7 и 5.5.29

Прилагаю дамп трех этих таблиц.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.07.2018, 17:47
    #39669036
Jonnik
Jonnik
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Незапускает рекурсивная процедура
Не ужели ни укого не было таких проблем ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.07.2018, 07:26
    #39669214
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Незапускает рекурсивная процедура
JonnikПолучается нужна рекурсивная функция.Поскольку максимальная глубина вложенности известна и ограничена - нет, не обязательно.

JonnikА по сути у меня нет такой вложенности.Логики у тебя нет. А вложенность такая - есть.
Посмотри на свой код. Вот на этот фрагмент:

Jonnik
Код: plsql
1.
2.
3.
4.
5.
6.
    OPEN BankCursor;
	WHILE Done = 0 DO
	  FETCH BankCursor INTO IDMaterial, TNumberDetais;
	  CALL PGetListDetails(IDMaterial, TNumberDetais);
	END WHILE;
    CLOSE BankCursor;



Ты открываешь курсор. Потом входишь в цикл (на старте Done = 0, не спорь, входишь в любом случае). Потом фетчишь курсор. Если вдруг он пуст - то присваиваешь Done = 1. А дальше безусловно, без всяких проверок, вызываешь следующий уровень рекурсии. И так продолжается, пока он не станет равен 255 - и, как итог, ошибка.

Запомни накрепко - если используешь DO-LOOP, первый фетч всегда делается ДО первой провеки флага. В твоём случае - до входа в цикл. И следующие фетчи также делаются непосредственно перед проверкой - то есть последним оператором в цикле.

Но ещё лучше - используй REPEAT. И тупой IF Done = 1 THEN LEAVE REPEAT сразу после фетча.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2018, 11:07
    #39669893
Jonnik
Jonnik
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Незапускает рекурсивная процедура
Спасибо.
Я так и думал, что там проблема.
Делал первый раз курсор. И брал готовый пример, но видимо там был не хороший пример.
Сделал другим методом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Незапускает рекурсивная процедура / 4 сообщений из 4, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]