Skip to content

mysql 存储过程和事件实践

mysql 存储过程和事件实践 published on

阶梯计费:http://bbs.csdn.net/topics/390613594
阶梯电价、提成和个税怎么算:http://www.ydt.com.cn/h/Y5ybYmeWlGzIkZdmmpfDbmZnyZeab5dl.html
最近几天研究了下mysql 的存储过程和事件以及workbench的使用,做了一个模拟电表运转的练习,根据用户设置的电器开关时间和阶梯电价,每秒更新一次用电量和电费。
建立数据库的脚本如下:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `eKo` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `eKo` ;
-- -----------------------------------------------------
-- Table `eKo`.`USER`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `eKo`.`USER` (
  `NAME` VARCHAR(20) NOT NULL COMMENT '姓名' ,
  `PWD` VARCHAR(20) NULL COMMENT '密码' ,
  PRIMARY KEY (`NAME`) )
ENGINE = InnoDB
COMMENT = '用户信息表,存储用户姓名和密码';

-- -----------------------------------------------------
-- Table `eKo`.`DIANJIA`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `eKo`.`DIANJIA` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `USER_NAME` VARCHAR(20) NOT NULL ,
  `DIANJIA` DECIMAL(5,2) NOT NULL COMMENT '电费,默认单位为度(KWh)' ,
  `F` TIME NOT NULL COMMENT '开始时间' ,
  `T` TIME NOT NULL COMMENT '结束时间' ,
  PRIMARY KEY (`ID`) ,
  CONSTRAINT `fk_RATE_USER`
    FOREIGN KEY (`USER_NAME` )
    REFERENCES `eKo`.`USER` (`NAME` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '电费表,存储电费的计价,可以按照时间段设置,每个用户都可以进行自由设定';

-- -----------------------------------------------------
-- Table `eKo`.`DIANQI`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `eKo`.`DIANQI` (
  `NAME` VARCHAR(30) NOT NULL COMMENT '名字' ,
  `POWER` INT NOT NULL COMMENT '功率' ,
  PRIMARY KEY (`NAME`) )
ENGINE = InnoDB
COMMENT = '电器表,存储电器名字和功率';

-- -----------------------------------------------------
-- Table `eKo`.`YUSUAN`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `eKo`.`YUSUAN` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `USER_NAME` VARCHAR(20) NOT NULL ,
  `DAY` INT NOT NULL COMMENT '日计划' ,
  `WEEK` INT NOT NULL COMMENT '周计划' ,
  `MONTH` INT NOT NULL COMMENT '月计划' ,
  PRIMARY KEY (`ID`) ,
  CONSTRAINT `fk_PLAN_USER1`
    FOREIGN KEY (`USER_NAME` )
    REFERENCES `eKo`.`USER` (`NAME` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '用电计划表,存储每个用户的用电计划,每天、每周和每月';

-- -----------------------------------------------------
-- Table `eKo`.`DIANFEI`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `eKo`.`DIANFEI` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `USER_NAME` VARCHAR(20) NOT NULL ,
  `DATE` DATE NOT NULL ,
  `DIANFEI` DECIMAL(10,3) NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `fk_BILL_USER1` (`USER_NAME` ASC) ,
  CONSTRAINT `fk_BILL_USER1`
    FOREIGN KEY (`USER_NAME` )
    REFERENCES `eKo`.`USER` (`NAME` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '电费统计,用户的每日、每周和每月电费';

-- -----------------------------------------------------
-- Table `eKo`.`DIANLIANG`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `eKo`.`DIANLIANG` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `USER_NAME` VARCHAR(20) NOT NULL ,
  `DATE` DATE NOT NULL ,
  `DIANLIANG` DECIMAL(20,10) NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `fk_USE_USER1` (`USER_NAME` ASC) ,
  CONSTRAINT `fk_USE_USER1`
    FOREIGN KEY (`USER_NAME` )
    REFERENCES `eKo`.`USER` (`NAME` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '用电数量统计表,存储用户每天、每周和每月的用电数量\n';

-- -----------------------------------------------------
-- Table `eKo`.`SETTIME`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `eKo`.`SETTIME` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `USER_NAME` VARCHAR(20) NOT NULL ,
  `DIANQI_NAME` VARCHAR(30) NOT NULL ,
  `START` TIME NOT NULL COMMENT '开始时间' ,
  `OFF` TIME NOT NULL COMMENT '关闭时间' ,
  PRIMARY KEY (`ID`) ,
  INDEX `fk_SET_USER1` (`USER_NAME` ASC) ,
  INDEX `fk_SET_ELECTRIC1` (`DIANQI_NAME` ASC) ,
  CONSTRAINT `fk_SET_USER1`
    FOREIGN KEY (`USER_NAME` )
    REFERENCES `eKo`.`USER` (`NAME` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_SET_ELECTRIC1`
    FOREIGN KEY (`DIANQI_NAME` )
    REFERENCES `eKo`.`DIANQI` (`NAME` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '用户电器设置表,存储用户设置的电器工作时间';

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

每天插入一条新记录的存储过程脚本如下:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `count_insert`()
    READS SQL DATA
BEGIN
     DECLARE NA VARCHAR(20);
     DECLARE done INT DEFAULT 0;
     DECLARE cur1 CURSOR FOR SELECT NAME FROM USER;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

     OPEN cur1;
     FETCH cur1 INTO NA;
     REPEAT
        insert into DIANLIANG (USER_NAME,DATE,DIANLIANG) values (NA,CURRENT_DATE(),0);
        insert into DIANFEI (USER_NAME,DATE,DIANFEI) values (NA,CURRENT_DATE(),0);
        FETCH cur1 INTO NA;
     UNTIL done=1 END REPEAT;
     CLOSE cur1;
END

每秒更新用电量和电费的存储过程如下:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `count_update`()
    READS SQL DATA
BEGIN
    DECLARE dlt_dianliang DECIMAL(10,10) DEFAULT 0;   -- 用电量
    DECLARE dlt_dianfei DECIMAL(10,10) DEFAULT 0;     -- 电费
    DECLARE e_dianliang DECIMAL(10,10) DEFAULT 0; -- 单个电器用电量
    DECLARE u_name VARCHAR(20);
    DECLARE done1 INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT NAME FROM USER;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1=1;

    OPEN cur1;
        FETCH cur1 INTO u_name;
        REPEAT
        -- 电量和电费初始化,每个用户为0
        SET dlt_dianliang=0;
        SET dlt_dianfei=0;
        BEGIN
            DECLARE e_name VARCHAR(20);     -- 电器名称
            DECLARE e_power INT DEFAULT 0;      -- 电器功率
            DECLARE jifei_time INT DEFAULT 0;   -- 计费时间,此处为电器只要开机为1秒,关机为0
            DECLARE jifei_dianjia DECIMAL(10,2);    -- 当前时间段的电价
            DECLARE start_time,off_time TIME;
            DECLARE done2 INT DEFAULT 0;
            DECLARE cur2 CURSOR FOR SELECT DIANQI_NAME,START,OFF FROM SETTIME WHERE USER_NAME=u_name;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2=1;


            OPEN cur2;
                -- 取用户设置的电器的开关时间
                FETCH cur2 INTO e_name,start_time,off_time;
                -- SELECT e_name,start_time,off_time; -- 调试输出*******************************
                REPEAT
                -- 计费标志初始化
                SET jifei_time=0;
                -- 跨天时间修改
                IF start_time+0 > off_time+0 THEN
                    SET off_time=off_time+240000;
                END IF;
                IF (CURRENT_TIME+0) between (start_time+0) and (off_time+0) THEN
                    SET jifei_time=1;
                END IF;
                -- 电器用电量初始化
                SET e_dianliang=0;
                IF jifei_time=1 THEN
                    BEGIN
                        DECLARE done3 INT DEFAULT 0;
                        DECLARE cur3 CURSOR FOR SELECT POWER FROM DIANQI WHERE NAME=e_name;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done3=1;

                        OPEN cur3;
                        -- 取电器功率
                            FETCH cur3 INTO e_power;
                            REPEAT
                             -- 计算单个电器的每秒用电量
                            SET e_dianliang=e_power/1000/3600; -- 每秒用电量,因为存储过程每秒执行1次

                            BEGIN
                                DECLARE f_time,t_time TIME;
                                DECLARE c_dianjia DECIMAL(20,10) DEFAULT 0;
                                DECLARE done4 INT DEFAULT 0;
                                DECLARE cur4 CURSOR FOR SELECT DIANJIA,F,T FROM DIANJIA WHERE USER_NAME=u_name;
                                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done4=1;
                                OPEN cur4;
                                    -- 取阶梯电价
                                    FETCH cur4 INTO c_dianjia,f_time,t_time;
                                    REPEAT
                                        -- 跨天时间修改
                                        IF f_time+0 > t_time+0 THEN
                                            SET t_time=t_time+240000;
                                        END IF;
                                        IF (CURRENT_TIME+0) between (f_time+0) and (t_time+0) THEN
                                            SET jifei_dianjia=c_dianjia;
                                        END IF;
                                        -- SELECT u_name,CURRENT_TIME,c_dianjia; -- 调试输出********************
                                    FETCH cur4 INTO c_dianjia,f_time,t_time;
                                    UNTIL done4=1 END REPEAT;
                                CLOSE cur4;
                            END;
                            FETCH cur3 INTO e_power;
                            UNTIL done3=1 END REPEAT;
                        CLOSE cur3;
                    END;
                END IF;
                -- 各种电器用电量累加
                SET dlt_dianliang=dlt_dianliang+e_dianliang;
                SET dlt_dianfei=dlt_dianliang*jifei_dianjia;  -- 每秒电费
                -- SELECT u_name,CURRENT_TIME,dianjia,dlt_dianliang,dlt_dianfei; -- 调试输出****
                -- SELECT u_name,e_name,jifei_time; -- 调试输出***********************************
                FETCH cur2 INTO e_name,start_time,off_time;
                UNTIL done2=1 END REPEAT;
            CLOSE cur2;
        END;
        -- 根据用户名计算每个用户的电量和电费
        -- SELECT u_name; -- 调试输出*************************************
        -- SELECT dlt_dianliang,dlt_dianfei;
        -- 更新每个用户的电量,按秒累计
        UPDATE DIANLIANG SET DIANLIANG=DIANLIANG+dlt_dianliang WHERE USER_NAME=u_name AND DATE=CURRENT_DATE();
        -- 更新每个用户的电费
        UPDATE DIANFEI SET DIANFEI=DIANFEI+dlt_dianfei WHERE USER_NAME=u_name AND DATE=CURRENT_DATE();
        FETCH cur1 INTO u_name;
        UNTIL done1=1 END REPEAT;
    CLOSE cur1;
END

建立事件:
每天零时定时执行插入新记录的存储过程:

CREATE EVENT e_count_insert ON SCHEDULE EVERY 1 DAY STARTS '2012-07-01 00:00:00' ON COMPLETION NOT PRESERVE DO call count_insert;

每秒执行一次更新电量和电费的存储过程:

CREATE EVENT e_count_upate ON SCHEDULE EVERY 1 SECOND DO call count_update;