mysql -uroot -p

show databases;

use scm;

show tables;

show procedure status;

【执行存储过程】

call avgUpdatePice(); 

/*============================*/

/* Table: 存储过程运行日志表结构           */

/*============================*/

create table pro_log

(

   /* 日志编号,自动增长 */

   pro_id                 int not null auto_increment,

   /* 存储过程名字  */

   pro_name               varchar(20),

   /* 存储过程执行产生的信息*/

   pro_remark             varchar(20),

   /* 存储过程执行的时间 */

   pro_date                datetime,

   /* 设置日志编号为主键 */

   primary key (pro_id)

);

insert   into pro_log(pro_name,pro_remark,pro_date)

values ('avgUpdatePrice','','2015-03-09');

delimiter //

CREATE PROCEDURE avgUpdatePice()

BEGIN

DECLARE iCount   INT DEFAULT 0;

 create table avg_prices as

      select goods_id,goods_type,goods_name,in_buy_price,ROUND(sum(in_total_price)/sum(in_amount),2) as 'junjia',NOW(),count(*) from scm.in_warehouse_detail

 group by goods_id,goods_type,goods_name

 order by goods_id,goods_type,goods_name;

    select count(*) into iCount   from goods g,avg_prices a

           where g.goods_Id = a.goods_id

           and g.goods_sel_price != a.junjia

           and g.goods_buy_price != a.junjia;

           if  iCount  > 0 then

            update avg_prices a,goods g set  g.goods_buy_price = a.junjia

            where g.goods_Id = a.goods_id and g.goods_sel_price != a.junjia ;

     end if;

      insert   into pro_log(pro_name,pro_remark,pro_date) values ('avgUpdatePice',iCount,NOW());

 DROP TABLE IF EXISTS avg_prices;

END; // delimiter ;

call avgUpdatePice(); 

-----------------------------每天定时执行存储过程 在命令行里执行一次加入就可以了--------------------------------------------------------------------

CREATE EVENT course_avgUpdatePice

ON SCHEDULE EVERY 1 DAY STARTS '2016-04-21 21:30:00'

ON COMPLETION PRESERVE

ENABLE

DO

call avgUpdatePice();