mysql 存储过程（二）

遍历库中所有表, 更新指定字段为指定的值，若指定的字段不存在就更新 id 为 uuid。存储过程相互间的调用。

drop PROCEDURE if exists update_orgCode;
delimiter//
create procedure update_orgCode(IN SEND_SYSTEM VARCHAR(50),IN UPLOAD_ORG_CODE VARCHAR(50),IN PROVIDER_NAME VARCHAR(50))
begin

    DECLARE tablename VARCHAR(30);
        DECLARE columntype VARCHAR(30);
    /* 遍历游标结束标志 */
    DECLARE Done INT DEFAULT 0;
    DECLARE err,err2 INT DEFAULT 0;
    /* 定义预处理sql语句 */
    DECLARE SQL_FOR_UPDATE VARCHAR(500);
    /* 申明游标 */
    DECLARE rs CURSOR FOR select table_name from information_schema.tables where table_schema='test2';
    /* 游标异常处理 */
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/  
    /* update 异常处理1 */
    DECLARE CONTINUE HANDLER FOR 1054 SET err = 1; /*when "Unknown column 'SEND_SYSTEM' in 'field list'" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/ 
    /* update 异常处理2 */
    DECLARE CONTINUE HANDLER FOR 1062 SET err2 = 1; /*[Err] 1062 - Duplicate entry '10839-xianfengYL-xianfengYL' for key 'PRIMARY'" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/ 

    /* 打开游标 */
    open rs;

        /* 获取表名 */
        FETCH rs INTO tablename;

        /* 遍历开始 */
        REPEAT

            SET SQL_FOR_UPDATE = CONCAT("UPDATE ",tablename," SET SEND_SYSTEM = '",SEND_SYSTEM,"',UPLOAD_ORG_CODE='",UPLOAD_ORG_CODE,"',PROVIDER_NAME='",PROVIDER_NAME,"';");   -- 拼接查询sql语句
            SET @sql = SQL_FOR_UPDATE;
            PREPARE stmt FROM @sql;         -- 预处理动态sql语句
            IF err 
                        THEN 
                            SET err = 0;    -- 预处理sql 不成功, 改变 重置 err
                            SELECT DATA_TYPE INTO columntype FROM information_schema.`COLUMNS` where COLUMN_NAME like 'ID' and TABLE_SCHEMA like 'test2' and TABLE_NAME like CONCAT('',tablename);
                            IF columntype = 'varchar'
                            THEN
                                    CALL update_id(tablename);
                            END IF;
            ELSE        -- 预处理 sql 成功, 执行 update sql
                EXECUTE stmt ;                  -- 执行sql语句
                deallocate prepare stmt;      -- 释放prepare
            END IF;

            IF err2 THEN select tablename;set Done = 1;     -- 输出表名,退出遍历
            END IF;

            /* 调试 */
            -- SELECT tablename;

            /* 获取表名 */
            FETCH rs INTO tablename;

        /* 遍历结束 */
        UNTIL Done END REPEAT;

    /* 关闭游标 */
    close rs;
end;
//
delimiter;

drop PROCEDURE if exists update_id;
delimiter//
create procedure update_id(IN tablename VARCHAR(50))
begin

        DECLARE pre_id VARCHAR(50);
    /* 遍历游标结束标志 */
    DECLARE Done INT DEFAULT 0;
    /* 申明游标 */
    DECLARE rs CURSOR FOR select id from dynamic_view;
    /* 游标异常处理 */
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/  

        SELECT tablename;

    DROP VIEW IF EXISTS dynamic_view;
    SET @sqlstr = "CREATE VIEW dynamic_view as ";
    SET @sqlstr = CONCAT(@sqlstr , "SELECT id FROM ", tablename);
    PREPARE stmt FROM @sqlstr;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    /* 打开游标 */
    open rs;

        /* 获取id */
        FETCH rs INTO pre_id;

        /* 遍历开始 */
        REPEAT
                        SET @sqlstr = "update ";
                        SET @sqlstr = CONCAT(@sqlstr , tablename, " set id = UUID() where id = '", pre_id, "'");
                        PREPARE stmt FROM @sqlstr;
                        EXECUTE stmt;
                        DEALLOCATE PREPARE stmt;                        
            /* 获取id */
            FETCH rs INTO pre_id;

        /* 遍历结束 */
        UNTIL Done END REPEAT;

    /* 关闭游标 */
    close rs;

        DROP VIEW IF EXISTS dynamic_view;
end;
//
delimiter;

refrences

mysql动态游标学习(mysql存储过程游标)