Mysql 游标示例

CREATE  PROCEDURE `gk_test`()

COMMENT '测试数据'  -- 函数注释

BEGIN

    DECLARE done INT DEFAULT FALSE; -- 定义遍历数据结束标志。非游标变量，需要定义在游标变量前

    DECLARE m_id CHAR(32); -- 定义接收游标数据中id变量

    DECLARE m_or VARCHAR(255); -- 定义接收游标数据中orderno变量

    DECLARE m_c INT(11);  -- 定义接收游标数据中count变量

    DECLARE m_soult CURSOR FOR SELECT `id`, `orderno`, count(*) t FROM hb_shipper_order GROUP BY orderno HAVING t >1;   -- 定义游标变量，即接收查询结果集

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 将结束标志绑定到游标

    OPEN m_soult;    -- 打开游标

    read_loop:LOOP   -- 开始循环。这个是mysql 3种循环中的1种。

        FETCH m_soult INTO m_id,m_or,m_c;   -- 提取游标数据

        IF done THEN   -- 判断，当为true时

            LEAVE read_loop;   -- 跳出循环

        END IF;

        -- 处理事 自己想处理的事

        UPDATE `hb_shipper_waybill` SET

        ordernos = REPLACE (`ordernos`, m_or, CONCAT(m_or,'-2')),

        goodsname = REPLACE (`goodsname`, m_or, CONCAT(m_or,'-2'))

        WHERE `id` IN ( SELECT `waybillid` FROM `hb_shipper_order_waybill_relation` WHERE orderid = m_id);

        UPDATE `hb_shipper_order` SET orderno = CONCAT(m_or,'-2') WHERE id = m_id;

    END LOOP;  -- 结束循环

    -- 关闭游标

    CLOSE m_soult;

END

上图：

DEMO: 2    // 通过 存储过程，处理  copy 数据并处理对应数据关系

delimiter $$
DROP PROCEDURE IF EXISTS gk;
CREATE DEFINER =  PROCEDURE `gk`(IN pname varchar(255))
    COMMENT '测试'
    BEGIN
        DECLARE new_id CHAR(32);
        DECLARE m_id CHAR(32);
        DECLARE new_noids VARCHAR(255);
        DECLARE done INT DEFAULT 0;
        DECLARE m_result CURSOR FOR select id from `xxx`;
        DECLARE m_result2 CURSOR FOR select id from `xxx`;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        DROP TABLE IF EXISTS `xxx`;
        CREATE TABLE `xxx` (
                `id` char(32) NOT NULL COMMENT 'ID',
                `subsystem` char(32) NOT NULL COMMENT '',
                `name` varchar(20) NOT NULL COMMENT '',
                `type` varchar(15) NOT NULL COMMENT '',
                `url` varchar(200) DEFAULT NULL COMMENT '',
                `icon` varchar(20) DEFAULT NULL COMMENT '',
                `target` varchar(10) NOT NULL DEFAULT '_self' COMMENT '',
                `method` mediumtext COMMENT '',
                `orderby` smallint(3) NOT NULL DEFAULT '0' COMMENT '',
                `parentid` char(32) NOT NULL DEFAULT '0' COMMENT '',
                `relateid` char(32) NOT NULL COMMENT '',
                `isopen` tinyint(1) NOT NULL DEFAULT '1' COMMENT '',
                `nodeids` varchar(255) DEFAULT NULL COMMENT '',
                `nodenum` smallint(3) NOT NULL DEFAULT '0' COMMENT '',
                `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
                `createuser` char(32) NOT NULL DEFAULT '0' COMMENT '',
                `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '',
                `udateuser` char(32) NOT NULL DEFAULT '0' COMMENT '',
                `deleted` tinyint(1) DEFAULT '0' COMMENT '',
                `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '',
                `isdefault` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '',
                `isdisable` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '',
                `projecttype` varchar(255) NOT NULL COMMENT '',
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
        INSERT INTO `xxxx` SELECT * FROM `aaa` WHERE `projecttype` = 'T';
        UPDATE `xxx` SET `projecttype` = pname;
        OPEN m_result;
        REPEAT
                FETCH m_result INTO m_id;
                        SET new_id = UPPER(REPLACE(UUID(),'-',''));
                        UPDATE `xxx` SET `id` = new_id WHERE `id` = m_id;
                        UPDATE `xxx` SET `parentid` = new_id WHERE `parentid` = m_id;
        UNTIL done END REPEAT;
        CLOSE m_result;
        SET done = 0;
        OPEN m_result2;
        REPEAT
                FETCH m_result2 INTO m_id;
                        SELECT CONCAT(',', GROUP_CONCAT(`id`)) INTO new_noids FROM `xxx` WHERE `parentid` = m_id;
                        UPDATE `xxx` SET `nodeids` = new_noids WHERE `id` = m_id;
        UNTIL done END REPEAT;    
        CLOSE m_result2;
        INSERT INTO `aaa` SELECT * FROM `xxx`;
        DROP TABLE IF EXISTS xxx;
    END $$
delimiter ;