【MySQL开发技巧】 - 存储过程

编程任务

本关任务：为customers表创建一个存储过程，使该存储过程能通过用户的信用额度来区分用户的等级。

创建存储过程GetCustomerLevel(in p_customNumber int(11),out p_customerLevel varchar(10))，通过查询customers表中客户的信用额度，来决定客户级别，并将客户编号和对应等级输出，具体输出内容参考测试集。

存储过程的定义
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的 SQL 语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

简单的说存储过程就是具有名字的一段代码，用来完成一个特定的功能。

存储过程的创建和查询

创建存储过程：create procedure 存储过程名(参数)

例如：

CREATE PROCEDURE proc1()
BEGIN
SELECT * FROM user;
END;

在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL 将会执行该命令，但在创建存储过程中我们并不希望 MySQL 这么做。

MySQL 本身将分号识别为语句分隔符，因此必须临时重新定义分隔符以使 MySQL 将整个存储的程序定义传递给服务器。

要重新定义 MySQL 分隔符，请使用该 delimiter命令。使用 delimiter 首先将结束符定义为//，完成创建存储过程后，使用//表示结束，然后将分隔符重新设置为分号（;）：

DELIMITER //
CREATE PROCEDURE proc1()
BEGIN
SELECT * FROM user;
END //
DELIMITER ;

注意：/也可以换成其他符号，例如$;

执行存储过程：

call 存储过程名

创建带有参数的存储过程

存储过程的参数有三种：

IN	输入参数，也是默认模式，表示该参数的值必须在调用存储过程时指定在存储过程中修改该参数的值不能被返回
OUT	输出参数，该值可在存储过程内部被改变，并可返回
INOUT	输入输出参数，调用时指定，并且可被改变和返回

存储过程的查询和删除

查询

SHOW PROCEDURE STATUS WHERE db=‘数据库名’;

查看存储过程的详细定义信息：

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

当我们不再需要某个存储过程时，我们可以使用：

DROP PROCEDURE [IF EXISTS] 数据库名.存储过程名;

customers表数据结构：

customerNumber	creditlimit
101	2000
102	12000
103	6000

等级设定：

1. PLATINUM （creditlim>10000）
2. GOLD （5000<=creditlim<=10000）
3. SILVER（creditlim<5000）

变量的定义和使用：

定义变量

declare 变量名 类型;

将table表中的id列值赋给变量。

select id into 变量名 from table;

SQL 中的if和case语法

IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
…
ELSE
else-statements;
END IF;
CASE case-expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
…
ELSE commands
END CASE;

代码实现

输出结果

±---------------±----------------+
| customerNumber | p_customerLevel |
±---------------±----------------+
| 101 | SILVER |
±---------------±----------------+
±---------------±----------------+
| customerNumber | p_customerLevel |
±---------------±----------------+
| 102 | PLATINUM |
±---------------±----------------+
±---------------±----------------+
| customerNumber | p_customerLevel |
±---------------±----------------+
| 103 | GOLD |
±---------------±----------------+