Mysql存储过程

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然他们的作用不仅限于批处理。

 为什么要使用存储过程:优点

1 通过吧处理封装在容易使用的单元中,简化复杂的操作

2 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果开发人员和应用程序都使用了同一存储过程,则所使用的代码是相同的。还有就是防止错误,需要执行的步骤越多,出错的可能性越大。防止错误保证了数据的一致性。

3 简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码,使用它的人员不会改自己的代码了都。

4 提高性能,因为使用存储过程比使用单条SQL语句要快

5 存在一些职能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码

 换句话说3个主要好处简单、安全、高性能

 缺点

1 一般来说,存储过程的编写要比基本的SQL语句复杂,编写存储过程需要更高的技能,更丰富的经验。

2 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建,允许用户使用存储过程,但不允许创建存储过程

 存储过程是非常有用的,应该尽可能的使用它们

 执行存储过程

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;

更详细的使用方法可以参考:http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MYSQL 谈谈各存储引擎的优缺点

    1、存储引擎其实就是如何实现存储数据,如何为存储的数据建立索引以及如何更新,查询数据等技术实现的方法。

    Java架构师历程
  • MySQL 谈谈Memory存储引擎

    memory存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表,而且所有数据也放在内存中。这些特性都与InnoDB,MyISAM存储引...

    Java架构师历程
  • IT圈子里鬼混—谈谈IT行业的一些生存之道!

    本文摘自:http://blog.csdn.net/mengtech/article/details/2279047

    Java架构师历程
  • 数据迁移与一致性思考与实践

    在上一篇中我们讲了通用优惠券系统的设计,这篇主要是以优惠券重构后,我们现有系统接入到该通用优惠券系统过程中遇到的数据迁移与一致性问题相关的思考与实践。我们早期的...

    榴莲其实还可以
  • 干货!基于Ceph对象存储的分级混合云存储方案

    Unlimited Capacity:公有云的存储服务具有易扩展的特性,用户可以非常方便的根据其存储容量需求,对其已有的存储服务的容量进行扩展,因此从用户角度来...

    养码场
  • 云存储定价:顶级供应商的价格比较

    静一
  • 腾讯云-对象存储介绍

    首先介绍存储的分类,并主要介绍对象存储的分类,接着介绍用户的常见问题包括计费项和计费周期,最后介绍对象存储的控制台和使用案例。

    研究僧
  • 干货 | 如何评估Kubernetes持久化存储方案

    从用户角度看,存储就是一块盘或者一个目录,用户不关心盘或者目录如何实现,用户要求非常“简单”,就是稳定,性能好。为了能够提供稳定可靠的存储产品,各个厂家推出了各...

    焱融科技
  • 聊聊越来越火的对象存储

    随着云计算的发展,云存储作为一种更基础的云上资源池设施也越来越受到重视和欢迎。从云存储的类型来讲,目前流行的有块存储、文件存储和对象存储三种。今天的主角是对象存...

    飞雪无情
  • 对象存储,为什么那么火?

    上期文章,小枣君给大家详细介绍了数据存储技术的基本知识,其中重点对DAS、SAN和NAS技术进行了对比分析。

    鲜枣课堂

扫码关注云+社区

领取腾讯云代金券