MYSQL中的存储过程编写实例

此存储过程实现功能为：在目前的级别账号表account_exts上，通过升级日志表level_logs，回退到某一个时刻账号的级别状态，从而达到统计特定某一个时刻级别情况的目的。

账号表：account_exts
升级日志表：level_logs
级别统计表：level_by_hours

以下是相应的存储过程代码，请参考。

CREATE PROCEDURE `count_level_status_by_day`(IN `level_date_input` date)
BEGIN
-- 定义级别日志表中的变量
DECLARE
    levelvar INT;

DECLARE
    accountvar VARCHAR (255);

DECLARE
    gatewayvar VARCHAR (255);

DECLARE
    channelvar VARCHAR (255);

-- 定义循环结束的标志
DECLARE
    done INT DEFAULT 0;

-- 定义游标循环级别日志表
DECLARE
    level_log_cursor CURSOR FOR SELECT
        account,
        channel,
        gateway,
        MIN(`level`) - 1
    FROM
        level_logs
    WHERE
        modified > DATE_ADD(
            level_date_input,
            INTERVAL 1 DAY
        )
    GROUP BY
        account,
        channel,
        gateway;

-- 循环结束的条件
DECLARE
    CONTINUE HANDLER FOR SQLSTATE '02000'
SET done = 1;

-- 创建临时的当天的账号级别表
CREATE TABLE
IF NOT EXISTS level_by_account (
    level_date date,
    account VARCHAR (255) NOT NULL,
    channel VARCHAR (255) NOT NULL,
    gateway VARCHAR (255) NOT NULL,
    now_level INT (11) NOT NULL
);

-- 删除其中的历史数据
DELETE
FROM
    level_by_account;

-- 插入临时表中数据
INSERT INTO level_by_account (
    account,
    channel,
    gateway,
    now_level
) SELECT
    account,
    channel,
    gateway,
    last_level
FROM
    account_exts;

UPDATE level_by_account
SET level_date = level_date_input;

OPEN level_log_cursor;

-- 循环循环处理，后来升级的账号的级别，更新为最小的升级级别-1
REPEAT
    FETCH level_log_cursor INTO accountvar,
    channelvar,
    gatewayvar,
    levelvar;

-- 更新账号的当时级别
UPDATE level_by_account
SET now_level = levelvar
WHERE
    account = accountvar
AND channel = channelvar
AND gateway = gatewayvar;

UNTIL done
END
REPEAT
;

CLOSE level_log_cursor;

-- 删除统计表中的数据
DELETE
FROM
    level_by_hours
WHERE
    level_date = level_date_input;

-- 插入最新的统计数据
INSERT INTO level_by_hours (
    level_date,
    gateway,
    channel,
    LEVEL,
    amount,
    modified
)(
    SELECT
        level_date,
        gateway,
        channel,
        now_level,
        COUNT(*),
        NOW()
    FROM
        level_by_account
    GROUP BY
        level_date,
        gateway,
        channel,
        now_level
);

END

1、调用存储过程的语句：call count_level_status_by_day(‘2015-04-02’)；
2、带有out参数的存储过程：call count_level(@total)；
3、删除存储过程：drop procedure name;
4、其中也可以使用if逻辑，语法为：if 。。。then 。。。end if;
5、查询创建存储过程的语句为：SHOW CREATE PROCEDURE count_level_status_by_day;
6、获得何时，谁创建和修改了存储过程：SHOW PROCEDURE STATUS LIKE ‘count_level_status_by_day’;