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/