oracle表名和列名改成小写,【Oracle】表名/列名，大/小写转换

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;

标签：begin,end,cn,列名,tablename,VARCHAR2,表名,Oracle,table

来源： https://www.cnblogs.com/xiaostudy/p/14240256.html