mysql 存储过程（一）

最新推荐文章于 2021-10-12 09:14:50 发布

BMY

最新推荐文章于 2021-10-12 09:14:50 发布

阅读量213

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/bai449083657/article/details/52314480

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

遍历库中所有表, 更新指定字段为指定的值。(*有的表存在该字段,有的则不存在)

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 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='ehr10';
    /* 游标异常处理 */
    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
            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;