MYSQL中的function、trigger、存储过程使用实践

FUNCTION

就是创建一个函数，里面写上自己的逻辑,然后返回处理的结果

 

我们创建一个雇员表employees

create table employees  
(  
    employee_id int(11) primary key not null auto_increment,  
    employee_name varchar(50) not null,  
    employee_sex varchar(10) default '男',  
    hire_date datetime not null default current_timestamp,  
    employee_mgr int(11),  
    employee_salary float default 3000,  
    department_id int(11)  
);  

insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('David Tian','1',10,7500,1);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Black Xie','1',10,6600,1);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Moses Wang','1',10,4300,1);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Rena Ruan','0',10,5300,1);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Sunshine Ma','0',10,6500,2);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Scott Gao','1',10,9500,2);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Warren Si','1',10,7800,2);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Kaishen Yang','1',10,9500,3);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Simon Song','1',10,5500,3);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Brown Guan','1',10,5000,3);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Eleven Chen','0',10,3500,2);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Cherry Zhou','0',10,5500,4);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Klause He','1',10,4500,5);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Maven Ma','1',10,4500,6);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Stephani Wang','0',10,5500,7);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Jerry Guo','1',10,8500,1);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Gerardo Garza','1',10,25000,8);  
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Derek Wu','1',10,5500,5);  
  
  
select * from employees;  

写一个根据id查询雇员工资的函数

CREATE FUNCTION getInfoById (id INT) RETURNS VARCHAR (300)
BEGIN
	RETURN (
		SELECT
			CONCAT(
				'employee name:',
				employee_name,
				'---',
				'salary: ',
				employee_salary
			)
		FROM
			employees
		WHERE
			employee_id = id
	);


END

在NavicatForMySQL里面执行下

可以看到我们在mysql里面已经创建好了一个函数，在NavicatForMySQL也可以看到

然后调用下，试试效果

Procedure

存储过程，和函数功能差不多，但是相对功能更强大，限制更少

本质上没区别。只是函数有如：只能返回一个变量的限制。而存储过程可以返回多个。

而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。

执行的本质都一样。 
     函数限制比较多，比如不能用临时表，只能用表变量．

　　还有一些函数都不可用等等．而存储过程的限制相对就比较少 
 

　   1.    一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。 
      2.    对于存储过程来说可以返回参数，而函数只能返回值或者表对象。 
      3.    存储过程一般是作为一个独立的部分来执行（EXEC执行），

　　　　而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，

　　　　因此它可以在查询语句中位于FROM关键字的后面。 
      4.    当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。 
      Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

-- 下面是存储过程
DELIMITER //
CREATE PROCEDURE proc_select_teacher_info(in id int)
BEGIN
 select * from Teacher where Tid=id;
END;
//
call proc_select_teacher_info(1)

-- 下面是函数调用
CREATE FUNCTION getTeacherById (id INT) RETURNS VARCHAR (300)
BEGIN
	RETURN (
		SELECT
			CONCAT('teacher', tname)
		FROM
			teacher
		WHERE
			tid = id
	);
END
select getTeacherById(1);

两个都可以实现查询教师的功能

查询老师的数量并输出到out参数里面

create PROCEDURE proc_get_teacher_count(out n int)
BEGIN
select count(*) from teacher;
END
call proc_get_teacher_count(@n)

将学生id为1的年龄加以并放入inout

 

create procedure update_student_age(in id int,inout num int)
BEGIN
select age into num from student where sid=id;
set num:=num+1;
select num;
END

 call update_student_age(1,@num)

该学生本来年龄为10的，结果修改后，结果是11

Trigger

trigger 就是触发器，联想一下网页界面的按钮监听事件

一个道理，但是触发器不能经常用，因为每次操作一行数据都会触发这个监听事件，确实挺浪费的。

下面是语法

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

 根据以上的表格，可以使用一下格式来使用相应的数据：

NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据

我们针对于上面的雇员表创建一个插入的触发器，每次插入都会在操作日志里面插入当前雇员的名字和操作时间

操作日志表oplogs的表结构如下：

create trigger insert_trigger AFTER INSERT ON employees FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;
SET s2 = ' is created';
SET s1 = CONCAT(NEW.employee_name,s2);
INSERT into oplogs(optype,time) VALUES(s1,NOW());
END

然后我们插入一条数据

insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('meinv','0',10,2300,1);  

然后我们去操作日志看下，触发器已经自动触发，并在操作日志表插入了一条新的数据