//Written by Sun Zhenfang 20040903
, U! N% \, E% |# S' P- v! W create or replace procedure pr_CreateIdentityColumn
1 r( @0 n" I3 x (tablename varchar2,columnname varchar2)
N+ S- M8 a2 p/ c: q, H3 N as I* f% G* M5 c3 _0 V0 P
strsql varchar2(1000);
0 ] s( h o# e4 G begin2 q4 j, H6 P' n# L( d) R
strsql := 'create sequence seq_'||tablename||'
( }/ a8 [5 [& }/ f7 P" T% J9 D minvalue 1 maxvalue 999999999999999999
& e, R+ W, D l; l+ Q7 \# z start with 1 increment by 1 nocache';
$ k- x `# t0 i; f) N- w execute immediate strsql;4 q/ G% a3 P w2 @8 Q7 j% J6 x
strsql := 'create or replace trigger trg_'||tablename||'! J9 z x- X, a, q! D! Y. h1 e
before insert on '||tablename||' for each row begin
8 v9 J! r9 R6 @8 M select seq_'||tablename||'.nextval into :new.'||columnname||'7 M: {2 n9 `$ }" W! d# Q" x
from dual; end;';
/ N3 Z- j& b8 }3 D5 U$ {( x execute immediate strsql;
4 F. ]) Y; w0 l5 u2 X2 ]$ h end; 2、Oracle中执行动态SQL时要显示授权(即使该用户拥有该相关权限)! j$ E) R1 K- o
GRANT CREATE ANY SEQUENCE TO "UserName";
& X; M4 J1 o* T- {9 @5 S9 @5 A7 e GRANT CREATE ANY TRIGGER TO "UserName";
* v" K% ^. C1 u( R* _" Z: j+ e& f (注意:数据库用户名区分大小写)
' g; [9 [ a. u& E" G0 S, ^ 3、重新Compile存储过程pr_CreateIdentityColumn
1 }1 H* b% V; W) v6 B, P) _$ P 4、搞定,下面我们就可以用这个存储过程建立自增自段了。
3 ~( s' @" P* [5 F- p 5、调用存储过程建立自增字段(Note: 第一个参数是表名,第二个参数为自增字段的名字)& m7 W' \6 L P# m/ i2 A4 s
exec pr_createidentitycolumn('sdspdept','deptid');% O! E3 T. p8 s" ?, ^& o
exec pr_createidentitycolumn('sdspuser','userid');
: d9 Y7 J8 Q# ?* \; p9 | exec pr_createidentitycolumn('sdspsysrole','sysroleid');- a) `1 ~/ O- j1 X8 n/ T7 T
exec pr_createidentitycolumn('sdspfp','sysfpid'); M& O$ `. _- j$ c8 A
exec pr_createidentitycolumn('sdspphasemodel','phasemodelid');
3 _8 `' p! U% F, [ exec pr_createidentitycolumn('sdspphase','phaseid'); |