//Written by Sun Zhenfang 20040903
create or replace PRocedure pr_CreateIdentityColumn
(tablename varchar2,columnname varchar2)
as
strsql varchar2(1000);
begin
strsql := 'create sequence seq_'tablename' minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache';
execute immediate strsql;
strsql := 'create or replace trigger trg_'tablename' before insert on 'tablename' for each row begin select seq_'tablename'.nextval into :new.'columnname' from dual; end;';
execute immediate strsql;
end;
2、Oracle中執(zhí)行動(dòng)態(tài)SQL時(shí)要顯示授權(quán)（即使該用戶(hù)擁有該相關(guān)權(quán)限）GRANT CREATE ANY SEQUENCE TO "UserName";
GRANT CREATE ANY TRIGGER TO "UserName";
（注重：數(shù)據(jù)庫(kù)用戶(hù)名區(qū)分大小寫(xiě)）exec pr_createidentitycolumn('sdspdept','deptid');
exec pr_createidentitycolumn('sdspuser','userid');
exec pr_createidentitycolumn('sdspsysrole','sysroleid');
exec pr_createidentitycolumn('sdspfp','sysfpid');
exec pr_createidentitycolumn('sdspphasemodel','phasemodelid');
exec pr_createidentitycolumn('sdspphase','phaseid'); 新聞熱點(diǎn)
疑難解答
圖片精選
