目录
- 一、存储过程的介绍
- 二、存储过程的操作
- 2.1 创建存储过程
- 2.2 调用存储过程
- 2.3 查看存储过程
- 2.4 删除存储过程
- 三、存储过程中的语法构造
- 3.1 变量的声明以及赋值
- 3.2 条件判断
- 3.3 传递参数
- 3.4 case结构
- 3.5 while循环
- 3.6 repeat循环
- 3.7 loop循环与leave语句
- 感谢
一、存储过程的介绍
基本定义
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时,只需调用“CALL存储过程名字”即可自动完成。(对应的就是Java里面的函数)
我们通常使用的SQL 语句都是针对一个表或几个表的单条 SQL 语句,但是在数据库的实际操作中,并非所有操作都那么简单,有时候一个完整的操作需要多条 SQL 语句处理多个表才能完成。例如:为了确认学生能否毕业,需要同时查询学生档案表、成绩表和综合表。此时就需要使用多条 SQL 语句来针对几个数据表完成这个处理要求。存储过程可以有效地完成这个数据库操作。
常用操作数据库的 SQL 语句在执行的时候需要先编译,然后执行。存储过程则采用另一种方式来执行 SQL 语句。一个存储过程是一个可编程的函数,它在数据库中创建并保存,一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时,存储过程尤为合适。
存储过程通常有如下优点:
- 封装性。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的 SQL 语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码
- 可增强 SQL 语句的功能和灵活性。存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算
- 可减少网络流量。这个不难理解,以往我们使用单条sql,每次调用都需要发起一次IO请求将完整的IO请求发送过去,但是现在只需要【CALL 存储过程】就可以了。确切的说:由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载
- 高性能。存储过程执行一次后,产生的二进制代码就驻留在缓冲区,在以后的调用中,只需要从缓冲区中执行二进制代码即可,从而提高了系统的效率和性能
- 提高数据库的安全性和数据的完整性。使用存储过程可以完成所有数据库操作,并且可以通过编程的方式控制数据库信息访问的权限
特别声明
存储过程跟自定义函数很像。它们的区别是:
- 存储过程实现的功能要复杂一些;而函数的针对性更强。
- 存储过程可以返回多个值;函数只能有一个返回值。
- 存储过程一般独立的来执行;而函数可以作为其他SQL语句的组成部分实现出来。
二、存储过程的操作
2.1 创建存储过程
语法格式如下:
CREATE PROCEDURE <过程名>(参数列表 参数类型)
BEGIN
<过程体 sql语句>
END
语法说明如下:
1)上面的CREATE PROCEDURE
,BEGIN
和END
是固定的
2)过程名:存储过程的名字。我们在调用的时候,就是使用CALL + 过程名
调用。另外,尽量避免过程名跟Mysql已存在的函数名相同导致冲突
3)参数列表-参数类型:存储过程可以没有参数,也可以有多个参数,参数的声明是【参数名+参数类型】声明的。
使用示例:
CREATE PROCEDURE get_canteen_dish_sales ()
BEGINSELECT food_name, sales_weights FROM unify_dish_sales ORDER BY sales_weights DESC LIMIT 20;
END
2.2 调用存储过程
语法格式如下:
CALL 过程名(); # 调用的时候需要加上括号,因为可能存在参数
使用示例:
call get_canteen_dish_sales();
一个很简单的使用,查询的是我们生产库里面一个菜品分析表,获取菜品名称和销售量。
2.3 查看存储过程
语法格式如下:
# 查询指定数据库中的所有的存储过程
select name from mysql.proc where db='数据库名';
# 查询存储过程的状态信息
show procedure status;
使用示例:
select name from mysql.proc where db='unify-dev';
show PROCEDURE status;
2.4 删除存储过程
语法格式如下:
DROP PROCEDURE 过程名; # 删除的时候不要加小括号,直接给定存储过程的名字即可。
DROP PROCEDURE IF EXISTS 过程名; # 如果存储删除,不存在不删除并且不会报错
使用示例:
DROP PROCEDURE get_canteen_dish_sales;
DROP PROCEDURE IF EXISTS get_canteen_dish_sales;
三、存储过程中的语法构造
我们在最开始的时候介绍过,存储过程是可以编程的,意味着可以使用变量、表达式、控制语句来完成比较复杂的功能。接下来我们学习一下如何使用更丰富的语法来完成复杂的存储过程。
3.1 变量的声明以及赋值
变量的声明
变量声明的语法格式如下:
DECLARE 变量名[,...] type [DEFAULT value]
注意:声明变量的时候可以一次性声明多个,使用逗号隔开。
示例如下:
CREATE PROCEDURE calculate ()
BEGINDECLARE num1 INT;DECLARE num2 INT DEFAULT 1;SELECT num1 + num2;
END
PS:注意上面示例num1
是没有默认值的,所以SELECT num1 + num2;
是一个null
值。
变量的赋值
变量的赋值有2种方式,分别为:SET
赋值,以及SELECT INTO
赋值。先来介绍下SET
的赋值方式:
- SET : 直接赋值使用SET关键字,可以赋常量或者是表达式,具体语法如下:
-- 注意:一次可以给多个变量赋值,中间使用逗号隔开。
SET 变量名 = 变量值 [,变量名 = 变量值] ...
- 使用示例如下:
CREATE PROCEDURE calculate ()
BEGINDECLARE num1 INT;DECLARE num2 INT DEFAULT 1;SET num1=2,num2=3;SELECT num1 + num2;
END
再来看看SELECT INTO
的赋值方式:
SELECT INTO
:语法格式如下
SELECT <column | 聚合函数> INTO 变量名;
- 使用示例:
CREATE PROCEDURE calculate ()
BEGINDECLARE num1 INT;DECLARE num2 INT DEFAULT 1;SELECT COUNT(1) INTO num1 FROM unify_dish_sales;SELECT num1+num2;
END
3.2 条件判断
if条件判断
语法格式如下:
# 只有满足差选条件才会执行 then 后面的SQL语句
if search_condition(查询条件) then statement_list(SQL语句)[else if search_condition(查询条件) then statement_list(SQL语句)]...[else statement_list(SQL语句)]
end if;
使用示例:
CREATE PROCEDURE calculate ()
BEGINDECLARE openStatus VARCHAR(20);DECLARE storeTurnover DECIMAL(10, 2);SELECT turnover INTO storeTurnover FROM unify_report_store_line LIMIT 1;IF storeTurnover > 0THENSET openStatus='开业了';ELSESET openStatus='歇业中';END IF;SELECT openStatus;
END
上面的源码敢的事情如下:
- 声明了一个
openStatus
变量,用来描述今天的营业装填 - 声明了
storeTurnover
用来接收营业额 - 去数据库表查询营业额,由于我现在生产库营业额有多条记录,所以我用了个
LIMIT 1
限定只获取一条 - 判断
storeTurnover
,如果大于0说明今天营业了;反之则没有
3.3 传递参数
传递参数需要在创建存储过程的时候,就要做好。语法格式如下:
CREATE PROCEDURE <过程名>([in/out/inout] 参数列表 参数类型)
BEGIN
<过程体 sql语句>
END
大家注意这个语法跟们最开始创建存储过程的时候,不同的是,参数列表前面的[in/out/inout]
,他们的作用分别如下:
- in : 该参数可以作为输入,调用该存储过程需要传入的值,默认的选项
- out : 该参数作为输出,调用该存储过程之后返回的值。
- inout : 既可以作为输入参数也可以作为输出参数
使用示例1:in参数
CREATE PROCEDURE calculate (IN storeTurnover DECIMAL(10, 2))
BEGINDECLARE openStatus VARCHAR(20);IF storeTurnover > 0THENSET openStatus='开业了';ELSESET openStatus='歇业中';END IF;SELECT openStatus;
END
很简单的一个示例,改自前一个示例。之前是从数据库获取,现在是由用户自己定义。如果传入的数大于0,则返回开业了
,反之返回歇业中
。然后调用的时候,跟调用函数一样就可以了。
CALL calculate(0);
使用示例2:out参数
CREATE PROCEDURE calculate (IN storeTurnover DECIMAL(10, 2), OUT openStatus VARCHAR(20))
BEGINIF storeTurnover > 0THENSET openStatus='开业了';ELSESET openStatus='歇业中';END IF;SELECT openStatus;
END
调用的话,需要用一个Mysql的会话变量来接收,然后select
:
CALL calculate(0, @openStatus);
select @openStatus;
@标识符的作用
@openStatus:这种在变量名前面加上”@“符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。当前会话就是代表的,比如我们在命令提示窗口中给好多带有 @ 符号变量进行赋值,此时这些变量的值只作用于当前的会话,当我们把这个窗口关闭的时候,此时这些变量的值就会释放掉。
@@global : 这种在变量名前加上 “@@” 符号,叫做系统变量。
3.4 case结构
语法格式如下:
# 方式一
case case_value(判断的值)when when_value(比较的值) then statement_list(SQL语句)[when when_value(比较的值) then statement_list(SQL语句)]...[else statement_list(SQL语句)]
end case;
# 方式二
case when search_condition(查询条件) then statement_list(SQL语句)[when search_condition(查询条件) then statement_list(SQL语句)]...[else statement_list(SQL语句)]
end case;
使用示例:
CREATE PROCEDURE calculate (IN storeTurnover DECIMAL(10, 2), OUT openStatus VARCHAR(20))
BEGINCASE WHEN storeTurnover > 1000 THENSET openStatus='赚麻了';WHEN storeTurnover > 500 THENSET openStatus='小赚';ELSESET openStatus='亏了亏了';END CASE;
END
3.5 while循环
有条件的循环控制语句,当满足条件的时候进入循环,不满足条件的时候退出循环。语法结构如下:
# 只要查询条件一直成立就会一直指定do后面的SQL语句,当查询条件不成立的时候直接跳出while循环
while search_condition(查询条件) dostatement_list(SQL语句)
end while;
使用示例:(计算从1加到n的值)
CREATE PROCEDURE pro_sum (IN num INT(11))
BEGINDECLARE number INT DEFAULT 0;DECLARE total INT DEFAULT 0;WHILE number <= num DOSET total = total + number;SET number = number + 1;END WHILE;SELECT total;
END
3.6 repeat循环
有条件的循环控制语句,当不满足条件的时候进入循环,满足条件的时候跳出循环。他和while循环是反着的。语法结构如下:
repeat statement_list(SQL语句)until search_condition(查询添加)
end repeat;
使用示例:(计算从1加到n的值)
CREATE PROCEDURE pro_sum (IN num INT(11))
BEGINDECLARE total INT DEFAULT 0;REPEAT set total = total + num;set num = num - 1;# 注意:这个 unti 后的查询条件不要加分号,加分号会报错。until num = 0END REPEAT;SELECT total;
END
3.7 loop循环与leave语句
loop:实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用leave语句实现,具体语法如下:
[begin_label:] loopstatement_list
end loop [end_label]
(PS:如果不在statement_list中增加退出循环的语句,那么loop语句可以永安里实现简单的死循环。)
leave:用来从标注的流程构造中退出,通常和 begin…end 或循环一起使用。下面是一个使用loop和leave的简单例子,退出循环。
上面两个结合起来的示例如下:
CREATE PROCEDURE pro_sum (IN num INT(11))
BEGINDECLARE total INT DEFAULT 0;mySum:loopset total = total + num;set num = num - 1;# 借助leave组织退出条件if num <= 0 thenleave mySum;end if; end loop mySum;SELECT total;
END
感谢
感谢小白教程的《Mysql教程》
感谢知乎大佬作者:程云博,的文章《MySQL高级篇——存储过程以及语法》