Gelöst: Stored Procedure

Datenbanklösungen mit AOO/LO

Moderator: Moderatoren

WSO
*****
Beiträge: 233
Registriert: Di, 22.04.2014 13:03

Gelöst: Stored Procedure

Beitrag von WSO »

Hallo Forum,
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 |
+-------------------+-----------+------+--------+
Zuletzt geändert von WSO am Sa, 13.12.2014 12:10, insgesamt 1-mal geändert.
WSO
*****
Beiträge: 233
Registriert: Di, 22.04.2014 13:03

Stehe auf dem Schlauch: Stored Procedure

Beitrag von WSO »

Moin Zusammen,
stehe voll auf dem Schlauch, vielleicht kann mir ja doch noch jemand helfen ...

Habe in Mysql eine procedure angelegt, die 4 Spalten als resultset liefert.
Die Datenbank ist via MySQL-JDBC abgebunden.
Über das Zeilenkommando in MySql:

Code: Alles auswählen

Call name (inputparameter)
wird die procedure korrekt ausgeführt.
In Base als SQL-Direktkommando geht es auch (Befehl wird ausgeführt, Output aber nicht angezeigt).
Als Abfrage (SQL-mode) funktioniert es aber nicht. Eine Base-Abrfage mit:

Code: Alles auswählen

Call name (inputparameter)
liefert mir als Ergebnis wieder nur die Befefhlszeile: "Call name (inputparameter)".

Habe die Nacht über intensiv das Netz durchkämmt, bin aber nur auf jede Menge JAVA-Beispiele und Anleitungen für den JDBC-Connector gestossen,
z.B.:

Code: Alles auswählen

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
Das hilft mir nicht wirklich weiter, denn ich brauche den Output der procedure als input für mein Formular und nicht als resultset in einem Makro.

Wäre sehr froh über eine Idee, ich komme da nicht mehr weiter ...
Vielen Dank und Gruss
WSO
Zuletzt geändert von WSO am Sa, 13.12.2014 21:07, insgesamt 1-mal geändert.
WSO
*****
Beiträge: 233
Registriert: Di, 22.04.2014 13:03

Re: Stored Procedure

Beitrag von WSO »

Habe einen workaraound gefunden:

1) Die procedure schreibt als Ergebnis eine temporäre Tabelle.
2) Aufruf erfogt via Makro vor Aufruf des Formulars:

Code: Alles auswählen

	stSql = "CALL procedurename( inputparameter )"
	oSql.executeUpdate(stSql)
3) Das Formular verwendet die temporäre Tabelle als Datenquelle.

Funktioniert einwandfrei.

Gruss,
WSO
Johanna
Beiträge: 3
Registriert: Di, 05.09.2017 17:25

Re: Gelöst: Stored Procedure

Beitrag von Johanna »

Könntest Du unter Umständen noch einmal präzisieren, wie Dir das genau gelungen ist? Ich habe ein ähnliches Problem und kann diesen allgemein formulierten Lösungsansatz nur bedingt nachvollziehen. Danke!

_____________________
Moderationshinweis:
Bitte keine "alten" Threads mehr aufwärmen.
Siehe auch Infos zur Forenbenutzung (dort unter: "Eröffnet neue Threads").
Eröffne bitte einen neuen Thread mit Deiner Frage.
Thread geschlossen.
Gesperrt