How to set delay for MySQL trigger/procedure execution?

To set delay, you can use SLEEP(). Let us implement SLEEP() in the procedure execution delay.

First, we will create a stored procedure −

mysql> DELIMITER //
mysql> CREATE PROCEDURE delayInMessage()
   -> BEGIN
   -> SELECT SLEEP(20);
   -> SELECT "AFTER SLEEPING 20 SECONDS, BYE!!!";
   -> END
   -> //
Query OK, 0 rows affected (0.30 sec)
mysql> DELIMITER ;

Now you can call the stored procedure with the help of CALL command. Following is the syntax −

CALL yourStoredProcedureName();

Following is the query to call the above-stored procedure and check the execution delay −

mysql> call delayInMessage();

This will produce the following output −

+-----------+
| SLEEP(20) |
+-----------+
| 0         |
+-----------+
1 row in set (20.00 sec)

+------------------------------------+
| AFTER SLEEPING 20 SECONDS, BYE!!!  |
+------------------------------------+
| AFTER SLEEPING 20 SECONDS, BYE!!!  |
+------------------------------------+
1 row in set (20.01 sec)
Query OK, 0 rows affected (20.04 sec)

Look at the above sample output, the execution delay is 20.01 sec.

Nishtha Thakur

Published on 15-Apr-2019 13:28:18

Previous Page Print Page