MySQL存储过程

Notes:SQL文件

什么是存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

为什么要使用存储过程

• 通过把处理封装在容易使用的单元中，简化操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
• 简化对变动的管理。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQl元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

存储过程

创建存储过程

不带参数的存储过程

创建：

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

调用：

CALL productpricing();

删除：

DROP PROCEDURE productpricing;

MySQL命令行客户机的分隔符

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

理由：
DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END //而不是END ；。这样，存储过程体内的“；”仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER ；。

带有参数的存储过程–OUT

创建：

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);

所有MySQL变量都必须以@开始。
查看：

SELECT @pricelow,@pricehigh,@priceaverage;

带有参数的存储过程–IN，OUT

创建：

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;

调用：

CALL ordertotal(20005,@total);

查看：

SELECT @total;

带有参数的存储过程–INOUT

创建：

CREATE PROCEDURE squre(
    INOUT num int
    )
BEGIN
    SET num = num*num;
END;

调用：

SET @num = 3;
CALL squre(@num);

查看：

SELECT @num;

建立智能存储过程

创建：

CREATE PROCEDURE ordertoatal(
     IN onumber INT,
     IN taxable BOOLEAN,
     OUT ototal DECIMAL(8,2)
) COMMENT'存储过程实现逻辑判断,非零值考虑为真，只有0被视为假'
BEGIN 
   -- 申明变量
   DECLARE total DECIMAL(8,2);
   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;

调用：

CALL ordertoatal(20005,0,@total);

查看：

SELECT @total;

检查存储过程

SHOW PROCEDURE STATUS LIKE 'ordertoatal';