实例:获取登录、登出、操作日志、命令日志(从系统日志表查询,命令日志表查询结过存储到临时表中,得到所有的日志,然后按条件进行筛选)
create function getLog(uname VARCHARACTER(200),startTime datetime,endTime datetime,logtype VARCHAR(2),logremark VARCHAR(200),page int(11),pagesize int(11),out total int)
BEGIN
DECLARE t_item VARCHAR(1);drop table if exists t;
/**--创建临时表-**/
CREATE TEMPORARY TABLE t(
userid VARCHARACTER(20),
username VARCHARACTER(100),
time datetime ,
remark VARCHARACTER(512),
type VARCHAR(1)
);
/**-将systemlog、command_log表数据插入到临时表中-**/
set t_item = 1; /*--登入--*/
insert into t(`userid` ,`username`,`time`,`remark`,`type`)
select s.userid,m_user.username,s.time,s.remark,t_item from systemlog s left join m_user on m_user.userid=s.userid where s.operation="登录";
set t_item = 2; /*--登出--*/
insert into t(`userid` ,`username`,`time`,`remark`,`type`)
select s.userid,m_user.username,s.time,s.remark,t_item from systemlog s left join m_user on m_user.userid=s.userid where s.operation="注销";
set t_item = 3;/*--操作日志--*/
insert into t(`userid` ,`username`,`time`,`remark`,`type`)
select s.userid,m_user.username,s.time,s.remark,t_item from systemlog s left join m_user on m_user.userid=s.userid where s.operation!="登录" and s.operation!="注销";
set t_item = 4;/*--命令日志--*/
insert into t(`userid` ,`username`,`time`,`remark`,`type`) select c.userid,m_user.username,c.send_command_dt,c.send_command_comment,t_item from command_log c left join m_user on m_user.userid=c.userid ;
SELECT count(*) INTO total FROM T left join m_user on m_user.userid=T.userid where T.type like concat('%',logtype,'%') and m_user.username like concat('%',uname,'%') and T.time BETWEEN startTime and endTime and T.remark like concat('%',logremark,'%');
/**--查询日志-**/
SELECT T.userid,m_user.username,T.time,T.remark,T.type,total FROM T left join m_user on m_user.userid=T.userid where T.type like concat('%',logtype,'%') and m_user.username like concat('%',uname,'%') and T.time BETWEEN startTime and endTime and T.remark like concat('%',logremark,'%') order by T.time desc limit page,pagesize;
END
定义与使用变量时需要注意以下几点:
◆ DECLARE语句必须用在DEGIN…END语句块中,并且必须出现在DEGIN…END语句块的最前面,即出现在其他语句之前。
◆ DECLARE定义的变量的作用范围仅限于DECLARE语句所在的DEGIN…END块内及嵌套在该块内的其他DEGIN…END块。
◆ 存储过程中的变量名不区分大小写。
定义后的变量采用SET语句进行赋值,语法格式如下:
SET var_name = expr [,var_name = expr] ...
其中,var_name为变量名,expr为值或者返回值的表达式,可以使任何MySQL支持的返回值的表达式。一次可以为多个变量赋值,多个“变量名=值”对之间以逗号“,”隔开。 (摘录)