How To Create “EVENT” In My-Sql

My-Sql Event with Scheduling Logic

 

To Create  Event in My-Sql .

To create event in My-SQL , we have to follow a Pattern which is written below..

1. Declare Delimiter

2. Define Name of the event

3. Define when to schedule

4. Start with “DO”

5. Then “Begin”

6. Define Business logic

(Like variable declaration, job which you want to schedule through your Event, any condition…So mainly it is the body of your Event)

7. Then Declare end of your event like “END <delimiter>”

8. Change Delimiter to normal Delimiter.

Example:- Here I tried to call a stored procedure in this event which is scheduled after every 4-Hour, and passed the parameter for that stored procedure by taking two date parameter, and also with some additional parameters, I tried to use loop and if-else condition also in this event…     (Tested and executed Event)

……………………………………………………………………………………………………………….

delimiter $$

CREATE EVENT ue_schedule_test

ON SCHEDULE

EVERY 4 HOUR

DO

BEGIN

DECLARE to_temp TEXT(25);

DECLARE from_temp TEXT(25);

DECLARE pv_temp TEXT(20);

DECLARE done INT DEFAULT FALSE;

DECLARE curs1 CURSOR FOR SELECT name FROM data ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN curs1;

read_loop: LOOP

FETCH curs1 INTO pv_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL -4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO from_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL 4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO to_temp;

DELETE FROM availibility WHERE Date=from_temp;

CALL usp_availability_test(from_temp,to_temp,pv_temp,’Total’,’+00:00′,’custom’);

IF done THEN

LEAVE read_loop;

END IF;

END LOOP;

CLOSE curs1;

END $$

delimiter ;

………………………………………………………………………………………………..