1、表名，小写转大写。如下：my_test转换MY_TEST
DECLARE
v_tablename_x VARCHAR2(20);
v_tablename_d VARCHAR2(20);
begin
v_tablename_x:=‘my_test‘;
v_tablename_d:=‘MY_TEST‘;
execute immediate ‘alter table "‘||v_tablename_x||‘" rename to ‘||v_tablename_d||‘‘;
exception
when others then
dbms_output.put_line(v_tablename_d||‘已存在‘);
end;
2、列名，小写转大写
DECLARE
v_tablename_d VARCHAR2(20);
begin
v_tablename_d:=‘MY_TEST‘;
for c in (select COLUMN_NAME cn from all_tab_columns where table_name=v_tablename_d) loop
begin
execute immediate ‘alter table "‘||v_tablename_d||‘" rename column "‘||c.cn||‘" to ‘||c.cn;
exception
when others then
dbms_output.put_line(v_tablename_d||‘.‘||c.cn||‘已经存在‘);
end;
end loop;
end;
3、表和列一起，小写转大写
DECLARE
v_tablename_x VARCHAR2(20);
v_tablename_d VARCHAR2(20);
begin
v_tablename_x:=‘my_test‘;
v_tablename_d:=‘MY_TEST‘;
for c in (select COLUMN_NAME cn from all_tab_columns where table_name=v_tablename_x) loop
begin
execute immediate ‘alter table "‘||v_tablename_x||‘" rename column "‘||c.cn||‘" to ‘||c.cn;
exception
when others then
dbms_output.put_line(v_tablename_x||‘.‘||c.cn||‘已经存在‘);
end;
end loop;
execute immediate ‘alter table "‘||v_tablename_x||‘" rename to ‘||v_tablename_d||‘‘;
exception
when others then
dbms_output.put_line(v_tablename_d||‘已存在‘);
end;
4、表和列一起，大写转小写
DECLARE
v_tablename_x VARCHAR2(20);
v_tablename_d VARCHAR2(20);
begin
v_tablename_x:=‘my_test‘;
v_tablename_d:=‘MY_TEST‘;
for c in (select COLUMN_NAME cn from all_tab_columns where table_name=v_tablename_d) loop
begin
execute immediate ‘alter table ‘||v_tablename_d||‘ rename column ‘||c.cn||‘ to "‘||LOWER(c.cn)||‘"‘;
exception
when others then
dbms_output.put_line(v_tablename_d||‘.‘||c.cn||‘已经存在‘);
end;
end loop;
execute immediate ‘alter table ‘||v_tablename_d||‘ rename to "‘||v_tablename_x||‘"‘;
exception
when others then
dbms_output.put_line(v_tablename_x||‘已存在‘);
end;
5、列名，大写转小写
DECLARE
v_tablename_d VARCHAR2(20);
begin
v_tablename_d:=‘MY_TEST‘;
for c in (select COLUMN_NAME cn from all_tab_columns where table_name=v_tablename_d) loop
begin
execute immediate ‘alter table ‘||v_tablename_d||‘ rename column ‘||c.cn||‘ to "‘||LOWER(c.cn)||‘"‘;
exception
when others then
dbms_output.put_line(v_tablename_d||‘.‘||c.cn||‘已经存在‘);
end;
end loop;
end;
6、表名，大写转小写
DECLARE
v_tablename_x VARCHAR2(20);
v_tablename_d VARCHAR2(20);
begin
v_tablename_x:=‘my_test‘;
v_tablename_d:=‘MY_TEST‘;
execute immediate ‘alter table ‘||v_tablename_d||‘ rename to "‘||v_tablename_x||‘"‘;
exception
when others then
dbms_output.put_line(v_tablename_x||‘已存在‘);
end;
【Oracle】表名/列名，大/小写转换