MySQL Function

Example of MySQL function:

(находит самую свежую транзакцию по указанному invoice_id и возвращает её последнюю дату оплаты "transaction.operation_date")

DELIMITER $$

CREATE OR REPLACE FUNCTION get_last_pay_date ( wanted_invoice_id INT ) RETURNS DATE

BEGIN
    DECLARE last_pay_date DATE;
    DECLARE transaction_id INT;

    SELECT	MAX(transaction.transaction_id) AS transaction_id,
              transaction.operation_date
    INTO transaction_id, last_pay_date
    FROM transaction
             JOIN invoice ON
                invoice.`type` = 'external'
            AND
                invoice.invoice_id = wanted_invoice_id
            AND
                invoice.invoice_id = transaction.invoice_id
    WHERE transaction.value != 0
      AND transaction.status = 'complete'
    GROUP BY transaction.transaction_id DESC
    LIMIT 1;

    RETURN last_pay_date;

END;$$

DELIMITER ;

Example of use case:

    /*
    !!! Разовая функция! !!!
    находит самую свежую транзакцию по указанному invoice_id и возвращает её "transaction.operation_date"
    */
    UPDATE
        invoice
    SET
        invoice.last_pay_date = get_last_pay_date(invoice.invoice_id);
    WHERE
        invoice.`type` = 'external'