Wednesday, 20 October 2021

Scheduled Events sql videos in telugu112

 https://youtu.be/ihA4tiRCjAE

--------------------------------------------------------
MySQL - Scheduled Events Basics


What is Scheduled Event? –

Events are those tasks, defined in the form of SQL statements and stored in the database, to run automatically for once or at specified interval repeatedly.

MySQL events are also recognized as Temporal Triggers. Triggers, because they are activated automatically, but instead of in response to DML, they are invoked by time. Hence they are also called as Scheduled Events.

Why create Scheduled Events –

Scheduled events can be used for variety of purposes like – Optimizing database tables, cleaning up logs, archiving data, generating complex reports, etc. to name a few, during the off-peak time of use of database.

Event Scheduler –

In many ways the MySQL Event Scheduler is similar to CRON utility in Linux or Windows' task scheduler. It is a thread in the MySQL DBMS to execute all scheduled events and it must be running for that purpose.

SHOW PROCESSLIST;



SET GLOBAL event_scheduler = ON;

Setting it to OFF with the same command as above will stop the Event Scheduler.

Creating an Event –

You have to use CREATE EVENT command to create an event. The generic syntax is –

CREATE EVENT [IF NOT EXISTS] <event_name>
ON SCHEDULE <schedule>
DO
<event_body>

The event_body may contain an executable block enclosed in combined statement BEGIN..END or there can also be call to a stored procedure, or one or more SQL statements. You can drop an event, but remember than if you have used a procedure in the event then dropping it does not drop that procedure –

DROP EVENT [IF EXISTS] <event_name>;

Visit lesson next to continue to learn how to manage the Scheduled Events.


---------
DROP EVENT IF EXISTS upd_orders;

CREATE EVENT IF NOT EXISTS upd_orders
ON SCHEDULE AT NOW() + INTERVAL 1 MINUTE
## ON COMPLETION PRESERVE
DO
UPDATE orders SET amount = amount + 500;
----------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ramesh
Database changed
mysql> select * from emp;
+--------+--------+------------+--------+------------+
| emp_id | name   | birthdate  | gender | hire_date  |
+--------+--------+------------+--------+------------+
|    102 | Joseph | 1978-05-12 | M      | 2014-10-21 |
|    103 | Mike   | 1984-10-13 | M      | 2017-10-28 |
|    105 | Marie  | 1990-02-11 | F      | 2018-10-12 |
+--------+--------+------------+--------+------------+
3 rows in set (0.06 sec)

mysql> show tables;
+------------------+
| Tables_in_ramesh |
+------------------+
| customers        |
| emp              |
| orders           |
| pap_del_log      |
| pap_log          |
| papers           |
| pay              |
| students         |
| studpap          |
+------------------+
9 rows in set (0.76 sec)

mysql> select * from orders;;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 |         300 |
| 13 | 2021-09-11 | 321.50 |         301 |
| 14 | 2021-06-12 | 789.67 |         303 |
| 15 | 2021-01-03 | 102.50 |         304 |
| 16 | 2021-04-11 | 850.25 |         305 |
| 17 | 2021-03-13 | 689.25 |         500 |
| 18 | 2021-02-11 |  31.50 |         301 |
| 19 | 2021-06-12 |  79.67 |         303 |
| 20 | 2021-08-03 | 102.50 |         304 |
| 21 | 2021-07-11 |  80.25 |         305 |
| 22 | 2021-12-13 | 669.25 |         500 |
+----+------------+--------+-------------+
11 rows in set (0.02 sec)

ERROR:
No query specified

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db     | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL   | Daemon  | 808446 | Waiting on empty queue | NULL             |
| 35 | root            | localhost:50284 | ramesh | Query   |      0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------------+--------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 |         300 |
| 13 | 2021-09-11 | 321.50 |         301 |
| 14 | 2021-06-12 | 789.67 |         303 |
| 15 | 2021-01-03 | 102.50 |         304 |
| 16 | 2021-04-11 | 850.25 |         305 |
| 17 | 2021-03-13 | 689.25 |         500 |
| 18 | 2021-02-11 |  31.50 |         301 |
| 19 | 2021-06-12 |  79.67 |         303 |
| 20 | 2021-08-03 | 102.50 |         304 |
| 21 | 2021-07-11 |  80.25 |         305 |
| 22 | 2021-12-13 | 669.25 |         500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)

mysql> DROP EVENT IF EXISTS upd_orders;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql>
mysql> CREATE EVENT IF NOT EXISTS upd_orders
    -> ON SCHEDULE AT NOW() + INTERVAL 2 MINUTE
    -> ## ON COMPLETION PRESERVE
    -> DO
    -> UPDATE orders SET amount = amount + 500;
Query OK, 0 rows affected (0.43 sec)

mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 |         300 |
| 13 | 2021-09-11 | 321.50 |         301 |
| 14 | 2021-06-12 | 789.67 |         303 |
| 15 | 2021-01-03 | 102.50 |         304 |
| 16 | 2021-04-11 | 850.25 |         305 |
| 17 | 2021-03-13 | 689.25 |         500 |
| 18 | 2021-02-11 |  31.50 |         301 |
| 19 | 2021-06-12 |  79.67 |         303 |
| 20 | 2021-08-03 | 102.50 |         304 |
| 21 | 2021-07-11 |  80.25 |         305 |
| 22 | 2021-12-13 | 669.25 |         500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)

mysql> show events;
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| Db     | Name       | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| ramesh | upd_orders | [email protected] | SYSTEM    | ONE TIME | 2021-10-10 18:14:52 | NULL           | NULL           | NULL   | NULL | ENABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_0900_ai_ci |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.42 sec)

mysql> show events \G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:14:52
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show events \G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:14:52
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show events \G;
Empty set (0.00 sec)

ERROR:
No query specified

mysql> select * from orders;
+----+------------+---------+-------------+
| id | order_date | amount  | customer_id |
+----+------------+---------+-------------+
| 12 | 2021-05-10 |  699.99 |         300 |
| 13 | 2021-09-11 |  821.50 |         301 |
| 14 | 2021-06-12 | 1289.67 |         303 |
| 15 | 2021-01-03 |  602.50 |         304 |
| 16 | 2021-04-11 | 1350.25 |         305 |
| 17 | 2021-03-13 | 1189.25 |         500 |
| 18 | 2021-02-11 |  531.50 |         301 |
| 19 | 2021-06-12 |  579.67 |         303 |
| 20 | 2021-08-03 |  602.50 |         304 |
| 21 | 2021-07-11 |  580.25 |         305 |
| 22 | 2021-12-13 | 1169.25 |         500 |
+----+------------+---------+-------------+
11 rows in set (0.00 sec)

mysql> show events \G;
Empty set (0.00 sec)

ERROR:
No query specified

mysql> DROP EVENT IF EXISTS upd_orders;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql>
mysql> CREATE EVENT IF NOT EXISTS upd_orders
    -> ON SCHEDULE AT NOW() + INTERVAL 1 MINUTE
    -> ON COMPLETION PRESERVE
    -> DO
    -> UPDATE orders SET amount = amount + 500;
Query OK, 0 rows affected (0.15 sec)

mysql> show events;
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| Db     | Name       | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| ramesh | upd_orders | root[email protected] | SYSTEM    | ONE TIME | 2021-10-10 18:17:13 | NULL           | NULL           | NULL   | NULL | ENABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_0900_ai_ci |
+--------+------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> show events\G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:17:13
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show events\G;
*************************** 1. row ***************************
                  Db: ramesh
                Name: upd_orders
             Definer: [email protected]
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-10-10 18:17:13
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: DISABLED
          Originator: 1
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.05 sec)

ERROR:
No query specified

mysql> select * from orders;
+----+------------+---------+-------------+
| id | order_date | amount  | customer_id |
+----+------------+---------+-------------+
| 12 | 2021-05-10 | 1199.99 |         300 |
| 13 | 2021-09-11 | 1321.50 |         301 |
| 14 | 2021-06-12 | 1789.67 |         303 |
| 15 | 2021-01-03 | 1102.50 |         304 |
| 16 | 2021-04-11 | 1850.25 |         305 |
| 17 | 2021-03-13 | 1689.25 |         500 |
| 18 | 2021-02-11 | 1031.50 |         301 |
| 19 | 2021-06-12 | 1079.67 |         303 |
| 20 | 2021-08-03 | 1102.50 |         304 |
| 21 | 2021-07-11 | 1080.25 |         305 |
| 22 | 2021-12-13 | 1669.25 |         500 |
+----+------------+---------+-------------+
11 rows in set (0.02 sec)

mysql>

No comments:

Post a Comment

Note: only a member of this blog may post a comment.