批量修改数据表字段结构，批量查询相同数据类型的表

一，查询数据库中拥有相同类型的表

-- 批量查询有相同类型的表
SELECT
    TABLE_SCHEMA AS '库名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '列名',
    ORDINAL_POSITION AS '列的排列顺序',
    COLUMN_DEFAULT AS '默认值',
    IS_NULLABLE AS '是否为空',
    DATA_TYPE AS '数据类型',
    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    NUMERIC_PRECISION AS '数值精度(最大位数)',
    NUMERIC_SCALE AS '小数精度',
    COLUMN_TYPE AS 列类型,
    COLUMN_KEY 'KEY',
    EXTRA AS '额外说明',
    COLUMN_COMMENT AS '注释'
FROM
    information_schema.`COLUMNS`
WHERE
    TABLE_SCHEMA = 'test_table'
    and DATA_TYPE = 'char'
		AND CHARACTER_MAXIMUM_LENGTH<2
AND COLUMN_NAME != 'del_flag'

ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

二，批量修改数据库中的某个字段

DECLARE sqlS VARCHAR (32760) ;
BEGIN
	FOR table_name IN (
		SELECT
			table_name
		FROM
			user_tab_columns
		WHERE
			column_name = 'DEL_FLAG'
	) 
		loop 
			sqlS := (
					'alter table PROS_GUIZ.' || table_name.TABLE_NAME || ' modify DEL_FLAG CHAR(1)'
					) ;

			EXECUTE IMMEDIATE sqlS ;
			dbms_output.put_line (sqlS) ;
		END loop ; 
		dbms_output.put_line ('更新结束！') ;
END ;