查看存储过程状态:
show procedure status;
查看存储过程详细内容:
show create procedure sp;
DELIMITER //
create PROCEDURE test(OUT s int)
BEGIN
select session_id into s from raw_http_transaction where id=1;
set s=s+1;
update raw_http_transaction set session_id=s where id=1;
END
//
DELIMITER ;
查询定时器设置:
show global variables like ‘%event_scheduler%’;
设置定时器:
SET GLOBAL event_scheduler=1;
将定时器与存储过程绑定:
create EVENT if not EXISTS event_test
on schedule every 1 SECOND
on COMPLETION PRESERVE
do call test(@s);
开启针对该事件的定时器:
ALTER EVENT event_test ON COMPLETION PRESERVE ENABLE;
查看定时器事件:
show EVENTS;
调用存储过程:
call test(@s)
较完整的MySQL存储过程案例
DELIMITER //
create PROCEDURE raw_http_transaction_proc()
BEGIN
DECLARE ident int default 0;
declare id char(64);
declare bytesReceived int;
select count(*) into ident from raw_http_transaction;
set ident=ident+1;
select CAST(ident AS CHAR(64)) into id;
select ceiling(rand() * 10000) into bytesReceived;
INSERT INTO raw_http_transaction (
id,
session_id,
create_time,
url,
total_time,
status_code,
error_code,
bytes_sent,
bytes_received,
appData,
http_method,
remote_ip,
stats_carrier,
stats_wan_type,
stats_app_version,
stats_contry,
stats_area,
stats_device_model,
stats_os_version,
stats_host,
stats_url_path
)
VALUES
(
id,
'1',
now(),
'http://apm.ideal.com/test.do',
round(rand() * 100,4),
'200',
'0',
0,
bytesReceived,
NULL,
'POST',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
END
//
DELIMITER ;