MySQL：基础教程-12（个人笔记）

存储过程（stored procedure）： 将多个sql语句组合称一个只需要使用命令call xx就能执行的集合；

创建存储过程：

create procedure 存储过程名（）
begin
    sql 语句1
    sql 语句2
end

从begin到end为止的内容是存储过程的主体

修改分隔符的设置，使用delimiter命令将分隔符修改为  ‘//’；

delimiter //

ex：

delimiter //
create procedure pr1()
begin
select * from tb;
select * from tb1;
end //

执行存储过程

call 存储过程名；

ex：

call pr1；

创建只显示大于等于指定值的记录的存储过程

存储过程中参数的编写

create procedure 存储过程名（参数名 数据类型）；

ex:

创建存储过程pr2。如果指定整数类型的参数d的值执行该存储过程，表tb中销售额大于等于d的记录就会显示出来

delimiter //
create procedure pr2(d int)
begin
select * from tb where sales >= d;
end //

call pr2(200);

在参数中加上in的例子

在参数的前面加上in也会得到相同的结果 （in d int），相反，如果想将处理结果传给参数，则需要加上out；

delimiter //
create procedure pr2(in d int)
begin
select * from tb where sales >= d;
end //

---

显示存储过程的内容

show create procedure 存储过程名；

删除存储过程

drop procedure 存储过程名；

---

存储函数（store function） 的思考方式和操作方式与存储过程基本相同，与存储过程唯一不同的一点是，存储函数在执行后会返回一个值；

创建存储函数

create function 存储函数名（参数 数据类型） returns 返回值的数据类型
begin
    sql 语句...
    return 返回值·表达式
end

使用存储函数之前，需要开启修改一处设置

set global log_bin_trust_function_creators = 1;
show variables like 'log_bin_trust_function_creators';

ex:

标准体重＝身高（cm）×身高（cm）×22 /10000

使用这个等式来创建存储函数fu1

存储函数fu1 返回的是包含小数的标准体重，所以我们将存储函数fu1返回的值数据类型指定可以处理小数部分的double类型；

delimiter //
create function fu1(height int) returns double
begin
return height * height * 22/10000;
end //


# 让fu1作为函数来返回值
select fu1(174);

返回记录平均值的存储函数

创建存储函数fu2来返回表中tb中列sales的平均值

create function fu2() returns double
begin

# 要使用变量前，先通过declare定义变量；
declare r double;

# 从表tb中提取列sales的平均值
select avg(sales) into r from tb;

#让平均值作为存储函数的值返回
return r;
end;


# 使用select命令显示平均值
select fu2();

显示和删除存储函数

drop function存储函数名；

显示存储函数的内容

show create function 存储函数名

---

触发器（tigger）：一种对表执行某操作后会触发执行其他命令的机制，当执行insert、update和delete等命令时，作为触发器提前设置好的操作也会被执行；

创建一个空表tb1_from用于插入表tb1中删除的记录。我们可以通过复制表tb1的列结构来创建这个表

create table tb1_from like tb1;

触发器被触发的时机包括以下两种：

before ： 在对表进行处理之前触发

after：在对表进行处理之后触发

列值

old.列名：对表进行处理之前的列值

new.列名：对表进行处理之后的列值

创建触发器

create trigger 触发器名 before (或者after) delete 等命令
on 表名 for each row
begin
    使用更新前（old.列名）或者更新后 （new.列名）的处理
end

ex: 

 

delimiter //
create trigger tr1 before delete on tb1 for each row
begin
insert into tb1_from values(old.empid,old.name,old.age);
end //

现在tb1中删除的记录应该能够插入到表tb1_from中，使用删除所有记录

delete from tb1;

查看tb1_from中的内容

select * from tb1_from;

删除触发器

drop trigger触发器名；

显示触发器

show triggers;

---

练习题

问题1：
请使用下面的表tb，创建如果指定4～6月任意一个月（month）的数值给参数t（INT类型），就返回该月销售额（sales）总和（INT类型）的存储函数f_sales。

delimiter //
create function f_sales(t int) returns int
begin
declare u int;
select sum(sales) into u from tb where month = t;
return u;
end //

select f_sales(4);