答秋千
执行存储过程
MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句为CALL .CALL接受存储过程的名字以及需要传递给它的任意参数
CALL productpricing(@pricelow , @pricehigh , @priceaverage);
//执行名为productpricing的存储过程,它计算并返回产品的最低、最高和平均价格
创建存储过程
CREATE PROCEDURE 存储过程名()
一个例子说明:一个返回产品平均价格的存储过程如下代码:
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
//创建存储过程名为productpricing,如果存储过程需要接受参数,可以在()中列举出来。即使没有参数后面仍然要跟()。BEGIN和END语句用来限定存储过程体,过程体本身是个简单的SELECT语句
在MYSQL处理这段代码时会创建一个新的存储过程productpricing。没有返回数据。因为这段代码时创建而不是使用存储过程。
Mysql命令行客户机的分隔符
默认的MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身的 ; 字符,则他们最终不会成为存储过程的成分,这会使存储过程中的SQL出现句法错误
解决方法是临时更改命令实用程序的语句分隔符
DELIMITER // //定义新的语句分隔符为//
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
DELIMITER ; //改回原来的语句分隔符为 ;
除\符号外,任何字符都可以作为语句分隔符
CALL productpricing(); //使用productpricing存储过程
执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数,所以存储过程名后面要有()符号
删除存储过程
DROP PROCEDURE productpricing ; //删除存储过程后面不需要跟(),只给出存储过程名
为了删除存储过程不存在时删除产生错误,可以判断仅存储过程存在时删除
DROP PROCEDURE IF EXISTS
使用参数
Productpricing只是一个简单的存储过程,他简单地显示SELECT语句的结果。
一般存储过程并不显示结果,而是把结果返回给你指定的变量
CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2),
)
BEGIN
SELECT Min(prod_price)
INTO p1
FROM products;
SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END;
此存储过程接受3个参数,p1存储产品最低价格,ph存储产品最高价格,pa存储产品平均价格。每个参数必须指定类型,这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传给一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程中传出、如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内,如前所见,它们是一些列SELECT语句,用来检索值,然后保存到相应的变量(通过INTO关键字)
调用修改过的存储过程必须指定3个变量名:
CALL productpricing(@pricelow , @pricehigh , @priceaverage);
这条CALL语句给出3个参数,它们是存储过程将保存结果的3个变量的名字
变量名 所有的MySQL变量都必须以@开始
使用变量
SELECT @priceaverage ;
SELECT @pricelow , @pricehigh , @priceaverage ; //获得3给变量的值
下面是另一个例子,这次使用IN和OUT参数。ordertotal接受订单号,并返回该订单的合计
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,因为要从存储过程中返回合计,SELECT语句使用这两个参数,WHERE子句使用onumber选择正确的行,INTO使用ototal存储计算出来的合计
为了调用这个新的过程,可以使用下列语句:
CALL ordertotal(2005 , @total); //这样查询其他的订单总计可直接改变订单号即可
SELECT @total;
建立智能的存储过程
上面的存储过程基本都是封装MySQL简单的SELECT语句,但存储过程的威力在它包含业务逻辑和智能处理时才显示出来
例如:你需要和以前一样的订单合计,但需要对合计增加营业税,不活只针对某些顾客(或许是你所在区的顾客)。那么需要做下面的事情:
1 获得合计(与以前一样)
2 吧营业税有条件地添加到合计
3 返回合计(带或不带税)
存储过程的完整工作如下:
— Name: ordertotal
— Parameters: onumber = 订单号
— taxable = 1为有营业税 0 为没有
— ototal = 合计
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
— COMMENT()中的内容将在SHOW PROCEDURE STATUS ordertotal()中显示,其备注作用
) COMMENT ‘Obtain order total , optionally adding tax’
BEGIN
— 定义total局部变量
DECLARE total DECIMAL(8,2)
DECLARE taxrate INT DEFAULT 6;
— 获得订单的合计,并将结果存储到局部变量total中
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
— 判断是否需要增加营业税,如为真,这增加6%的营业税
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
— 把局部变量total中才合计传给ototal中
SELECT total INTO ototal;
END;
此存储过程有很大的变动,首先,增加了注释(前面放置—)。在存储过程复杂性增加时,这样很重要。在存储体中,用DECLARE语句定义了两个局部变量。DECLARE要求制定变量名和数据类型,它也支持可选的默认值(这个例子中taxrate的默认设置为6%),SELECT 语句已经改变,因此其结果存储到total局部变量中而不是ototal。IF语句检查taxable是否为真,如果为真,则用另一SELECT语句增加营业税到局部变量total,最后用另一SELECT语句将total(增加了或没有增加的)保存到ototal中。
COMMENT关键字 本列中的存储过程在CREATE PROCEDURE 语句中包含了一个COMMENT值,他不是必需的,但如果给出,将在SHOW PROCEDURE STATUS的结果中显示
IF语句 这个例子中给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句(前者还使用THEN子句,后者不使用)
检查存储过程
为显示用来创建一个存储过程的CREATE语句,使用SHOW CREATE PROCEDURE语句
SHOW CREATE PROCEDURE ordertotal;
为了获得包括何时、有谁创建等详细信息的存储过程列表。使用SHOW PROCEDURE STATUS.限制过程状态结果,为了限制其输出,可以使用LIKE指定一个过滤模式,例如:SHOW PROCEDURE STATUS LIKE ”ordertotal;