MySQL简单的存储过程

（本文仅仅是我学习数据库的笔记，供自己记忆了解，如有错误请纠正）

创建total表
total表格内容如下

**

1、创建无参数的存储过程

#删除存储过程
DROP PROCEDURE IF EXISTS price;
#创建存储过程
CREATE PROCEDURE price()
BEGIN
SELECT avg(total_price) FROM total;
END;
#使用储存过程
CALL price();  

运行结果

2、带有参数的存储过程

只带有in输入变量的存储过程

查询total表中vend_id=1001的所有信息

DROP PROCEDURE IF EXISTS sumproducts;
CREATE PROCEDURE sumproducts(
IN vi INT)
BEGIN
SELECT * FROM total WHERE vend_id=vi;
END;
CALL sumproducts(1001);

运行结果

只带有out输出变量的存储过程

DROP PROCEDURE  IF EXISTS productprice;
CREATE PROCEDURE productprice(
OUT a1 DECIMAL(10,2),
OUT a2 DECIMAL(10,2),
OUT a3 DECIMAL(10,2))
BEGIN 
SELECT avg(total_price)INTO a1 FROM total;
SELECT avg(cost) INTO a2 FROM total;
SELECT sum(total_price) INTO a3 FROM total;
END;
CALL productprice(@a1,@a2,@a3);

运行结果

带有输入in、输出out变量的存储过程

DROP PROCEDURE IF EXISTS productprice2;
CREATE PROCEDURE productprice2(
in v1 INT,
OUT a1 DECIMAL(10,2),
OUT a2 DECIMAL(10,2),
OUT a3 DECIMAL(10,2),
OUT a4 DECIMAL(10,2),
OUT a5 DECIMAL(10,2),
OUT a6 DECIMAL(10,2))
BEGIN
SELECT AVG(total_price) INTO a1 from total WHERE vend_id=v1 ;
SELECT AVG(cost) INTO a2 FROM total WHERE vend_id=v1 ;
SELECT MIN(total_price) INTO a3 FROM total WHERE vend_id=v1 ;
SELECT MIN(cost) INTO a4 FROM total WHERE vend_id=v1 ;
SELECT MAX(total_price) INTO a5 FROM total WHERE vend_id=v1 ;
SELECT MAX(cost)INTO a6 FROM total WHERE vend_id=v1 ;
END;
CALL productprice2 (1002,@a1,@a2,@a3,@a4,@a5,@a6);
SELECT @a1,@a2,@a3,@a4,@a5,@a6;

运行结果