【MySQL系统学习专栏】- 存储过程和存储函数

最新推荐文章于 2023-03-15 01:40:37 发布

陈七.

最新推荐文章于 2023-03-15 01:40:37 发布

阅读量599

点赞数 1

分类专栏：数据库文章标签： mysql 数据库学习

本文链接：https://blog.csdn.net/qq_45491628/article/details/129326547

版权

数据库专栏收录该内容

16 篇文章 3 订阅

订阅专栏

一、概述

（一）什么是存储过程和存储函数

MySQL中提供存储过程与存储函数机制，我们先将其统称为存储程序，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

存储过程和函数是在数据库中定义的一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL服务器中存储和执行的，可以减少客户器端和服务端的数据传输。

实际上就是数据库 SQL 语言层面的代码封装与重用。

（二）优点

通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。

1.性能：存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

2.复用：存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

3.安全：存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

（三）缺点

1.如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。此外，如果在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不偏于逻辑运算。

2.很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

二、创建存储过程

（一）基本语法

CREATE PROCEDURE procedure_name([proc_param[,…]]) routine_body

在上述语句中，参数 procedure_name 表示所要创建的存储过程名字，参数 proc_param 表示存储过程的参数，参数 routine_body 表示存储过程的 SQL 语句代码，可以用 BEGIN…END 来标志 SQL 语句的开始和结束。

提示: 在具体创建存储过程时，存储过程名不能与已经存在的存储过程名重名，实战中推荐存储过程名命名procedure_xxx 或者 proc_xxx。

proc_param 中每个参数的语法形式如下：

[IN|OUT|INOUT] param_name type

在上述语句中，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。其中，输入/输出类型有三种类型，分别为 IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）。param_name表示参数名；type 表示参数类型，可以是 MySQL 软件所支持的任意一个数据类型。

实例：

use school; #选择数据库 school
DELIMITER $$

create PROCEDURE proc_delete_student (IN sid int )
BEGIN
declare cid int ; #定义变量 cid
Select class_id into cid from student where id = sid; #通过查询语句设置变量
delete from grade where id = sid; #删除成绩表中的记录
delete from student where id = sid; #删除学生表中的记录
update class set count=count-1 where id = cid; #更新班级表中的记录
END;
$$

DELIMITER ;
call proc_delete_student(2); #调用存储过程

（二）变量的使用

在存储过程和函数中，可以定义和使用变量。用户可以使用关键字 DECLARE 来定义变量，然后为变量赋值。这些变量的作用范围是在 BEGIN…END 程序段中。

1. 定义变量

在 MySQL 中，可以使用 DECLARE 关键字来定义变量。定义变量的基本语法如下：

DECLARE var_name[,…] type [DEFAULT value]

其中，关键字 DECLARE 是用来声明变量的；参数 var_name 是变量的名称，可以同时定义多个变量；参数 type用来指定变量的类型；DEFAULT value 子句将变量默认值设置为 value，没有使用 DEFAULT 子句时，默认值为NULL。定义变量 cid，数据类型为 INT 型，默认值为 10，代码如下：

DECLARE cid INT DEFAULT 10;

2. 为变量赋值

（1）在 MySQL 中可以使用关键字 SET 来为变量赋值，SET 语句的基本语法如下：

SET var_name=expr[,var_name=expr]…

其中，关键字 SET 用来为变量赋值；参数 var_name 是变量的名称；参数 expr 是赋值表达式。一个 SET 语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

例如，将变量 tmp_id 赋值为 88，代码如下：

SET tmp_id = 88;

（2）在 MySQL 中，还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：

SELECT col_name[,…] INTO var_name[,…] FROM table_name WHERE condition

其中，参数 col_name 表示查询的字段名称；参数 var_name 是变量的名称；参数 table_name 指表的名称；参数condition 指查询条件。

实例：

use school; #选择数据库 school
drop PROCEDURE if exists query_student_class_info;
DELIMITER $$

create procedure query_student_class_info (IN sid int, OUT cname varchar(128), OUT ccount int)
BEGIN
declare tmp_name varchar(128);
declare tmp_count int;
declare tmp_cid int;
select class_id into tmp_cid from student where id = sid;
select name, count into tmp_name, tmp_count from class where id = tmp_cid;
set cname = tmp_name, ccount = tmp_count;
END;
$$

DELIMITER ;
call query_student_class_info(4, @name, @count); #调用存储过程
select @name, @count;

（三）光标（游标）的使用

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标称为游标。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1. 声明光标

在 MySQL 中，可以使用 DECLARE 关键字来声明光标，其基本语法如下：

DECLARE cursor_name CURSOR FOR select_statement;

其中，参数 cursor_name 表示光标的名称；参数 select_statement 表示 SELECT 语句的内容。

实例：

下面声明一个名为 cur_student 的光标，代码如下：

use school; #选择数据库 school
DELIMITER $$

create procedure query_student (IN sid int, OUT cname varchar(128), OUT class_id int )
BEGIN
DECLARE cur_student CURSOR FOR SELECT name, class_id FROM student;
END;
$$

DELIMITER ;

在上面的示例中，光标的名称为 cur_student；SELECT 语句部分是从表 student 中查询出字段 name 和 class_id的值。

2. 打开光标

在 MySQL 中，使用关键字 OPEN 来打开光标，其基本语法如下：

OPEN cursor_name;

其中，参数 cursor_name 表示光标的名称。

下面代码打开一个名为 cur_student 的光标，代码如下：

OPEN cur_student;

3. 使用光标

在 MySQL 中，使用关键字 FETCH 来使用光标，其基本语法如下：

FETCH cursor_name INTO var_name[,var_name…];

其中，参数 cursor_name 表示光标的名称；参数 var_name 表示将光标中的 SELECT 语句查询出来的信息存入该参数中。var_name 必须在声明光标之前就定义好。

实例：

下面声明一个名为 cur_student 的光标，代码如下：

use school; #选择数据库 school
DELIMITER $$

create procedure query_student (IN sid int, OUT cname varchar(128), OUT cid int)
BEGIN
declare tmp_name varchar(128); #必须定义在声明光标之前
declare tmp_cid int;
declare done int default 0;
declare cur_student CURSOR FOR SELECT name, class_id FROM student where id = sid;
declare continue handler for not found set done = 1; #将结束标志绑定到游标上

open cur_student;

select done;
fetch cur_student into tmp_name, tmp_cid;
select done;
select tmp_name, tmp_cid;  #打印从光标中获取到的值

close cur_student;

set cname = tmp_name, cid = tmp_cid;
END;
$$

DELIMITER ;

4. 关闭光标

在 MySQL 中，使用关键字 CLOSE 来关闭光标，其基本语法如下：

CLOSE cursor_name;

其中，参数 cursor_name 表示光标的名称。

例如: 关闭一个名为 cur_student 的光标，代码如下：

CLOSE cur_student;

在上面的示例中，关闭了这个名称为 cur_student 的光标。关闭了之后就不能使用 FETCH 来使用光标了。提示如果存储过程或函数中执行了 SELECT 语句，并且 SELECT 语句会查询出多条记录，这种情况最好使用光标来逐条读取记录，光标必须在处理程序之前且在变量和条件之后声明，而且光标使用完毕后一定要关闭。

（四）流程控制的使用

在存储过程和函数中，可以使用流程控制来控制语句的执行。在 MySQL 中，可以使用 IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。

1. IF 语句

IF 语句用来进行条件判断。根据条件执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list]
END IF

参数 search_condition 表示条件判断语句；参数 statement_list 表示不同条件的执行语句。

实例：

IF age>20 THEN SET @count1=@count1+1;
ELSEIF age=20 THEN @count2=@count2+1;
ELSE @count3=@count3+1;
END IF;

该示例根据 age 与 20 的大小关系来执行不同的 SET 语句。如果 age 值大于 20，将 count1 的值加 1；如果 age值等于 20，就将 count2 的值加 1；其他情况将 count3 的值加 1。IF 语句都需要使用 END IF 来结束。

2. CASE 语句

CASE 语句可实现比 IF 语句更复杂的条件判断，其语法的基本形式如下：

CASE case_value
WHEN when_value THEN statement_list
[ WHEN when_value THEN statement_list ]
[ELSE statement_list]
END CASE

其中，参数 case_value 表示条件判断的变量；参数 when_value 表示变量的取值；参数 statement_list 表示不同when_value 值的执行语句。

实例：

CASE level

WHEN 20 THEN SET attack = attack + 5;

WHEN 30 THEN SET attack = attack + 10;

WHEN 40 THEN SET attack = attack + 15;

ELSE SET attack = attack + 1;

END CASE

当级别 level 值为 20 时，attack 值加 5；当级别 level 值为 30 时，attack 值加 10；当级别 level 值为 40 时，attack值加 15；否则，attack + 1。CASE 语句使用 END CASE 结束。

3. LOOP 语句

LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。LOOP 语句本身没有停止循环，只有遇到 LEAVE语句等才能停止循环。LOOP 语句的语句形式如下：

[begin_label:] LOOP

statement_list

END LOOP [end_label]

其中，参数 begin_label 和参数 end_label 分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；参数 statement_list 表示需要循坏执行的语句。

实例：

add_num:LOOP

SET @count = @count + 1;

END LOOP add_num;

该示例循环执行 count 加 1 的操作。因为没有跳出循环的语句，这个循环成了一个死循环。LOOP 循环都以 END LOOP 结束。

4. LEAVE 语句

LEAVE 语句主要用于跳出循环控制，其语法形式如下：

LEAVE label

其中，参数 label 表示循环的标志。

实例：

add_num: LOOP
SET @count=@count + 1;
Select @count;
IF @count = 100 THEN LEAVE add_num;
END IF;
END LOOP add_num;

该示例循环执行 count 值加 1 的操作。当 count 的值等于 100 时，LEAVE 语句跳出循环。

5. ITERATE 语句

ITERATE 语句也是用来跳出循环的语句，但是 ITERATE 语句是跳出本次循环，然后直接进入下一次循环，ITERATE

语句的语法形式如下：

ITERATE label

其中，参数 label 表示循环的标志。

实例：

add_num1:LOOP
Set @count = @count +1
IF @count=100 THEN LEAVE add_num1
ELSE IF MOD(@count, 3) = 0 then ITERATE add_num1;
Select * from student;
END LOOP add_num1;

该示例循环执行 count 加 1 的操作，count 的值为 100 时结束循环。如果 count 的值能够整除 3，就跳出本次循环，不再执行下面的 SELECT 语句。

注意： LEAVE 语句和 ITERATE 语句都用来跳出循环语句，但是两者的功能是不一样的。LEAVE 语句是跳出整个循环，然后执行循环后面的程序，和 C++ break 相似。ITERATE 语句是跳出本次循环，然后进入下一次循环, 和 C++ continue 相似。使用这两个语句时一定要区分清楚。

6. REPEAT 语句

REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本语法形式如

下：

[begin_label:] REPEAT

statement_list;

UNTIL search_condition

END REPEAT [end_label]

其中，参数 statement_list 表示循环的执行语句；参数 search_condition 表示结束循环的条件，满足该条件时循环结束。

实例：

REPEAT

SET @count=@count+1;

UNTIL @count=100

END REPEAT;

该示例循环执行 count 加 1 的操作，count 值为 100 时结束循环。REPEAT 循环都用 END REPEAT 结束。奇牛编程

7. WHILE 语句

WHILE 语句也是有条件控制的循环语句，但 WHILE 语句和 REPEAT 语句是不一样的。WHILE 语句是当满足条件时执行循环内的语句。WHILE 语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO

Statement_list

END WHILE [end_label]

其中，参数 statement_condition 表示循环执行的条件，满足该条件时循环执行；参数 statement_list 表示循环的执行语句。

实例：

WHILE @count<100 DO

SET @count = @count + 1;

END WHILE;

流程控制综合运用

（1）加上循环访问光标操作，访问光标中的所有记录,代码如下：

use school; #选择数据库 school
DELIMITER $$

create procedure query_all_students (IN sid int, OUT cname varchar(128), OUT cid int)
BEGIN

declare tmp_name varchar(128); #必须定义在声明光标之前
declare tmp_cid int;
declare done int default 0;

declare cur_student CURSOR FOR SELECT name, class_id FROM student ;
declare continue handler for not found set done = 1; #将结束标志绑定到游标上

open cur_student;

read_loop:LOOP #循环读取
fetch cur_student into tmp_name, tmp_cid;
IF done=1 then Leave read_loop;
END IF;
select tmp_name, tmp_cid; #打印从光标中获取到的值
END LOOP read_loop;

close cur_student;
set cname = tmp_name, cid = tmp_cid;
END;
$$

DELIMITER ;

（2）在学生表中插入一条记录，并返回记录的自增长 id

use school; #选择数据库 school

DELIMITER $$

create procedure fetch_insert_student_id (IN p_name varchar(128), in p_class_id int, IN p_sex char(1), OUT rid int)

BEGIN
Insert into student (name, class_id, sex) values(p_name, p_class_id, p_sex);
select last_insert_id() as rid;
END;
$$

DELIMITER ;

三、查看存储过程

存储过程创建以后，用户可以通过 SHOW STATUS 语句来查看存储过程的状态，也可以通过 SHOW CREATE语句来查看存储过程的定义。用户也可以通过查询 information_schema 数据库下的 Routines 表来查看存储过程的信息。本节将详细讲解查看存储过程的状态与定义的方法。

1.SHOW STATUS 语句查看存储过程

在 MySQL 中，可以通过 SHOW STATUS 语句。其基本语法形式如下：

SHOW PROCEDURE STATUS [ like ‘pattern’ ] ;

其中，参数 PROCEDURE 表示查询存储过程；参数 LIKE 'pattern’用来匹配存储过程的名称。

2.使用 SHOW CREATE 语句查看存储过程的定义

在 MySQL 中，可以通过 SHOW CREATE 语句查看存储过程的状态，语法形式如下：

SHOW CREATE PROCEDURE proc_name

其中，参数 PROCEDURE 表示查询存储过程；参数 proc_name 表示存储过程的名称

3.从 information_schema.Routine 表中查看存储过程的信息

存储过程和函数的信息存储在 information_schema 数据库下的 Routines 表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

SELECT * FROM information_schema.Routines Where ROUTINE_NAME = ‘proc_name’;

其中，字段 ROUTINE_NAME 是 Routines 存储存储过程和函数的列名称；参数 proc_name 表示存储过程或函数的名称。

四、删除存储过程

在 MySQL 中删除存储过程通过 SQL 语句 DROP 完成：

DROP PROCEDURE proc_name;

在上述语句中，关键字 DROP PROCEDURE 用来表示实现删除存储过程，参数 proc_name 表示所要删除的存储过程名称

实例：

DROP PROCEDURE proc_delete_student;

五、存储函数

存储函数的用法大部分与存储过程一致，这里就简单介绍一下，有些地方直接称呼存储函数为函数，其实也是可以的，不过为了和内置函数区分开，更加准确的表达含义，我们就称呼为存储函数

（一）函数创建

语法：

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体
END

注意：

参数列表包含两部分：参数名、参数类型
函数体：肯定会有return语句，如果没有会报错。如果return语句没有放在函数体的最后也不会报错，但不建议
函数体中仅有一句话，则可以省略begin end
使用delimiter语句设置结束标记
在return value语句中包含select语句时，select语句的返回结果只能是一行且只能是一列值（即单值）；

实例：

在数据库db_school中，创建一个存储函数，要求该函数根据给定的学号返回学生的姓名；

Delimiter $$

Create function f_name(sno int) Returns char(20)
Begin
Return (select studentname from tb_student where studentno=sno);
End
$$
Select f_name(2013110101)$$

（二）函数调用

select 函数名（参数列表）

（三）查看存储函数

show create function myf3;

（四）删除存储函数

drop function myf3;

六、存储过程与存储函数的区别

（1）存储函数不能拥有输出参数。存储过程拥有输出参数。

（2）可以直接对存储函数调用（select），不需要使用CALL语句，存储过程调用需要。

（3）存储函数中必须包含RETURN语句，此语句不允许出现在存储过程中。

（4）存储过程和存储函数都是存储在服务器端的SQL语句的集合，要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现，存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的，区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

（5）存储函数的限制比较多，例如不能用临时表,只能用表变量，而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。

（6）返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out，inout)。