So hier die gespeicherte Prozedur. Erstellt und bearbeitet mit dem
MySQL Query Browser.
Also in dem Tool bzw. der Console läßt sich die Prozedur astrein ausführen nur gelingt
mir der Aufruf in Delphi nicht, keine ahnnung wieso.
SQL-Code:
DELIMITER $$
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `CBase`.`GetList` $$
CREATE DEFINER=`CBaseAdmin`@`%`
PROCEDURE `GetList`(
IN _ParentID
INT)
BEGIN
DECLARE _done
INT DEFAULT 0;
DECLARE _PoolID
INT;
DECLARE _Ident
VARCHAR(10);
DECLARE _Quantity
FLOAT(10,2);
DECLARE curNodes
CURSOR FOR
SELECT PartsUnit.PID, PartsPool.Ident, PartsUnit.Quantity
FROM PartsUnit
LEFT JOIN PartsPool
ON PartsPool.ID=PartsUnit.PID
WHERE GID = _ParentID
ORDER BY (
ASCII(PartsPool.Ident) > 48
AND ASCII(PartsPool.Ident) < 57), PartsPool.Ident;
DECLARE CONTINUE HANDLER
FOR SQLSTATE '
02000'
SET _done = 1;
Open curNodes;
REPEAT
FETCH curNodes
INTO _PoolID, _Ident, _Quantity;
IF _done = 0
Then
IF(
SELECT Count(*)
FROM PartsUnit
WHERE GID = _PoolID) > 0
Then
INSERT INTO Temp(PID, ParentID, Ident, Quantity)
VALUES (_PoolID, _ParentID, _Ident, _Quantity);
END IF;
CALL GetList(_PoolID);
END IF;
UNTIL _done
END REPEAT;
END $$
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `CBase`.`GetTree` $$
CREATE DEFINER=`CBaseAdmin`@`%`
PROCEDURE `GetTree`(
IN _ParentID
INT)
BEGIN
SET @@max_sp_recursion_depth = 100;
CREATE TEMPORARY TABLE Temp(PID
INT, ParentID
INT, Ident
VARCHAR(45), Quantity
FLOAT(10,2));
CALL GetList(_ParentID);
SELECT *
FROM Temp;
DROP TEMPORARY TABLE Temp;
SET @@max_sp_recursion_depth = 0;
END $$
-- -----------------------------------------------------------------------------
DELIMITER ;