[MySQL]存储过程：如何用一条命令查询合同金额+发货记录+收款记录

表结构

假设表结构如下：

• 合同表 (contracts):

• contract_code (合同编码，主键)
• contract_amount (合同金额)

• 发货表 (deliveries):

• delivery_id (发货记录编号，主键)
• contract_code (合同编码，外键)
• delivery_amount (发货金额)
• delivery_date (发货日期)

• 收款表 (payments):

• payment_id (收款记录编号，主键)
• contract_code (合同编码，外键)
• payment_amount (收款金额)
• payment_date (收款日期)

存储过程

DELIMITER //

CREATE PROCEDURE GetContractDetailsByCode(IN p_contract_code VARCHAR(10))
BEGIN
    -- 声明变量
    DECLARE total_delivery_amount DECIMAL(15, 2);
    DECLARE total_payment_amount DECIMAL(15, 2);
    DECLARE contract_amount DECIMAL(15, 2);
    DECLARE payment_percentage DECIMAL(5, 2);

    -- 获取合同金额
    SELECT contract_amount INTO contract_amount
    FROM contracts
    WHERE contract_code = p_contract_code;

    -- 获取总发货金额
    SELECT COALESCE(SUM(delivery_amount), 0) INTO total_delivery_amount
    FROM deliveries
    WHERE contract_code = p_contract_code;

    -- 获取总收款金额
    SELECT COALESCE(SUM(payment_amount), 0) INTO total_payment_amount
    FROM payments
    WHERE contract_code = p_contract_code;

    -- 计算收款百分比
    IF contract_amount > 0 THEN
        SET payment_percentage = (total_payment_amount / contract_amount) * 100;
    ELSE
        SET payment_percentage = 0;
    END IF;

    -- 输出结果
    SELECT 
        p_contract_code AS contract_code,
        contract_amount,
        total_delivery_amount AS total_delivery,
        total_payment_amount AS total_payment,
        payment_percentage AS payment_percentage;

    -- 查询发货记录
    SELECT 
        delivery_id,
        contract_code,
        delivery_amount,
        delivery_date
    FROM deliveries
    WHERE contract_code = p_contract_code;

    -- 查询收款记录
    SELECT 
        payment_id,
        contract_code,
        payment_amount,
        payment_date
    FROM payments
    WHERE contract_code = p_contract_code;

END //

DELIMITER ;

调用存储过程

CALL GetContractDetailsByCode('20230001');