现在学习存储过程，有一种四九年入国军的感觉，之前看公司计费相关的业务上还在用，所以还是抽时间简单学习了一下，这里记录一下。

说到存储过程，它的意义自不必提，各大老牌数据库都支持，而且经常以此来挤兑一些还不支持存储过程的新数据库。反过来，它的缺点也很明显，能见到很多开发团队在努力拆除存储过程，在应用程序中实现所有的业务逻辑。

存储过程在数据库管理中扮演着重要的角色，尽管随着技术的发展和编程范式的变化，它们的使用可能不如过去那样普遍。存储过程的主要优点包括：

• 提高性能：存储过程只在创建时进行编译，之后每次执行时不需要重新编译，这比每次执行SQL语句都要编译一次要快得多。此外，存储过程可以封装复杂的业务逻辑，减少网络流量，因为不需要在网络中发送大量的SQL代码。
• 模块化程序设计：存储过程允许将复杂的SQL代码封装在数据库中，使得应用程序的代码更加清晰和易于维护。这有助于减少重复的代码编写，提高代码的可重用性。
• 安全性：通过存储过程，可以设置只有某些用户才具有对指定存储过程的使用权，这提供了一定程度的安全性。与直接执行SQL语句相比，使用存储过程可以减少SQL注入的风险。

存储过程是事先经过编译并存储在数据库中的一段SQL语句集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率还是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码封装和重用。

简单来说，就是SQL只能写成单句，但有些逻辑需要多步才能完成，复杂时还会出现分支循环，用单句的SQL就很难写甚至写不出来，但是存储过程就可以解决这些问题。

基本结构

下面我们直接看存储过程的语法。

create procedure 存储过程名称(参数)begin    ...end;

其实跟java创建方法是一个思路。这里的begin end就是java中的{}

下面举一个简单的例子

create procedure pppp1()begin    select '666';end;

初次执行时可能会提示有sql语法错误，像我由于使用的是国产数据库greatdb（其实还是mysql），执行时提示我有语法错误：[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'begin select '666'; end' at line 2'

通常这种情况下是因为begin end是在ANSI模式下支持的，一些mysql版本默认可能不支持。

//查看系统中的sql模式select @@sql_mode;//将sql模式设置为session级别的ANSI_QUOTESSET session sql_mode = 'ANSI_QUOTES';

如果你的MySQL服务器版本支持ANSI SQL模式，你可能需要在会话开始时启用它，因为某些语法特性（如BEGIN和END块）在某些旧版本的MySQL中是特定于模式的。

设置完sql模式后，就提示执行成功了。

存储过程的调用

存储过程的调用：

call 存储过程名称(参数)

以上面我举的简单例子：

调用结果

小提示：

以上命令我都是在datagrip中执行的，如果我们不使用客户端工具，而是在mysql中执行时，通常会提示报错有语法错误。因为在mysql中，";"通常被认为是命令结束符号，我的例子中在sql后面就有分号，因此mysql认为此存储过程到此就结束了，并没有执行最后的end;

对于这个问题，我们引入一个命令：delimiter

delimiter命令用来指定结束符号，举例：

delimiter $：指的是定义一个结束符$，当以$结尾时，即是命令结束之时，定义语法需要优先执行后，再指定对应的创建存储过程的sql，一旦设置了结束符是$后，";"就不再是结束符了。

查看数据库中的存储过程

//这里的XXX指的是我数据库的名字select * from information_schema.ROUTINES where ROUTINE_SCHEMA='XXX';

通过系统表information_schema.ROUTINES查看存储过程的详细信：information_schema.ROUTINES 是 MySQL 数据库中一个系统表，存储了所有存储过程、函数、触发器的详细信息，包括名称、返回值类型、参数、创建时间、修改时间等。

举例

一些关键字段的解释：

• SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表。
• ROUTINE_SCHEMA：存储过程所在的数据库名称。
• ROUTINE_NAME：存储过程的名称。
• ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。
• ROUTINE_DEFINITION：存储过程的定义语句。
• CREATED：存储过程的创建时间。
• LAST_ALTERED：存储过程的最后修改时间。
• DATA_TYPE：存储过程的返回值类型、参数类型等。

查看存储过程的创建语句

与查看sql建表语句一样，存储过程也可以查看创建存储过程的信息

//这里的XXX指的是存储过程的名称show create procedure XXX;

举例

删除存储过程

//XXX是存储过程名称，if exists表示可选操作，是否存在drop procedure [if exists] XXX;

创建变量

变量分为：

• 系统变量

全局变量（即便是设置成全局变量，当系统重启之后，依然会恢复成默认值。想要不失效，在etc/my.cnf中配置）

session变量（开一个窗口就是一个session，写命令时可以不指定session或者global，默认是session）

• 用户自定义变量
• 局部变量

系统变量

系统变量是数据库中提供的变量信息，不是用户定义的，属于数据库层面，分为全局（global）变量和session变量

查看系统变量命令：

//查看所有系统变量show [session\global] variables;//模糊搜索某个系统变量show [session\global] variables like '';//查看指定的变量select @@session.sql_mode;

设置系统变量

set [global\session] 变量名='';set @@[global\session].sql_mode='';

在基本结构一章中我设置sql模式就是系统变量

//查看系统中的sql模式select @@sql_mode;//将sql模式设置为session级别的ANSI_QUOTESSET session sql_mode = 'ANSI_QUOTES';

用户自定义变量

自定义变量不需要提前声明，直接用@变量名即可，如果没有这个变量，返回null。

查看用户自定义变量：

//查询某一个自定义变量select @变量名;//查询多个select @变量名,@变量名,@变量名;

设置自定义变量：

//设置一个用户自定义变量set @变量名='wangpeng';//设置多个用户自定义变量set @变量名='wangpeng',@变量名='wangpeng',@变量名='wangpeng';//另一种设置方式，这个也支持设置多个set @变量名:='wangpeng';//将查询数据中的某个字段设置给自定义变量select SKU_NUM into @sku_num from prodord_sku where PRODORD_SKU_NUM='';

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ...END块。

声明局部变量：

//变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等.DECLARE 变量名 变量类型[DEFAULT...];

设置局部变量，与设置自定义变量类似：

SET 变量名=值;SET 变量名:=值SELECT 字段名 INTO 变量名 FROM 表名

举例：

create procedure p1()begin		//可以设置默认值    declare name varchar(32) default 'wangpeng';		//设置name的值    set name='wangpo';    select '666';end;

方法参数

刚才我们举例的是无参的存储过程，存储过程也是支持参数的。

• IN：该类参数作为输入，也就是需要调用时传入值
• OUT：该类参数作为输出，也就是该参数可以作为返回值，传入参数的时候在定义也行
• INOUT：既可以作为输入参数，也可以作为输出参数，需要提前定义变量

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])BEGIN		sqlEND

IN+OUT举例：

//创建一个存储过程create procedure p1(in name varchar(32),out age varchar(32))begin    set age=concat(name,'666');end;//调用存储过程call p1('wangpeng',@age);//查询输出的值select @age;//结果为：wangpeng666

INOUT举例：

//创建存储过程create procedure p2(inout score int)begin   set score :=score*2;end;//设置一个自定义变量set @score=50;//调用p2call p2(@score);//查询参数select @score;//结果：100

IF逻辑判断与WHEN逻辑判断

存储过程内部是可以写逻辑的，IF的基本语法规则：

if 条件一 then    ...elseif 条件二 then    ...elseif 条件三 then    ...else    ...end if;

举例：

create procedure p3(in score int, out result varchar(32))begin    if score > 80 then        set result := '优秀';    elseif score > 70 then        set result := '良';    elseif score>60 then        set result:='及格';    else        set result:='不及格';    end if;end;call p3(99,@result);select @result//结果：优秀

WHEN的基本语法规则：

case   when 条件一 then       ...   when 条件二 then       ...   when 条件三 then       ...   else       ...end case;

举例：

create procedure p5(in score int, out result varchar(32))begin    case        when score > 90 then            set result = '优秀';        when score > 70 then            set result = '良';        when score > 60 then            set result = '及格';        else            set result = '不及格';        end case;end;call p5(59, @result);select @result;//结果:不及格

后面还有循环处理（while、repeat、loop）、游标cursor、条件处理程序（handler）、存储函数，篇幅问题，我们在下一章节再说。