(本文仅仅是我学习数据库的笔记,供自己记忆了解,如有错误请纠正)
创建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;
运行结果