ich arbeite an einer Produktkalkulation mit mehrstufiger Stücklistenauflösung (MySql).
Im Netz habe ich einen eleganten Lösunsansatz als stored procedure gefunden, den ich gerne adaptieren möchte.
Ich verspreche mir davon eine bessere Performance von einem basic makro.
Ist der Weg über eine stored procedure grundsätzlich zu empfehlen ?
Wie funktioniert der Aufruf ?
Gruss,
WSO
Für alle die der Lösunsansatz interessiert, hier das Beispiel :
Code: Alles auswählen
DROP TABLE IF EXISTS ww_nodes;
CREATE TABLE ww_nodes (
nodeID int,
description CHAR(50),
cost decimal(10,2)
);
INSERT INTO ww_nodes VALUES (1,'finished bookcase',10);
INSERT INTO ww_nodes VALUES (2,'backboard2x1',1);
INSERT INTO ww_nodes VALUES (3,'laminate2x1',8);
INSERT INTO ww_nodes VALUES (4,'screw',.10);
INSERT INTO ww_nodes VALUES (5,'side',4);
INSERT INTO ww_nodes VALUES (6,'plank',20);
INSERT INTO ww_nodes VALUES (7,'shelf',4);
INSERT INTO ww_nodes VALUES (8,'shelf bracket',.5);
INSERT INTO ww_nodes VALUES (9,'feet',1);
INSERT INTO ww_nodes VALUES (10,'cube4cmx4cm',1);
INSERT INTO ww_nodes VALUES (11,'bookcase kit',2);
INSERT INTO ww_nodes VALUES (12,'carton',1);
DROP TABLE IF EXISTS ww_edges;
CREATE TABLE ww_edges (
rootID INT,
nodeID int,
parentnodeID int,
qty decimal(10,2)
);
INSERT INTO ww_edges VALUES (1,1,null,1);
INSERT INTO ww_edges VALUES (1,2,1,1);
INSERT INTO ww_edges VALUES (1,3,2,1);
INSERT INTO ww_edges VALUES (1,4,2,8);
INSERT INTO ww_edges VALUES (1,5,1,2);
INSERT INTO ww_edges VALUES (1,6,5,1);
INSERT INTO ww_edges VALUES (1,4,5,12);
INSERT INTO ww_edges VALUES (1,7,1,8);
INSERT INTO ww_edges VALUES (1,6,7,.5);
INSERT INTO ww_edges VALUES (1,8,7,4);
INSERT INTO ww_edges VALUES (1,9,1,4);
INSERT INTO ww_edges VALUES (1,10,9,1);
INSERT INTO ww_edges VALUES (1,4,9,1);
INSERT INTO ww_edges VALUES (11,11,null,1);
INSERT INTO ww_edges VALUES (11,2,11,1);
INSERT INTO ww_edges VALUES (11,3,2,1);
INSERT INTO ww_edges VALUES (11,4,2,8);
INSERT INTO ww_edges VALUES (11,5,11,2);
INSERT INTO ww_edges VALUES (11,6,5,1);
INSERT INTO ww_edges VALUES (11,4,5,12);
INSERT INTO ww_edges VALUES (11,7,11,8);
INSERT INTO ww_edges VALUES (11,6,7,.5);
INSERT INTO ww_edges VALUES (11,8,7,4);
INSERT INTO ww_edges VALUES (11,9,11,4);
INSERT INTO ww_edges VALUES (11,10,9,1);
INSERT INTO ww_edges VALUES (11,4,9,11);
INSERT INTO ww_edges VALUES (11,12,11,1);
DROP PROCEDURE IF EXISTS ww_bom;
DELIMITER go
CREATE PROCEDURE ww_bom( root INT )
BEGIN
DECLARE lev INT DEFAULT 0;
DECLARE totalcost DECIMAL( 10,2);
DROP TABLE IF EXISTS temp;
CREATE TABLE temp -- initialise temp table with root node
SELECT
e.nodeID AS nodeID,
n.description AS Item,
e.parentnodeID,
e.qty,
n.cost AS nodecost,
e.qty * n.cost AS cost,
0 as level, -- tree level
CONCAT(e.nodeID,'') AS path -- path to this node as a string
FROM ww_nodes n
JOIN ww_edges e USING(nodeID) -- root node
WHERE e.nodeID = root AND e.parentnodeID IS NULL;
WHILE FOUND_ROWS() > 0 DO
BEGIN
SET lev = lev+1; -- increment level
INSERT INTO temp -- add children of this level
SELECT
e.nodeID,
n.description AS Item,
e.parentnodeID,
e.qty,
n.cost AS nodecost,
e.qty * n.cost AS cost,
lev,
CONCAT(t.path,',',e.nodeID)
FROM ww_nodes n
JOIN ww_edges e USING(nodeID)
JOIN temp t ON e.parentnodeID = t.nodeID
WHERE e.rootID = root AND t.level = lev-1;
END;
END WHILE;
WHILE lev > 0 DO -- percolate costs up the graph
BEGIN
SET lev = lev - 1;
DROP TABLE IF EXISTS tempcost;
CREATE TABLE tempcost -- compute child cost
SELECT p.nodeID, SUM(c.nodecost*c.qty) AS childcost
FROM temp p
JOIN temp c ON p.nodeid=c.parentnodeid
WHERE c.level=lev
GROUP by p.nodeid;
UPDATE temp JOIN tempcost USING(nodeID) -- update parent item cost
SET nodecost = nodecost + tempcost.childcost;
UPDATE temp SET cost = qty * nodecost -- update parent cost
WHERE level=lev-1;
END;
END WHILE;
SELECT -- list BoM
CONCAT(SPACE(level*2),Item) AS Item,
ROUND(nodecost,2) AS 'Unit Cost',
ROUND(Qty,0) AS Qty,ROUND(cost,2) AS Cost FROM temp
ORDER by path;
END go
DELIMITER ;
CALL ww_bom( 1 );
+-------------------+-----------+------+--------+
| Item | Unit Cost | Qty | Cost |
+-------------------+-----------+------+--------+
| finished bookcase | 206.60 | 1.0 | 206.60 |
| backboard2x1 | 9.80 | 1.0 | 9.80 |
| laminate2x1 | 8.00 | 1.0 | 8.00 |
| screw | 0.10 | 8.0 | 0.80 |
| side | 25.20 | 2.0 | 50.40 |
| screw | 0.10 | 12.0 | 1.20 |
| plank | 20.00 | 1.0 | 20.00 |
| shelf | 16.00 | 8.0 | 128.00 |
| plank | 20.00 | 0.5 | 10.00 |
| shelf bracket | 0.50 | 4.0 | 2.00 |
| foot | 2.10 | 4.0 | 8.40 |
| cube4cmx4cm | 1.00 | 1.0 | 1.00 |
| screw | 0.10 | 1.0 | 0.10 |
+-------------------+-----------+------+--------+