6.2 存储函数

6.2.1 创建存储函数

    create function语法如下：

create function 存储过程名([参数...])
returns type
[特征...] 存储函数体

• 存储函数的定义格式和存储过程相差不大
• 存储函数不能和已有的存储过程名字相同
• 存储函数的参数只有名称和类型，不能指定 in、out和inout。returns type子句声明函数返回值的数据类型
• 存储函数体：所有存储过程中使用的SQL语句在存储函数中也适用，包括流程控制语句、游标等。但是存储函数体中必须包含一个return value语句，value为存储函数的返回值。这是存储过程体中没有的。

例如：创建一个存储函数，返回xs表中学生的人数作为结果

 delimiter $$
 create function num_of_xs()
 returns integer
 begin
return( select count(*) from xs);
end $$
delimiter ;

例如：创建一个存储函数来删除xs_kc表中存在但xs表中不存在的学号。

delimiter $$
 create function delete_xh(xh char(6))
 returns boolean
 begin
 declare stu char(6)
 select 姓名 into stu from xs where 学号=xh；
 if stu is null then
 delete from xs_kc where 学号=xh;
 return true;
 else
 return false;
 end if
 end$$
 delimiter ;

6.2.2 存储函数的调用、删除和修改

1. 存储函数的调用

    存储函数创建完成后，如同系统提供的内置函数（version（）函数）、所以调用存储函数的方法也差不多，都是使用select关键字。
    其语法格式如下：

select 存储函数名(参数[,...])

例如：

select num_of_xs();

例如：创建一个存储函数，通过调用存储函数name_of_stu获得学号的姓名，判断姓名是否是王林，是则返回王林的出生日期，不是则返回‘false’

delimiter $$
create function name_of_stu(xh char(6))
returns char(10)
begin
declare xm char(8);
select 姓名 into xm from xs where 学号=xh;
if xm ='王林' then
return(select 出生日期 from xs where 学号=xh);
else
return 'False';
end if;
end$$
delimiter ;

    接着调用存储函数name_of_stu

select name_of_stu('081102');

    输出结果：

 +-----------------------+
| name_of_stu('081102') |
+-----------------------+
| False                 |
+-----------------------+
1 row in set (0.00 sec)

2. 删除存储函数

    删除存储函数与删除存储过程的方法基本一样，其语法格式如下：

drop function [if exists] 存储过程名

例如，删除存储函数num_of_xs;

drop function if exists num_of_stu;

可以使用 show function status进行查看当前数据库的存储函数

6.3 触发器

    触发器是一个被指定关联到一个表的数据对象，用于保护表的数据，当有影响到触发器保护的数据时，触发器自动执行。利用触发器可以方便的实现数据库中数据的完整性，例如，对于xscj数据库有学生表、成绩表和课程表，当要删除学生表中一个学生的数据时，该学生在成绩表中对应的记录可以利用触发器进行相应的删除，这样才不会出现不一致的冗余数据。

1. 创建触发器

create trigger的语法格式如下：

create trigger 触发器名 触发时刻 触发事件
on 表名 for each row 触发器动作

• 触发器名称在当前数据库必须唯一，如果要在某个特定数据库中创建，名称前面要加上数据库名称

• 触发时刻，有两个选项 after 和 before 表示触发器在激活它的语句之前或之后触发，如果想要在激活触发器的语句执行之后执行几个或更多的改变，通常使用after，如果要验证新数据是否满足使用的限制，则使用before选项

• 触发事件：指明了激活触发程序的语句的类型，可以是下述值之一：
  insert：将新行插入表时激活触发器，例如通过 insert、load data和replace语句；
  update：更改某一行时激活触发器，例如update语句
  delete：从表中删除某一行时激活触发器。例如：delete和replace语句

• 表名：，触发器绑定的表，同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如：不能有两个before update触发器，但是可以有一个before update和一个before insert触发器，也可以有一个before update和一个after update触发器。

• for each row ：声明对于受触发事件的每一行都要激活触发器动作，例如：使用一条语句向一个表插入多行，触发器会对每一次执行相应触发器动作。

• 触发器动作：包含要执行的语句，如果包含多个语句可以使用begin…end 复合语句结构。支持使用存储过程中允许的相同语句

要查看数据库中有哪些触发器，使用show triggers命令
例如：创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将用户变量str的值设置为‘trigger is working’

 create table table1(a integer);
 create trigger table_1 after insert
 on table1 for each row 
 set @str = 'trigger is working';

    尝试向表中添加一个数据：

insert into table1 values(10);

    查看用户变量的值：

select @str;

+--------------------+
| @str               |
+--------------------+
| trigger is working |
+--------------------+
1 row in set (0.00 sec)

(1)触发器中关联表中的列

    在MySQL触发器中，SQL语句可以关联表中的任何列，但不能直接使用列的名称去标识，那会使系统混淆，因为激活触发器的语句可能已经修改了，删除或添加了新的列名，而旧的列名同时存在，因此必须用’new.列名’引用新的一列，用’old.列名’引用更新或删除它之前已有行的一列。

备注：对于insert触发事件，只有new是合法的，对于delete触发事件，只有old是合法的，对于update触发事件，new和old可以同时使用。

例如：创建一个触发器，当删除xs表中某个学生的信息时，同时将xs_kc表中与该信息有关的数据删除

 create trigger xs_delete after delete
 on xs for each row
 delete from xs_kc where 学号=old.学号;

例如：创建一个触发器，当修改xs_kc表中数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0，否则将学分改成对应课程的学分

  delimiter $$
  create trigger update_cj before update
  on xs_kc for each row
  begin
  declare xf int;
  select 学分 into xf from kc where 课程号=new.课程号
  if new.成绩<60 then
  update xs_kc set new.学分=0 ；
  else
  update xs_kc set new.学分=xf；
  end if;
  end$$
  delimiter ;

备注：当触发器涉及对触发表自身的更新操作时，只能使用before，after触发器将不被允许。

例如：创建触发器，实现当向xs_kc表插入一行数据时，根据成绩对xs表的总学分进行修改，如果成绩>=60，总学分加上该课程的学分，否则总学分不变。

delimiter $$
create trigger num_1 after insert
on xs_kc for each row
begin
declare xf int;
insert 学分 into xf from kc where 课程号=new.课程号 ；
if new.成绩 >=60 then
update xs set 总学分=总学分+xf where 学号=new.学号;
end if;
end$$
delimiter ;

(1)触发器中调用存储过程

例如;假设xscj数据库中有一个与xs表结构完全一样的表student，创建一个触发器，在xs表中添加数据的时候，调用存储过程，将student表中的数据与xs表中的数据同步

创建存储过程

create procedure update_1()
replace into student select * from xs;

创建触发器

create trigger stu_change after insert
on xs for each row
call update_1();

2. 删除触发器

    和其他数据对象一样，使用drop语句即可将触发器从数据库中删除，其语法格式如下：

drop trigger [schema_name.]trigger_name

备注：schema_name为触发器所在数据库的名字

6.4 事件

    事件和触发器相似，都是在某些事情发生的时候启动，也被称作临时性触发器（temporal trigger）。事件是MySQL在相应的时刻调用的过程式数据库对象，一个事件可以只调用一次，也可周期性的调用。例如：2014年的10月1日下午两点，或每周日晚上。
事件的主要作用如下：

1. 关闭账户
2. 打开或关闭数据库指示器
3. 使数据库中的数据在某个间隔后刷新
4. 执行对进入数据的复制的检查工作

6.4.1 创建事件

    创建事件可以使用create event语句。语法格式如下：

create event 事件名
on SCHEDULE schedule
[on completion [not] preserve]
[enable | disable | disable on slave]
[comment 'comment']
do sql 语句；

    其中schedule格式如下：

at 时间点 [+ interval 时间间隔]
| every 时间间隔
[ starts 时间点[+ interval 时间间隔]]
[ ends 时间点[+ interval 时间间隔]]

    interval格式如下：

count{ year | quarter | month | day | hour | minute |
 week | second | year_month | day_hour | day_minute 
 | day_second | hour_minute | hour_second | minute_second }

• schedule:时间调度，表示事件何时发生或者每隔多久发生一次。

• at 子句：表示在某个时刻事件发生，在指定时间后，后面可以加上一个时间间隔，由一个数值和单位构成，count是间隔时间的数值。
  every 子句：表示在指定时间区间内每隔多长时间事件发生一次
  starts子句：指定开始时间
  ends子句：指定结束时间

• sql语句：包含事件启动执行的代码。如果包含多条语句，可以使用begin…end复合结构。

• 事件的属性：对于每一个事件都可以定义几个属性。

• on completion [not] preserve :not
  表示事件最后一次调用后将自动删除该事件(默认)，否则最后一次调用后将保留该事件

• enable | disable | disable on slave：enable表示该事件是活动的，活动意味着调度器检查事件动作是否必须调用。关闭意味事件存在但是不调用，disable on slave表示事件从机中是关闭的。如果不指定，事件创建后立即变为活动的。

    一个打开的事件可以执行一次或多次，一个事件的执行称作调用事件，每次调用一个事件，MySQL都处理事件动作。

    MySQL事件调度器负责调用事件，这个模块是MySQL数据库服务器的一部分，它不断监视一个事件是否需要调用，要创建事件，必须打开调度器。可以使用系统变量EVENT_SCHEDULER来打开事件调度器，true为打开，false为关闭。

  set global event_scheduler= true;

例如：创建一个立即启动的事件

create event direct
on schedule at now()
do insert into xs values(..)

备注：该事件只调用一次，在事件创建后立即调用

例如：创建一个30秒后启动的事件

create event num_1
on schedule at now() + interval 30 second
do insert into xs values(..);

例如：创建一个事件，每个月启动一次，开始于下个月并且在2014年的12月31日结束

create event num_1
on schedule every month
start curdate() + interval 1 month
ends '2014-12-31'
do insert into xs values(..);

6.4.2 修改和删除事件

1. 修改事件

    事件创建后可以通过alter event语句来进行其定义和相关属性，其语法格式如下：

 alter even even_name
 [on schedule schedule]
 [on completion [not] preserve]
 [rename to new_event_name]
 [enable | disable | disable on slave]
 [comment 'comment']
 [do sql_statement]

2. 删除事件

    删除事件的语法格式如下：
drop event [if exists] event_name
    同样，使用show events命令查看操作结果

最后

以上就是醉熏口红为你收集整理的2021-08-18MySQL 第六章过程式数据库对象.23存储函数，触发器6.2 存储函数6.3 触发器6.4 事件的全部内容，希望文章能够帮你解决2021-08-18MySQL 第六章过程式数据库对象.23存储函数，触发器6.2 存储函数6.3 触发器6.4 事件所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。