文档章节

MySQL笔记 存储过程 游标 触发器

深圳大道
 深圳大道
发布于 2016/12/29 15:39
字数 5060
阅读 2
收藏 0
第二十三章 使用存储过程
    MySQL5 中添加了存储过程的支持。
    大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成
    存储过程简单来说,就是为以后的使用而保存的一条或多条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;
第二十四章  使用游标
    MySQL5添加了对游标的支持
    只能用于存储过程
    由前几章可知,mysql检索操作返回一组称为结果集的行。都与mysql语句匹配的行(0行或多行),使用简单的SELECT语句,没有办法得到第一行、下一行或前10行,也不存在每次行地处理所有行的简单方法(相对于成批处理他们)
    有时,需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标(cursor)是一个存储在MYSQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
    游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改。
    使用游标
        使用游标涉及几个明确的步骤:
            1 在能够使用游标前,必须声明(定义)它,这个过程实际上没有检索数据,它只是定义要使用的SELECT语句
            2 一旦声明后,必须打开游标以供使用。这个过程用钱吗定义的SELECT语句吧数据实际检索出来
            3 对于填有数据的游标,根据需要取出(检索)的各行
            4 在接受游标使用时,必须关闭它 如果不明确关闭游标,MySQL将会在到达END语句时自动关闭它
    创建游标
        游标可用DECLARE 语句创建。 DECLARE命名游标,并定义相应的SELECT语句。根据需要选择带有WHERE和其他子句。如:下面第一名为ordernumbers的游标,使用了检索所有订单的SELECT语句
            CREATE PROCEDURE processorders()
            BEGIN
            DECLARE ordernumbers CURSOR
            FOR
            SELECT order_num FROM orders ;
            END;
            存储过程处理完成后,游标就消失,因为它局限于存储过程
    打开和关闭游标
            CREATE PROCEDURE processorders()
            BEGIN
            DECLAREordernumbers CURSOR
            FOR
            SELECT order_num FROM orders ;
            Open ordernumbers ;
            Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源,因此,每个游标不需要时都应该关闭
            END;
    使用游标数据
        在一个游标被打开后,可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的要列),检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行,相当于PHP中的each()函数
循环检索数据,从第一行到最后一行
            CREATE PROCEDURE processorders()
            BEGIN
            -- 声明局部变量
            DECLARE done BOOLEAN DEFAULT 0;
            DECLARE o INT;
 
            DECLAREordernumbers CURSOR
            FOR
            SELECT order_num FROM orders ;
            -- 当SQLSTATE为02000时设置done值为1
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
            --打开游标
            Open ordernumbers ;
            -- 开始循环
            REPEAT
            -- 把当前行的值赋给声明的局部变量o中
            FETCH ordernumbers INTO o;
            -- 当done为真时停止循环
            UNTIL done END REPEAT;
            --关闭游标
            Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源,因此,每个游标不需要时都应该关闭
            END;
        语句中定义了CONTINUE HANDLER ,它是在条件出现时被执行的代码。这里,它指出当SQLSTATE '02000'出现时,SET done=1。SQLSTATE '02000'是一个未找到条件,当REPEAT没有更多的行供循环时,出现这个条件。
    DECLARE 语句次序  用DECLARE语句定义局部变量必须在定义任意游标或句柄之前定义,而句柄必须在游标之后定义。不遵守此规则就会出错
重复和循环   除这里使用REPEAT语句外,MySQL还支持循环语句,它可用来重复执行代码,直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行的循环。
为了把这些内容组织起来,这次吧取出的数据进行某种实际的处理
        CREATE PROCEDURE processorders()
        BEGIN
        -- 声明局部变量
        DECLARE done BOOLEAN DEFAULT 0;
        DECLARE o INT;
        DECLARE t DECIMAL(8,2)
 
        DECLAREordernumbers CURSOR
        FOR
        SELECT order_num FROM orders ;
        -- 当SQLSTATE为02000时设置done值为1
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
        -- 创建一个ordertotals的表
        CREATE TABLE IF NOT EXISTS ordertotals( order_num INT , total DECIMAL(8,2))
        --打开游标
        Open ordernumbers ;
        -- 开始循环
        REPEAT
        -- 把当前行的值赋给声明的局部变量o中
        FETCH ordernumbers INTO o;
        -- 用上文讲到的ordertotal存储过程并传入参数,返回营业税计算后的合计传给t变量
        CALL ordertotal(o , 1 ,t)
        -- 把订单号和合计插入到新建的ordertotals表中
        INSERT INTO ordertotals(order_num, total) VALUES(o , t);
        -- 当done为真时停止循环
        UNTIL done END REPEAT;
        --关闭游标
        Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源,因此,每个游标不需要时都应该关闭
        END;
        最后SELECT * FROM ordertotals就能查看结果了
第二十五章 使用触发器
    MySQL5版本后支持触发器
    只有表支持触发器,视图不支持触发器
    MySQL语句在需要的时被执行,存储过程也是如此,但是如果你想要某条语句(或某些语句)在事件发生时自动执行,那该怎么办呢:例如:
        1 每增加一个顾客到某个数据库表时,都检查其电话号码格式是否正确,区的缩写是否为大写
        2 每当订购一个产品时,都从库存数量中减少订购的数量
        3 无论何时删除一行,都在某个存档中保留一个副本
    这写例子的共同之处是他们都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句)
    1 DELETE
    2 INSERT
    3 UPDATE
    其他的MySQL语句不支持触发器
    创建触发器
        创建触发器需要给出4条信息
        1 唯一的触发器名;  //保存每个数据库中的触发器名唯一
        2 触发器关联的表;
        3 触发器应该响应的活动(DELETE、INSERT或UPDATE)
        4 触发器何时执行(处理前还是后,前是BEFORE 后是AFTER)
        创建触发器用CREATE TRIGGER
        CREATE TRIGGER newproduct AFTER INSERT ON products
        FOR EACH ROW SELECT'Product added'
       创建新触发器newproduct ,它将在INSERT语句成功执行后执行。这个触发器还镇定FOR EACH ROW,因此代码对每个插入的行执行。这个例子作用是文本对每个插入的行显示一次product added
        FOR EACH ROW 针对每个行都有作用,避免了INSERT一次插入多条语句
    触发器定义规则
        触发器按每个表每个事件每次地定义,每个表每个事件每次只允许定义一个触发器,因此,每个表最多定义6个触发器(每条INSERT UPDATE 和DELETE的之前和之后)。单个触发器不能与多个事件或多个表关联,所以,如果你需要一个对INSERT 和UPDATE存储执行的触发器,则应该定义两个触发器
    触发器失败  如果BEFORE(之前)触发器失败,则MySQL将不执行SQL语句的请求操作,此外,如果BEFORE触发器或语句本身失败,MySQL将不执行AFTER(之后)触发器
    删除触发器
        DROP TRIGGER newproduct;
        触发器不能更新或覆盖,所以修改触发器只能先删除再创建
    使用触发器
        我们来看看每种触发器以及它们的差别
    INSERT 触发器
        INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点:
    1 在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行
    2 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改插入的值)
    3 对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值
        提示:通常BEFORE用于数据验证和净化(目的是保证插入表中的数据确实是需要的数据)。本提示也适用于UPDATE触发器
    DELETE 触发器
        DELETE触发器在语句执行之前还是之后执行,需要知道以下几点:
    1 在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行;
    2 OLD中的值全部是只读的,不能更新
        例子演示适用OLD保存将要除的行到一个存档表中
        CREATE TRIGGERdeleteorder BEFORE DELETE ON orders
        FOR EACH ROW
        BEGIN  
        INSERT INTO archive_orders(order_num , order_date , cust_id)
        VALUES(OLD.order_num , OLD.order_date , OLD.cust_id);
        END;
        //此处的BEGIN  END块是非必需的,可以没有
    在任何订单删除之前执行这个触发器,它适用一条INSERT语句将OLD中的值(将要删除的值)保存到一个名为archive_orders的存档表中
    BEFORE DELETE触发器的优点是(相对于AFTER DELETE触发器),如果由于某种原因,订单不能被存档,DELETE本身将被放弃执行。
    多语言触发器  正如上面所见,触发器deleteorder 使用了BEGIN和END语句标记触发器体。这在此例中并不是必需的,不过也没有害处。使用BEGIN  END块的好处是触发器能容纳多条SQL语句。
    UPDATE触发器
        UPDATE触发器在语句执行之前还是之后执行,需要知道以下几点:
        1 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问(UPDATE语句前)的值,引用一名为NEW的虚拟表访问新更新的值
        2 在BEFORE UPDATE触发器中,NEW中的值可能被更新,(允许更改将要用于UPDATE语句中的值)
        3 OLD中的值全都是只读的,不能更新
            例子:保证州名的缩写总是大写(不管UPDATE语句给出的是大写还是小写)
            CREATE TRIGGER updatevendor BEFORE UPDATE ON vendores FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state)
    触发器的进一步介绍
    1 与其他DBMS相比,MySQL5中支持的触发器相当初级。以后可能会增强
    2 创建触发器可能需要特殊的安全访问权限,但是触发器的执行时自动的.如果INSERT UPDATE DELETE能执行,触发器就能执行
    3 应该用触发器来保证数据的一致性(大小写、格式等)。在触发器中执行这种类型的处理的优点是它总是进行这个处理,而且是透明地进行,与客户机应用无关
    4 触发器的一种非常有意义的使用创建审计跟踪。使用触发器把更改(如果需要,甚至还有之前和之后的状态)记录到另一表非常容易
    5 遗憾的是,MySQL触发器中不支持CALL语句,这表示不能从触发器中调用存储过程。所需要的存储过程代码需要复制到触发器内

本文转载自:http://blog.csdn.net/smartsmile2012/article/details/38382473

深圳大道
粉丝 3
博文 877
码字总数 0
作品 0
深圳
架构师
私信 提问
《MySQL必知必会》读书笔记(四) 22~30章 完结篇

1、视图 视图不包含表中应该有的任何列或数据,它包含的是一个SQL查询。在视图创建之后,可以用于表基本相同的方式利用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视...

一万
2016/02/08
329
0
《MySQL必知必会》笔记2

2016/2/26-27 第二十一章创建和操纵表 创建表 CREATE TABLE语句 IF NOT EXISTS:查看表名是否存在,并且仅在表名不存在时创建它。 NULL列和NOT NULL列。(NULL为默认设置) PRIMARY KEY:定义...

fxdhdu
2016/03/01
35
0
Oracle笔记 目录索引

Oracle笔记 一、oracle的安装、sqlplus的使用 Oracle笔记 二、常用dba命令行 Oracle笔记 三、function 、selectOracle笔记 四、增删改、事务 Oracle笔记 五、创建表、约束、视图、索引、序列...

ibm_hoojo
2011/05/03
0
0
SQL Server 开发指南

SQL Server 数据库设计 一、数据库设计的必要性 二、什么是数据库设计 三、数据库设计的重要 四、数据模型 实体-关系(E-R)数据模型 实体(Entity) 属性(Attribute) 关系(Relationship)...

ibm_hoojo
2011/07/21
0
0
MySQL必知必会笔记

【英】ben Forta著 5   1.0 使用扩展查询         |---->select note from table where Match(note) Against('anl');                        |--->where ......

wei-spring
2014/05/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

centos7 新手阿里云服务器安装mongodb

简介 MongoDB 是一个基于分布式 文件存储的NoSQL数据库 由C++语言编写,运行稳定,性能高 旨在为 WEB 应用提供可扩展的高性能数据存储解决方案 MongoDB特点 模式自由 :可以把不同结构的文档存...

醉雨
18分钟前
1
0
sql注入漏洞,应屏蔽SQL注入攻击

注:SQL注入好比是前端URL传参数请求时参数以SQL 做为参数传入,如 select 1 from dual where 1=1 or 1=1 and 1=2;(类似这样的SQL语句) 防御方法 如果自己编写防注代码,一般是先定义一个函...

颜丽
18分钟前
1
0
装饰者模式

 代理模式与装饰者模式看起来很像,都实现基础对象实现的接口,在其自身对象中都保存着对被代理/被装饰者的对象引用。   先来看看装饰者模式的定义:动态的将责任附加到被装饰者对象上,用...

铁骨铮铮
22分钟前
0
0
我为什么飞行 10000 公里去西班牙参加 KubeCon?

2019 年 5 月 20 日至 23 日, 由 Cloud Native Computing Foundation (CNCF) 主办的云原生技术大会 KubeCon + CloudNativeCon EU(欧洲场)即将在热情洋溢的巴塞罗那盛装启幕。 作为云计算领...

zhaowei121
40分钟前
0
0
Node.getTextContent() not found 解决办法【不需要调整builder path下面包顺序】

新导入的工程,w3c的getTextContent找不到,response的setCharacterEncoding找不到,网上很多教程都是“调整工程builder path的lib包顺序把jre、tomcat调到上面即可”,但是进入项目的build...

嘿嘿嘿IT
47分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部