begin for r in (select a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.DATA_LENGTH,b.comments from user_tab_columns a INNER JOIN user_col_comments b on a.table_name=b.table_name and a.column_name=b.column_name where data_type='NUMBER' and data_precision='20' and data_scale='2' and a.TABLE_NAME not IN('M_TOP_TEN_CREDIT_CUST','A_G15_ALL_RELATION_TXN','A_G15_TOP_TEN_CREDIT_CUST','V_GLF_CUST_INFO_NOREPEAT','A_INT_FEE','A_NONCREDIT_TXN_ALL') AND B.comments NOT IN ('12个月同类交易笔数','关联方期初持有本行的股数','关联方期末持有本行的股数','期初持有银行股数（股）','期末持有银行股数（股）指标值')) LOOP --增加临时新字段 execute immediate 'ALTER table "'|| r.TABLE_NAME ||'" ADD "'|| r.COLUMN_NAME ||'1" NUMBER(18,2)'; --复制内容 execute immediate 'Update "'|| r.TABLE_NAME ||'" Set "'|| r.COLUMN_NAME ||'1" = "'|| r.COLUMN_NAME ||'"'; --清空原有字段值 execute immediate 'Update "'|| r.TABLE_NAME ||'" Set "'|| r.COLUMN_NAME ||'" = null'; --修改原有的字段长度 execute immediate 'ALTER table "'|| r.TABLE_NAME ||'" MODIFY "'|| r.COLUMN_NAME ||'" NUMBER(18,2)'; --把值迁回原有字段 execute immediate 'Update "'|| r.TABLE_NAME ||'" Set "'|| r.COLUMN_NAME ||'" = "'|| r.COLUMN_NAME ||'1"'; --删除新增字段 execute immediate 'ALTER table "'|| r.TABLE_NAME ||'" drop column "'|| r.COLUMN_NAME ||'1"'; end loop; end ;改写成MySQL

在 MySQL 中，可以按照以下方式重写该代码：

BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE tableName VARCHAR(100);
  DECLARE columnName VARCHAR(100);
  DECLARE dataType VARCHAR(100);
  DECLARE dataLength INT;
  DECLARE comments VARCHAR(100);
  DECLARE sql_stmt VARCHAR(1000);
  DECLARE cur CURSOR FOR SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND DATA_TYPE = 'decimal' AND NUMERIC_PRECISION = 20 AND NUMERIC_SCALE = 2 AND TABLE_NAME NOT IN ('M_TOP_TEN_CREDIT_CUST','A_G15_ALL_RELATION_TXN','A_G15_TOP_TEN_CREDIT_CUST','V_GLF_CUST_INFO_NOREPEAT','A_INT_FEE','A_NONCREDIT_TXN_ALL') AND COLUMN_COMMENT NOT IN ('12个月同类交易笔数','关联方期初持有本行的股数','关联方期末持有本行的股数','期初持有银行股数（股）','期末持有银行股数（股）指标值');

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tableName, columnName, dataType, dataLength, comments;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET sql_stmt = CONCAT('ALTER TABLE `', tableName, '` MODIFY COLUMN `', columnName, '` DECIMAL(18,2)');
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET sql_stmt = CONCAT('ALTER TABLE `', tableName, '` ADD COLUMN `', columnName, '1` DECIMAL(18,2)');
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET sql_stmt = CONCAT('UPDATE `', tableName, '` SET `', columnName, '1` = `', columnName, '`');
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET sql_stmt = CONCAT('UPDATE `', tableName, '` SET `', columnName, '` = NULL');
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET sql_stmt = CONCAT('UPDATE `', tableName, '` SET `', columnName, '` = `', columnName, '1`');
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET sql_stmt = CONCAT('ALTER TABLE `', tableName, '` DROP COLUMN `', columnName, '1`');
    PREPARE stmt FROM sql_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END;

需要注意的是，MySQL 中 DECIMAL 数据类型的定义方式与 Oracle 中的 NUMBER 数据类型不同。在 MySQL 中，DECIMAL(18,2) 表示该字段的总长度为 18 位，小数位为 2 位。此外，MySQL 中需要使用反引号来引用表名和字段名。