execute immediate 改mysql

这是一个Python中的字符串变量，其中包含一个十六进制编码，想要使用它需要先将其解码。该字符串看起来像是一条修改MySQL表结构的SQL语句，使用execute immediate可以直接执行该语句。

相关问题

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 中需要使用反引号来引用表名和字段名。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE sdle9 SET READ_COMMI' at line 1

It seems like you are trying to execute multiple SQL statements at once. The first statement is incomplete and the second statement is not properly separated from the first one.

To fix this error, you should separate each SQL statement with a semicolon (;) and execute them one by one. Here is an example:

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE sdle9 SET READ_COMMITTED_SNAPSHOT ON;

This will execute the first statement to set the database in single-user mode and roll back any active transactions. Then, the second statement will set the database to use the read committed snapshot isolation level.