MySQL存储过程

变量

一般可以将MySQL中的变量分为全局变量、会话变量、用户变量和局部变量，这是很常见的分类方法

首先我们知道MySQL服务器维护了许多系统变量来控制其运行的行为，这些变量有些是默认编译到软件中的，有些是可以通过外部配置文件来配置覆盖的

全局变量

接下来简单了解一下这几类变量的应用范围，首先MySQL服务器启动时会使用其软件内置的变量（俗称写死在代码中的）和配置文件中的变量（如果允许，是可以覆盖源代码中的默认值的）来初始化整个MySQL服务器的运行环境，这些变量通常就是我们所说的全局变量，这些在内存中的全局变量有些是可以修改的。

会话变量/用户变量

当有客户端连接到MySQL服务器的时候，MySQL服务器会将这些全局变量的大部分复制一份作为这个连接客户端的会话变量，这些会话变量与客户端连接绑定，连接的客户端可以修改其中允许修改的变量，但是当连接断开时这些会话变量全部消失，重新连接时会从全局变量中重新复制一份。

其实与连接相关的变量不只有会话变量一种，用户变量也是这样的，用户变量其实就是用户自定义变量，当客户端连接上MySQL服务器之后就可以自己定义一些变量，这些变量在整个连接过程中有效，当连接断开时，这些用户变量消失。

局部变量

局部变量实际上最好理解，通常由DECLARE 关键字来定义，经常出现在存储过程中，非常类似于C和C++函数中的局部变量，而存储过程的参数也和这种变量非常相似，基本上可以作为同一种变量来对待。

变量的修改

先说全局变量有很多是可以动态调整的，也就是说可以在MySQL服务器运行期间通过 SET 命令修改全局变量，而不需要重新启动 MySQL 服务，但是这种方法在修改大部分变量的时候都需要超级权限，比如root账户。

相比之下会话对变量修改的要求要低的多，因为修改会话变量通常只会影响当前连接，但是有个别一些变量是例外的，修改它们也需要较高的权限，比如 binlog_format 和 sql_log_bin，因为设置这些变量的值将影响当前会话的二进制日志记录，也有可能对服务器复制和备份的完整性产生更广泛的影响。

至于用户变量和局部变量，听名字就知道，这些变量的生杀大权完全掌握在自己手中，想改就改，完全不需要理会什么权限，它的定义和使用全都由用户自己掌握。

变量查询与设置

全局变量查询

这些变量来源于软件自编译、配置文件中、以及启动参数中指定的变量，其中大部分是可以由root用户通过 SET 命令直接在运行时来修改的，一旦 MySQL 服务器重新启动，所有修改都被还原。如果修改了配置文件，想恢复最初的设置，只需要将配置文件还原，重新启动 MySQL 服务器，一切都可以恢复原来的样子。

查询所有的全局变量：

show global variables; 

一般不会这么用，这样查简直太多了，大概有500多个，通常会加个like控制过滤条件：

show global variables like 'sql%'; 

设置全局变量也有两种方式：

set global sql_mode=''; 

会话变量

这些变量基本来自于全局变量的复制，与客户端连接有关，无论怎样修改，当连接断开后，一切都会还原，下次连接时又是一次新的开始。

类比全局变量，会话变量也有类似的查询方式，查询所有会话变量

show session variables; 

添加查询匹配，只查一部分会话变量：

show session variables like 'sql%'; 

查询特定的会话变量:

select @@session.sql_mode; 

会话变量的设置方法:

set session sql_mode = ''; 

定义用户变量

用户变量就是用户自己定义的变量，也是在连接断开时失效，定义和使用相比会话变量来说简单许多。

要创建用户定义的变量，请使用格式@variable_name，其中variable_name由字母数字字符组成。 用户自定义变量的最大长度为64个字符(MySQL 5.7.5之前的版本)

用户定义的变量不区分大小写。 这意味着@id和@ID是一样的。

可以将用户定义的变量分配给某些数据类型，例如整数，浮点，小数，字符串或NULL。

由一个客户端定义的用户定义的变量不被其他客户端看到。 换句话说，用户定义的变量是特定于会话的。

定义用户变量的案例:

定义name 和sex变量

set @t_name=0; 
 
set @t_sex=''; 
 
set @t_money=0.0; 
 
set @t_str=""; 

在定义变量的时候必须声明类型 ’ ’ 和 " " 都是字符串类型

用户变量赋值

有两种方法可以将值分配给用户定义的变量。

使用SET语句如下：

set @count=1; 
set @sum:=0; 

您可以使用:=或=作为SET语句中的赋值运算符。

为什么会有 := 这样的赋值? ，因为在SELECT语句中，MySQL将=运算符视为相等运算符。

SELECT @variable_name := value; 

列:

select  @count1:=count(id)   from t_student ; 
SELECT @count 

在SELECT中 也可以使用select into语句来设置值，比如：

select count(id) into @count from items; 
SELECT @count1 

查询用户变量

直接一个select语句就可以了：

select @variable_name; 

用户变量使用示例

以下语句在products表中查询获得最昂贵的产品，并将价格分配给用户定义的变量@msrp：

SELECT  
    @msrp:=MAX(msrp) 
FROM 
    products; 

执行上面查询语句，得到以下结果 -

mysql> select @msrp as max_price ; 
+-----------+ 
| max_price | 
+-----------+ 
| 214.30    | 
+-----------+ 
1 row in set 

以下语句使用@msrp变量来查询最昂贵产品的信息。

SELECT  
    productCode, productName, productLine, msrp 
FROM 
    products 
WHERE 
    msrp = @msrp; 

用户定义的变量只能保存一个值。 如果SELECT语句返回多个值，则变量将获取结果中最后一行的值。

SELECT  
    @buyPrice:=buyprice 
FROM 
    products 
WHERE 
    buyprice > 95 
ORDER BY buyprice; 
 

执行上面语句，得到以下结果 -

+---------------------+ 
| @buyPrice:=buyprice | 
+---------------------+ 
| 95.34               | 
| 95.59               | 
| 98.30               | 
| 98.58               | 
| 101.51              | 
| 103.42              | 
+---------------------+ 
6 rows in set 

接下来，查询上面变量(@buyprice)的值，结果如下所示 -

mysql> SELECT @buyprice; 
+-----------+ 
| @buyprice | 
+-----------+ 
| 103.42    | 
+-----------+ 
1 row in set 

存储过程的变量和范围

要在存储过程中声明一个变量，可以使用DECLARE语句(相当于java的局部变量)，如下所示：

DECLARE variable_name datatype(size) DEFAULT default_value; 

下面来更详细地解释上面的语句：

• 首先，在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
• 其次，指定变量的数据类型及其大小。变量可以是任何MySQL数据类型，如INT，VARCHAR，DATETIME等。
• 当声明一个变量时，它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。
• 存储过程的declare声明变量必须放在最前面,不能在代码里进行穿插,否则报错

例如，可以声明一个名为total_sale的变量，数据类型为INT，默认值为0，如下所示：

DECLARE total_sale INT DEFAULT 0;        

MySQL允许您使用单个DECLARE语句声明共享相同数据类型的两个或多个变量，如下所示：

DECLARE x, y INT DEFAULT 0;   

我们声明了两个整数变量x和y，并将其默认值设置为0。

分配变量值

当声明了一个变量后，就可以开始使用它了。要为变量分配一个值，可以使用SET语句，例如：

DECLARE total_count INT DEFAULT 0; 
SET total_count = 10; 

上面语句中，分配total_count变量的值为10。

除了SET语句之外，还可以使用SELECT INTO或者 SELECT COUNT(*):name 语句将查询的结果分配给一个变量。 (只能是一个值得情况,而不是列表)

请参阅以下示例：

DECLARE total_products INT DEFAULT 0 
SELECT COUNT(*) INTO total_products  FROM products 

在上面的例子中：

• 首先，声明一个名为total_products的变量，并将其值初始化为0。
• 然后，使用SELECT INTO语句来分配值给total_products变量，获取products表中产品数量。

查询局部变量

declare count int(4);  -- 设置一个局部变量 
 
select count;   -- 查询局部变量 

变量范围(作用域)

一个变量有自己的范围(作用域)，它用来定义它的生命周期。 如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。

如果您在BEGIN END块内声明一个变量，那么如果达到END，它将超出范围。 可以在不同的作用域中声明具有相同名称的两个或多个变量，因为变量仅在自己的作用域中有效。 但是，在不同范围内声明具有相同名称的变量不是很好的编程习惯。

以@符号开头的变量是会话变量。直到会话结束前它可用和可访问。

几种变量的对比使用表

操作类型	全局变量	会话变量	用户变量	局部变量
文档常用名	global variables	session variables	user-defined variables	local variables
出现的位置	命令行、函数、存储过程	命令行、函数、存储过程	命令行、函数、存储过程	函数、存储过程
定义的方式	只能查看修改，不能定义	只能查看修改，不能定义	直接使用，@var形式	declare count int(4);
有效生命周期	服务器重启时恢复默认值	断开连接时，变量消失	断开连接时，变量消失	只能在函数或存储过程的作用域，在其他地方变量无效
查看所有变量	show global variables;	show session variables;	-	-
查看部分变量	show global variables like 'sql%';	show session variables like 'sql%';	-	-
查看指定变量	select @@global.sql_mode、 select @@max_connections;	select @@session.sql_mode;、 select @@local.sql_mode;、 select @@sql_mode;	select @var;	select count;
设置指定变量	set global sql_mode='';、 set @@global.sql_mode='';	set session sql_mode = '';、 set local sql_mode = '';、 set @@session.sql_mode = '';、 set @@local.sql_mode = '';、 set @@sql_mode = '';、 set sql_mode = '';	set @var=1;、 set @var:=101;、 select 100 into @var;	set count=1;、 set count:=101;、 select 100 into coun

相信看了这个对比的表格，之前的很多疑惑就应该清楚了，

总结

1. MySQL 中的变量通常分为：全局变量、 会话变量、 用户变量、 局部变量
2. 其实还有一个存储过程和函数的参数，这种类型和局部变量基本一致，当成局部变量来使用就行了
3. 在表格中有一个容易疑惑的点就是无论是全局变量还是会话变量都有select@@变量名的形式。
4. select@@变量名这种形式默认取的是会话变量，如果查询的会话变量不存在就会获取全局变量，比如@@max_connections
5. 但是SET操作的时候，set @@变量名=xxx 总是操作的会话变量，如果会话变量不存在就会报错

存储过程介绍

我们前面所学习的MySQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，并非所有操作都那么简单，经常会有一个完整的操作需要多条 SQL 语句处理多个表才能完成。例如，为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表，此时就需要使用多条 SQL 语句来针对几个数据表完成这个处理要求。存储过程可以有效地完成这个数据库操作。

存储过程是数据库存储的一个重要的功能，但是 MySQL 在 5.0 以前并不支持存储过程，这使得 MySQL 在应用上大打折扣。好在 MySQL 5.0 终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL 存储过程名字”即可自动完成。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。而存储过程则采用另一种方式来执行 SQL 语句。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

存储过程通常有如下优点：

1) 封装性

存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

2) 可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

3) 可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

4) 高性能

存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。

5) 提高数据库的安全性和数据的完整性

使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

注意: 存储过程会和表一起导出的不用担心移植问题但是不同关系型数据库之间存储过程语法可能不会兼容这里需要你自己去测试

如果你不想导入存储过程的话那么这里以Navicat Premium 15为例

1. 软件内头部工具->数据传输(根据情况选择)这里我选择文件
2. 点击... 然后进入到选择文件,右键你自己创建一个后缀为.sql的文件就行 然后下一步
3. 存储过程都在函数-自定义里,你别选择就行,其他的自己看着办 然后下一步-> 开始

查询存储过程/存储函数

我们想知道数据库下面有那些表，我们一般采用 showtables; 进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是,是另一钟方式。

我们可以用以下语句进行查询：

select name from mysql.proc where db='数据库名';      --  常用 

或者

select routine_name from information_schema.routines where routine_schema='数据库名'; 

或者

show procedure status where db='数据库名'; 

如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用 describe 表名 进行查看呢？

**答案是：**我们可以查看存储过程的详细，但是需要用另一种方法：

SHOW CREATE PROCEDURE 数据库名.存储过程名称; 

找到Create Procedure字段这里面就是你创建存储过程使用的语句

创建存储过程

MySQL 存储过程是一些 SQL 语句的集合，比如有的时候我们可能需要一大串的 SQL 语句，或者说在编写 SQL 语句的过程中还需要设置一些变量的值，这个时候我们就完全有必要编写一个存储过程。下面我们来介绍一下如何创建一个存储过程。

基本语法

可以使用 CREATE PROCEDURE 语句创建存储过程。

语法格式如下：

CREATE PROCEDURE <过程名> ( [ IN | OUT | INOUT ] <参数名> <类型> .... ) <过程体> 

语法说明如下：

1) 过程名

存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2) 过程参数

存储过程的参数列表。其中， <参数名>为参数名， <类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。

MySQL 存储过程支持三种类型的参数，即: 输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与存储过程内部的,数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。

3) 过程体

存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN - END 标志。

在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。为解决这个问题，通常可使用 DELIMITER 命令将结束命令修改为其他字符。

语法格式如下：

DELIMITER $$

语法说明如下：

• $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
• 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下SQL语句。

mysql > DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程，若要查看某个存储过程的具体信息，则可以使用 SHOW CREATE PROCEDURE <存储过程名>。

创建不带参数的存储过程

【实例 1】创建名称为 ShowStuScore 的存储过程，存储过程的作用是从学生成绩信息表中查询学生的成绩信息，输入的 SQL 语句和执行过程如下所示。

DELIMITER && -- 修改结束符号 
DROP PROCEDURE IF EXISTS GetScoreByStu&& -- 如果当前存储过程存在那么就删除 
CREATE PROCEDURE GetScoreByStu(IN t_name VARCHAR(30)) 
BEGIN -- 方法体开始 
 
SELECT id,name FROM tb_students_info  WHERE name=t_name; 
 
END &&  -- 方法体结束 
DELIMITER ; -- 修改为默认的结束符合 

创建存储过程 ShowStuScore 后，通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。

CALL ShowStuScore(); -- 调用存储过程 

+--------------+---------------+ 
| student_name | student_score | 
+--------------+---------------+ 
| Dany         |            90 | 
| Green        |            99 | 
| Henry        |            95 | 
| Jane         |            98 | 
| Jim          |            88 | 
| John         |            94 | 
| Lily         |           100 | 
| Susan        |            96 | 
| Thomas       |            93 | 
| Tom          |            89 | 
+--------------+---------------+ 
10 rows in set (0.00 sec) 
Query OK, 0 rows affected (0.02 sec) 

创建带参数的存储过程

一定要注意: 方法的参数名称不能和查询的表字段名称一样 包括查询条件名称…

【实例 2】创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息，输入的 SQL 语句和执行过程如下所示。

DELIMITER && -- 修改结束符号 
DROP PROCEDURE IF EXISTS ShowStuScore&& -- 如果当前存储过程存在那么就删除 
CREATE PROCEDURE GetScoreByStu(IN t_name VARCHAR(30)) 
BEGIN -- 方法体开始 
SELECT id,name FROM tb_students_info 
WHERE name=t_name; 
END &&  -- 方法体结束 
DELIMITER ; -- 修改为默认的结束符合 
 

创建存储过程 GetScoreByStu 后，通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。

mysql> CALL GetScoreByStu('Green'); 
+----+-------+ 
| id | name  | 
+----+-------+ 
|  2 | Green | 
+----+-------+ 
1 row in set (0.01 sec) 
 
Query OK, 0 rows affected (0.00 sec) 

in、out 、inout 使用

in类型：内部运算变化不影响外部；

out类型：传入的必须是个变量,内部运算变化影响外部变化, 并且传参到储存过程时默认初始化参数为null (外部变量也会变为null)；

inout类型: 与out类型相比不同是默认初始化参数不为null，传的是什么就是什么,

in、out区别：
in:表示输入一个值.

out:你往外输出一个值，你输出的那个值我就拿一个变量来接收你给我输出的那个值

out和inout区别

out 传入的变量默认为null 不管是否存入的变量是否有值都为null

inout 传入的变量是什么值那么就是什么值 而不是null

注意: 传入的变量名没必要和参数名称一致 当然一样也没事

in演示

类似java的方法参数一样 从外面传进去参数 在上面案例中我们已经演示过了

out演示

这个和java的方法参数不太同 和c#的引用传参一样的效果

传进去的变量,在内部如果改变,变量值的话,那么这个变量在全局的值都会被改变

DELIMITER && -- 修改结束符号 
DROP PROCEDURE IF EXISTS TestOut&& -- 如果当前存储过程存在那么就删除 
CREATE PROCEDURE TestOut(out outResult  VARCHAR(30)) 
BEGIN -- 方法体开始 
 
		SELECT  outResult; -- Null 
		SET  outResult='A'; -- 设置传进来的变量值为A 
		SELECT  outResult; -- A 
 
END &&  -- 方法体结束 
DELIMITER ; -- 修改为默认的结束符合 

SET @outResult = 'W'; 
 
CALL testOut(@outResult) -- 调用之后  返回两个值结果     结果1为null    结果2为A 
 
SELECT @outResult;  --  A 

INOUT使用

inout就是out的升级版 原本的out 不管你传进去的值,原来是什么都为null 而inout你传进去是什么就是什么

DELIMITER && -- 修改结束符号 
DROP PROCEDURE IF EXISTS TestOut&& 
CREATE PROCEDURE TestOut(INOUT outResult  VARCHAR(30)) 
BEGIN -- 方法体开始 
 
		SELECT  outResult; --  传进来是什么就是什么 
		SET  outResult='A'; -- 设置传进来的变量值为A 
		SELECT  outResult; -- A 
 
END &&  -- 方法体结束 
DELIMITER ; -- 修改为默认的结束符合 

SET @outResult_test = 'W'; 
CALL testOut(@outResult_test) -- 调用之后  返回两个值结果     结果1为W    结果2为A 
SELECT @outResult_test;  --  A 

in 和inout组合使用

DELIMITER && -- 修改结束符号 
DROP PROCEDURE IF EXISTS TestNum&& 
CREATE PROCEDURE TestNum(IN num int ,INOUT id int) 
BEGIN -- 方法体开始 
		SET  id=id+num;  -- 111 
		SELECT  id;     
END &&  -- 方法体结束 
DELIMITER ; -- 修改为默认的结束符合 

SET @id_test  = 101; 
CALL TestNum(11,@id_test) ; 
SELECT @id_test; -- 112 

注意:在上面我们基本都是演示单个参数,在实际中可能会出现多个参数的形式 (in out inout 都可以随意使用)

下面就演示常用的in 随意组合方式就不演示了自己根据下面sql语句进行推断,

列:

CREATE PROCEDURE TestNum(IN num int ,IN num1 int,IN num2 int)........... 

调用也一样

CALL TestNum(1,2,3); 

修改存储过程

修改存储过程可以通过删除原存储过程，再以相同的命名创建新的存储过程。

删除存储过程

当MySQL 数据库中存在废弃的存储过程是，我们需要将它从数据库中删除。

基本语法

存储过程被创建后，保存在数据库服务器上，直至被删除。可以使用 DROP PROCEDURE 语句删除数据库中已创建的存储过程。

语法格式如下：

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

语法说明如下：

1) 过程名

指定要删除的存储过程的名称。

2) IF EXISTS

指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。

注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

依赖关系就是存储过程中使用了其他的存储过程

删除存储过程

删除存储过程 GetScoreByStu，查看存储过程的运行结果如下所示。

mysql> DROP PROCEDURE IF EXISTS GetScoreByStu; 
Query OK, 0 rows affected (0.00 sec) 
mysql> CALL GetScoreByStu('Green'); 
ERROR 1305 (42000): PROCEDURE test_db.GetScoreByStu does not exist 

if语句

MySQL IF语句允许您根据表达式的某个条件或值结果来执行一组SQL语句。 要在MySQL中形成一个表达式，可以结合文字，变量，运算符，甚至函数来组合。表达式可以返回TRUE,FALSE或NULL，这三个值之一。

请注意，有一个IF函数与本教程中指定的IF语句是不同的。

还有就是不能在SELECT 中… 嵌套这种IF 这种IF只适用于存储过的 ,就算SELECT在存储过程里也不行

在SELECT 中只能使用mysql的函数或者自定义的函数

如果您不想处理IF内的逻辑，同时又要防止 MySQL 引发错误，则可以在身体中放置一个 BEGIN END 。

下面说明了IF语句的语法：

IF expression THEN  
   statements; 
END IF; 

如果表达式(expression)计算结果为TRUE，那么将执行statements语句，否则控制流将传递到END IF之后的下一个语句。

IF ELSE语句:

如果表达式计算结果为FALSE时执行语句，请使用IF ELSE语句，如下所示：

IF expression THEN 
   statements; 
ELSE 
   else-statements; 
END IF; 

IF ELSEIF ELSE语句

如果要基于多个表达式有条件地执行语句，则使用IF ELSEIF ELSE语句如下：

IF expression THEN 
   statements; 
ELSEIF elseif-expression THEN 
   elseif-statements; 
... 
ELSE 
   else-statements; 
END IF; 

如果表达式(expression)求值为TRUE，则IF分支中的语句(statements)将执行；如果表达式求值为FALSE，则如果elseif_expression的计算结果为TRUE，MySQL将执行elseif-expression，否则执行ELSE分支中的else-statements语句。

注意: 在java中必须要有( )的但是在MySql中if 的( ) 可有可无,可以省略不写,也可以写

MySQL IF语句示例

以下示例说明如何使用IF ESLEIF ELSE语句，GetCustomerLevel()该存储过程,接受客户编号和客户级别的两个参数。

首先，它从customers表中获得信用额度

然后，根据信用额度，它决定客户级别：PLATINUM , GOLD 和 SILVER 。

参数p_customerlevel存储客户的级别，并由调用程序使用。

DELIMITER  && 
DROP PROCEDURE IF EXISTS GetCustomerLevel; 
CREATE PROCEDURE GetCustomerLevel( 
   in   p_customerNumber  varchar(22), 
   out  p_customerLevel  varchar(10) 
) 
BEGIN 
   DECLARE creditlim double; 
    
   SELECT customerMoney INTO  creditlim 
   FROM   customers 
   WHERE  customerNumber = p_customerNumber; 
    
     IF creditlim >5000  THEN 
       SET p_customerLevel ='PLATINUM'; 
     ELSEIF (creditlim<=50000 AND creditlim >=1000)THEN 
       SET p_customerLevel='GOLD'; 
     ELSEIF creditlim <10000 THEN 
       SET p_customerLevel='SILVER'; 
	 END IF; 
 
END && 
DELIMITER; 

调用:

SET @p_c_customerLevel=''; 
 
CALL GetCustomerLevel('0002',@p_c_customerLevel); 
 
SELECT @p_c_customerLevel; 

customers表结构

CREATE TABLE `customers` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `customerNumber` varchar(50) DEFAULT NULL COMMENT '客户编号', 
  `customerName` varchar(20) DEFAULT NULL COMMENT '客户名称', 
  `customerMoney` decimal(11,0) DEFAULT NULL COMMENT '客户额度', 
  `country` varchar(6) DEFAULT NULL COMMENT '币种', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 

插入数据

INSERT INTO `customers` (`id`, `customerNumber`, `customerName`, `customerMoney`, `country`) VALUES (1, '0001', '王五', 50000, 'USA'); 
INSERT INTO `customers` (`id`, `customerNumber`, `customerName`, `customerMoney`, `country`) VALUES (2, '0002', '张三', 50001, 'CNY'); 
INSERT INTO `customers` (`id`, `customerNumber`, `customerName`, `customerMoney`, `country`) VALUES (3, '0003', '李四', 1001, 'Canada'); 
INSERT INTO `customers` (`id`, `customerNumber`, `customerName`, `customerMoney`, `country`) VALUES (4, '0004', '牛6', 92222, 'USA'); 

CASE语句

类似java的switch语句 MySQL提供了一个替代的条件语句CASE。 MySQL CASE语句使代码更加可读和高效。

CASE语句有两种形式：简单CASE语句和搜索CASE语句。

简单CASE语句

我们来看一下简单CASE语句的语法：

CASE  case_expression 
   WHEN when_expression_1 THEN commands 
   WHEN when_expression_2 THEN commands 
   ... 
   ELSE commands 
END CASE; 

您可以使用简单CASE语句来检查表达式的值与一组唯一值的匹配。

case_expression可以是任何有效的表达式。我们将case_expression的值与每个WHEN子句中的when_expression进行比较，例如when_expression_1，when_expression_2等。如果case_expression和when_expression_n的值相等，则执行相应的WHEN分支中的命令(commands)。

如果WHEN子句中的when_expression与case_expression的值匹配，则ELSE子句中的命令将被执行。ELSE子句是可选的。 如果省略ELSE子句，并且找不到匹配项，MySQL将引发错误。 如果您不想处理ELSE子句中的逻辑，同时又要防止MySQL引发错误，则可以在ELSE子句中放置一个空的BEGIN END块。

以下示例说明如何使用简单的CASE语句：

DELIMITER $$ 
DROP PROCEDURE IF EXISTS GetCustomerShipping$$ 
CREATE PROCEDURE GetCustomerShipping( 
 	in  p_customerNumber int(11),  
 	out p_shiping        varchar(50)) 
BEGIN 
 
    DECLARE customerCountry varchar(50); 
 
    SELECT country INTO customerCountry 
    FROM customers 
    WHERE customerNumber = p_customerNumber; 
 
    CASE customerCountry 
         WHEN  'USA' THEN 
            SET p_shiping = '2-day Shipping'; 
         WHEN 'Canada' THEN 
            SET p_shiping = '3-day Shipping'; 
         ELSE  -- 如果都不满足的话执行 
            SET p_shiping = '5-day Shipping'; 
    END CASE; 
  
END$$ 
DELIMITER ; 

调用:

SET @val='' 
CALL  GetCustomerShipping('0001',@val) 
SELECT @val 

可搜索CASE语句

简单CASE语句仅允许您将表达式的值与一组不同的值进行匹配。 为了执行更复杂的匹配，如范围，您可以使用可搜索CASE语句。 可搜索CASE语句等同于IF语句，但是它的构造更加可读。

以下说明可搜索CASE语句的语法：

CASE 
    WHEN condition_1 THEN commands 
    WHEN condition_2 THEN commands 
    ... 
    ELSE commands 
END CASE; 
SQL 

MySQL评估求值WHEN子句中的每个条件，直到找到一个值为TRUE的条件，然后执行THEN子句中的相应命令(commands)。

如果没有一个条件为TRUE，则执行ELSE子句中的命令(commands)。如果不指定ELSE子句，并且没有一个条件为TRUE，MySQL将发出错误消息。

MySQL不允许在THEN或ELSE子句中使用空的命令。 如果您不想处理ELSE子句中的逻辑，同时又要防止MySQL引发错误，则可以在ELSE子句中放置一个空的BEGIN END块。

以下示例演示如何使用可搜索CASE语句来根据客户的信用额度来查找客户级：SILVER，GOLD或PLATINUM。

DELIMITER $$ 
DROP PROCEDURE IF EXISTS GetCustomerLevel$$ 
CREATE PROCEDURE GetCustomerLevel( 
 in  p_customerNumber int(11),  
 out p_customerLevel  varchar(10)) 
BEGIN 
 
 DECLARE creditlim double; 
 
 SELECT customerMoney INTO creditlim 
 FROM customers 
 WHERE customerNumber = p_customerNumber; 
 
CASE   
     WHEN creditlim > 50000 THEN  
        SET p_customerLevel = 'PLATINUM'; 
     WHEN (creditlim <= 50000 AND creditlim >= 10000) THEN 
        SET p_customerLevel = 'GOLD'; 
     WHEN creditlim < 10000 AND creditlim>=1000  THEN 
        SET p_customerLevel = 'SILVER'; 
     ELSE 
     	SET p_customerLevel = 'poor'; 
END CASE; 
 
END$$ 
DELIMITER ; 

调用:

SET @val='' 
CALL  GetCustomerLevel("0001",@val) 
SELECT  @val 

在上面查询语句逻辑中，如果信用额度是：

• 大于50K，则客户是PLATINUM客户。
• 小于等于50K，大于等于10K，则客户是GOLD客户。
• 小于10K，大于等于1000,则客户就是SILVER客户。
• 都不满足, 那么客户就是poor客户

循环

MySQL提供循环语句，允许您根据条件重复执行一个SQL代码块。 MySQL中有三个循环语句：WHILE，REPEAT和LOOP。

我们将在以下部分中更详细地检查每个循环语句。

WHILE循环

WHILE语句的语法如下：

WHILE expression DO 
   statements 
END WHILE 

WHILE循环在每次迭代开始时检查表达式。 如果expression为TRUE，MySQL将执行DO和END之间的语句，直到expression为FALSE ,就结束循环 因此WHILE循环语句也称为前循环。

以下是在存储过程中使用WHILE循环语句的示例：

DELIMITER $$ 
DROP PROCEDURE IF EXISTS test_mysql_while_loop$$ 
CREATE PROCEDURE test_mysql_while_loop() 
 BEGIN 
     DECLARE x  INT; 
     DECLARE str  VARCHAR(255); 
 
     SET x = 1; 
     SET str =  ''; 
 
     WHILE x  <= 5 DO 
     SET  str = CONCAT(str,x,','); 
     SET  x = x + 1;  
     END WHILE; 
 
     SELECT str; 
 END$$ 
DELIMITER ; 

在上面的test_mysql_while_loop存储过程中：

• 首先，重复构建str字符串，直到x变量的值大于5。
• 然后，使用SELECT语句显示最终的字符串。

要注意，如果不初始化x变量的值，那么它默认值为NULL。 因此，WHILE循环语句中的条件始终为TRUE，并且您将有一个不确定的循环，这是不可预料的。

下面来测试test_mysql_while_loopstored调用存储过程：

CALL test_mysql_while_loop(); 

执行上面查询语句，得到以下结果 -

mysql> CALL test_mysql_while_loop(); 
+------------+ 
| str        | 
+------------+ 
| 1,2,3,4,5, | 
+------------+ 
1 row in set 
 
Query OK, 0 rows affected 

REPEAT循环

REPEAT循环语句的语法如下：

REPEAT 
 statements; 
UNTIL expression 
END REPEAT 

首先，MySQL执行语句，然后评估求值表达式(expression)。如果表达式(expression)的计算结果为FALSE，则MySQL将重复执行该语句，直到该表达式计算结果为TRUE。 这个和正常的语句不一样 必须是true才会结束循环

因为REPEAT循环语句在执行语句后检查表达式(expression)，因此REPEAT循环语句也称为后循环。

下面的流程图说明了REPEAT循环语句的执行过程：

我们可以使用REPEAT循环语句重写test_mysql_while_loop存储过程，使用REPEAT循环语句：

DELIMITER $$ 
 DROP PROCEDURE IF EXISTS mysql_test_repeat_loop$$ -- 删除原来的 
 CREATE PROCEDURE mysql_test_repeat_loop() 
 BEGIN 
     DECLARE x INT; 
     DECLARE str VARCHAR(255); 
 
     SET x = 1; 
            SET str =  ''; 
 
     REPEAT 
         SET  str = CONCAT(str,x,','); 
         SET  x = x + 1;  
     UNTIL x  > 5 
     END REPEAT; 
 
     SELECT str; 
 END$$ 
DELIMITER ; 

要注意的是UNTIL表达式中没有分号(;)。

执行上面查询语句，得到以下结果 -

mysql> CALL mysql_test_repeat_loop(); 
+------------+ 
| str        | 
+------------+ 
| 1,2,3,4,5, | 
+------------+ 
1 row in set 
 
Query OK, 0 rows affected 

LOOP，LEAVE和ITERATE语句

有两个语句允许您用于控制循环：

• LEAVE语句用于立即退出循环，而无需等待检查条件。LEAVE语句的工作原理就类似PHP，C/C++，Java]等其他语言的break语句一样。
• ITERATE语句允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于PHP，C/C++，Java等中的continue语句。

MySQL还有一个LOOP语句，它可以反复执行一个代码块也就是无限循环，需要配合LEAVE进行跳出死循环。

以下是使用LOOP循环语句的示例。

DELIMITER $$ 
DROP PROCEDURE IF EXISTS test_mysql_loop$$ -- 删除原来的 
CREATE PROCEDURE test_mysql_loop() 
BEGIN 
     DECLARE x  INT; 
     DECLARE str  VARCHAR(30); 
 
     SET x = 1; 
     SET str = ''; 
 
				 loop_label:  LOOP 
						 IF  x > 10 THEN  --  满足条件跳出循环 
								LEAVE  loop_label; 
						 END  IF; 
 
						 SET  x = x + 1;   
						 IF (x mod 2) THEN   -- x除于2余数为1(因为1是true,0是false) 返回循环 
								 ITERATE  loop_label; 
						 ELSE 
								SET  str = CONCAT(str,x,',');  -- 2,4,6,8,10, 
						 END IF; 
						  
				 END LOOP;     
		  
    	SELECT str; 
END $$ 
DELIMITER ; 

• 以上存储过程仅构造具有偶数字符串的字符串，例如2,4,6等。
• 在LOOP语句之前放置一个loop_label循环标签。
• 如果x的值大于10，则由于LEAVE语句，循环被终止。
• 如果x的值是一个奇数，ITERATE语句忽略它下面的所有内容，并开始一个新的迭代。
• 如果x的值是偶数，则ELSE语句中的块将使用偶数构建字符串。

执行上面查询语句，得到以下结果 -

mysql> CALL test_mysql_loop(); 
+-------------+ 
| str         | 
+-------------+ 
| 2,4,6,8,10, | 
+-------------+ 
1 row in set 
 
Query OK, 0 rows affected 

错误处理

SIGNAL语句

使用SIGNAL语句在存储的程序(例如存储过程，存储函数，触发器或事件中向调用者返回错误或警告条件。 SIGNAL语句提供了对返回值(如值和消息SQLSTATE)的信息的控制。

以下说明SIGNAL语句的语法：

SIGNAL SQLSTATE '45000'; 
SET MESSAGE_TEXT = value; 

请注意，SIGNAL语句必须始终指定使用SQLSTATE值定义的SQLSTATE值或命名条件。 使用45000就行

要向调用者提供信息，请使用SET子句。

DELIMITER $$ 
DROP PROCEDURE IF EXISTS AddItem$$ -- 删除原来的 
CREATE PROCEDURE AddItem() 
BEGIN 
 DECLARE c INT DEFAULT 0;   
 
 SELECT COUNT(*) INTO C FROM t_student ; 
 
 IF (c!=1) THEN  
	 SIGNAL SQLSTATE '45000'   
	 SET MESSAGE_TEXT = '报错了O(∩_∩)O哈哈~';   -- 固定写法 
 END IF; 
 
END $$ 
DELIMITER ; 

调用存储过程AddOrderItem() 报错

CALL AddItem() 
> 1644 - 报错了O(∩_∩)O哈哈~1 
> 时间: 0s 
 

RESIGNAL语句

除了SIGNAL语句，MySQL还提供了用于引发警告或错误条件的RESIGNAL语句。

RESIGNAL语句在功能和语法方面与SIGNAL语句相似，只是：

• 必须在错误或警告处理程序中使用RESIGNAL语句，
• 否则您将收到一条错误消息，指出“RESIGNAL when handler is not active”。
• 请注意，您可以在存储过程中的任何位置使用SIGNAL语句。
• 可以省略RESIGNAL语句的所有属性，甚至可以省略SQLSTATE值。

如果单独使用RESIGNAL语句，则所有属性与传递给条件处理程序的属性相同。

在将发送给调用者之前更改错误消息的存储过程：

语法:

DECLARE 变量 CONDITION FOR SQLSTATE '45000'; --固定写法 
DECLARE CONTINUE HANDLER FOR   引用上面的变量   RESIGNAL SET MESSAGE_TEXT = '错误消息提示' 

然后我们在需要调用的地方直接 SIGNAL 错误变量; 就行了

列:

DELIMITER $$ 
DROP PROCEDURE IF EXISTS divide$$ -- 删除原来的 
CREATE PROCEDURE divide(IN numerator INT, IN denominator INT, OUT result DOUBLE) 
BEGIN 
	 DECLARE division_by_zero CONDITION FOR SQLSTATE '45000'; 
	 DECLARE CONTINUE HANDLER FOR division_by_zero  RESIGNAL SET MESSAGE_TEXT = ' 被除数不能为0'; 
  
	 IF denominator = 0 THEN 
			SIGNAL division_by_zero; 
	 ELSE 
			SET result := numerator / denominator; 
	 END IF; 
  
END $$ 
DELIMITER ; 

set @t_result=0.0; 
CALL divide(10,0,@t_result); 

CALL divide(10,0,@t_result)

1644 - 被除数不能为0
时间: 0s

游标(遍历结果集)

显示如何在存储过程中使用MySQL游标循环遍历结果集并一次处理每一行。

mysql> SELECT * FROM  tb_students_info ; 
	 
+----+--------+---------+-----+-----+--------+---------------------+ 
| id | name   | dept_id | age | sex | height | login_date          | 
+----+--------+---------+-----+-----+--------+---------------------+ 
|  1 | Dany   |       1 |  21 | F   |    160 | 2015-09-10 00:00:00 | 
|  2 | Green  |       3 |  23 | F   |    158 | 2016-10-22 00:00:00 | 
|  3 | Henry  |       2 |  23 | M   |    185 | 2015-05-31 00:00:00 | 
|  4 | jane   |       1 |  22 | F   |    162 | 2016-12-20 00:00:00 | 
|  5 | jim    |       1 |  14 | M   |    175 | 2016-01-15 00:00:00 | 
|  6 | jon    |       2 |  21 | M   |    172 | 2015-10-01 00:00:00 | 
|  7 | lily   |       6 |  22 | F   |    165 | 2016-02-26 00:00:00 | 
|  8 | susan  |       4 |  23 | F   |    170 | 2015-10-01 00:00:00 | 
|  9 | Thomas |       3 |  22 | M   |    178 | 2016-06-07 00:00:00 | 
| 10 | tom    |       4 |  23 | M   |    165 | 2016-08-05 00:00:00 | 
+----+--------+---------+-----+-----+--------+---------------------+ 
10 rows in set (0.05 sec) 

使用游标的方式一行一行的遍历

DELIMITER $$ 
DROP PROCEDURE IF EXISTS pa$$ -- 删除原来的 
CREATE PROCEDURE pa() BEGIN 
-- 定义的三个变量用于将fetch取值出来的值放到对应的变量中 
declare row_cat_id int; 
declare row_cat_name VARCHAR(10); 
declare row_cat_age CHAR(5); 
 
	-- 定义游标   
	declare getcategory cursor for select `id`,`name`,`age` from `tb_students_info` ; 
	 
	-- 打开游标 
	open getcategory; 
	 
	-- 取值 第一行 
	fetch getcategory into row_cat_id,row_cat_name,row_cat_age; 
	--  查询第一行取出来的值 
	select row_cat_id,row_cat_name,row_cat_age; 
	 
		-- 取值第二行 
	fetch getcategory into row_cat_id,row_cat_name,row_cat_age; 
	--  查询第一行取出来的值 
	select row_cat_id,row_cat_name,row_cat_age; 
-- 	 
	-- 关闭游标 
	close getcategory; 
 
END $$ 
DELIMITER; 

mysql> call pa();  -- 调用存储过程 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          1 | Dany         | 21          | 
+------------+--------------+-------------+ 
1 row in set (0.04 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          2 | Green        | 23          | 
+------------+--------------+-------------+ 
1 row in set (0.09 sec) 
 
Query OK, 0 rows affected (0.00 sec) 

注意: 上面的游标取值会出现问题,当我们取值到第10行(数据的最后一行)后在取值的话就会报【02000】游标溢出这个错误

所以啊，我们如何循环游标来取出所有行？

使用循环的方式变量所有行

思路：

1. 计算数据内容的总行数 ( SELECT COUNT(*) FROM table)
2. 利用循环的方式进行 (while …)
3. 当循环的次数=总行数那么结束循环

我们修改上面的存储过程

delimiter $$ 
DROP PROCEDURE IF EXISTS pa$$ -- 删除原来的 
CREATE PROCEDURE pa(in num int) BEGIN 
-- 定义的三个变量用于将fetch取值出来的值放到对应的变量中 
declare row_cat_id int; 
declare row_cat_name VARCHAR(10); 
declare row_cat_age CHAR(5); 
declare row_cat_count int default 0; -- 记录总行数 
declare i int default 0; -- 记录循环次数 
 
	-- 定义游标  
	declare getcategory cursor for select id,name,age from t_student  WHERE id>=1 AND id<=num; 
	 
	-- 获取总行数  查询后赋给 row_cat_count变量 
	 select  COUNT(*) into row_cat_count from t_student  WHERE  id>=1 AND id<=num; 
 
	 
	-- 打开游标 
	open getcategory; 
	 
		--  循环取值 
		WHILE i<row_cat_count do 
				-- 取每一行的值 
			fetch getcategory into row_cat_id,row_cat_name,row_cat_age; 
			--  查询取出来的值 
			select row_cat_id,row_cat_name,row_cat_age; 
			SET i=i+1; 
		end WHILE ; 
	 
	-- 关闭游标 
	close getcategory; 
 
END $$ 
delimiter ; 

调用存储过程效果

mysql> call   pa(5); -- 调用存储过程 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          1 | Dany         | 21          | 
+------------+--------------+-------------+ 
1 row in set (0.05 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          2 | Green        | 23          | 
+------------+--------------+-------------+ 
1 row in set (0.10 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          3 | Henry        | 23          | 
+------------+--------------+-------------+ 
1 row in set (0.15 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          4 | jane         | 22          | 
+------------+--------------+-------------+ 
1 row in set (0.19 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          5 | jim          | 14          | 
+------------+--------------+-------------+ 
1 row in set (0.24 sec) 
 

在上面案例中我们还需要计算出所有的行数才能遍历全部行的数据 ,这样就增加了存储过程的执行时间了.

但是在Mysql中还有更好的办法使用NOT FOUND 事件

比如: declare continue handler for NOT FOUND set ergodic:=0;

意思是说，我要声明一个句柄事件，你往后取，一旦发生NOT FOUND 事件就会出发set ergodic:=0这个语句

这里注意:

continue 是触发之后继续往下执行,只是当前代码块跳过了,在自定义函数中必须使用continue而不是exit (最常用)

exit 触发后，后面的语句不再执行 相当于java的return;结束方法了

根据上面提供的语法我们来继续改造之前写的存储过程

DELIMITER $$ 
DROP PROCEDURE IF EXISTS pa$$ -- 删除原来的 
CREATE PROCEDURE pa() BEGIN 
-- 定义的三个变量用于将fetch取值出来的值放到对应的变量中 
declare row_cat_id int; 
declare row_cat_name VARCHAR(10); 
declare row_cat_age CHAR(5); 
declare ergodic int default 1; -- 定义报错后更改状态的变量 1=true,0=false 
 
	-- 定义游标     
	declare getcategory cursor for select id,name,age from tb_students_info ; 
	 
	--  定义发生错误后   更改变量状态  必须放置在定义游标之后 
	declare continue handler for NOT FOUND set ergodic:=0;  -- 0=false 
	 
	-- 打开游标 
	open getcategory; 
	 
	--  循环取值 
	WHILE ergodic do 
	    -- 取每一行的值 
		fetch getcategory into row_cat_id,row_cat_name,row_cat_age; 
		-- 防止使用continue 时候报错继续往下执行剩余的代码 
		IF ergodic THEN 
					--  查询取出来的值 
				select row_cat_id,row_cat_name,row_cat_age; 
		END IF; 
		 
	end WHILE ; 
	-- 关闭游标 
	close getcategory; 
	 
	SELECT "测试是否继续往下执行了"; -- exit 没有执行   continue执行了 
 
END $$ 
DELIMITER ; 

mysql> call   pa();  
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          1 | Dany         | 21          | 
+------------+--------------+-------------+ 
1 row in set (0.04 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          2 | Green        | 23          | 
+------------+--------------+-------------+ 
1 row in set (0.10 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          3 | Henry        | 23          | 
+------------+--------------+-------------+ 
1 row in set (0.15 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          4 | jane         | 22          | 
+------------+--------------+-------------+ 
1 row in set (0.19 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          5 | jim          | 14          | 
+------------+--------------+-------------+ 
1 row in set (0.24 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          6 | jon          | 21          | 
+------------+--------------+-------------+ 
1 row in set (0.28 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          7 | lily         | 22          | 
+------------+--------------+-------------+ 
1 row in set (0.33 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          8 | susan        | 23          | 
+------------+--------------+-------------+ 
1 row in set (0.37 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|          9 | Thomas       | 22          | 
+------------+--------------+-------------+ 
1 row in set (0.42 sec) 
 
+------------+--------------+-------------+ 
| row_cat_id | row_cat_name | row_cat_age | 
+------------+--------------+-------------+ 
|         10 | tom          | 23          | 
+------------+--------------+-------------+ 
1 row in set (0.46 sec) 
 
Query OK, 0 rows affected (0.00 sec) 

建议使用 NOT FOUND的方式 这样可以不用在查询一次数据库了,加快存储过程的速度.

自定义存储函数

存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

存储过程和函数存在以下几个区别：

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；而用户定义函数不能用于执行一组修改全局数据库状态的操作。

2）函数只能返回一个变量；而存储过程可以返回结果集。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

3）存储过程内可以调用存储函数。但存储函数不能调用存储过程

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

如果学过java你可以把存储函数理解为方法原理基本一样

存储函数语法:

以下说明了创建新存储函数的最简单语法：

CREATE FUNCTION func_name(params) RETURNS type 
[characteristics ...]  
routine_body 

CREATE FUNCTION为创建存储函数的关键字；

func_name为存储函数的名称(唯一性，没有所谓的重载方法概念)；

params为参数列表；

RETURNS type语句表示函数返回数据的类型，可以是mysql中的任意数据类型；

characteristics指定存储函数的特性(和存储过程一样)；

routine_body是返回值(必须的)。

入门函数:

DELIMITER $$ -- 更改结束符 
CREATE FUNCTION count_dept() RETURNS INT -- 返回值类型 
	COMMENT '统计数量'  -- 函数注释 
	BEGIN  -- 函数体 
	  DECLARE num int; -- 定义变量 
	   
		SELECT COUNT(*) INTO num  FROM tb_students_info;  -- 将查询结果赋值个num 
		 
		RETURN num;  -- 返回的只能是单个值不能是多个值或者结果集  
	END $$ 
DELIMITER ; -- 还原结束符 

SELECT count_dept();  -- 调用存储函数 

结果: 10

带参数的函数

set @name_a="哈"; 
 
DELIMITER $$ 
DROP FUNCTION IF EXISTS genPerson$$ -- 如果原来有此函数那么就删除 
CREATE FUNCTION genPerson(name1 varchar(20),name2 varchar(20)) RETURNS varchar(50) 
BEGIN 
  DECLARE str VARCHAR(50) DEFAULT ''; 
   
  SET str=CONCAT(name1,name2,@name_a); -- 拼接 
	 
  return str;  
END $$ 
DELIMITER ; 

SELECT genPerson("abc","456");  -- 调用存储函数 

结果: abc456哈

到这里有的小伙们就漂了,想要实现类似MAX() ,SUM()函数… 这样的聚合函数 但是我想告诉你一句话做梦

MYSQL中没有办法定义聚集函数。 如果非要修改那么只能通过修改MYSQL源代码自己加入所需要功能，不过花的时间太多了。需要研究MYSQL的C代码，然后编译。

嵌入到SELECT查询语句中

我们可以定义类似MAX的函数但是是针对指定的表

DELIMITER $$ 
DROP FUNCTION IF EXISTS Fun_max$$ -- 如果原来有此函数那么就删除 
CREATE FUNCTION Fun_max() RETURNS int 
BEGIN 
	declare ergodic int default 1; -- 定义报错后更改状态的变量 
 
	declare row_cat_age int; 
	 
	declare max int default 0;   
	 
		-- 定义游标 
	declare getcategory cursor for select age from tb_students_info; 
	 
	--  定义发生错误后   更改变量状态 
	declare continue handler for NOT FOUND set ergodic:=0; 
	 
			-- 打开游标 
	open getcategory; 
		 
		--  循环取值 
		WHILE ergodic do 
					-- 取每一行的值 
				fetch getcategory into row_cat_age; 
			 
				IF row_cat_age>max THEN 
					Set	max:=row_cat_age; 
				END IF; 
 
	  end WHILE ; 
		 
	 
		return max; 
		 
END $$ 
DELIMITER ; 

使用函数

mysql> SELECT Fun_max() AS MAX; 
+-----+ 
| MAX | 
+-----+ 
|  23 | 
+-----+ 
1 row in set (0.05 sec) 

函数还可以这样使用微改动每一列的数据

在使用SELECT xx FROM TABLE 的时候 每一行数据其实都调用一次自定义函数 利用这个机制我们实现数据转换

实例: 将sex字段是F的值转换为男 其余女

DELIMITER $$ 
DROP FUNCTION IF EXISTS genPerson$$ -- 如果原来有此函数那么就删除 
CREATE FUNCTION genPerson(sex varchar(2)) RETURNS varchar(5) 
BEGIN 
		 
	IF sex='0' THEN 
     return '男';  
  ELSEIF sex='1' THEN 
	   return '女'; 
	ELSE 
	   return '人妖'; 
  END IF;	 
END $$ 
DELIMITER ; 

调用函数

mysql>  SELECT name,age,genPerson(sex)as sex FROM t_user 
王五	21	男 
李四	22	女 
张三	22	人妖 
丽丽	31	女 
10 rows in set (0.06 sec) 

还可以将多列一起整合

DELIMITER $$ 
DROP FUNCTION IF EXISTS genPersonMore$$ -- 如果原来有此函数那么就删除 
CREATE FUNCTION genPersonMore(val1 VARCHAR(20),val2 VARCHAR(20)) RETURNS VARCHAR(20) 
BEGIN 
	   return CONCAT(val1,val2); 
   
END $$ 
DELIMITER ; 

调用函数:

 
mysql> SELECT name,age,genPerson(sex)as sex,genPersonMore(name,age) as var FROM t_user 
王五	21	男	王五21 
李四	22	女	李四22 
张三	22	人妖	张三22 
丽丽	31	女	丽丽31 
10 rows in set (0.05 sec) 

修改自定义函数

若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。

删除自定义函数

自定义函数被创建后，一直保存在数据库服务器上以供使用，直至被删除。删除自定义函数的方法与删除存储过程的方法基本一样，可以使用 DROP FUNCTION 语句来实现。

语法格式如下：

DROP FUNCTION [ IF EXISTS ] <自定义函数名> 

注意事项

显示行不够的问题

navicat10及以后只支持10个结果集显示 ,navicat15及以后只支持20个结果集显示

超过以上结果集显示那么剩于的结果集就会被隐藏不显示

变量无法被sql识别

常规的增删改查都能识别存储过程的变量,但是比如ALTER这种类型的语句就无法识别存储过程的变量,那么怎么解决呢?,我们可以使用字符串拼接,然后在将字符串转换为可执行的sql就行了

--row_cat_TABLE_NAME和 row_cat_TABLE_NAME 就是变量 
SET @ALTER_FOREIGN = CONCAT(' ALTER TABLE ', row_cat_TABLE_NAME, ' DROP FOREIGN KEY ', row_cat_CONSTRAINT_NAME, ';'); 
/*预处理SQL，如果SQL格式不对，则会报错。*/ 
PREPARE sql_sentence FROM @ALTER_FOREIGN; 
/*执行SQL*/ 
EXECUTE sql_sentence; 
/*取消预处理。*/ 
DEALLOCATE PREPARE sql_sentence; 

存储过程中模拟return的实现方式

mysql不支持quit, exit或return的方式退出

编写存储过程时，为了业务规则需要，我们可能需要提前退出存储过程

那么，我们可以利用leave label方式模拟实现quit退出的效果 把存储过程原生的BEGIN替换掉

应用示例，存储过程如下：

DELIMITER $$ 
USE `study`$$ 
DROP PROCEDURE IF EXISTS `updateGrade`$$ 
CREATE PROCEDURE `study`.`updateGrade`(IN para_new_grade INT, IN para_old_grade INT)   
label:BEGIN  -- 关键地方 替换掉原来的BEGIN 
 
 
   DECLARE var_grade_count INT; 
 
   SELECT COUNT(grade) INTO var_grade_count FROM students WHERE grade = para_old_grade; 
   IF 0 = var_grade_count THEN 
        SELECT var_grade_count; 
        LEAVE label; -- 退出存储过程 
   ELSE 
        UPDATE students SET grade = para_new_grade WHERE grade = para_old_grade; 
   END IF; 
 
   SELECT var_grade_count; -- 打印变量值 
 
END$$ 
 
DELIMITER ; 

注意书写格式：BEGIN前面加label ，需要退出时直接leave label;

点赞 -收藏加 -关注 便于以后复习和收到最新内容
本文参考链接：https://huanmin.blog.csdn.net/article/details/120248277