oracle将表名和字段名变为大写, ?8 d9 e1 x/ H( J' T! B4 f
5 h1 s: v4 a1 Z9 a5 n# a# }
当使用powerdesigner创建数据库时要注意大小写。
. d, o+ l$ V7 G& c9 h3 f; j , r* V, z1 F9 _4 F
注:以下脚本在oracle 10g,11g上正确执行
[3 F' l3 K3 ?6 T. v ) U2 q& I& k& Q
批量将表名变为大写8 [( f6 X7 T& g% L8 Z3 R
E6 y2 c0 Y) s! u: s9 H1 ^
begin. d9 f( B, B) W+ F) h; V
& {0 C6 m$ D; |! g for c in (select table_name tn from user_tables where table_name upper(table_name)) loop
0 E" Z5 v( t$ b$ G$ i 0 s% j! O4 q U {, K
begin
6 r7 L% r; K- M/ e" G+ \% x* R
! m! j. W6 Z* p$ k! r execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;) x5 g$ G: N0 }: g% a
7 F% ~: s; B9 n' I J) D4 D5 p
exception T @+ C( y9 X; O& N. `$ @
( Y5 n, Z2 x" V2 R% J) s- S when others then
3 p# g. _- R0 W" C% U
% z6 Y. `: s9 Q1 t+ | U dbms_output.put_line(c.tn||'已存在');3 h) H1 O+ w t k4 r8 y
) L* R0 B$ e: C J+ c
end;& G+ l+ B4 G/ z2 |' h8 M' W
4 k& n! m8 t* P' u5 x& k end loop;$ H+ M& m/ x2 s$ |- ~% G4 F
3 [$ j7 {8 d x* R end;7 ~# K3 [/ E" x& B4 K! G. b
* d" R/ A: O* X0 e 批量将空间内所有表的所有字段名变成大写
) Y% P! K L) G, F% Z0 t
$ B8 y" K* C" _5 C begin
& J$ u0 Z2 C4 t j" ~# d+ ~& X * Y2 k; t8 H' Z" N* Q+ E$ t( x
for t in (select table_name tn from user_tables) loop# X- @- C7 ]- P
3 d( p, J8 ^' |/ A$ W/ H% O7 z begin
- g% l9 I% D3 j5 v4 n" n+ z
5 _, y1 K: L0 u for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop/ \* Q# x, k6 @7 [
0 O9 M7 n5 d. o9 |: g begin$ |' a- N6 S4 n* T5 {- ^
; a1 U8 B a7 Z9 T; P execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn;
: ] ]- J% v8 D. P3 F1 c# R; ` & c, N, c M5 ^, ^6 f) w; A# @& O- F
exception
% g9 k6 w0 @4 T0 q4 t+ t% ? & }4 H2 x. @. j/ }
when others then5 P/ `4 G" `6 [! M' Q1 @2 B& a
F4 X' p5 a2 i9 B5 p ~4 R dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');
2 z6 J% p3 y; |8 O # V) J6 m& P7 k9 _: n. d
end;
2 b. a# \" f2 m* ~
% @3 c0 ?0 ^$ M5 |; {* O end loop;/ n: { r$ M! x
2 W! O5 q5 G! k+ { C0 t
end;5 L0 P& o$ J5 b9 |7 I: X& n
- ?/ J. [. t5 J
end loop;
% f+ b# t1 \' ^5 l2 Z& B( | 0 a8 e! X3 o P4 k
end;3 H; w* e% a2 T! y$ d# {0 v
+ D) Z2 S9 p6 ` G0 A- K 将用户空间的所有表名及所有字段变为大写
! m, c7 x9 ], @/ o4 D. w + ^0 Y- W3 `" B0 H. O
begin
4 C; }& |/ C8 {; Y
. `) W2 R' K2 q# l+ m- ] for t in (select table_name tn from user_tables where table_name upper(table_name)) loop
% u. v. l0 v1 ^' C# r. q & D$ r( U) w: W5 ~2 G
begin
$ c4 u7 ?1 k+ `+ a; c5 ~* F - ?9 b3 G% B5 u# u
for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop
* V3 d) v w7 ]0 X5 |
- ~7 S: z g7 l% S. B begin6 y% ~2 k# [" ]
. e0 R! v8 k# v# P$ M [
execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn; Y) P$ c" _( N% J. m
4 B. ~9 G& t6 \
exception$ D, ]3 D0 i+ L: E+ E
- E. s- Z9 y* c* c
when others then6 s2 b( E$ ~. F( R
, p9 n C8 J% d3 y dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');
# y0 \# @: M8 Y( c* z
8 P1 T( F0 ^8 m+ {0 j/ C. i( Q end;* \" c* I, y1 e
: f# i# _+ T6 P( M# v; k }+ C$ y& p& H
end loop;
3 O" A R) a0 \1 e7 c4 r
% E3 E# U& w( i4 g3 q. p) J execute immediate 'alter table "'||t.tn||'" rename to '||t.tn;
2 }/ g/ X# K* I2 o$ O
/ x% O! j- M6 |( g0 J7 r" b& A W exception
2 Y# h' U& o" m* L$ }, W
5 j% ?# ~: m5 M when others then" C* w7 [; i5 [
2 J( Q$ z3 Q m$ N& f dbms_output.put_line(t.tn||'已存在');8 \, R( w1 @- j! Y
% v4 L' }: _0 u& V
end;
% x8 X8 ^* a8 x
1 y+ ?1 o% R$ v l; K- H end loop;
; D; u8 P1 R% r2 K; \, B
" `1 U: |5 d4 D5 p4 f0 _0 _/ L$ @" e$ E end; |