mysql用事件和存储过程_mysql 存储过程和事件实践

最近几天研究了下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 = '用户电器设置表，存储用户设置的电器工作时间';

每天插入一条新记录的存储过程脚本如下：

-- --------------------------------------------------------------------------------

-- 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;