MySQL 和 MariaDB 中存储过程游标嵌套游标的转换示例来自 Advantage。

huangapple
go评论59阅读模式
英文:

Conversion Example of Stored Procedure Cursor within a Cursor for MySQL and MariaDB from Advantage

问题

I am migrating from Advantage Database Server 11 to MySQL 8.0.33 and also testing MariaDB 11.0.2. I haven't landed on which one to use yet but I know I will need to use cursor variables and cursors within cursors a lot. I believe MariaDB has that feature. I successfully converted the SQL for MySQL/MariaDB so the procedure will finally create and run, but it does not complete or return results.

Here is the original Advantage SQL that runs correctly and returns results:

CREATE PROCEDURE AllItemIDBom
   ( 
      RootItemID Integer OUTPUT,
      SortChar CHAR ( 44 ) OUTPUT,
      LevelID Integer OUTPUT,
      ParentItemID Integer OUTPUT,
      Seq Integer OUTPUT,
      Line Integer OUTPUT,
      ItemID Integer OUTPUT,
      LevelQty DOUBLE ( 2 ) OUTPUT,
      LevelWastePC DOUBLE ( 2 ) OUTPUT,
      RollDownQty DOUBLE ( 2 ) OUTPUT,
      OptionStdCost LOGICAL OUTPUT
   ) 
BEGIN 
DECLARE SOItems CURSOR;
DECLARE cursor1 CURSOR;
DECLARE cursor2 CURSOR;
DECLARE cursor3 CURSOR;
// return variables  
DECLARE cycleID Integer;
DECLARE @tempSort Char(40);
DECLARE setNodeID1 Integer;

// select the group of Items
OPEN SOItems AS select ItemID from Item where ItemMfgTypeID >1;

WHILE FETCH SOItems DO
setNodeID1 =1000;
// set top level record as a place holder
  insert into __OUTPUT (RootItemID,SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost) 
    values (SOItems.ItemID,'0',0,SOItems.ItemID,0,0,SOItems.ItemID,1,0,1,TRUE);
// first pass go to the ItemMtl
  OPEN cursor1 AS SELECT ItemMtl.* FROM ItemMtl 
        where ItemMtl.ItemID=SOItems.ItemID
        and (EffectDate IS NULL OR EffectDate <= curdate())  
            and (ExpireDate IS NULL OR curdate() < ExpireDate)
        ORDER BY LINE ;

  WHILE FETCH cursor1 DO
    setNodeID1 =setNodeID1 +1;
    insert into __OUTPUT (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost) 
      values (SOItems.ItemID, cast (setNodeID1 as SQL_CHAR) ,1, cursor1.ItemID,   
        cursor1.Seq, cursor1.Line, cursor1.MaterialItemID,  cursor1.UtopianQty, 
        ifNull(cursor1.WastePC,0),Round(Cursor1.UtopianQty /(1-IFNULL(Cursor1.WastePC,0)/100),4),cursor1.OptionStdCost);   
      
  END WHILE;
  CLOSE cursor1;

  cycleID = 1;
  // loop through
  WHILE cycleID < 10 DO
    OPEN cursor2 AS SELECT * FROM __OUTPUT  where  LevelID=cycleID and RootItemID =SOItems.ItemID;
    WHILE FETCH cursor2 DO    
      OPEN cursor3 AS SELECT ItemMtl.*
        FROM ItemMtl 
          where ItemMtl.ItemID=cursor2.ItemID 
              and (EffectDate IS NULL OR EffectDate <= curdate())  
                and (ExpireDate IS NULL OR curdate() < ExpireDate)
      ORDER BY LINE;
        setNodeID1 = 2000;          
      WHILE FETCH cursor3 DO
          setNodeID1 =setNodeID1 +1;      
            @tempSort =  RTRIM(cursor2.SortChar) + RTRIM(cast(setNodeID1 as SQL_CHAR));      
        insert into __OUTPUT  (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost)   
        values (SOItems.ItemID, @tempSort, cycleID+1, cursor3.ItemID, 
             cursor3.Seq, cursor3.Line, cursor3.MaterialItemID, ifnull(cursor3.UtopianQty,0), ifnull(cursor3.WastePC,0),
            Round(Cursor3.UtopianQty /(1-IFNULL(Cursor3.WastePC,0)/100) * cursor2.RollDownQty,4), cursor3.OptionStdCost );
      END WHILE;  
      CLOSE cursor3;
      
    END WHILE;    
    CLOSE cursor2;
    cycleID = cycleID +1;
  END WHILE;
END WHILE;
CLOSE SOItems;

And here is the converted procedure I am testing in MySQL 8.0.33 and MariaDB 11.0.2. It doesn't complete or return results so something isn't right. I'm used to declaring the cursor, setting it later, then accessing and using its results in another cursor, so I had to move things around a bit to convert it. Advantage could also return entire result sets so had to use temp table in this version:

DELIMITER //

CREATE PROCEDURE AllItemIDBom()
BEGIN                  
-- return variables  
DECLARE cycleID Integer;
DECLARE tempSort Char(40);
DECLARE setNodeID1 Integer;
-- cursor variables
DECLARE SOItems_ItemID Integer; 
DECLARE cursor1_ItemID Integer; 
DECLARE cursor1_Seq Integer; 
DECLARE cursor1_Line Integer; 
DECLARE cursor1_MaterialItemID Integer; 
DECLARE cursor1_UtopianQty DOUBLE; 
DECLARE cursor1_WastePC VARCHAR(200); 
DECLARE cursor1_OptionStdCost BOOLEAN; 
DECLARE cursor2_ItemID Integer; 
DECLARE cursor2_SortChar Integer; 
DECLARE cursor2_RollDownQty DOUBLE; 
DECLARE cursor3_ItemID Integer; 
DECLARE cursor3_Seq Integer; 
DECLARE cursor3_Line Integer; 
DECLARE cursor3_MaterialItemID Integer; 
DECLARE cursor3_UtopianQty DOUBLE; 
DECLARE cursor3_WastePC DOUBLE; 
DECLARE cursor3_OptionStdCost BOOLEAN;      
-- setup cursor and handler
DECLARE SOItemsDone Integer DEFAULT 0;
DECLARE SOItems CURSOR FOR SELECT ItemID FROM Item WHERE ItemMfgTypeID > 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET SOItemsDone = 1;  
    
-- prepare processing table
DROP TABLE IF EXISTS temp_AllItemIDBom;
CREATE TEMPORARY TABLE temp_AllItemIDBom
   (
      RootItemID Integer,
      SortChar CHAR (44),
      LevelID Integer,
      ParentItemID Integer,
      Seq Integer,
      Line Integer,
      ItemID Integer,
      LevelQty DOUBLE,
      LevelWastePC DOUBLE,
      RollDownQty DOUBLE,
      OptionStdCost CHAR(1)
   );

-- select the group of Items
OPEN SOItems;
loop1: REPEAT   
      FETCH SOItems INTO SOItems_ItemID;
      SET setNodeID1 = 1000;
      -- set top level record as a place holder
      INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID

<details>
<summary>英文:</summary>

I am migrating from Advantage Database Server 11 to MySQL 8.0.33 and also testing MariaDB 11.0.2. I haven&#39;t landed on which one to use yet but I know I will need to use cursor variables and cursors within cursors a lot. I believe MariaDB has that feature. I successfully converted the SQL for MySQL/MariaDB so the procedure will finally create and run, but it does not complete or return results. 

Here is the original Advantage SQL that runs correctly and returns results:

CREATE PROCEDURE AllItemIDBom
(
RootItemID Integer OUTPUT,
SortChar CHAR ( 44 ) OUTPUT,
LevelID Integer OUTPUT,
ParentItemID Integer OUTPUT,
Seq Integer OUTPUT,
Line Integer OUTPUT,
ItemID Integer OUTPUT,
LevelQty DOUBLE ( 2 ) OUTPUT,
LevelWastePC DOUBLE ( 2 ) OUTPUT,
RollDownQty DOUBLE ( 2 ) OUTPUT,
OptionStdCost LOGICAL OUTPUT
)
BEGIN
DECLARE SOItems CURSOR;
DECLARE cursor1 CURSOR;
DECLARE cursor2 CURSOR;
DECLARE cursor3 CURSOR;
// return variables
DECLARE cycleID Integer;
DECLARE @tempSort Char(40);
DECLARE setNodeID1 Integer;

// select the group of Items
OPEN SOItems AS select ItemID from Item where ItemMfgTypeID >1;

WHILE FETCH SOItems DO
setNodeID1 =1000;
// set top level record as a place holder
insert into __OUTPUT (RootItemID,SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost)
values (SOItems.ItemID,'0',0,SOItems.ItemID,0,0,SOItems.ItemID,1,0,1,TRUE);
// first pass go to the ItemMtl
OPEN cursor1 AS SELECT ItemMtl.* FROM ItemMtl
where ItemMtl.ItemID=SOItems.ItemID
and (EffectDate IS NULL OR EffectDate <= curdate())
and (ExpireDate IS NULL OR curdate() < ExpireDate)
ORDER BY LINE ;

WHILE FETCH cursor1 DO
setNodeID1 =setNodeID1 +1;
insert into __OUTPUT (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost)
values (SOItems.ItemID, cast (setNodeID1 as SQL_CHAR) ,1, cursor1.ItemID,
cursor1.Seq, cursor1.Line, cursor1.MaterialItemID, cursor1.UtopianQty,
ifNull(cursor1.WastePC,0),Round(Cursor1.UtopianQty /(1-IFNULL(Cursor1.WastePC,0)/100),4),cursor1.OptionStdCost);

END WHILE;
CLOSE cursor1;

cycleID = 1;
// loop through
WHILE cycleID < 10 DO
OPEN cursor2 AS SELECT * FROM __OUTPUT where LevelID=cycleID and RootItemID =SOItems.ItemID;
WHILE FETCH cursor2 DO
OPEN cursor3 AS SELECT ItemMtl.*
FROM ItemMtl
where ItemMtl.ItemID=cursor2.ItemID
and (EffectDate IS NULL OR EffectDate <= curdate())
and (ExpireDate IS NULL OR curdate() < ExpireDate)
ORDER BY LINE;
setNodeID1 = 2000;
WHILE FETCH cursor3 DO
setNodeID1 =setNodeID1 +1;
@tempSort = RTRIM(cursor2.SortChar) + RTRIM(cast(setNodeID1 as SQL_CHAR));
insert into __OUTPUT (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost)
values (SOItems.ItemID, @tempSort, cycleID+1, cursor3.ItemID,
cursor3.Seq, cursor3.Line, cursor3.MaterialItemID, ifnull(cursor3.UtopianQty,0), ifnull(cursor3.WastePC,0),
Round(Cursor3.UtopianQty /(1-IFNULL(Cursor3.WastePC,0)/100) * cursor2.RollDownQty,4), cursor3.OptionStdCost );
END WHILE;
CLOSE cursor3;

END WHILE;    
CLOSE cursor2;
cycleID = cycleID +1;

END WHILE;
END WHILE;
CLOSE SOItems;


And here is the converted procedure I am testing in MySQL 8.0.33 and MariaDB 11.0.2. It doesn&#39;t complete or return results so something isn&#39;t right. I&#39;m used to declaring the cursor, setting it later, then accessing and using its results in another cursor, so I had to move things around a bit to convert it. Advantage could also return entire result sets so had to use temp table in this version.

DELIMITER //

CREATE PROCEDURE AllItemIDBom()
BEGIN
-- return variables
DECLARE cycleID Integer;
DECLARE tempSort Char(40);
DECLARE setNodeID1 Integer;
-- cursor variables
DECLARE SOItems_ItemID Integer;
DECLARE cursor1_ItemID Integer;
DECLARE cursor1_Seq Integer;
DECLARE cursor1_Line Integer;
DECLARE cursor1_MaterialItemID Integer;
DECLARE cursor1_UtopianQty DOUBLE;
DECLARE cursor1_WastePC VARCHAR(200);
DECLARE cursor1_OptionStdCost BOOLEAN;
DECLARE cursor2_ItemID Integer;
DECLARE cursor2_SortChar Integer;
DECLARE cursor2_RollDownQty DOUBLE;
DECLARE cursor3_ItemID Integer;
DECLARE cursor3_Seq Integer;
DECLARE cursor3_Line Integer;
DECLARE cursor3_MaterialItemID Integer;
DECLARE cursor3_UtopianQty DOUBLE;
DECLARE cursor3_WastePC DOUBLE;
DECLARE cursor3_OptionStdCost BOOLEAN;
-- setup cursor and handler
DECLARE SOItemsDone Integer DEFAULT 0;
DECLARE SOItems CURSOR FOR SELECT ItemID FROM Item WHERE ItemMfgTypeID >1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET SOItemsDone = 1;

-- prepare processing table
DROP TABLE IF EXISTS temp_AllItemIDBom;
CREATE TEMPORARY TABLE temp_AllItemIDBom
(
RootItemID Integer,
SortChar CHAR (44),
LevelID Integer,
ParentItemID Integer,
Seq Integer,
Line Integer,
ItemID Integer,
LevelQty DOUBLE,
LevelWastePC DOUBLE,
RollDownQty DOUBLE,
OptionStdCost CHAR(1)
);

-- select the group of Items
OPEN SOItems;
loop1: REPEAT
FETCH SOItems INTO SOItems_ItemID;
SET setNodeID1 = 1000;
-- set top level record as a place holder
INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost)
VALUES (SOItems_ItemID,'0',0,SOItems_ItemID,0,0,SOItems_ItemID,1,0,1,TRUE);

  -- first pass go to the ItemMtl
BEGIN
-- setup cursor and handler
DECLARE Cur1Done Integer DEFAULT 0;
DECLARE cursor1 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=SOItems_ItemID
AND (EffectDate IS NULL OR EffectDate &lt;= curdate())  
AND (ExpireDate IS NULL OR curdate() &lt; ExpireDate)
ORDER BY LINE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur1Done = 1;  
OPEN cursor1;
loop2: REPEAT
FETCH cursor1 INTO cursor1_ItemID,cursor1_Seq,cursor1_Line,cursor1_MaterialItemID,cursor1_UtopianQty,cursor1_WastePC,cursor1_OptionStdCost;    
SET setNodeID1 =setNodeID1 +1;
INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
VALUES (SOItems_ItemID, cast(setNodeID1 as CHAR) ,1, cursor1_ItemID,   
cursor1_Seq, cursor1_Line, cursor1_MaterialItemID, cursor1_UtopianQty, 
ifNull(cursor1_WastePC,0),Round(Cursor1_UtopianQty /(1-IFNULL(Cursor1_WastePC,0)/100),4),cursor1_OptionStdCost); 
UNTIL Cur1Done END REPEAT loop2;
CLOSE cursor1;
END;
SET cycleID = 1;
-- loop through children
loop3: WHILE cycleID &lt; 10 DO
BEGIN
-- setup cursor and handler
DECLARE Cur2Done Integer DEFAULT 0;
DECLARE cursor2 CURSOR FOR SELECT ItemID,SortChar,RollDownQty FROM temp_AllItemIDBom WHERE LevelID=cycleID AND RootItemID=SOItems_ItemID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur2Done = 1; 
OPEN cursor2;
loop4: REPEAT  
BEGIN
DECLARE Cur3Done Integer DEFAULT 0;
DECLARE cursor3 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=cursor2_ItemID 
AND (EffectDate IS NULL OR EffectDate &lt;= curdate())  
AND (ExpireDate IS NULL OR curdate() &lt; ExpireDate)
ORDER BY LINE;      
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur3Done = 1; 
FETCH cursor2 INTO cursor2_ItemID,cursor2_SortChar,cursor2_RollDownQty; 
OPEN cursor3;
SET setNodeID1 = 2000;  
loop5: REPEAT
FETCH cursor3 INTO cursor3_ItemID,cursor3_Seq,cursor3_Line,cursor3_MaterialItemID,cursor3_UtopianQty,cursor3_WastePC,cursor3_OptionStdCost;
SET setNodeID1 =setNodeID1 +1;      
SET tempSort = RTRIM(cursor2_SortChar) + RTRIM(cast(setNodeID1 as CHAR));      
INSERT INTO temp_AllItemIDBom  (RootItemID, SortChar, LevelID, ParentItemID, Seq, Line, ItemID, LevelQty, LevelWastePC, RollDownQty,OptionStdCost)   
VALUES (SOItems_ItemID, tempSort, cycleID+1, cursor3_ItemID, 
cursor3_Seq, cursor3_Line, cursor3_MaterialItemID, ifnull(cursor3_UtopianQty,0), ifnull(cursor3_WastePC,0),
Round(Cursor3_UtopianQty /(1-IFNULL(Cursor3_WastePC,0)/100) * cursor2_RollDownQty,4), cursor3_OptionStdCost );
UNTIL Cur3Done END REPEAT loop5;
CLOSE cursor3;   
END;  
UNTIL Cur2Done END REPEAT loop4;    
CLOSE cursor2;
SET cycleID = cycleID +1;  
END;
END WHILE loop3;

UNTIL SOItemsDone END REPEAT loop1;
CLOSE SOItems;

SELECT * FROM temp_AllItemIDBom;

END
//

DELIMITER ;

Example Usage: ItemNo 1016-FR is ItemID 10004, a 16&quot; BMX Bike Frame with a bill of material of four components. 
[![enter image description here][1]][1]
The procedure gets the top level and all it&#39;s children, and their children, through all indented levels, outputting the below results by level (though in this particular example there is only a single level).
[![enter image description here][2]][2]
Here is a script to setup a small database with sample data for testing the procedure.

CREATE DATABASE TestProc;
GRANT ALL PRIVILEGES ON TestProc.* TO 'root'@'localhost' WITH GRANT OPTION;
USE TestProc;
CREATE TABLE Item (
ItemID Integer,
ItemMfgTypeID Integer);
INSERT INTO Item (ItemID,ItemMfgTypeID)
VALUES (10004,2),
(10001,1),
(10008,1),
(10009,1),
(10010,1);
CREATE TABLE ItemMtl (
ItemID Integer,
Seq Integer,
Line Integer,
MaterialItemID Integer,
UtopianQty Double,
WastePC Double,
OptionStdCost Boolean,
EffectDate Date,
ExpireDate Date);
INSERT INTO ItemMtl (ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost,EffectDate,ExpireDate)
VALUES (10004,1,10,10001,6,0,True,NULL,NULL),
(10004,2,20,10008,5.5,0,True,NULL,NULL),
(10004,3,30,10009,0.35,0,True,NULL,NULL),
(10004,4,40,10010,0.25,0,True,NULL,NULL);


Thank you in advance!
[1]: https://i.stack.imgur.com/TaA2v.png
[2]: https://i.stack.imgur.com/anH4c.png
</details>
# 答案1
**得分**: 1
感谢提供测试用例。我测试了您的过程。由于逻辑问题，它导致了无限循环。老实说，我无法相信您的原始Advantage SQL工作流程，因为它似乎存在相同的逻辑问题。
基本上，根据我的阅读，您将`ItemID`插入了临时表中，而应该插入`MaterialItemID`。因此，每个项目都变成了自己的父项目，您会陷入无限循环。
以下是使用递归通用表达式的解决方案：
```sql
WITH RECURSIVE cte AS (
SELECT Item.ItemID AS RootItemID,
0 AS LevelID,
Item.ItemID AS ParentItemID,
0 AS Seq,
0 AS Line,
ItemID AS ItemID,
CAST(1 AS DECIMAL(9,2)) AS LevelQty,
0 AS LevelWastePC,
CAST(1 AS DECIMAL(9,2)) AS RollDownQty,
True AS OptionStdCost
FROM Item
WHERE ItemMfgTypeID > 1
UNION ALL
SELECT cte.RootItemID,
cte.LevelID + 1,
cte.ItemID,
l.Seq,
l.Line,
l.MaterialItemID,
l.UtopianQty,
l.WastePC,
l.UtopianQty,
l.OptionStdCost
FROM ItemMtl AS l JOIN cte USING (ItemID)
WHERE (EffectDate IS NULL OR EffectDate <= curdate())
AND (ExpireDate IS NULL OR curdate() < ExpireDate)
)
SELECT RootItemID,
CAST(1000*LevelID + ROW_NUMBER() OVER (PARTITION BY LevelID ORDER BY Line) AS CHAR(44)) AS SortChar,
LevelID,
ParentItemID,
Seq,
Line,
ItemID,
LevelQty,
LevelWastePC,
RollDownQty,
OptionStdCost
FROM cte;

给出您的示例数据，输出如下：

+------------+----------+---------+--------------+------+------+--------+----------+--------------+-------------+---------------+
| RootItemID | SortChar | LevelID | ParentItemID | Seq  | Line | ItemID | LevelQty | LevelWastePC | RollDownQty | OptionStdCost |
+------------+----------+---------+--------------+------+------+--------+----------+--------------+-------------+---------------+
|      10004 | 1        |       0 |        10004 |    0 |    0 |  10004 |     1.00 |            0 |        1.00 |             1 |
|      10004 | 1001     |       1 |        10004 |    1 |   10 |  10001 |     6.00 |            0 |        6.00 |             1 |
|      10004 | 1002     |       1 |        10004 |    2 |   20 |  10008 |     5.50 |            0 |        5.50 |             1 |
|      10004 | 1003     |       1 |        10004 |    3 |   30 |  10009 |     0.35 |            0 |        0.35 |             1 |
|      10004 | 1004     |       1 |        10004 |    4 |   40 |  10010 |     0.25 |            0 |        0.25 |             1 |
+------------+----------+---------+--------------+------+------+--------+----------+--------------+-------------+---------------+

Dbfiddle中演示了在MySQL 8.0上运行测试的示例：https://dbfiddle.uk/C_WPR7DI

这个查询不必在存储过程中。您可以直接从客户端代码执行查询。

老实说，MySQL存储过程不是我的最爱。它们性能不佳，没有包或调试器或编译器功能。没有存储过程的标准库。文档几乎只是参考文档，没有良好的教程或面向任务的文档，所以很难学习。我自己不在MySQL中使用存储过程，也不建议使用。

英文:

Thank you for providing a test case. I tested your procedure. It results in an infinite loop due to the logic. Frankly, I can't believe that your original procedure for Advantage SQL worked, because it appears to have the same logic problem.

Basically, as I read it, you're inserting ItemID into the temp table where you should be inserting MaterialItemID. So every item becomes its own parent, and you loop forever.

Here's a solution using a recursive common table expression:

WITH RECURSIVE cte AS (
SELECT Item.ItemID AS RootItemID,
0 AS LevelID,
Item.ItemID AS ParentItemID,
0 AS Seq,
0 AS Line,
ItemID AS ItemID,
CAST(1 AS DECIMAL(9,2)) AS LevelQty,
0 AS LevelWastePC,
CAST(1 AS DECIMAL(9,2)) AS RollDownQty,
True AS OptionStdCost
FROM Item
WHERE ItemMfgTypeID &gt; 1
UNION ALL
SELECT cte.RootItemID,
cte.LevelID + 1,
cte.ItemID,
l.Seq,
l.Line,
l.MaterialItemID,
l.UtopianQty,
l.WastePC,
l.UtopianQty,
l.OptionStdCost
FROM ItemMtl AS l JOIN cte USING (ItemID)
WHERE (EffectDate IS NULL OR EffectDate &lt;= curdate())
AND (ExpireDate IS NULL OR curdate() &lt; ExpireDate)
)
SELECT RootItemID,
CAST(1000*LevelID + ROW_NUMBER() OVER (PARTITION BY LevelID ORDER BY Line) AS CHAR(44)) AS SortChar,
LevelID,
ParentItemID,
Seq,
Line,
ItemID,
LevelQty,
LevelWastePC,
RollDownQty,
OptionStdCost
FROM cte;

Output given your sample data:

+------------+----------+---------+--------------+------+------+--------+----------+--------------+-------------+---------------+
| RootItemID | SortChar | LevelID | ParentItemID | Seq  | Line | ItemID | LevelQty | LevelWastePC | RollDownQty | OptionStdCost |
+------------+----------+---------+--------------+------+------+--------+----------+--------------+-------------+---------------+
|      10004 | 1        |       0 |        10004 |    0 |    0 |  10004 |     1.00 |            0 |        1.00 |             1 |
|      10004 | 1001     |       1 |        10004 |    1 |   10 |  10001 |     6.00 |            0 |        6.00 |             1 |
|      10004 | 1002     |       1 |        10004 |    2 |   20 |  10008 |     5.50 |            0 |        5.50 |             1 |
|      10004 | 1003     |       1 |        10004 |    3 |   30 |  10009 |     0.35 |            0 |        0.35 |             1 |
|      10004 | 1004     |       1 |        10004 |    4 |   40 |  10010 |     0.25 |            0 |        0.25 |             1 |
+------------+----------+---------+--------------+------+------+--------+----------+--------------+-------------+---------------+

Dbfiddle to demonstrate working test on MySQL 8.0: https://dbfiddle.uk/C_WPR7DI

This query doesn't have to be in a stored procedure. You can execute the query directly from client code.

To be honest, MySQL stored procedures are not my favorite. They have poor performance, they have no feature of packages, or a debugger, or a compiler. There's no standard library of stored procedures. The documentation is pretty much only reference documentation, without a good tutorial or task-oriented docs, so it's hard to learn. I don't use stored procedures in MySQL myself, and I don't recommend them.

答案2

得分: 0

我理解了你的请求，以下是翻译好的部分：

我找到了答案，想与大家分享。这个代码段可以产生所需的结果/输出。虽然我可能最终会重写此过程，采用类似于Bill建议的递归CTE方式，但这个代码段将其保留为存储过程，并真正地将其从Advantage转换为MySQL 8.0.33 / MariaDB 11.0.2。我们之所以希望它作为一个存储过程，是为了避免在我们的应用程序中嵌入SQL，将其全部保留在数据库中。

DELIMITER //

CREATE PROCEDURE AllItemIDBom()
BEGIN                  
-- 返回变量  
DECLARE cycleID Integer;
DECLARE tempSort Char(40);
DECLARE setNodeID1 Integer;
-- 游标变量
DECLARE SOItems_ItemID Integer; 
DECLARE cursor1_ItemID Integer; 
DECLARE cursor1_Seq Integer; 
DECLARE cursor1_Line Integer; 
DECLARE cursor1_MaterialItemID Integer; 
DECLARE cursor1_UtopianQty DOUBLE; 
DECLARE cursor1_WastePC VARCHAR(200); 
DECLARE cursor1_OptionStdCost BOOLEAN; 
DECLARE cursor2_ItemID Integer; 
DECLARE cursor2_SortChar VARCHAR(16383); 
DECLARE cursor2_RollDownQty DOUBLE; 
DECLARE cursor3_ItemID Integer; 
DECLARE cursor3_Seq Integer; 
DECLARE cursor3_Line Integer; 
DECLARE cursor3_MaterialItemID Integer; 
DECLARE cursor3_UtopianQty DOUBLE; 
DECLARE cursor3_WastePC DOUBLE; 
DECLARE cursor3_OptionStdCost BOOLEAN;      
-- 设置处理程序开关
DECLARE SOItemsDone Integer DEFAULT 0;
DECLARE Cur1Done Integer;
DECLARE Cur2Done Integer;
DECLARE Cur3Done Integer;
-- 设置第一个游标和处理程序
DECLARE SOItems CURSOR FOR SELECT ItemID FROM Item WHERE ItemMfgTypeID IN (2,3,4) AND suspended = false AND MRPInclude= true;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET SOItemsDone = 1;  
  
-- 准备处理表
DROP TABLE IF EXISTS temp_AllItemIDBom;
CREATE TEMPORARY TABLE temp_AllItemIDBom
   (
      RootItemID Integer,
      SortChar CHAR (44),
      LevelID Integer,
      ParentItemID Integer,
      Seq Integer,
      Line Integer,
      ItemID Integer,
      LevelQty DOUBLE,
      LevelWastePC DOUBLE,
      RollDownQty DOUBLE,
      OptionStdCost CHAR(1)
   );
  
-- 选择物品组
SET SOItemsDone = 0;
OPEN SOItems;
BLOCK1: REPEAT   
  SET Cur1Done = 0;
  FETCH SOItems INTO SOItems_ItemID;      
  IF NOT SOItemsDone THEN 
    SET setNodeID1 = 1000;
    -- 将顶级记录设置为占位符
    INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
          VALUES (SOItems_ItemID,'0',0,SOItems_ItemID,0,0,SOItems_ItemID,1,0,1,TRUE);

    -- 第一次循环到ItemMtl
    BEGIN
      -- 设置第二个游标和处理程序
      DECLARE cursor1 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=SOItems_ItemID
                                 AND (EffectDate IS NULL OR EffectDate <= curdate())  
                                 AND (ExpireDate IS NULL OR curdate() < ExpireDate)
                                 ORDER BY LINE;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur1Done = 1;  
      
      OPEN cursor1;
      BLOCK2: REPEAT
        FETCH cursor1 INTO cursor1_ItemID,cursor1_Seq,cursor1_Line,cursor1_MaterialItemID,cursor1_UtopianQty,cursor1_WastePC,cursor1_OptionStdCost;    
        IF NOT Cur1Done THEN 
          SET setNodeID1 = setNodeID1 + 1;
          INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
            VALUES (SOItems_ItemID, cast(setNodeID1 as CHAR) ,1, cursor1_ItemID,cursor1_Seq, cursor1_Line, cursor1_MaterialItemID, cursor1_UtopianQty, 
                  ifNull(cursor1_WastePC,0),Round(Cursor1_UtopianQty /(1-IFNULL(Cursor1_WastePC,0)/100),4),cursor1_OptionStdCost); 
        END IF;
      UNTIL Cur1Done END REPEAT BLOCK2;
      CLOSE cursor1;
    END;

    SET cycleID = 1;
    -- 循环处理子级
    BLOCK3: WHILE cycleID < 10 DO
      SET Cur2Done = 0;
      BEGIN
        -- 设置第三个游标和处理程序
        DECLARE cursor2 CURSOR FOR SELECT ItemID,SortChar,RollDownQty FROM temp_AllItemIDBom WHERE LevelID=cycleID AND RootItemID=SOItems_ItemID;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur2Done = 1; 
        OPEN cursor2;
        BLOCK4: REPEAT 
          SET Cur3Done = 0;
          FETCH cursor2 INTO cursor2_ItemID,cursor2_SortChar,cursor2_RollDownQty; 
          IF NOT Cur2Done THEN 
            BEGIN
              -- 设置游标和处理程序
              DECLARE cursor3 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=cursor2_ItemID 
                                         AND (EffectDate IS NULL OR EffectDate <= curdate())  
                                         AND (ExpireDate IS NULL OR curdate() < ExpireDate)
                                         ORDER BY LINE;      
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur3Done = 1; 
              OPEN cursor3;
              SET setNodeID1 = 2000;  
              BLOCK5: REPEAT
                FETCH cursor3 INTO cursor3_ItemID,cursor3_Seq,cursor3_Line,cursor3_MaterialItemID,cursor3_UtopianQty,cursor3_WastePC,cursor3_OptionStdCost;
                IF NOT Cur3Done THEN
                  SET setNodeID1 = setNodeID1 + 1;      
                  SET tempSort = CONCAT(RTRIM(cursor2_SortChar),RTRIM(cast(setNodeID1 as CHAR)));
                  INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,

<details>
<summary>英文:</summary>

I figured it out and thought I would share the answer. This one produces the desired results/output. Though I will probably end up rewriting the procedure as a recursive CTE based something akin to Bill&#39;s recommendation, this one keeps it as a stored procedure and truly rewrites it, converted from Advantage to MySQL 8.0.33 / MariaDB 11.0.2. Part of our desire for it as a procedure is to avoid any embedding of SQL in our app, keeping it all in the database.

    DELIMITER //
    
    CREATE PROCEDURE AllItemIDBom()
    BEGIN                  
    -- return variables  
    DECLARE cycleID Integer;
    DECLARE tempSort Char(40);
    DECLARE setNodeID1 Integer;
    -- cursor variables
    DECLARE SOItems_ItemID Integer; 
    DECLARE cursor1_ItemID Integer; 
    DECLARE cursor1_Seq Integer; 
    DECLARE cursor1_Line Integer; 
    DECLARE cursor1_MaterialItemID Integer; 
    DECLARE cursor1_UtopianQty DOUBLE; 
    DECLARE cursor1_WastePC VARCHAR(200); 
    DECLARE cursor1_OptionStdCost BOOLEAN; 
    DECLARE cursor2_ItemID Integer; 
    DECLARE cursor2_SortChar VARCHAR(16383); 
    DECLARE cursor2_RollDownQty DOUBLE; 
    DECLARE cursor3_ItemID Integer; 
    DECLARE cursor3_Seq Integer; 
    DECLARE cursor3_Line Integer; 
    DECLARE cursor3_MaterialItemID Integer; 
    DECLARE cursor3_UtopianQty DOUBLE; 
    DECLARE cursor3_WastePC DOUBLE; 
    DECLARE cursor3_OptionStdCost BOOLEAN;      
    -- setup handler toggles
    DECLARE SOItemsDone Integer DEFAULT 0;
    DECLARE Cur1Done Integer;
    DECLARE Cur2Done Integer;
    DECLARE Cur3Done Integer;
    -- setup 1st cursor and handler
    DECLARE SOItems CURSOR FOR SELECT ItemID FROM Item WHERE ItemMfgTypeID IN (2,3,4) AND suspended = false AND MRPInclude= true;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET SOItemsDone = 1;  
        
    -- prepare processing table
    DROP TABLE IF EXISTS temp_AllItemIDBom;
    CREATE TEMPORARY TABLE temp_AllItemIDBom
       (
          RootItemID Integer,
          SortChar CHAR (44),
          LevelID Integer,
          ParentItemID Integer,
          Seq Integer,
          Line Integer,
          ItemID Integer,
          LevelQty DOUBLE,
          LevelWastePC DOUBLE,
          RollDownQty DOUBLE,
          OptionStdCost CHAR(1)
       );
    
    -- select the group of Items
    SET SOItemsDone = 0;
    OPEN SOItems;
    BLOCK1: REPEAT   
      SET Cur1Done = 0;
      FETCH SOItems INTO SOItems_ItemID;      
      IF NOT SOItemsDone THEN 
        SET setNodeID1 = 1000;
        -- set top level record as a place holder
        INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
              VALUES (SOItems_ItemID,&#39;0&#39;,0,SOItems_ItemID,0,0,SOItems_ItemID,1,0,1,TRUE);
    
        -- first pass go to the ItemMtl
        BEGIN
          -- setup 2nd cursor and handler
          DECLARE cursor1 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=SOItems_ItemID
                                     AND (EffectDate IS NULL OR EffectDate &lt;= curdate())  
                                     AND (ExpireDate IS NULL OR curdate() &lt; ExpireDate)
                                     ORDER BY LINE;
          DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur1Done = 1;  
          
          OPEN cursor1;
          BLOCK2: REPEAT
            FETCH cursor1 INTO cursor1_ItemID,cursor1_Seq,cursor1_Line,cursor1_MaterialItemID,cursor1_UtopianQty,cursor1_WastePC,cursor1_OptionStdCost;    
            IF NOT Cur1Done THEN 
              SET setNodeID1 = setNodeID1 + 1;
              INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost) 
                VALUES (SOItems_ItemID, cast(setNodeID1 as CHAR) ,1, cursor1_ItemID,cursor1_Seq, cursor1_Line, cursor1_MaterialItemID, cursor1_UtopianQty, 
                      ifNull(cursor1_WastePC,0),Round(Cursor1_UtopianQty /(1-IFNULL(Cursor1_WastePC,0)/100),4),cursor1_OptionStdCost); 
            END IF;
          UNTIL Cur1Done END REPEAT BLOCK2;
          CLOSE cursor1;
        END;
    
        SET cycleID = 1;
        -- loop through children
        BLOCK3: WHILE cycleID &lt; 10 DO
          SET Cur2Done = 0;
          BEGIN
            -- setup 3rd cursor and handler
            DECLARE cursor2 CURSOR FOR SELECT ItemID,SortChar,RollDownQty FROM temp_AllItemIDBom WHERE LevelID=cycleID AND RootItemID=SOItems_ItemID;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur2Done = 1; 
            OPEN cursor2;
            BLOCK4: REPEAT 
              SET Cur3Done = 0;
              FETCH cursor2 INTO cursor2_ItemID,cursor2_SortChar,cursor2_RollDownQty; 
              IF NOT Cur2Done THEN 
                BEGIN
                  -- setup cursor and handler
                  DECLARE cursor3 CURSOR FOR SELECT ItemID,Seq,Line,MaterialItemID,UtopianQty,WastePC,OptionStdCost FROM ItemMtl WHERE ItemMtl.ItemID=cursor2_ItemID 
                                             AND (EffectDate IS NULL OR EffectDate &lt;= curdate())  
                                             AND (ExpireDate IS NULL OR curdate() &lt; ExpireDate)
                                             ORDER BY LINE;      
                  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Cur3Done = 1; 
                  OPEN cursor3;
                  SET setNodeID1 = 2000;  
                  BLOCK5: REPEAT
                    FETCH cursor3 INTO cursor3_ItemID,cursor3_Seq,cursor3_Line,cursor3_MaterialItemID,cursor3_UtopianQty,cursor3_WastePC,cursor3_OptionStdCost;
                    IF NOT Cur3Done THEN
                      SET setNodeID1 = setNodeID1 + 1;      
                      SET tempSort = CONCAT(RTRIM(cursor2_SortChar),RTRIM(cast(setNodeID1 as CHAR)));
                      INSERT INTO temp_AllItemIDBom (RootItemID,SortChar,LevelID,ParentItemID,Seq,Line,ItemID,LevelQty,LevelWastePC,RollDownQty,OptionStdCost)   
                      VALUES (SOItems_ItemID,tempSort,cycleID+1,cursor3_ItemID,cursor3_Seq,cursor3_Line,cursor3_MaterialItemID,ifnull(cursor3_UtopianQty,0),ifnull(cursor3_WastePC,0),
                        Round(Cursor3_UtopianQty /(1-IFNULL(Cursor3_WastePC,0)/100) * cursor2_RollDownQty,4),cursor3_OptionStdCost);
                    END IF;
                  UNTIL Cur3Done END REPEAT BLOCK5;
                  CLOSE cursor3;   
                END;  
              END IF;
            UNTIL Cur2Done END REPEAT BLOCK4;    
            CLOSE cursor2;
            SET cycleID = cycleID +1;  
          END;
        END WHILE BLOCK3;
      END IF;
    UNTIL SOItemsDone END REPEAT BLOCK1;
    CLOSE SOItems;
    
    SELECT * FROM temp_AllItemIDBom ORDER BY RootItemID,SortChar;
    END
    //
    DELIMITER ;

As it turns out, to prevent the infinite looping issue, I (a) added &quot;IF NOT done THEN&quot; type of statements to each block and (b) moved all fetch statements to just before those IF statements.

</details>

huangapple
  • 本文由 发表于 2023年6月26日 01:04:20
  • 转载请务必保留本文链接：https://go.coder-hub.com/76551557.html
  • advantage-database-server
  • cursor
  • mariadb
  • mysql
  • stored-procedures
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定