Learning notes for stored procedures in Mariadb,mysql

Environment: Win7 64-bit + navicate for MySQL 10.1.7 + vmware11.1.0 + CentOS6.6 64-bit + MariaDB10.0.20

Two tables: data5_table table and data15_table table.

Business needs, data5_table will be in time for every five minutes when the data, such as 00,05,10,15 ...

Data15_table table will be in time for every 15 minutes when the data, such as 00,15,30,45,00 ...

And data15_table is the average of three records in Data5_table.

The stored procedure is triggered whenever a data5_table is entered into a piece of data.

Two table fields are the same: ID (primary key, bigint), Datatime (time, time_t), Val (data, float)

DATA5 is set in the following

BEGIN call Data15_insert (new.datatime,new.id); END

The DATA15 is configured as follows:

BEGIN call Data15_insert (new.datatime,new.id); END

Create the storage as follows:

' Data15_insert ' (in ' endtime ' bigint,in ' Sid ' BigInt)

begin  #Routine  body goes here... declare  starttime ,lasttime  Temp  bigint; declare sval float;  select endtime %900 into  temp; IF temp=0 THEN  SELECT endtime-600 INTO starttime ;   SELECT endtime   INTO lasttime; ELSEIF temp=600 THEN   SELECT endtime-300 INTO starttime;  SELECT endtime+300 INTO  Lasttime;      elseif temp=300 then  select endtime  INTO starttime;  SELECT endtime+600 INTO lasttime; END IF;   select avg (val)  INTO sval  FROM data5_table   WHERE  datatime<=lasttime and datatime>=starttime and id=sid;      Select count (ID)  into ncount from data15_table where datatime=lasttime and id=sid;   IF ncount=1 THEN     DELETE FROM data15_table       WHERE datatime=lasttime AND id=sid;  END IF;   Insert into data15_table (Datatime,id,val)    values (lasttime ,id,sval); END

Attention:

1, do not use "select A=b;" This way, you should use "Select B into A;" The error "Cannot return data set".

2, do not say the variables in the stored procedure are defined as field names, compilation can pass, execution does not pass.

3, do not update the table in the stored procedure, I update the Times wrong "wireless recursion" and other errors, the above example, I used the deletion after the insertion of the way to replace the update.

4, note that if after without parentheses, and the difference between ordinary code and stored procedures equals the number of the difference between the value, the place uses "=" instead of "= =".

This article is from "Throat Ding Dong Squire" blog, please be sure to keep this source http://lddyw.blog.51cto.com/4151746/1699198

Learning notes for stored procedures in Mariadb,mysql