存储过程

1. 替换全库某个字段的值

CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateCountyValues`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tableName VARCHAR(255);
    DECLARE colExists INT;
    DECLARE colType VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'lnrf_bus' AND COLUMN_NAME = 'county';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 检查表中是否存在county字段
        SELECT COUNT(*) INTO colExists
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'lnrf_bus'
        AND TABLE_NAME = tableName
        AND COLUMN_NAME = 'county';

        IF colExists = 1 THEN
            -- 获取字段类型
            SELECT DATA_TYPE INTO colType
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = 'lnrf_bus'
            AND TABLE_NAME = tableName
            AND COLUMN_NAME = 'county';

            -- 构造UPDATE SQL语句并执行
            IF colType = 'VARCHAR' THEN
                SET @sql = CONCAT('UPDATE lnrf_bus.', tableName, ' SET county = "6212" WHERE county = "621200"');
            ELSEIF colType = 'INT' THEN
                SET @sql = CONCAT('UPDATE lnrf_bus.', tableName, ' SET county = 6212 WHERE county = 621200');
            END IF;

            IF @sql IS NOT NULL THEN
                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END IF;
        END IF;
    END LOOP;

    CLOSE cur;
END

2. 将字段A的值赋值给字段B

-- 将DMNM 的值赋值给county,并将地区编码最后两位00替换为空，如将621200 替换为 6212
-- 替换库名
DELIMITER //

CREATE PROCEDURE UpdateCountyFromDMNM()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tableName VARCHAR(255);
    
    -- 定义游标，用于遍历所有表
    DECLARE cur CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'hengheng'; -- 替换为您的数据库名称
    
    -- 设置异常处理程序，以处理游标遍历过程中的异常
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO tableName;
        
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        
        -- 使用动态SQL查询检查表是否具有DMNM和county字段
        SET @checkQuery = CONCAT('SELECT COUNT(*) INTO @count FROM information_schema.columns WHERE table_schema = \'hengheng\' AND table_name = ? AND column_name IN (\'DMNM\', \'county\');');
        PREPARE stmt FROM @checkQuery;
        SET @tableName = tableName;
        EXECUTE stmt USING @tableName;
        
        -- 如果DMNM和county字段都存在，则更新county字段
        IF @count = 2 THEN
            SET @updateQuery = CONCAT('UPDATE ', tableName, ' SET county = CASE WHEN RIGHT(DMNM, 2) = \'00\' THEN LEFT(DMNM, LENGTH(DMNM) - 2) ELSE DMNM END WHERE DMNM IS NOT NULL;');
            PREPARE stmt FROM @updateQuery;
            EXECUTE stmt;
        END IF;
        
        DEALLOCATE PREPARE stmt;
    END LOOP;
    
    CLOSE cur;
END;
//

DELIMITER ;