【MySQL开发技巧】 - 存储过程_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 存储过程名

创建带有参数的存储过程

存储过程的参数有三种：

存储过程的查询和删除

查询

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

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

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

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

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

customers表数据结构：

等级设定：

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 |
±---------------±----------------+