mysql存储过程结合job小实例

最近在看MySQL数据库，结合Oracle的job，写了一个MySQL的job例子，大家可以借鉴下，互相学习

 

 

实例说明：

例子中有两个表，一个是t_org，利用event，每隔2分钟插入三个随即整数；另一个表t_tempsums，利用event调用存储过程，每隔4分钟，将表t_org的数据按列求和，将统计数据插入到表中

 

 

实现步骤：

1.         创建表t_org，t_tempsums

Use mdb;

Create table t_org(id int not null aotu_increment,v1 int,v2 int, v3 int,primary key(id));

Create table t_ tempsums (id int not null aotu_increment,v1sum int,v2sum int, v3sum int,primary key(id));

2.         创建操作表t_org的事件t_org_insert

Delimiter //

Create event t_org_insert

On schedule

Every 2 minute

Starts now()

Do

Begin

Insert into t_org(v1,v2,v3) values(round(rand()*10),round(rand()*10),round(rand()*10));

End//

3.         创建操作表t_tempsums的存储过程t_sum

Delimiter //

Create procedure t_sum

Begin

Declare v1sum int default 0;

Declare v2sum int default 0;

Declare v3sum int default 0;

Declare sum1 int default 0;

Declare sum2 int default 0;

Declare sum3 int default 0;

Declare flag int default 0;

Declare cur1 cursor for select v1,v2,v3 from t_org;

Declare continue handler for sqlstate '02000' set flag = 1;

Open cur1;

Fetch cur1 into v1sum,v2sum,v3sum;

While(flag !=1) do

Set sum1 = sum1+v1sum;

Set sum2 = sum2+v2sum;

Set sum3 = sum3+v3sum;

Fetch cur1 into v1sum,v2sum,v3sum;

End while;

Insert into t_tempsums(v1sum,v2sum,v3sum) values(sum1,sum2,sum3);

Close cur1;

End//

4.         创建运行存储过程t_sum的事件t_tempsums_sums

Create event t_tempsums_sums

On schedule

Every 4 minute

Starts now()

Do

Begin

Call t_sum();

End//

 

 

 

补充：忘了说一句，大家按照例子做完以后，可能发现没有运行event，原因大都是因为event_scheduler状态没打开

查看event_scheduler状态

show variables like '%sche%';

修改event_schedule状态

set global event_scheduler=1;