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;