MySQL储存过程

概念

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

存储过程相关的SQL语句

查看数据库下的所有储存过程： show procedure status;
查看指定的存储过程： show procedure status like “new_procedure”;
查看存储过程或函数的创建代码：
show create procedure proc_name;
show create function func_name;
创建存储过程

USE `test`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `test`$$
CREATE PROCEDURE `new_procedure` ()
BEGIN
	select id from students;
END$$

DELIMITER ;

其中，DELIMITER $ $ 告诉命令行实用程序使用 $ $ 作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END $$而不是END;。这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，

调用存储过程： call proc_name;
删除存储过程： drop procedure proc_name;

使用参数

变量

	-- 首先遍历所有的猪栏id
    declare pigpen_id int; -- 猪栏id变量
    declare pig_age int; -- 猪栏下的平均日龄

USE `zxzl_dev`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `zxzl_dev`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
out d1  int, out d2 int
)
BEGIN
	select min(swinery_name) into d1 from swinery_link_dev;
    select min(device_id) into d1 from swinery_link_dev;
    
END$$

DELIMITER ;

调用 call new_procedure(@n, @d);
查看参数 selece @n;

游标

。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

游标只能用于存储过程和函数

游标的使用步骤

• 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
• 对于填有数据的游标，根据需要取出（检索）各行。
• 在结束游标使用时，必须关闭游标。

创建游标 declare d_p cursor
声明游标 declare d_p cursor for select id, average_age, feed_num, guest_id from farm_record where swinery_name is not null;

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
	declare room int;
    declare device int;
    declare d_p cursor for select room_id, device_id from room_device;
    open d_p;
    fetch d_p into room, device;
    close d_p;
END

也可使用多个游标，嵌套使用

+----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure                              | sql_mode                                                                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
+----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| update_reality_feeding_chart_procedure | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `update_reality_feeding_chart_procedure`()
BEGIN
        -- 首先遍历所有的猪栏id
    declare pigpen_id int; -- 猪栏id变量
    declare pig_age int; -- 猪栏下的平均日龄
    declare pig_total int;
    declare guest_id_t int;
    declare flag_farm_record int default 0;

    -- 定义游标，将sql的结果查询集赋值到游标中
    declare d_p cursor for select id, average_age, feed_num, guest_id from farm_record where swinery_name is not null;

        -- 声明当游标遍历完后将标志变量置成某个值
        declare continue handler for not found set flag_farm_record=1;
    -- 更新猪日龄
    -- update farm_record set average_age = average_age + 1;
        -- 打开游标
        open d_p;

        -- 变量顺序要和sql结果列的顺序一致
    fetch d_p into pigpen_id, pig_age, pig_total, guest_id_t;

    while flag_farm_record<>1 do
                -- 执行业务逻辑
        begin
            declare device_id_t int; -- 猪栏id变量
            declare devs_feeding_total int default 0;
                        declare flag_swinery_dev int default 0;
                        declare devs_total int default 0;
                declare swinery_dev_dp cursor for select device_id from swinery_link_dev where swinery_name = pigpen_id;
                        declare continue handler for not found set flag_swinery_dev=1;

            open swinery_dev_dp;
            fetch swinery_dev_dp into device_id_t;
            -- 查询当前猪栏下的所有设备
                        while flag_swinery_dev<>1 do
                                -- 根据device_id_t查询前一天的此设备的料量
                                begin
                                        declare dev_feeding_total int default 0;
                    declare flag_statistics_info int default 0;
                    declare statistics_info_dp cursor for select equi_acc_mat from dev_statistics_info where to_days(CURDATE()) - to_days(record_time) = 1 and device_id = device_id_t;
                                        declare continue handler for not found set flag_statistics_info=1;
                                        open statistics_info_dp;
                    fetch statistics_info_dp into dev_feeding_total;
                    set devs_feeding_total =  devs_feeding_total + dev_feeding_total;
                    set devs_total = devs_total + 1;
                    close statistics_info_dp;
                                end;
                                fetch swinery_dev_dp into device_id_t;
                        end while;
            close swinery_dev_dp;
            -- 实际饲喂曲线中插入
            if devs_total then
                                insert into feeding_reality_chart (guest_id, swinery_id, average_feeding, pig_age) values (guest_id_t, pigpen_id, devs_feeding_total / pig_total, pig_age);
                        end if;
                        set devs_feeding_total = 0;
                        set devs_total = 0;
        end;
                fetch d_p into pigpen_id, pig_age, pig_total, guest_id_t;
        -- 查找这个猪栏下的所有绑定的设备
                -- declare d_p cursor for select id, average_age, feed_num from farm_record where swinery_name is not null;
        end while;
    close d_p;
    -- 更新猪日龄
    update farm_record set average_age = average_age + 1;
END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+