《MySQL必知必会》第二十三章-使用存储过程

执行存储过程

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage); -- 所有MySQL变量都必须以@开始

创建存储过程

DELIMITER //  -- 告诉命令行实用程序使用//作为新的语句结束分隔符
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END //
DELIMITER ;  -- 恢复为原来的语句分隔符
-- CALL productpricing();

删除存储过程

DROP PROCEDURE productpricing;
DROP PROCEDURE IF EXISTS productpricing;

使用参数

MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)、INOUT(对存储过程传入和传出) 类型的参数

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 ;
-- CALL productpricing(@pricelow, @pricehigh, @priceaverage);
-- SELECT @pricehigh, @pricelow, @priceaverage;

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;

建立智能存储过程

-- 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//
DELIMITER ;
-- CALL ordertotal(20005, 0, @total);
-- SELECT @total;

检查存储过程

SHOW CREATE PROCEDURE ordertotal;  -- 显示用来创建一个存储过程的CREATE语句
SHOW PROCEDURE STATUS;    -- 详细信息
SHOW PROCEDURE STATUS LIKE 'ordertotal';