sequence避免主键唯一约束

---为某个新加的表创建序列
create sequence S_ESP_N
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

---使用序列
SELECT empseq.currval FROM DUAL;
SELECT empseq.Nextval FROM DUAL;

---为用户授予创建序列的权限
GRANT CREATE ANY sequence TO OAIS

---使用存储过程为已存在数据ESP_N的表创建序列
create or replace procedure createS(a out number) is
cursor structureIdsCur is SELECT distinct(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE

(table_name,'_CHILDREN',''),'ESP_',''),'_PARENT',''),'_RULE_CONTENT',''),'_RULE_PACKAGE',''),'_TAMP','')) as id FROM USER_TABLES WHERE TABLE_NAME LIKE 'ESP_%' ;
oneId structureIdsCur%rowtype ;
tablename varchar2(100);
str varchar2(100);
strsql varchar2(500);
stat number ;
cou number ;
begin
for oneId in structureIdsCur loop
tablename:= 'esp_' || oneId.id ;
str:= 'select count(*) as int from tab where tname=upper('''|| tablename||''')' ;
dbms_output.put_line(str);
execute immediate str into cou ;
if cou>0 then
begin
strsql:= 'select nvl(max(id),1) from ' || tablename ;
--dbms_output.put_line(strsql);
execute immediate strsql into stat ;
strsql:='create sequence seq_' || tablename || ' minvalue 1 maxvalue 999999999999999999999999999 start with '|| stat ||' increment by 1 nocache';
--dbms_output.put_line(strsql);
execute immediate strsql;
end;
end if ;
end loop ;

end createS;