MySQL ROUTINE & EVENT

ROUTINE(procedure&funcation)
建立：

Create procedure|function sp

修改

Alter procedure|function sp

呼叫

Call sp(param)

建立sp前指定：DELIMITER $$ 將語句結束的; 替代

DELIMITER $$

CREATE PROCEDURE SP()

…

End $$

DELIMITER;

增加trigger，限制delete操作

DELIMITER $$

CREATE TRIGGER test1.tri

 BEFORE DELETE ON test1.emp

FOR EACH ROW

BEGIN

 DECLARE msg varchar(50) ;

 SET msg = "table記錄無法刪除"; 

 SIGNAL SQLSTATE 'HY000' SET mysql_errno = 22, message_text = msg;

END;$$

delimiter ;

說明：HY000為系統內部錯誤號，22為自定義的顯示錯誤號，msg為錯誤文字 

檢視狀態

Show procedure status like ‘xxx’

檢視定義

Show procedure xxx;

檢視資料字典

select * from information_schema.routines

EVENT(schedule)

類似oracle job

建立

create event myevent

    on schedule every 1 hour

    do

        update emp1 set emp_no=emp_no+1;

檢視狀態

mysql> show events\G;

*************************** 1. row ***************************

                  Db: test1

                Name: myevent

             Definer: root@localhost

           Time zone: SYSTEM

                Type: ONE TIME

          Execute at: 2016-10-14 12:10:53

      Interval value: NULL

      Interval field: NULL

              Starts: NULL

                Ends: NULL

              Status: ENABLED

          Originator: 0

character_set_client: utf8

collation_connection: utf8_general_ci

  Database Collation: utf8_general_ci

1 row in set (0.01 sec)

開啟排程器,預設關閉：

mysql> show variables like '%schedule%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| event_scheduler | OFF   |

+-----------------+-------+

mysql> set global event_scheduler=1;

Query OK, 0 rows affected (0.01 sec)

mysql> show processlist \G;

*************************** 4. row ***************************

     Id: 17

   User: event_scheduler

   Host: localhost

     db: NULL

Command: Daemon

   Time: 62

  State: Waiting on empty queue

   Info: NULL

禁用：

mysql> alter event myevent disable;