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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25583515/viewspace-2152060/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/25583515/viewspace-2152060/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值