mysql必知必会(23)：存储过程

2019-08-16 本文已影响0人 warmsirius

一、存储过程

版本要求：MySQL5+

存储过程，简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

可将其视为批文件，虽然他们的作用不仅限于批处理。

1. 为什么要使用存储过程

使用存储过程的理由：

优点
□ 通过把处理封装在容易使用的单元中，保证数据的完整性。
□ 由于不要求反复简历一系列处理步骤，保证了数据的完整性。如果所有开发人员和应用程序都使用同一存储过程，则使用的代码都是相同的。(减少错误)
□ 简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要修改存储过程的代码。使用它的人员甚至不需要知道这些变化。(安全性)
□ 提高性能。存储过程比单独的SQL语句要快。
□ 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写共鞥更强更灵活的代码。
缺点
□ 存储过程比SQL语句复杂得多
□ 并不是所有人都有创建存储过程的安全访问权限。

三、使用存储过程

1. 执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。

CALL接收存储过程的名字以及需要传递给它的任意参数。

CALL 存储过程名(@param1,
              @param2,
              ...
);

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaveage
);

2. 创建存储过程

例如：返回产品平均价格的存储过程

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

mysql命令行客户机的分隔符

默认的MySQL分隔符为;(和SQL语句一样)，mysql命令行实用程序也使用，作为语句分隔符。如果命令行实用程序要解释存储过程自身内；字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL语句出现句法错误。

解决办法就是临时更改命令行实用程序的语句分隔符：
DELIMITER //
CREATE PRODUCER productpricing()
BEGIN
 SELECT Avg(prod_price) AS priceaverage
  FROM products;
END //
DELEMITER ;

DELEMITER // 告诉实用程序实用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END //。这样存储体内的;可以保持不变，正确地传递给引擎，最后恢复为原来的语句分隔符。

3. 删除存储过程

DROP PROCEDURE 存储过程名;

仅当存在时删除，如果指定的不存在，则DROP PROCEDURE将产生一个错误。

DROP PROCUDURE 存储过程名 IF EXISTS;

4. 使用参数

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

变量：内存中一个特定的位置，用来临时存储数据。

DELIMITER //
CREATE PRODUCER 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;
   SELECT Max(prod_price) 
   INTO ph
   FROM products;
   SELECT Avg(prod_price) 
   INTO pa
   FROM products;
END //
DELEMITER ;

此存储过程接收3个参数：pl:最低价格，ph: 最高价格，pa:平均价格。

每个参数必须具有指定的类型，这里使用十进制值。

OUT：指出相应的参数用来从存储过程穿出一个值(返回给调用者)
IN：传递给存储过程
INOUT：对存储过程传入和传出

注意：记录集是不允许的类型，因此不能通过一个参数返回多个行和列。这就是前面的例子为啥要使用三个参数的原因。

调用前面的存储过程

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaveage
);

变量名：所有MySQL的变量都必须以@开始

在调用的时候，这条语句并不显示任何数据。它返回以后可以显示的变量。

显示检索出的产品价格

SELECT @priceaverage;

SELECT @pricelow, @pricehigh, @priceaveage;

举例

DELIMITER //
CREATE PRODUCER ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8, 2)
)
BEGIN
    SELECT Sum(item_price*quantity)
    FROM  orderitems
    WHERE order_num = onumber
    INTO ototal;
END //
DELEMITER ;

onumber定义为IN，因为订单号被传入存储过程。
ototal定义为OUT，因为要从存储过程中返回合计

调用这个新存储过程

CALL orderitems(20005, @total);

查看合计

select @total;

四、建立智能存储过程

考虑这个场景：需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些客户。那么，需要做以下几件事儿：
□ 获得合计
□ 把营业税有条件地添加到合计
□ 返回合计(带税或不带税)

-- Name：ordertotal
-- Parameters：onumber = order number
--            taxable = 0 if not taxable, 1 if taxable
--            ototal = order total variable

DELIMITER //
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8, 2)
) COMMIT “obtain order total, optionally adding tex”
BEGIN
  -- Declare variable for total
  DECLARE total DECIMAL(8, 2);
  -- Declare tax percentage
  DECLARE taxrate INT DEFAULT 6;

  -- Get the order total
  SELECT Sum(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
 INTO total;
  
  -- Is it taxable?
  IF taxable THEN
    --   Yes, so add taxrate to the total
    SELECT total+(total/100*taxrate) INTO total;
  END IF;
  -- And finally, save to out variable
  SELECT total INTO ototal;
END //

--：注释

IF：条件语句，还支持ELSEIF， ELSE语句

DECLARE：定义局部变量

COMMIT：不是必需的，但是如果给出，将在SHOW PROCEDURE STATUS的结果中显示

调用这个存储过程

CALL orderototal(20005, 0, @total);
SELECT @total;

五、检查存储过程

SHOW CREATE PROCEDURE ordertotal;