【面试题5】Mysql的存储过程

助力小白闯IT 2020-05-23

1949

点击上方“助力小白闯IT”，关注公众号

面试刷题，查漏补缺

本文摘抄自MySQL必知必会

一：什么是存储过程

如果遇到一些操作并非一条sql就能完成的，而是需要多条sql语句才能完成，如下情况：

为了处理订单，需要核对以保证库存中有相应的物品
如果库存有物品，这些物品需要预定以便不将它们再卖给别人，并且要减少可用的物品数量以反映正确的库存量
库存中没有的物品需要订购，这需要与供应商进行某种交互
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户

虽然上面的例子不是一个完整的，但是要执行这个处理需要针对许多表的多条sql语句，并且需要执行的具体语句和其次序也是不固定的。

方法一：使用单独编写每一条sql，并根据结果有条件的执行另外的语句。方法二：使用存储过程。存储过程就是一条一条的sql语句的一个集合。

二：为什么要使用存储过程

简单、安全、高性能

通过把处理封装在容易使用的单元中，简化复杂的操作
由于不要求反复建立一系列处理步骤，这保证了数据的完整性
简化对变动的管理。如表名、列名或业务逻辑有变化的，只需要更改存储过程的的代码
提高了性能，使用存储过程比使用单独的sql语句更快
存在一些只能用在单个请求中的sql元素和特性，存储过程可以使用他们来编写功能更强更灵活的代码

三：如何使用存储过程

1、执行存储过程

mysql称存储过程的执行为调用，因此mysql在执行存储过程的语句为call。call接受存储过程的名字以及需要传递给它的任意参数。如下：

CALL productpricing(@pricelow,@pricehigh,@priceaverage);
# 分析：其中，执行名为productpricing的存储过程，它计算并返回产品的最低，最高和平均价格
# 备注：存储过程可以显示结果，也可以不显示结果

2、创建存储过程

案例：一个返回产品平均价格的存储过程

CREATE PROCEDURE productpricing()
BEGIN
  SELECT Avg(prod_price) AS priceaverage 
  FROM products;
END;
# 分析：
# 1、使用CREATE PROCEDURE productpricing()来定义一个存储过程productpricing
# 2、如果存储过程接收参数，需要哎productpricing()的括号中列举出来
# 3、BEGIN和END语句用来限定存储过程体
# 4、此过程体就是一个简单的select语句

补充：如果要使用Mysql命令行创建存储过程的话需要使用分割符：（命令行业就是小黑窗）

DELIMITER 
CREATE PROCEDURE productpricing()
BEGIN
  SELECT Avg(prod_price) AS priceaverage 
  FROM products;
END 
DELIMITER;

3、使用存储过程

# 我们就使用上述创建的存储过程：
CALL productpricing();

4、删除存储过程

DROP PROCEDURE IF EXISTS productpricing ;
# 如果存在就删除

5、使用参数

一般情况下，我们使用存储过程需要将结果返回，我们就需要使用变量来接收。

案例一：

CREATE PROCEDURE productpricing(
  OUT pl DECIMAL(8,2),
  OUT ph DECIMAL(8,2),
  OUT pa DECIMAL(8,2)
)
BEGIN
  SELECT Min(prod_price) 
  INTO pl
  FROM products; # 查询最低价格保存到变量pl中
  SELECT Max(prod_price) 
  INTO ph 
  FROM products; # 查询最高价格保存到变量ph中
  SELECT Avg(prod_price) 
  INTO pa 
  FROM products; # 查询平均价格保存到变量pa中
END;
# 知识点：
# 1、MySQL支持IN（传递给存储过程），OUT（从存储过程中传出），INOUT（对存储过程传入和传出）；此处是使用 OUT将存储过程的值传出去
# 2、INTO关键字：将检索的值保存到对应的变量中
# 3、DECIMAL(8,2)：整数部分和小数部分的个数不超过8个数，小数点精确到2


# 调用此存储过程：
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
# 变量必须以@开头


# 查询这个最小价格的值
SELECT @pricelow;
# 查询这个最高价格的值
SELECT @pricehigh;
# 查询这个平均价格的值
SELECT @priceaverage;

案例二：使用IN和OUT参数，ordertotal 接收订单号并返回该订单的合计

CREATE PROCEDURE ordertotal(
  IN onumber INT,
  OUT ototal DECIMAL(8,2)
)
BEGIN
  SELECT Sum(item_price*quanity)
  FROM orderitems
  WHERE order_num = onumber 
  INTO ototal;
END;
# 知识点：
# 1、onumber 定义为IN，因为订单号被传入存储过程
# 2、ototal 定义为OUT，因为要从存储过程中返回合计


# 调用此存储过程：
CALL ordertotal(20005,@total);# 20005是传入的订单号，@total是用来存储此存储过程返回的值
# 查询此合计
SELECT @total;

6、建立智能存储过程

案例功能：获得合计、把营业税有条件地添加到合计、返回合计（带或不带税）

CREATE PROCEDURE ordertotal(
  -- 订单号
  IN onumber INT,
  -- 时候有税，0表示没有，1表示有
  IN taxable BOOLEAN,
  -- 计算了税后的订单合计
  OUT ototal DECIMAL(8,2)
)
BEGIN
  -- 定义一个税前总合计 
  DECLARE total DECIMAL(8,2);
  -- 定义一个税率
  DECLARE taxrate DECIMAL(8,2);
  -- 查询订单合计
  SELECT Sum(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
  INTO total;
  -- 判断是否有税
  IF taxable THEN
    -- 如果有税就计算出税后合计 然后存到total中
    SELECT total+(total/100*taxrate) INTO total;
  END IF;
  -- 最后将最终的合计保存到OUT变量ototal中
  SELECT total INTO ototal;
END;


# 调用此存储过程
CALL ordertotal(20005,0,@total); # 订单号20005，没有税0，@total存结果
# 查询中最终合计
SELECT @total;


# 调用此存储过程
CALL ordertotal(20005,1,@total); # 订单号20005，有税1，@total存结果
# 查询中最终合计
SELECT @total;


# 知识点：
# 1、DECLEARE 用于定义局部变量，要求指定变量名和数据类型,如：DECLARE total DECIMAL(8,2)定义了一个局部变量total是小数类型的
# 2、IF语句用于检查taxable是否为真，也可以结合ENDIF ；格式：IF....THEN...ENDIF...THEN....END

往期Mysql面试题：

1、数据库锁

2、数据库并发策略

3、数据库事务

mysql 面试

文章转载自助力小白闯IT，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【面试题5】Mysql的存储过程

评论