MySQL开发技巧 - 存储过程第1关：存储过程

任务描述

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

相关知识

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

存储过程的定义

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

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

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

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

存储过程的创建和查询

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

• 下面我们来创建第一个存储过程

每个存储的程序都包含一个由 SQL 语句组成的主体。此语句可能是由以分号（;）字符分隔的多个语句组成的复合语句。例如：

1. CREATE PROCEDURE proc1()
2. BEGIN
3. SELECT * FROM user;
4. END;

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

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

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

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

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

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

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

IN参数示例：

OUT参数示例：

INOUT参数示例：

存储过程的查询和删除

我们如何在数据库中查询我们已经创建过的存储过程呢：

1. SHOW PROCEDURE STATUS WHERE db='数据库名';

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

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

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

1. 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. PLATINUM （creditlim>10000）
2. GOLD （5000<=creditlim<=10000）
3. SILVER（creditlim<5000）

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

1. 变量的定义和使用：

   1. declare 变量名 类型; #定义变量
   2. select id into 变量名 from table; #将table表中的id列值赋给变量。

2. SQL 中的if和case语法：

   1. IF expression THEN
   2. statements;
   3. ELSEIF elseif-expression THEN
   4. elseif-statements;
   5. ...
   6. ELSE
   7. else-statements;
   8. END IF;
   1. CASE case-expression
   2. WHEN when_expression_1 THEN commands
   3. WHEN when_expression_2 THEN commands
   4. ...
   5. ELSE commands
   6. END CASE;

测试说明

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

USE mydb;
#请在此处添加实现代码
########## Begin ##########
delimiter //
create PROCEDURE GetCustomerLevel(in p_customNumber int(11),out p_customerLevel varchar(10))
Begin
    declare level int;
    select creditlimit into level from customers where customerNumber = p_customNumber;
    if level < 5000 then
        set p_customerLevel = 'SILVER';
    elseif level <= 10000 then
        set p_customerLevel = 'GOLD';
    else
        set p_customerLevel = 'PLATINUM';
    end if;
    select p_customNumber as customerNumber,p_customerLevel;
End //
delimiter ;
 
########## End ##########