存储过程示例001

编写存储过程
入参：(IN p_tjdate VARCHAR(10), IN p_channel VARCHAR(64), IN p_account_id VARCHAR(64),
IN p_amount INT)

SELECT sum(payment_amount) into v_amount from payment_tj_dayhour00000 where tjdate>=p_tjdate and accountid = p_account_id;

如果test00002表中存在记录，（根据tjdate和account_id查询存在则更新）
更新语句：
UPDATE test00002
SET amount = v_amount
WHERE
tjdate = p_tjdate
AND channel = p_channel
AND account_id = p_account_id;
否则插入：
INSERT test00002 ( tjdate, channel, account_id, amount, create_time )
VALUES
(
p_tjdate,
p_channel,
p_account_id,
p_amount,
NOW());

DELIMITER //

DROP PROCEDURE IF EXISTS proc_test0001 //

CREATE PROCEDURE proc_test0001(
    IN p_tjdate VARCHAR(10),
    IN p_channel VARCHAR(64),
    IN p_account_id VARCHAR(64),
    IN p_amount INT
)
BEGIN
    DECLARE v_amount INT;
    
    SELECT SUM(payment_amount) INTO v_amount
    FROM payment_tj_dayhour00000
    WHERE tjdate >= p_tjdate AND accountid = p_account_id;
    
    IF v_amount IS NOT NULL AND v_amount > 0 THEN
        IF EXISTS (
            SELECT 1
            FROM test00002
            WHERE tjdate = p_tjdate AND channel = p_channel AND account_id = p_account_id
        ) THEN
            UPDATE test00002
            SET amount = v_amount
            WHERE tjdate = p_tjdate AND channel = p_channel AND account_id = p_account_id;
        ELSE
            INSERT INTO test00002 (tjdate, channel, account_id, amount, create_time)
            VALUES (p_tjdate, p_channel, p_account_id, v_amount, NOW());
        END IF;
    END IF;
END //


DELIMITER ;