MySQL 函数存储过程触发器定义简单示例

filer

1.变量提示
NEW 是新值-- OLD 是旧值
INSERT 只有NEW ----UPDATE有NEW和OLD ---DELETE只有OLD

2.准备测试表(userinfo、userinfolog)
use test;
create table userinfo(userid int,username varchar(10),userbirthday date);
create table userinfolog(logtime datetime,loginfo varchar(100));
describe userinfo;

3.建立同时插入两张表的触发器beforeinsertuserinfo
# 定义触发器
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;

4.建立插入数据存储过程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',current_date);
select * from userinfo;

5.自定义函数fngetage

update userinfo
set userbirthday='2000.01.01'
where userid='1';
drop function if exists fngetage;
# 函数定义
delimiter $$
create function fngetage(pbirthday date)
returns integer
begin
return year(now()) - year(pbirthday);
end;
$$
delimiter ;

6.建立视图viewuserinfo调用函数fngetage
# 建立视图
create view viewuserinfo
as select * ,fngetage(userbirthday) as userage from userinfo;
select * from viewuserinfo;

清除日志记录
truncate table userinfolog;
delete from userinfolog;

wind-cold

有竞争才有进步嘛

24cun_cn

比我有才的都没我帅，比我帅的都没我有才！

hudeya

修养的艺术,其实就是说谎的艺术。

baiyunjn

美女未抱身先走,常使色狼泪满襟。。。。。。

殇帝刘玢你

这是什么东东啊

13719654321

给你最大的报复，就是活的比你幸福