Oracle 利用sequence实现递增

以下代码由PHP站长网 52php.cn网络自互联网

此刻PHP站长网小编把它分享给各人，仅供参考

--建设存储进程 
CREATE OR REPLACE Procedure insert_vsunusedmark 
( VisaKindCode In varchar,StartSerialNo In Varchar,EndSerialNo In Varchar,NumInsert In Varchar,visapre In Varchar,visacode In Varchar,visaname In Varchar,pressbatchno In Varchar,businessno In Varchar,visaamount In NUMBER,usedate In DATE,usertype In Varchar,usercode In Varchar,username In Varchar,visastatus In Varchar,providetimes In NUMBER,recycletimes In NUMBER,verifiedcancelflag In Varchar,beforestatus In Varchar,remark In Varchar,flag In Varchar,comcode In Varchar,usedenddate In DATE,forcecalldays In Varchar,policypremium In NUMBER
) AS strsql varchar2(5000);
v_count int;
Begin
-- 判定sequence是否已存在，存在则删除重建
select count(1) into v_count from All_Sequences a where sequence_name = upper(VisaKindCode);
if v_count > 0 then
execute immediate 'drop sequence '||VisaKindCode;
end if;
-- Create sequence 
strsql := 'create sequence '||VisaKindCode||' 
minvalue '||StartSerialNo||' 
maxvalue '||EndSerialNo||' 
start with '||StartSerialNo||' 
increment by 1 
cache 20'; 
execute immediate strsql; 
strsql := 
'insert into vsunusedmark 
( visaserialno,visapre,visacode,visaname,pressbatchno,businessno,visaamount,usedate,usertype,usercode,username,visastatus,providetimes,recycletimes,verifiedcancelflag,beforestatus,remark,flag,comcode,usedenddate,forcecalldays,policypremium) 
select '||VisaKindCode||'.NEXTVAL,'''||visapre||''','''||visacode||''','''||visaname||''','''||pressbatchno||''','''||businessno||''','''||visaamount||''','''||usedate||''','''||usertype||''','''||usercode||''','''||username||''','''||visastatus||''','''||providetimes||''','''||recycletimes||''','''||verifiedcancelflag||''','''||beforestatus||''','''||remark||''','''||flag||''','''||comcode||''','''||usedenddate||''','''||forcecalldays||''','''||policypremium||'''
from vsunusedmark v where rownum<='||NumInsert||''; 
execute immediate strsql;
exception
WHEN OTHERS THEN
--产生非常,回滚
rollback;
Commit; 
End insert_vsunusedmark;

以上内容由PHP站长网【52php.cn】网络清算供各人参考研究

假如以上内容对您有辅佐，接待保藏、点赞、保举、分享。

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!