stored procedure
創建
create procedure sp_name()
begin
………
end
調用
call sp_name()
注意：存儲過程名稱后面必須加括號，哪怕該存儲過程沒有參數傳遞
刪除
drop procedure sp_name
其他
1.show procedure status
顯示數據庫中所有存儲的存儲過程基本信息，包括所屬數據庫，存儲過程名稱，創建時間等
2.show create procedure sp_name
顯示某一個存儲過程的詳細信息
例子:
CREATE PROCEDURE `bag_app_flow`()
insert into bag_app_flow
select pid,avg(in_speed),avg(out_speed),max(log_time) log_time
from bag_app_flow_raw
where hour(now()) - hour(log_time) = 1
group by pid,date_format(log_time,'%Y-%m-%d %H');
--------------
event
使用下列的任意一句開啟計劃任務：
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
Event Scheduler創建的基本語法是：
CREATE EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT 'comment']
DO sql_statement
如果要調用procedure
call sp_name() ,如
create event flow_job
on schedule every 1 hour
starts '2009-12-26 18:06:00'
do
call bag_ip_flow();
如果有多個procedures,則要加begin...end,如
create event flow_job
on schedule every 1 hour
starts '2009-12-26 18:06:00'
do
begin
call bag_app_flow();
call bag_ip_flow();
call bag_ip_group_flow();
end