《MySQL必知必会》之快速入门存储过程

使用存储过程

本章介绍什么是存储过程，为什么使用、如何使用，并介绍如何创建和使用存储过程的基本语法

存储过程

在实际应用中，往往需要执行多个表的多条sql语句

存储过程就是为以后的使用而保存的一条或者多条sql语句的集合

使用存储过程的好处：简单、安全、高性能

创建存储过程

需求：一个返回产品平均价格的存储过程

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

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义，如果有参数需要在()中列举

BEGIN...END用来限定存储过程体，过程体本身就是一个简单的select语句

注意：

; 分号冲突的问题，MySQL命令行实用程序也使用 ; 作为分隔符

解决办法是临时更改命令行实用程序的语句分隔符

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

DELIMITER //就是指定命令行实用程序实用//作为新的语句结束分隔符

CALL productpricing()； 

DROP PEOCEDURE productpricing; 

通过上述代码调用和删除存储过程

使用参数

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; 

此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。

每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数

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

上述存储过程中的三个形参对应了此时调用存储过程中的三个实参

注意MySQL中的变量都是以@开始的

于是就可以使用变量了

SELECT @priceaverage; 

需求：使用存储过程设计一个接收订单号并返回该订单的合计

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; 

其中onumber定义为IN表示该订单号需要被传入存储过程，ototal定义为OUT表示要从存储过程返回合计，INTO将select后的值存入ototal

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

上述sql即可返回订单合计

建立智能存储过程

想要完成更复杂的业务，需求：需要对合计添加营业税

-- Name: ordertotal -- Parameters: onumber = order number --             taxable = 0 if not taxable, 1 if taxable --             ototal = order total variable  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; 

上述sql在之前基础上增加了taxable的布尔类型，使用DECLARE定义了两个局部变量，通过IF子句检查taxable是否为真从而增加税收

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

指定0或者1来决定是否增添税收

检查存储过程

SHOW CREATE PROCEDURE ordertotal; 

上述sql显示ordertotal的存储过程的create语句

SHOW PROCEDURE STATUS ordertotal; 

上述sql显示一些具体信息