返回过程执行代码在MySQL

Question

我有一个存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNotExecutedOrders`(IN contractID INT(11)) 
BEGIN 
SELECT idContract, idOrder, ProductID, Quantity, (SUM(`order`.Quantity))*(product.Price) as 'Total amount' 
FROM typography.contract 
JOIN typography.`order` ON contract.idContract=`order`.ContractID 
JOIN typography.product ON `order`.ProductID=product.idProduct 
WHERE idContract=contractID and ExecutionDate IS NULL 
GROUP BY idOrder 
ORDER BY idOrder; 
END 

我需要那么它是否有与该合同ID没有合同返回的执行代码<> 0来解决它，和合同列表如果与该合同ID有合同，则执行代码= 0。

Answer 1

使用代码的OUT参数。然后使用COUNT(*)查询进行设置。

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNotExecutedOrders`(IN contractID INT(11), OUT executionCode INT) 
BEGIN 

    SELECT COUNT(*) > 0 INTO executionCode 
    FROM typography.contract 
    JOIN typography.`order` ON contract.idContract=`order`.ContractID 
    JOIN typography.product ON `order`.ProductID=product.idProduct 
    WHERE idContract=contractID and ExecutionDate IS NULL; 

    IF (executionCode) 
     SELECT idContract, idOrder, ProductID, Quantity, (SUM(`order`.Quantity))*(product.Price) as 'Total amount' 
     FROM typography.contract 
     JOIN typography.`order` ON contract.idContract=`order`.ContractID 
     JOIN typography.product ON `order`.ProductID=product.idProduct 
     WHERE idContract=contractID and ExecutionDate IS NULL 
     GROUP BY idOrder 
     ORDER BY idOrder; 
    END IF; 
END