Oracle 序列的创建和使用

创建序列
create sequence seq_a minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache;


查询序列
select seq_a.nextval from dual;


为每张表生成对应的序列

--创建存储过程
create or replace procedure p_createseq(tablename in varchar2)
is
strsql varchar2(500);
begin
strsql:='create sequence seq_'||tablename||' minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache';
execute immediate strsql;
end p_createseq;
/

--创建序列
exec p_createseq('t_power');
exec p_createseq('t_roler');
exec p_createseq('t_roler_power');
exec p_createseq('t_department');
exec p_createseq('t_quarters');
exec p_createseq('t_quarters_roler');
exec p_createseq('t_emp');
exec p_createseq('t_require_plan');
exec p_createseq('t_require_minutia');
exec p_createseq('t_require_audit');
exec p_createseq('t_engage');
exec p_createseq('t_home');
exec p_createseq('t_education');
exec p_createseq('t_works');
exec p_createseq('t_skill');
exec p_createseq('t_account');
exec p_createseq('t_licence');
exec p_createseq('t_title');
exec p_createseq('t_remove');
exec p_createseq('t_train');
exec p_createseq('t_pact');
exec p_createseq('t_assess');
exec p_createseq('t_attendance');
exec p_createseq('t_reward_punish');
exec p_createseq('t_dimission');
exec p_createseq('t_emp_roler');
exec p_createseq('t_code');

评论

1 楼 windowshead 2008-08-28  

比较适用,但是我执行存储过程,为什么老是出现这个错误呢,登录的是DBA
SQL> exec p_createseq('bil');
begin p_createseq('bil'); end;
ORA-01031: 权限不足
ORA-06512: 在"SYSTEM.P_CREATESEQ", line 6
ORA-06512: 在line 1