数据库字段变为大写

DECLARE
v_owner VARCHAR2(128) := 'YOUR_SCHEMA'; -- 替换为你的模式名
v_table_name VARCHAR2(128);
v_column_name VARCHAR2(128);
v_new_name VARCHAR2(128);
v_sql VARCHAR2(4000);
v_count NUMBER := 0;
v_success NUMBER := 0;
v_failed NUMBER := 0;
v_total NUMBER := 0;
v_commit_batch NUMBER := 10; -- 每处理10个列提交一次

-- 错误日志表不存在时创建
v_table_exists NUMBER;
BEGIN
-- 检查表是否存在
SELECT COUNT(*)
INTO v_table_exists
FROM all_tables
WHERE owner = v_owner
AND table_name = 'COLUMN_RENAME_LOG';

-- 如果日志表不存在，则创建
IF v_table_exists = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE ' || v_owner || '.COLUMN_RENAME_LOG (
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
table_name VARCHAR2(128),
old_column VARCHAR2(128),
new_column VARCHAR2(128),
status VARCHAR2(20),
error_message VARCHAR2(4000),
execution_time TIMESTAMP DEFAULT SYSTIMESTAMP
)';
END IF;

-- 清空日志表
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || v_owner || '.COLUMN_RENAME_LOG';

-- 收集需要重命名的列
FOR table_rec IN (
SELECT table_name
FROM all_tables
WHERE owner = v_owner
) LOOP
BEGIN
v_table_name := table_rec.table_name;

FOR column_rec IN (
SELECT column_name
FROM all_tab_columns
WHERE owner = v_owner
AND table_name = v_table_name
AND column_name != UPPER(column_name)
) LOOP
BEGIN
v_column_name := column_rec.column_name;
v_new_name := UPPER(v_column_name);
v_count := v_count + 1;

-- 构建重命名列的SQL语句
v_sql := 'ALTER TABLE "' || v_owner || '"."' || v_table_name || '" ' ||
'RENAME COLUMN "' || v_column_name || '" TO ' || v_new_name;

-- 输出当前处理的列
DBMS_OUTPUT.PUT_LINE('处理: ' || v_table_name || '.' || v_column_name || ' -> ' || v_new_name);

-- 执行SQL语句
EXECUTE IMMEDIATE v_sql;

-- 记录成功
v_success := v_success + 1;
EXECUTE IMMEDIATE 'INSERT INTO ' || v_owner || '.COLUMN_RENAME_LOG
(table_name, old_column, new_column, status)
VALUES (''' || v_table_name || ''', ''' || v_column_name || ''', ''' || v_new_name || ''', ''SUCCESS'')';

-- 分批提交
IF MOD(v_count, v_commit_batch) = 0 THEN
DBMS_OUTPUT.PUT_LINE('已处理 ' || v_count || ' 个列，提交中...');
COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
v_failed := v_failed + 1;
DBMS_OUTPUT.PUT_LINE('错误: 无法重命名表 "' || v_table_name || '" 的列 "' || v_column_name || '"');
DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);

-- 记录失败
EXECUTE IMMEDIATE 'INSERT INTO ' || v_owner || '.COLUMN_RENAME_LOG
(table_name, old_column, new_column, status, error_message)
VALUES (''' || v_table_name || ''', ''' || v_column_name || ''', ''' || v_new_name || ''', ''FAILED'', ''' || REPLACE(SQLERRM, '''', '''''') || ''')';
END;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('严重错误: 无法处理表 "' || v_table_name || '"');
DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
END;
END LOOP;

-- 提交最终更改
COMMIT;

-- 输出统计结果
DBMS_OUTPUT.PUT_LINE('====================================');
DBMS_OUTPUT.PUT_LINE('操作完成!');
DBMS_OUTPUT.PUT_LINE('总共尝试重命名: ' || v_count || ' 个列');
DBMS_OUTPUT.PUT_LINE('成功: ' || v_success || ' 个列');
DBMS_OUTPUT.PUT_LINE('失败: ' || v_failed || ' 个列');
DBMS_OUTPUT.PUT_LINE('====================================');
DBMS_OUTPUT.PUT_LINE('查看详细日志:');
DBMS_OUTPUT.PUT_LINE('SELECT * FROM ' || v_owner || '.COLUMN_RENAME_LOG ORDER BY id;');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('致命错误: ' || SQLERRM);
ROLLBACK;
END;