mysql存储过程

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语句，这表示不能从触发器中调用存储过程。所需要的存储过程代码需要复制到触发器内

实例：

#这个是定义一个分隔符，因为一般是;结束，则会执行，而在存储过程中我们需要的是命令输入完毕执行
DELIMITER $$
DROP PROCEDURE IF EXISTS `refresh_device_city`$$
CREATE PROCEDURE refresh_device_city(IN device_id BIGINT,IN table_num INT)
BEGIN
#定义变量
DECLARE locationTableName VARCHAR(255);
DECLARE delete_sql VARCHAR(200); 
DECLARE insert_sql VARCHAR(2000); 
DECLARE err INT DEFAULT 0;  
-- 如果出现异常，会自动处理并rollback  
DECLARE EXIT HANDLER FOR  SQLEXCEPTION ROLLBACK ;   
START TRANSACTION;
SET locationTableName= CONCAT('lbs_device_location_', table_num);
SET @delete_sql = CONCAT('DELETE FROM lbs_device_city WHERE device_id=',device_id);
SET @insert_sql = CONCAT('INSERT INTO LBS_DEVICE_CITY (device_id,city,into_city_time,create_time) 
                             SELECT ',device_id,',a.city city,a.ltime ltime,NOW() FROM( SELECT MIN(a.city) city,MIN(a.ltime) ltime FROM (
                     SELECT CASE WHEN a.city!=@city THEN @i:=@i+1 ELSE @i END i,
                 CASE WHEN a.city!=@city THEN @city:=a.city ELSE @city END tcity,
                 a.city city,a.ltime ltime
                     FROM ( SELECT a.CITY city,a.LOCATION_TIME ltime FROM ',locationTableName,' a 
                 WHERE a.DEVICE_ID = ',device_id,' AND a.city !=\'\' AND a.CITY != \'未知\' 
                 ORDER BY a.LOCATION_TIME ASC) a ,(SELECT @i:=-1,@city:=\'未知\') b 
                         )a WHERE a.i != -1 GROUP BY a.i ) a ORDER BY a.ltime ASC');
#执行SQL语句
PREPARE delstmt FROM @delete_sql;
EXECUTE delstmt;
DEALLOCATE PREPARE delstmt;
PREPARE insstmt FROM @insert_sql;
EXECUTE insstmt;
DEALLOCATE PREPARE insstmt;
COMMIT;
END$$
DELIMITER ;



DELIMITER $$
DROP PROCEDURE IF EXISTS `refresh_all_device_city`$$
CREATE PROCEDURE refresh_all_device_city()
BEGIN
#查询所有在设备城市表中有数据的设备和对应定位所在表的编号
#循环设备，删除设备所有城市记录，重新插入数据,调用另一个存储过程
/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/
DECLARE deviceid BIGINT; /*用于存储结果集S_S的记录（因为我这里S_S的记录只有一列且为bigint类型）*/
DECLARE tablenum INT;
/*定义游标*/
DECLARE idCur CURSOR FOR SELECT a.device_id,b.table_num FROM lbs_device_city a,lbs_device b WHERE a.device_id=b.id GROUP BY device_id;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur;  /*打开游标*/
REPEAT
FETCH idCur INTO deviceid,tablenum;  /*这部分可以看看书，还可以fetch多列（假设结果集S_S的记录不是单列的话）*/
IF NOT done THEN  /*数值为非0，MySQL认为是true*/
CALL refresh_device_city(deviceid,tablenum);
END IF;
UNTIL done END REPEAT;
CLOSE idCur;  /*关闭游标*/
SELECT deviceid;
END$$
DELIMITER ;

CALL refresh_all_device_city();

/**处理单个设备的数据插入到定位统计表中*/
DROP PROCEDURE IF EXISTS count_device_location;
DELIMITER $$ -- 分隔符 因为一般是;结束，则会执行，而在存储过程中我们需要的是命令输入完毕执行
CREATE PROCEDURE count_device_location(IN deviceid BIGINT,IN tablenum BIGINT)--  接收参数，根据设备ID，查询对应的设备编号,
BEGIN
--  多个列的情况下似乎只能用 into 方式
DECLARE insert_sql VARCHAR(4000); 
DECLARE err INT DEFAULT 0;  


-- 如果出现异常，会自动处理并rollback  
DECLARE EXIT HANDLER FOR  SQLEXCEPTION ROLLBACK ;   
START TRANSACTION;
SELECT `device_id`, `org_key` INTO @deviceno, @orgkey FROM lbs_device WHERE `id`=deviceid; -- 注意参数不能与表中任何列名相同，否则不会取到参数

/*执行插入SQL*/  
-- 如果出现异常，会自动处理并rollback  
SET @insert_sql = CONCAT('INSERT INTO  LBS_DEVICE_LOCATION_COUNT  (DEVICE_NO,ORG_KEY,START_TIME,END_TIME,LOCATION_COUNT) SELECT \'',@deviceno
                          ,'\',\'',@orgkey,'\',CONCAT(b.location_count_time,\':00:00\'),DATE_ADD(b.location_count_time,INTERVAL 1 HOUR),b.num FROM 
                          (SELECT COUNT(a.id) num,a.location_count_time FROM (SELECT DATE_FORMAT(location_time,\'%Y-%m-%d %H\') 
                          location_count_time,id  FROM lbs_device_location_',tablenum,' WHERE device_id=',deviceid,') a GROUP BY a.location_count_time) b');
#执行SQL语句
PREPARE insstmt FROM @insert_sql;
EXECUTE insstmt;
DEALLOCATE PREPARE insstmt;
COMMIT;
END$$
DELIMITER ;

CALL count_device_location(29632,2);


DELIMITER $$
DROP PROCEDURE IF EXISTS `query_location_device_table`$$
CREATE PROCEDURE query_location_device_table()
BEGIN
#查询所有在设备城市表中有数据的设备和对应定位所在表的编号
#循环设备，删除设备所有城市记录，重新插入数据,调用另一个存储过程
/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/
DECLARE deviceid BIGINT; /*用于存储结果集*/
/*定义游标*/
DECLARE idCur CURSOR FOR SELECT device_id FROM lbs_device_location_1 GROUP BY device_id ;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur;  /*打开游标*/
REPEAT
FETCH idCur INTO deviceid;  /*fetch多列*/
IF NOT done THEN  /*数值为非0，MySQL认为是true*/
CALL count_device_location(deviceid,1);
END IF;
UNTIL done END REPEAT;
CLOSE idCur;  /*关闭游标*/
END$$
DELIMITER ;

查询预警表中所有的记录，然后循环每一条，查询它是否有未结束的跟踪号，如果没有，则将该预警的状态置为无效

DELIMITER $$
USE `lbs_test`$$
DROP PROCEDURE IF EXISTS `clean_invalid_one_warning`$$
CREATE DEFINER=`lbs_test`@`%` PROCEDURE `clean_invalid_one_warning`(IN warning_id BIGINT)
BEGIN
    #查询这个预警是否有未结束的跟踪号
    SET @select_stmt=CONCAT('SELECT COUNT(b.id) INTO @cnt1 FROM lbs_warning a 
     INNER JOIN  `lbs_track_register` b ON a.`ORG_KEY`=b.`ORG_APIKEY` AND a.`TRACK_NO`=b.`TRACK_NO` WHERE a.id=',warning_id 
     ,' AND (b.`TRACK_END_TIME`>NOW() OR  b.`TRACK_END_TIME` IS NULL) ');  
 
    PREPARE stmt FROM @select_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT @cnt1;
    #如果查询到未结束的跟踪号，开启一个事物，修改它为结束
    IF (@cnt1=0) THEN 
       #开启事物修改
       START TRANSACTION;
       #执行SQL语句
       SET @update_stmt=CONCAT('UPDATE LBS_WARNING SET `STATUS`=0 WHERE ID=',warning_id);  
       PREPARE upstmt FROM @update_stmt;
       EXECUTE upstmt;
       DEALLOCATE PREPARE upstmt;
       COMMIT;
    END IF;
END$$
DELIMITER ;



DELIMITER $$
USE `lbs_test`$$
DROP PROCEDURE IF EXISTS `clean_invalid_warning`$$
CREATE DEFINER=`lbs_test`@`%` PROCEDURE `clean_invalid_warning`()
BEGIN
#查询所有未结束的预警
#循环每个预警，查询它是否有未结束的跟踪号
#如果没有，则修改该预警为无效

/*这种写法也可以：DECLARE done INT DEFAULT FALSE;*/
DECLARE done INT DEFAULT 0;  /*用于判断是否结束循环*/
DECLARE warningid BIGINT; /*用于存储结果集*/
/*定义游标*/
DECLARE idCur CURSOR FOR SELECT id FROM lbs_warning WHERE `status`='1' ;
/*定义 设置循环结束标识done值怎么改变 的逻辑*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; /*done = true;亦可*/
OPEN idCur;  /*打开游标*/
REPEAT
FETCH idCur INTO warningid;  /*fetch赋值*/
IF NOT done THEN  /*数值为非0，MySQL认为是true*/
/**根据预警ID查询它是否有未结束的跟踪号*/
    CALL clean_invalid_one_warning(warningid);
END IF;
UNTIL done END REPEAT;
CLOSE idCur;  /*关闭游标*/
END$$
DELIMITER ;

本站声明:网站内容来源于网络,如有侵权,请联系我们https://www.qiquanji.com,我们将及时处理。

微信扫码关注

更新实时通知