创建和使用存储过程

存储过程

大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都是如此简单,经常会有一个完整的操作需要多条语句才能完成。


  • 1.处理订单,核对库存中有相应的物品
    2.如果库存有物品,物品需要预定以便不卖给别人,库存数量减少
    3.库存中没有物品则需要于供应商进行交互
    4.物品入库和退订需要通知相应客户

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然其作用不限于此。

为什么要使用存储过程

主要好处:简单、安全、高效

  • 通过把处理分装在容易使用的单元中,简化复杂操作
  • 不要求反复建立一系列处理步骤,保证了数据的完整性,防止错误
  • 简化对变动的管理。如果表名、列名、业务逻辑有变化只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化
  • 提高性能,使用存储过程比使用单独的SQL语句要快
  • 存在一些只能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码

注意

  • 存储过程编写比SQL语句更复杂
  • 编写存储过程需要安全访问权限
  • MySQL将编写存储过程和使用存储过程的权限分开使用

使用存储过程

存储过程的执行远比其定义更经常遇到

执行存储过程

MySQL称存储过程的执行为调用,因此MySQL执行存储过程语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

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

执行名为productpricing的存储过程,计算并返回产品最低、最高和平均价格。
存储过程可以显示加过,也可以不显示结果

创建存储过程

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

注意其有2个分隔符;
创建名为productpricing的存储过程,此存储过程没有参数,但是()仍然需要。BEGIN和END语句用来限定存储过程体,过程本身是一个SELECT语句
image.png

MySQL命令行客户机的分隔符;

如果使用的是命令行,因为MySQL默认语句分隔为;
而存储过程需要使用自身内部的;字符,会导致SQL出现句法错误。
可以临时更改命令行实用程序的语句分隔符

SELIMITER //
CREATE PROCEDURE productpricing () BEGIN
	SELECT
		AVG( prod_price ) AS priceaverage 
	FROM
	products;
END//

DELIMITER ;

SELIMITER //告诉命令行使用//作为新的语句结束分隔符。最后DELIMITER ; 恢复为原来的语句分隔符。
除了\ 转义字符外,任何字符都可以用作语句分隔符

  • 执行该存储过程
CALL productpricing ();

image.png

因为存储过程实际上是一种函数,所以存储过程名后需要有()符号,即使不传参数也需要
![image.png](/upload/2021/07/image-2df35b9ff80040fe9f1c96a304ad2ef3.png)

删除存储过程

DROP PROCEDURE productpricing;

注意其没有(),只需要给出存储过程名即可
image.png

  • 仅当存在时删除
    如果指定过程不存在将会产生一个错误,可使用DROP PROCEDURE IF EXISTS...

使用参数

一般,存储过程并不显示结果,而是把结果返回指定的便利(用来临时存储数据)

  • 创建存储过程
CREATE PROCEDURE productpricing (
	OUT p1 DECIMAL ( 8, 2 ),
	OUT ph DECIMAL ( 8, 2 ),
	OUT pa DECIMAL ( 8, 2 )) BEGIN
	SELECT
		MIN( prod_price ) INTO p1 
	FROM
		products;
	SELECT
		MAX( prod_price ) INTO ph 
	FROM
		products;
	SELECT
		AVG( prod_price ) INTO pa 
	FROM
	products;
END;

此存储过程接受3个参数:p1最低、ph最高、pa平均。使用十进制。
OUT指出相应的参数用来从存储过程传出一个值返回给调用者。MySQL支持IN、OUT和INOUT类型的参数
image.png

  • 调用
CALL productpricing ( @1, @2, @3 );

MySQL变量必须以@开始
其不返回任何数据,但是可以检索出其变量
image.png
检索存储的变量

SELECT
	@1,
	@2,
	@3;

image.png

使用IN和OUT参数

ordertotal存储过程接受订单号,并返回该订单的合计价格。

CREATE PROCEDURE ordertotal (
	IN onumber INT,
	OUT ototal DECIMAL ( 8, 2 )) BEGIN
	SELECT
		SUM( item_price * quantity ) 
	FROM
		orderitems 
	WHERE
	order_num = onumber INTO ototal;
END;

image.png
调用存储过程,并查询变量值

CALL ordertotal ( 20005, @total );
SELECT
	@total;

image.png

建立智能存储过程

只有在存储过程内包含业务规则和智能处理时,它们的威力才真正显现出来。


  • 需要订单合计,针对某些顾客对合计增加营业税
    1.获得合计
    2.把营业税有条件地添加到合计
    3.返回合计(带税或不带税)
-- 	Name:ordertotal
-- 	parameters:onumber= order number 
-- 					 	taxable= 0 if not taxable,1 if taxable
-- 					 	ototal= order total variable
CREATE PROCEDURE ordertotal (
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL ( 8, 2 )) COMMENT "Obtain order total ,optionally adding tax" BEGIN
-- 	DECLARE variable for total
	DECLARE
		total DECIMAL ( 8, 2 );
-- 		get the order total 
	DECLARE
		taxrate INT DEFAULT 6;
	SELECT
		SUM( item_price * quantity ) 
	FROM
		orderitems 
	WHERE
		order_num = onumber INTO total;
-- 		is this 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;
  • 关于该SQL的解释
    1.增加注释--
    2.添加了宁外一个参数taxable,为boolean值是否增税
    3.declare定义了2个局部变量,支持可选的默认值
    4.SELECT语句的值存储到total
    5.检查taxable是否为真,如果为真则用宁一个SELECT语句增加营业税
    6.将total保存到ototal
    7.COMMENT关键字,不是必须的,如果给出将在SHOW PROCEDURE STATUS的结果中显示
    image.png
  • 调用存储过程
CALL ordertotal ( 20005, 0, @total );
SELECT
	@total;

image.png

CALL ordertotal ( 20005, 1, @total );
SELECT
	@total;

image.png

IF语句

IF语句还是吃ELSEIF(还可使用THEN子句)和ELSE子句

检查存储过程

显出用来创建一个存储过程的CREATE语句,使用

SHOW CREATE PROCEDURE ordertotal;

image.png

SHOW PROCEDURE STATUS LIKE "ordertotal";

SHOW PROCEDURE STATUS可获得包括合适、由谁创建等详细信息的存储过程列表,由于会列出所有的存储过程,故可以使用LIKE过滤限制输出
image.png


这个家伙很懒,啥也没有留下😋