存储过程（一）

视图、索引、存储过程和事务 是 MySQL 中非常重要的几个概念，也是数据库应用开发者必须掌握的知识。视图用于延伸表的边界，索引用于提高数据库查找效率，存储过程用于分装业务逻辑，事务用于提升数据库应用的健壮性。

本文的主要内容是掌握存储过程的基础知识。

存储过程（一）

任务描述

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

相关知识

为了完成本关任务，你需要掌握：
1.存储过程的定义；
2.存储过程的创建和查询；
3.存储过程的查询和删除。

存储过程的定义

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

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

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

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

存储过程的创建和查询

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

  • 下面我们来创建第一个存储过程
    每个存储的程序都包含一个由 SQL 语句组成的主体。此语句可能是由以分号（;）字符分隔的多个语句组成的复合语句。例如：
    1
    2
    3
    4
    CREATE PROCEDURE proc1()
    BEGIN
    SELECT * FROM user;
    END;
    在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL 将会执行该命令，但在创建存储过程中我们并不希望 MySQL 这么做。

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

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

1
2
3
4
5
6
DELIMITER //
CREATE PROCEDURE proc1()
BEGIN
SELECT * FROM user;
END //
DELIMITER ;

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

  • 执行存储过程：call 存储过程名

  • 创建带有参数的存储过程
    存储过程的参数有三种：

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

IN参数示例：

OUT参数示例：

INOUT参数示例：

存储过程的查询和删除

我们如何在数据库中查询我们已经创建过的存储过程呢：
SHOW PROCEDURE STATUS WHERE db='数据库名';

查看存储过程的详细定义信息：
SHOW CREATE PROCEDURE 数据库.存储过程名;

当我们不再需要某个存储过程时，我们可以使用：
DROP PROCEDURE [IF EXISTS] 数据库名.存储过程名;

编程要求

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

customers表数据结构：

customerNumber creditlimit
101 2000
102 12000
103 6000

等级设定：

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

提示：你可能需要使用到定义变量和判断，

  1. 变量的定义和使用：
    1
    2
    declare 变量名 类型; #定义变量
    select id into 变量名 from table; #将table表中的id列值赋给变量。
  2. SQL 中的if和case语法：
    1
    2
    3
    4
    5
    6
    7
    8
    IF expression THEN
     statements;
    ELSEIF elseif-expression THEN
     elseif-statements;
    ...
    ELSE
     else-statements;
    END IF;
1
2
3
4
5
6
CASE case-expression
 WHEN when_expression_1 THEN commands
 WHEN when_expression_2 THEN commands
 ...
 ELSE commands
END CASE;

测试说明

平台会对你编写的代码进行测试，将调用你编写的存储过程，具体输出请参考右侧测试集。

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE mydb;
#请在此处添加实现代码
########## Begin ##########
delimiter $$
create procedure GetCustomerLevel(in p_customNumber int(11),out p_customerLevel varchar(10))
begin
declare levels int;
select creditlimit into levels from customers where customerNumber=p_customNumber;
if levels <5000 then
    set p_customerLevel = 'SILVER';
elseif levels <10000 then
    set p_customerLevel = 'GOLD';
 else
    set p_customerLevel = 'PLATINUM';
end if;
select p_customNumber as customerNumber,p_customerLevel;
end $$
delimiter ;
########## End ##########