SQL笔记19：存储过程

内容要点

1. 什么是存储过程
2. 为什么要使用存储过程
3. 如何使用存储过程
4. 创建和使用存储过程的基本语法

存储过程

存储过程是为以后使用而保存的一条或多条SQL语句，可视为批文件，但不仅限于批文件。

为什么要使用存储过程

优势

1. 将处理封装到一个易用的单元中，简化复杂的操作
2. 保证数据的一致性
3. 简化对变动的管理
4. 限制对基础数据的访问，减少数据讹误
5. 存储过程通常以编译后的形式存储，提高了性能
6. 使用存储过程三个主要的好处：简单、安全、高性能

缺陷

1. 编写真正的可移植存储过程几乎不可能
2. 编写存储过程需要更高的技能、更丰富的经验

使用存储过程

执行存储过程

使用CALL关键字，CALL接受存储过程名和需要传递给它的任何参数。

CALL AddNewProduct('JTS01',
					  'Stuffed Eiffel Tower',
                      6.49,
                      'Plush stuffed ty with
the text La Tour Eiffel in red white and blue');

存储过程所完成的工作：

1. 验证传递的数据，保证所有参数都有值
2. 生成用于主键的唯一ID
3. 将新产品插入Products表，在合适的列中存储生成的主键和传递的数据

这就是存储过程执行的基础形式，对于不同的DBMS，可能包括以下的执行选择

1. 参数可选，具有不提供参数时的默认值
2. 不按次序给出参数，以“参数=值”的方式给出参数值
3. 输出参数，允许存储过程在正执行的应用程序中更新所用的参数
4. 用SELECT语句检索数据
5. 返回代码，允许存储过程返回一个值到正在执行的应用程序

创建存储过程

以MYSQL说明。

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

DELIMITER ;

存储过程实际上是一种函数。

删除存储过程

DROP PROCEDURE productpricing;

使用参数

以下是上述存储过程的修改版本：

例1

创建

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//

执行

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

输入

SELECT @priceaverage;

输出

输入

SELECT @pricehigh,@pricelow,@priceaverage;

输出：

例2

创建

DELIMITER //
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//
DELIMITER ;

执行

CALL ordertotal(20005,@total);

输入

SELECT @total;

输出

输入

CALL ordertotal(20009,@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)
)COMMENT 'Obtain order total,optionally adding tax'
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 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//

执行

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

输出

执行

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

输出

检查存储过程

为显示用来创建一个存储过程的CREATE语句，受用SHOW CREATE PROCEDURE语句；

SHOW CREATE PROCEDURE ordertotal;

输出

为获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。

SHOW PROCEDURE STATUS LIKE 'ordertotal';

输出

小结

1. 什么是存储过程
2. 为什么要使用存储过程
3. 存储过程的执行和创建语法
4. 使用存储过程的一些方法