mysql创建触发器、存储过程、函数等实例

FragranceM

############# 本blog可以直接在mysql客户端中执行 #############
# 0. 表准备
drop database abc;
create database abc;
use abc;
create table userinfo(userid int, username varchar(10), userbirthday date);
create table userinfolog(logtime datetime, loginfo varchar(100));
# 1. 触发器
delimiter //
create trigger beforeinsertuserinfo
before insert on userinfo
for each row begin
insert into userinfolog values(now(), concat(new.userid, new.username));
end;
//
delimiter ;
show triggers;
# 2. 存储过程
drop procedure if exists spinsertuserinfo;
delimiter //
create procedure spinsertuserinfo(puserid int, pusername varchar(10), puserbirthday date)
begin
insert into userinfo values(puserid, pusername, puserbirthday);
end;
//
delimiter ;
show procedure status like 'spinsertuserinfo';
call spinsertuserinfo(1, 'zhangsan', '1972-01-09');
call spinsertuserinfo(2, 'lisi', '1982-07-26');
call spinsertuserinfo(3, 'wanger', current_date);
# 3. 自定义函数
drop function if exists fngetage;
delimiter //
create function fngetage(pbirthday date)
returns integer
begin
return year(now()) - year(pbirthday);
end
//
delimiter ;
# 4. 视图
create view viewuserinfo as select *, fngetage(userbirthday) as userage from userinfo;
select * from viewuserinfo;


本示例实现如下效果：
0.test数据库有userinfo用户信息表和userinfolog用户信息日志表
1.建立一个userinfo表新增记录时的触发器将新增日志加入到userinfolog
2.建立一个向userinfo表新增记录的存储过程
3.根据userinfo表的出生日期字段我们将建立一个简单算得年龄的自定义函数
4.创建一个userinfo的视图调用年龄函数