<?php
--
-- Transaction Unit Balance Stored Procedure
--

USE `cics_app`;

DROP PROCEDURE IF EXISTS Transaction_UnitBalance;

DELIMITER |
CREATE PROCEDURE Transaction_UnitBalance (IN unit_number INT, IN unit_account_id INT)
BEGIN
    DECLARE unit_balance, unit_credit, unit_debit INT;

    SELECT
        SUM(t.amount) INTO unit_credit
    FROM
        transaction AS t
    WHERE
        t.unit_number = unit_number
      AND
        t.credit_account_id = unit_account_id;

    SELECT
        SUM(t.amount) INTO unit_debit
    FROM
        transaction AS t
    WHERE
        t.unit_number = unit_number
      AND
        t.debit_account_id = unit_account_id;

    SET unit_debit = IFNULL(unit_debit,0);
    SET unit_credit = IFNULL(unit_credit,0);

    SET unit_balance = unit_debit - unit_credit;

    SELECT unit_balance;
END;
|
DELIMITER ;
?>

‹ Transaction Open Units Stored ProcedureupTransactioon Max Unit Number Stored Procedure ›