什么是存储过程

简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;

ps:存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用;

存储过程特性

  1. 有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;
  2. 函数的普遍特性:模块化,封装,代码复用;
  3. 速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;

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:

其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数列表; characteristic参数指定存储过程的特性;routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。

技巧:创建存储过程时,系统默认指定CONTAINS SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO SQL。而且,存储过程中最好在COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时更加方便。

存储过程实例

下面创建一个名为num_from_employee的存储过程。

mysql存储过程实例详解(一文学会MYSQL数据库的存储过程和函数)(1)

上述存储过程名称为num_from_employee;输入变量为emp_id;输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录,并用COUNT(*)计算d_id值相同的记录的条数,最后将计算结果存入count_num中。执行结果如下:

mysql存储过程实例详解(一文学会MYSQL数据库的存储过程和函数)(2)

执行完毕后,没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程,数据库中会执行存储过程中的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的存储函数。

mysql存储过程实例详解(一文学会MYSQL数据库的存储过程和函数)(3)

上述存储函数的名称为name_from_employee;该函数的参数为emp_id;返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录,并将该记录的name字段的值返回。执行结果如下:

mysql存储过程实例详解(一文学会MYSQL数据库的存储过程和函数)(4)

结果显示,存储函数已经创建成功。该函数的使用和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;

结果如下:

mysql存储过程实例详解(一文学会MYSQL数据库的存储过程和函数)(5)

3、删除存储过程

语法:drop procedure procedure_name;