头歌-MySQL开发技巧（存储过程）

存储过程

存储过程的定义

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

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

数据库SQL语言层面的代码封装与重用。

存储过程的创建和查询

创建存储过程：create procedure 存储过程名（参数）
每个存储的程序都包含一个由SQL语句组成的主体。此语言可能是由以分号（;）字符分隔的多个语句组成的复合语句。
但MySQL本身将分号识别为语句分隔符，因此必须临时重新定义分隔符，以使MySQL将整个存储的程序定义传递给服务器。

使用delimiter命令重新定义分隔符。

将结束定义为//，完成创建存储过程后，使用//表示结束，然后将分隔符重新设置为分号(😉

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

创建带有参数的存储过程

存储过程的参数有三种：

• 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

等级设定：

PLATINUM （creditlim>10000）
GOLD （5000<=creditlim<=10000）
SILVER（creditlim<5000）

解法

//先使用delimiter修改分隔符
delimiter $$	
//创建存储过程
CREATE PROCEDURE GetCustomerLevel(
    in p_customNumber int(11), 
    out p_customerLevel varchar(10))
BEGIN
//定义一个变量，把原表中的creditlimit传入a字段中
declare a int;
select creditlimit into a from customers
//确定元组，条件是会员号相等
WHERE customerNumber = p_customNumber;
// if else 条件分支语句
if a > 10000 THEN 
    set p_customerLevel='PLATINUM';
elseif a >= 5000 THEN 
    set p_customerLevel='GOLD';
else
    set p_customerLevel='SILVER';
END if;

//输出结果
select p_customNumber as customerNumber,p_customerLevel;

END $$
//存储过程结束
delimiter ; 
//恢复定义分隔符