– 创建临时表存储字段信息和检查结果
CREATE TABLE CHAR_FIELD_CHECK_RESULTS (
TABLE_NAME VARCHAR(128),
COLUMN_NAME VARCHAR(128),
CURRENT_LENGTH INT,
TARGET_LENGTH INT,
MAX_DATA_LENGTH INT,
CAN_RESIZE CHAR(1) – Y/N
);

–将要更改的模式和表信息插入CHAR_FIELD_CHECK_RESULTS表中
DECLARE
v_schema_name VARCHAR(128) := ‘PAY_14_25’; – 替换为实际模式名
v_table_name VARCHAR(128);
v_column_name VARCHAR(128);
v_current_length INT;
v_target_length INT;
v_max_length INT;
v_sql VARCHAR(2000);

CURSOR char_columns IS
SELECT t.TABLE_NAME, c.COLUMN_NAME, c.CHAR_LENGTH
FROM ALL_TABLES t
JOIN ALL_TAB_COLUMNS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.OWNER = v_schema_name
AND c.DATA_TYPE = ‘CHAR’;
BEGIN
OPEN char_columns;
LOOP
FETCH char_columns INTO v_table_name, v_column_name, v_current_length;
EXIT WHEN char_columns%NOTFOUND;

– 计算目标长度（缩小4倍，至少为1）
v_target_length := GREATEST(1, TRUNC(v_current_length / 4));

– 动态SQL获取实际数据最大长度
/v_sql := ‘SELECT MAX(LENGTH("’ || v_column_name || ‘")) FROM "’ || v_schema_name || ‘"."’ || v_table_name || ‘"’;
EXECUTE IMMEDIATE v_sql INTO v_max_length;/

– 判断是否可以缩小
INSERT INTO CHAR_FIELD_CHECK_RESULTS VALUES (
v_table_name,
v_column_name,
v_current_length,
v_target_length,
v_max_length,
CASE WHEN v_max_length is null THEN ‘Y’ ELSE ‘N’ END
);
END LOOP;
CLOSE char_columns;
COMMIT;
END;

SELECT * FROM CHAR_FIELD_CHECK_RESULTS;

– 生成修改字段的SQL语句
SELECT
‘ALTER TABLE "’ || ‘PAY_14_25’ || ‘"."’ || TABLE_NAME ||
‘" MODIFY "’ || COLUMN_NAME || ‘" CHAR(’ || TARGET_LENGTH || ‘);’ AS SQL_CMD
FROM CHAR_FIELD_CHECK_RESULTS
WHERE CAN_RESIZE = ‘N’;

SELECT * FROM FASP_T_ACCTSYSSOR;