存储过程,函数跟触发器

存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
函数 ： 是一个有返回值的过程 ；
过程 ： 是一个没有返回值的函数 ；

存储过程

创建存储过程：

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
	-- SQL语句
end ;

示例 ：

create procedure pro_test1()
begin
	select 'Hello Mysql' ;
end;

调用存储过程:

call procedure_name() ; 

查看存储过程:
– 查询db_name数据库中的所有的存储过程

select name from mysql.proc where db='db_name';

– 查询存储过程的状态信息

show procedure status;

– 查询某个存储过程的定义

show create procedure test.pro_test1 \G;

删除存储过程:

DROP PROCEDURE [IF EXISTS] sp_name ；

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中:

create procedure pro_test2()
begin
	declare num int default 5;
	select num+ 10;
end;

直接赋值使用 SET，可以赋常量或者赋表达式:

CREATE PROCEDURE pro_test3()
 BEGIN
	DECLARE NAME VARCHAR(20);
	SET NAME = 'MYSQL';
	SELECT NAME ;
END;

也可以通过select … into 方式进行赋值操作 :

CREATE PROCEDURE pro_test5()
BEGIN
	declare countnum int;
	select count(*) into countnum from city;
	select countnum;
END;

存储函数

语法结构:

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
	...
END;

例：定义一个存储过程, 请求满足条件的总记录数 ;

create function count_city(countryId int)
returns int
begin
	declare cnum int ;
	select count(*) into cnum from city where country_id = countryId;
	return cnum;
end;

调用:

select count_city(1);

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

创建触发器

语法结构 :

create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ]  -- 行级触发器
begin
	trigger_stmt ;
end;

示例: 通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

创建 insert 型触发器，完成插入数据时的日志记录 :

create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
	 insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
	values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,',
	age:',new.age,', salary:',new.salary,')'));
end;

创建 update 型触发器，完成更新数据时的日志记录 :

create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
 insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,',
age:',new.age,', salary:',new.salary,')'));                    
              
end;

创建delete 行的触发器 , 完成删除数据时的日志记录 :

create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
 insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,')'));                    
              
end;

删除触发器:

drop trigger [schema_name.]trigger_name

如果没有指定 schema_name，默认为当前数据库 。

查看触发器:

show triggers ；

欢迎留言讨论…