存储过程、存储函数、触发器：

存储过程：

1、什么是存储过程：是指在大型数据库系统中，一组为了完成特定功能的语句集，存储在数据库中，经过一次编译后，再调用不需要再次编译（效率比较高），用户通过制定的存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，存储过程是数据库中的一个重要对象（针对sql编程）

与存储函数的区别：

相同点：

1、存储过程和函数都为了可重复执行操作数据库的SQL语句的集合。

2、都是一次编译，后续可多次执行

不同点：

1、关键字不同、函数的关键字为function，过程的关键字为procedure

2、函数中有返回值，且必须返回，而过程则没有返回值

3、一般来说、存储过程的功能的实现要复杂一些，而函数的实现功能针对行性比较强一些

4、存储过程一般是作为一个独立的部分来执行（EXEC执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

5、函数可以在select语句中直接使用，而过程则不能

创建存储过程：

1、创建的格式：

1、存储过程的创建

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。创建存储过程需要使用CREATE PROCEDURE语句，具体语法如下：

CREATE  [ OR REPLACE ]  PROCEDURE procedure_name

[( parameter [ IN | OUT | IN | IN OUT ] data_type )]

[ declaration_section ; ]

BEGIN

procedure_body ;

END [ procedure_name ] ;

说明：

OR REPLACE：表示如果存储过程已经存在，则替换已有存储过程。

procedure_name：存储过程名称

parameter：参数，可以为存储过程设置多个参数，参数定义之间用（,）分隔。

 IN | OUT | IN | IN OUT：指定参数的模式。IN表示输入参数，在调用存储过程时需要为输入参数返回值，而且其值不能在存储过程中修改；OUT表示输出参数（如果传入的参数带有值，则会把传入的值设置为null），存储过程通过输出参数返回值；IN OUT则表示输入输出参数，这种类型的参数既要接受传递值也允许在过程体重修改其值，并可以返回。默认为IN，在使用IN参数时，还可以使用DEFAULT关键字为该参数设置默认值：
 parameter [ IN ] data_type DEFAULT value ;
 
 data_type：参数的数据类型，不能指定精确数据类型，例如只能使用NUMBER，不能使用NUMBER(2)等。
declaration_section：声明变量。在储存声明的变量不能使用DECLARE语句，这些变量只要用于过程体中。
procedure_body：过程体。
END [ procedure_name ] ：END关键字后添加过程名，可以提高程序的阅读性，不是必须的。

2、创建过程的常见用法

--修改语句结束符号
create procedure 过程名字（[参数列表]）
begin
	过程体
end
结束符
--修改语句结束符号

注意：如果该过程中只有一条语句，那么 可以省略begin和end

代码实现：
--修改语句结束符号
delimiter $$
create procedure my_pro2()
begin
	--求1到100之间的和
	declare i int default 1；
	 --declare sum int default 0；--局部变量
	 set @sum=0；---会话变量
	 
	 while i <= 100 do
	 			--求和
	 			set @sum =@sum +i;
	 			set i =i+1;
	 end while;
	 
	 --显示结果
	 select @sum‘
end
--结束
$$
--修改语句结束符号
delimeter ;
	

2、查看过程

show procedure status [like 'pattern']

3、调用过程

没有返回值，select不可能调用，调用过程有专门的的语法：
call 过程名（[实参列表]）

call my_pro1();

4、删除过程

drop procedure 过程名字；
drop procedure my_pro1();

函数过程的参数列表

In:表示参数从外部传到里面使用（过程内部使用）可以是直接数据，也可以是保存数据的变量。
Out：表示参数是从过程里面把数据保存到变量中，交给外部使用，传入的必须必须是变量，如果传入的out变量本身在外部有数据，第一件事情就是数据被清空，设为null
Inout:数据可以从外部传入到内部使用，同时内部操作之后，又会将数据返回给外部。

用法：
过程类型  变量名  数据类型；
in   int_1  int;

存储函数：

1、什么是存储函数：

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

2、创建存储函数：

函数包括几个要素：function关键字、函数名、参数(形参和实参[可选])、确认函数返回值类型、函数体、返回值

注意事项：

1、函数内部的每条指令都是一个独立的个体，需要符合语句定义规范，需要语句结束分号；

2、函数是一个整体，而且函数是在调用的时候才会被执行，那么当设计函数时，意味着整体不能被中断；

3、mysql一旦见到语句结束符分号，就会自动执行；

解决方案：在定义函数之前，尝试 修改临时的语句结束符号，

语法：delimiter 新的结束符号【可以使用系统的非内置符号】 例如：delimiter $$;

修改完结束符号之后，在函数内部写入正常的sql指令，使用分号结束（系统不会执行，不认识分号）,当需要结束时，使用新修改的结束符号来进行结束，之后在修改回原来的语句修饰符号。

在MySQL中，创建存储函数使用CREATE FUNCTION关键字，其格式如下：

CREATE FUNCTION func_name ([param_name type[....]])  RETURNS type[characteristic ...] 
BEGIN
	routine_body
END;

参数说明：

（1）func_name ：存储函数的名称。

（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。

（3）RETURNS type：指定返回值的类型。

（4）characteristic：可选项，指定存储函数的特性。

（5）routine_body：SQL代码内容。

函数定义的常用语法：

修改语句结束符(修改为自己定义的结束符)

Create function 函数名(参数名 参数类型)  returns  返回值类型

Begin

	//函数体

	Return    返回值数据；//数据必须和结构中定义的返回值类型一致

end

语句结束符

修改语句结束符（把原来的结束符改回来）

具体的sql语句

--创建函数
--修改语句结束符
delimiter $$
create function  my_func1() returns int
begin
	return 10;
end
--结束
--修改语句结束符（改回来）
delimiter ；

**注意：**当函数体本身只有一提哦啊指令时（也就是有一条返回语句），可以省略begin和end

3、查看函数

查看function的状态，查看所有函数

Show function status [like 'pattern'];
[like 'pattern']:表示精确匹配一部分

**注意：**在该数据库下创建的函数，只能在该数据库下进行使用，但是可以在其他的数据库中进行查看

查看函数的创建语句

show create function my_func1;

4、调用函数

select my_func1();
select my_func2(10,100);

5、删除函数

DROP function my_func1;

注意：函数因为必须规范返回值，所有在函数内部不能使用select指令，因为select一旦执行就会返回一个结果，和函数的返回发生冲突。

实践小案例：

需求：从1开始，知道用户传入对应的值为止，自动求和：凡是5的倍数都不要

实现思路：
1、创建函数
2、需要一个形参、确定要累加到什么时候为止
3、需要定义一个变量来保存结果
4、内部需要一个循环来实现迭代累加
5、循环内部需要进行条件判断控制：5的倍数

代码实现：
--修改语句结束符号
delimiter $$
--创建函数
create function my_sum(end_value int) returns int
begin
		--声明变量（局部变量）：如果使用declare声明变量，则必须在函数体其他语句之前；
		declare res int default 0;
		declare i int default 1;
		--循环处理
		mywhile:while i<end_value do 
				--判断当前数据是否合理
				if i % 5= 0 then
					--5的倍数不要
					set i=i+1;
                    iterate mywhile;
                    end if;
                    
             	  --修改变量，进行累加
             	  set res=res+i;
             	  set i=i+1;
            end mywhile;

		--返回值
		return res;
end
--结束
$$
--修改语句结束符号（改回来）
delimiter ；
       

触发器：

什么是触发器：触发器是一种特殊类型的存储过程，不同于我们之前介绍过的存储过程，触发器主要通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

触发器：trigger

作用：

1、可在写入表前、强制检验或者转换数据（保证数据安全）

2、触发器发生错误时，异动的结果会被撤销（如果触发器执行错误，那么前面用户已经执行成功的操作也会被撤销，事务安全）

3、部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDl触发器

4、可依照特定的情况，替换异动的指令，（mysql不支持该操作）

触发器的优缺点：

1、触发器可通过数据库中的相关表实现级联更新（如果某张表的数据改变，可以利用触发器来实现其他表的无痕操作）

2、保证数据安全、进行安全验证

缺点：

1、对触发器过分的依赖，势必会影响数据库的结构，同时增加了维护的复杂程度

2、造成数据在程序层面不可控

触发器基本语法：

创建触发器

基本语法

create trigger 触发器名字 触发时间  触发事件 on 表 for each row
Begin

End

触发对象：on 表 for each row，触发器绑定实质是表中的所有行，因此当每一行发生指定的改变的时候，就会触发触发器。

触发时机

触发时机：每张表中对应的行都会有不同的状态，当SQL指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态：数据操作前和操作后

Before：在表中数据发生改变前的状态

After：在表中数据已经发生改变后的状态

触发事件

触发事件：mysql中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改）

Insert：插入操作

Update：更新操作

Delete：删除操作

注意事项

一张表中，每一个触发时机绑定的触发事件对应的触发器类型只能有一个：一张表中只能有一个对应after insert触发器

因此，一张表中最多的触发器只能有6个：before insert，before update，before delete，after insert，after update，after delete