存储过程:为以后的使用而保存的一条或多条MYSQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。
使用存储过程的理由:
- 通过处理封装在容易使用的单元中,简化复杂的操作。
- 保证数据的完整性
- 简化对变动的管理
- 提高性能。存储过程比单独的SQL语句要快。
总结起来,3个主要好处:简单、安全、高性能
1、使用存储过程
MySQL存储过程的执行称之为调用,因此MySQL执行存储过程的语句为CALL.
CALL 接收存储过程的名字以及需要传递给它的任意参数。
1.1创建存储过程
#productpricing为存储过程名称。
#DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符。
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT avg(prod_price) as priceaverage
FROM products;
END //
DELIMITER ;
1.2执行存储过程。
因为存储过程实际上是一种函数,所以存储过程名后需要有()符号。
CALL productpricing()
1.3删除存储过程
DROP PROCEDURE productpricing;
1.4使用参数
存储过程一般不显示结果,而是把结果返回给你指定的变量。
变量:内存中一个特定的位置,用来存储临时的数据。所有MYSQL变量都必须以@开始。
DELIMITER //
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;
SELECT Max(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pa FROM products;
END //
DELIMITER ;
此存储过程接受3个参数:p1存储产品的最低价,ph存储产品的最高价,pa存储产品的平均价。MYSQL支持IN(传递给存储过程)、OUT(从存储过程传出,返回给调用者)
- 调用存储过程
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage
);
- 显示检索出的产品平均价。
SELECT @priceaverage;
- 另一个实例,变量包括IN,OUT。
DELIMITER //
CREATE procedure ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity) INTO ototal FROM orderitems WHERE order_num=onumber;
END //
DELIMITER ;
CALL ordertotal(20005,@total);
SELECT @total;
3、智能存储过程
DELIMITER //
CREATE PROCEDURE ordertotal2(
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);
-- Declare taxrate INDEFUALT 6;
DECLARE taxrate INT DEFAULT 6;
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
SELECT total INTO ototal;
END //
DELIMITER ;
CALL ordertotal2(20005,0,@total);
select @total;
CALL ordertotal2(20005,1,@total);
select @total;
4、检查存储过程
SHOW CREATE PROCEDURE ordertotal2;
SHOW PROCEDURE STATUS;