一文学会MYSQL数据库的存储过程和函数：一文学会MYSQL数据库的存储过程和函数
一文学会MYSQL数据库的存储过程和函数：一文学会MYSQL数据库的存储过程和函数其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用" "分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型 IN OUT INOUT:CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[ [IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体DELIMITER // CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; END //
什么是存储过程简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
ps:存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；
存储过程特性
- 有输入输出参数，可以声明变量，有if/else case while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
- 函数的普遍特性：模块化，封装，代码复用；
- 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；
MYSQL存储过程的创建
1、语法
CREATE PROCEDURE sp_name ([proc_parameter[ ...]]) [characteristic ...] routine_body
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[ [IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER // CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; END // DELIMITER ;
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
2、参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用" "分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型 IN OUT INOUT:
- IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
- OUT:该值可在存储过程内部被改变，并可返回
- INOUT:调用时指定，并且可被改变和返回
其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。
上述存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录，并用COUNT(*)计算d_id值相同的记录的条数，最后将计算结果存入count_num中。执行结果如下：
执行完毕后，没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程，数据库中会执行存储过程中的SQL语句。
说明：MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来 结束。为了避免冲突，首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。
函数
在MySQL中，创建存储函数的基本形式如下：
CREATE FUNCTION sp_name ([func_parameter[ ...]]) RETURNS type [characteristic ...] routine_body
其中，sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；RETURNS type指定返回值的类型；characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。
func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：param_name type
其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以是MySQL数据库的任意数据类型。
函数实例
下面创建一个名为name_from_employee的存储函数。
上述存储函数的名称为name_from_employee；该函数的参数为emp_id；返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录，并将该记录的name字段的值返回。执行结果如下：
结果显示，存储函数已经创建成功。该函数的使用和MySQL内部函数的使用方法一样。
变量的使用
在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。
1、定义变量
MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：
DECLARE var_name[ ...] type [DEFAULT value]
其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。
下面定义变量my_sql，数据类型为INT型，默认值为10。
DECLARE my_sql INT DEFAULT 10 ;
2、为变量赋值
MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：
SET var_name = expr [ var_name = expr] ...
其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
下面为变量my_sql赋值为30。
SET my_sql = 30 ;
MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：
SELECT col_name[ …] INTO var_name[ …] FROM table_name WEHRE condition
其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。
下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。
SELECT d_id INTO my_sql FROM employee WEHRE id=2 ;
MySQL存储过程写法总结
1、创建无参存储过程。
create procedure product() begin select * from user; end;
一条简单的存储过程创建语句，此时调用的语句为：
call procedure();
2、创建有参存储过程
有参的存储包括两种参数，
一个是传入参数;
一个是传出参数;
例如一个存储过程：
create procedure procedure2( out p1 decimal(8 2) out p2 decimal(8 2) in p3 int ) begin select sum(uid) into p1 from user where order_name = p3; select avg(uid) into p2 from user ; end ;
从上面sql语句可以看出，p1和p2是用来检索并且传出去的值，而p3则是必须有调用这传入的具体值。
具体调用过程：
call product(); //无参
call procedure2(@userSum @userAvg 201708); //有参
当用完后，可以直接查询userSum和userAvg的值：
select @userSum @userAvg;
结果如下：
3、删除存储过程
语法：drop procedure procedure_name;
后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~
