- --创建存储过程
- 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;
- --该片段来自于http://www.codesnippet.cn/detail/290520133646.html
来源: http://www.codesnippet.cn/detail/290520133646.html