首页 > 代码库 > MySQL5-函数/存储过程与定时器、触发器

MySQL5-函数/存储过程与定时器、触发器

2024-10-01 14:47:01   219人阅读

目录
一、函数/存储过程
二、定时器
三、触发器
四、函数语句学习
 
 

一、函数/存储过程

1、函数与存储过程
（1） function与procedure的区别：一个有返回值，一个没有，仅此而已。上述说法是错误的，function和procedure的用法有很多不同，总体来说procedure受到的限制较少，function的限制较多；而且procedure可以使用out参数返回值，因此尽量采用procedure。比如，以下存储过程的创建是合法的，但函数的常见则是非法的。
#存储过程合法
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test()
BEGIN
DECLARE i INT;
SET i=1;
END;
 
#函数非法
DROP FUNCTION IF EXISTS test;
CREATE FUNCTION test()
BEGIN
DECLARE i INT;
SET i=1;
END;
（2）使用存储过程/函数，比使用单独的SQL语句要快。
 
2、创建函数
语法：
create procedure 存储过程名字([in|out|inout] 参数 datatype)
comment ‘here is zhushi‘
begin
MySQL 语句;
end;
参数：如果不指定in/out/inout，则默认为in；()不能省；参数前不加@；参数不能指定默认值。参数中可以使用各种类型，包括varchar，但是一定要指定长度，否则报错。
out参数：
drop procedure if exists bb;
create procedure bb(out total int)
begin
set total = 3;#方法1:不能有@，已验证
#方法2已验证可以：select count(*) into total from user;
end;
 
call bb(@total);
select @total;#调用语句和select语句中，都必须使用@，因为自定义变量不能与@分离，没有@相当于局部变量
返回值：
create function 存储过程名字([in|out|inout] 参数 datatype) returns int
begin
MySQL 语句;
return 1;
end;
注释：comment后面跟注释，在show procedure status中可以看到；可以省略
函数体：如果MySQL语句不止一句，则需要begin/end；每条语句都需要分号；注释可以使用/**/、--、#；函数中可以使用return，存储过程中不能使用。
创建函数实例：
drop procedure if exists pr_add;  
create procedure pr_add(a int,b int)
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c as sum;
/* return c; 不能在 MySQL 存储过程中使用。return 只能出现在函数中。*/
end;
 
3、delimiter：分界符；主要用在MySQL客户端。
delimiter的作用是规定分界符；当客户端遇到分界符时，便会执行MySQL命令。默认情况下，分界符是分号；但是在有些情景下，如定义函数，不希望遇到分号执行命令，而是等函数定义结束后执行命令，此时便使用形如delimiter $$的语句改变分界符，待函数定义结束后，再使用delimiter ;将分界符变回分号。
因此在非MySQL客户端，如sql文件、Navicat for MySQL中执行语句时，不需要使用delimiter。
 
4、错误说明
Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
原因：在主从复制的两台MySQL服务器中开启了二进制日志选项log-bin，slave会从master复制数据，而一些操作，比如function所得的结果在master和slave上可能不同，所以存在潜在的安全隐患。因此，在默认情况下回阻止function的创建。　　
解决方案1：将log_bin_trust_function_creators参数设置为ON，这样一来开启了log-bin的MySQL Server便可以随意创建function。这里存在潜在的数据安全问题，除非明确的知道创建的function在master和slave上的行为完全一致。
查看状态：SHOW VARIABLES LIKE ‘%log_bin_trust_function_creators%‘
设置：set global log_bin_trust_function_creators=1【动态设置在重启数据库后失效；修改配置文件后重启数据库可以永久改变】
 
5、变量声明：
declare用于在函数中声明局部变量，只在函数中有效
DECLARE variable_name1,variable_name2,…… datatype(size) DEFAULT default_value;
set variable_name1 = 1;//赋值
SELECT COUNT(*) INTO total_products FROM products#另外一种赋值方式，值得注意
set @用于定义会话变量(严格说是用户变量)，在整个会话中都有效；不需要指定datatype，MySQL自动指定。
set @var = 1;
set用于对局部变量赋值时，必须等所有的declare语句执行结束(个人怀疑是不是在函数中，所有的declare必须都放在最前面)
#以下语句会报错
drop procedure if exists test;
CREATE PROCEDURE test()
BEGIN
DECLARE i INT;
SET i = 1;
DECLARE o INT;
END;
 
#以下语句则正确
drop procedure if exists test;
CREATE PROCEDURE test()
BEGIN
DECLARE i INT;
DECLARE o INT;
SET i = 1;
END;
 
6、调用函数：不能省略参数，可以使用null代替
call pr_add(10,20);
或者
set @a = 10;
set @b = 20;
call pr_add(@a, @b);
 
7、删除函数
drop procedure if exists aa;
drop precedure aa;#如果aa不存在则会错误
 
8、查看函数
show procedure/function status：查看该服务器上所有数据库的所有procedure/function的状态
show create procedure/function 名称：查看指定函数的状态
show procedure status like ‘%……%‘
 
 
 
 
二、定时器
1、确认版本：5.1以上开始支持
select version()
2、开启event功能：默认是关闭的
show variables like ‘event_scheduler‘
set global event_scheduler=‘on‘;
3、创建event需要的存储过程
drop procedure if exists test_proce;
create procedure test_proce()
begin
insert into `user`(name,age) values(‘zhoudan‘,‘18‘);
end;
4、创建定时器
drop event if exists test_event;
create event test_event
on schedule every 1 second #执行的间隔时间
starts ‘2015-01-10 00:10:00‘ #开始执行的时间
on completion preserve disable #创建定时器后是否立即启动，如果是disable需要手动启动
do call test_proce();
5、启动、关闭、查看定时器
alter event test_event on completion preserve enable; #启动定时器
alter event test_event on completion preserve disable; #关闭定时器
show events; #显示本数据库下所有定时器
6、注意：如果存储过程执行过程中有错误，则整个过程中可能并不报错，但是定时器指定的存储过程无法正确执行。
 
 
 
三、触发器
1、创建触发器示例
create trigger newuser
after insert
on `user`
for each row
call test_proce1()
触发器名：之前是表中唯一，现在不确定；稳妥的做法是数据库唯一
执行时机：after/before
响应的活动：只支持insert/update/delete
关联的表：on ‘user‘；目前MySQL只有表支持触发器，视图和临时表都不支持
for each row：行级触发器，即影响多少行就触发多少次；与之对应的是语句触发器，即语句执行一次出发一次；由于MySQL只支持行级触发器，因此该语句不可省略。
执行的动作：
（1）可以是单条语句，可以是begin和end包围的多条语句，也可以支持call()
（2）现在版本的动作不能返回值，如含有return语句的function，select语句等；如果包含select语句，会报错Not allowed to return a result set from a trigger
（3）动作不能对本表进行 insert ,update ,delete 操作，以免递归循环触发；不仅是insert不能触发本表的insert语句，也不能触发本表的update和delete语句，其他同理
 
2、一些限制
（1）每个表对每个活动只能有两个触发器(一前一后)，因此一个表最多可以有6个触发器
（2）一个触发器不能与多个事件或多个表关联
（3）如果before触发器执行失败，则MySQL不会执行相应操作和after触发器；如果操作执行失败，则MySQL触发器不会执行after触发器
 
3、删除与查看：触发器不能修改或覆盖，如果要重新定义触发器，必须先删除在添加
drop trigger newuser;
show triggers;
show create trigger ‘newuser‘;
 
4、insert触发器
说明：引用名字为new的虚拟表，访问被插入的行；一般用before进行数据验证、净化，用after进行日志处理等；对于auto_increment列，new在insert之前值为0，insert之后是新的自动生成的值
before：被插入的值可以被更改；注意语法(使用普通的update语句，创建触发器的时候没有问题，执行插入的时候就会报错)
create trigger newuser2
before insert
on `user`
for each row
set new.name=‘zhoubapi‘
after
create trigger newuser
after insert
on `user`
for each row
insert into tmp values(new.id,new.name,new.age)
 
5、delete触发器：引用名字为old的虚拟表访问被删除的行；old中的值全部只可读
6、update触发器：既可以引用old，也可以引用new；可读性同上
 
 
 
四、函数语句学习
1、if
if a is null then
set a = 0;
end if;
 
if not exists(select 1 from ta_test where id = ‘1111‘) then
#mysql语句
end if;
 
2、while
declare i int;
set i=1;
while i<1000 do
#mysql语句
set i = i +1;
end while ;
 
3、错误处理【参考：http://blog.csdn.net/seteor/article/details/17791855】
（1）针对存储过程 、触发器或函数内部语句可能发生的错误或警告信息，需要进行相关异常的捕捉，然后作出相应的处理，异常处理的方式如下：
DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code| MySQL error code| condition_name} handler_actions
（2）Handler Type：CONTINUE | EXIT，处理类型，继续或退出；exit只退出当前Block。
（3）Handler Condition：SQLSTATE sqlstate_code| MySQL error code| condition_name，触发条件；其中condition_name(命名条件)既可以自己定义，也可是使用系统内置的SQLEXCEPTION、SQLWARNING和NOT FOUND。如果对于一个错误，同时有MySQL码、SQLSTATE和命名条件可以捕获，那么只能捕获一次错误，且捕获顺序是MySQL码->SQLSTATE->命名条件。
（4）handler_actions：错误发生时执行的操作，在continue或exit之前执行。
（5）作用域：如果定义在begin/end之内，则begin/end之外的错误不会捕获。
（6）示例：游标与错误处理结合
DROP PROCEDURE IF EXISTS test;
CREATE PROCEDURE test()
BEGIN
DECLARE no_more_record INT DEFAULT 0;
DECLARE pname VARCHAR(100);
DECLARE cur_record CURSOR FOR SELECT name FROM ta_tmp WHERE id < 8;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1;
 
OPEN cur_record;
FETCH cur_record INTO pname;
 
WHILE no_more_record != 1 DO
SELECT pname;
FETCH cur_record INTO pname;
END WHILE;
CLOSE cur_record;
END;
 
4、临时表【参考：http://blog.csdn.net/crazylaa/article/details/5368698】
（1）MySQL不支持数组，但是在存储过程中，有时候需要使用复杂的运算结果，尤其是组合使用几个表的数据时，此时可以考虑使用临时表。
（2）临时表：只有在当前连接情况下， TEMPORARY 表才是可见的。当连接关闭时， TEMPORARY 表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。
（3）基本的使用方法如下
#创建临时表
CREATE TEMPORARY TABLE IF not exists tmpTable(
user_id VARCHAR(30) primary KEY,
drawingNumber int
);
truncate TABLE tmpTable;#在使用前清理数据
 
......#在后面的语句中可以对tmpTable进行增删改查
 
 

MySQL5-函数/存储过程与定时器、触发器


联系
我们