最近几天研究了下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;
