SqlServer版如下：
CREATE proc [batchCreateVoucherNo]
@czlx nvarchar(50),--操作型別
@dfgy nvarchar(50),--對方柜員
@userid int,--考生ID
@examid int,
@taskid int,
@planid int,
@formid nvarchar(20),
@banksiteid int
as
declare @v_sql nvarchar(max);--憑證號sql
declare @model_count int;
select @model_count=COUNT(*) from tb_daily_voucher where planid = 21
declare @i int;
set @i=1;
while(@i<=@model_count)
begin
declare @pzlx nvarchar(50);--憑證型別
declare @qshm nvarchar(50);--起始號碼
declare @zzhm nvarchar(50);--終止號碼
declare @pzsl int;--憑證數量
select @pzlx=pzlx,@qshm=qshm,@zzhm=zzhm,@pzsl=pzsl from(select ROW_NUMBER() over(order by id) as rownumber, * from tb_daily_voucher where planid = 21) temp where temp.rownumber=@i
set @v_sql= 'insert into yw_050504(sle_czlx,sle_dfgy,sle_pzzl,txt_qshm,txt_zzhm,txt_pzsl,Operator,AddTime,UserId,ExamId,TaskId,PlanId) values (''' + @czlx + ''',''' + @dfgy + ''','''+@pzlx+''','''+@qshm+''','''+@zzhm+''','+ CAST(@pzsl as nvarchar(10))+',null,GETDATE(),'+ CAST( @userid as nvarchar(10))+','+CAST( @examid as nvarchar(10))+','+CAST( @taskid as nvarchar(10))+','+CAST( @planid as nvarchar(10))+');';
exec(@v_sql);
--print @v_sql;
declare @voucher_font nvarchar(50);
set @voucher_font = SUBSTRING(@qshm,0,LEN(@qshm)-5);--憑證號碼前半部分
declare @v_item_sql nvarchar(max);
declare @start int;--起始號碼后6位
declare @end int; --終止號碼后6位
set @start=SUBSTRING(@qshm,LEN(@qshm)-5,6);
set @end=SUBSTRING(@zzhm,LEN(@zzhm)-5,6);
while(@start<=@end)
begin
set @v_item_sql = 'insert into zhyw_FormVoucherNo(FormId,VoucherType,VoucherNo,BankSiteId,UserId,PlanId,TaskId,ExamId,CreateDate,[Status]) values('''+@formid+''','''+@pzlx+''','''+@voucher_font+right(replicate('0',20)+ltrim(CAST(@start as nvarchar(10))),6)+''','+CAST(@banksiteid as nvarchar(10))+','+CAST(@userid as nvarchar(10))+','+CAST(@planid as nvarchar(10))+','+CAST(@taskid as nvarchar(10))+','+CAST(@examid as nvarchar(10))+',GETDATE(),''未使用'');';
exec(@v_item_sql);
--print @v_item_sql;
set @start=@start+1;
end
set @i=@i+1;
end
GO
uj5u.com熱心網友回復：
在線等，求教！！！！！！！！！！！！！！！！！uj5u.com熱心網友回復：
create or replace procedure batchcreatevoucherno
(
czlx varchar2(50),--操作型別
dfgy varchar2(50),--對方柜員
userid number, --考生ID
examid number,
taskid number,
planid number,
formid varchar2(20),
banksiteid number
)
is
v_sql varchar2(4000);
model_count number;
v_pzlx varchar2(50); --憑證型別
v_qshm varchar2(50); --起始號碼
v_zzhm varchar2(50); --終止號碼
v_pzsl number; --憑證數量
v_start number; --起始號碼后6位
v_end number; --終止號碼后6位
v_voucher_font varchar2(50);
begin
select count(*) into model_count from tb_daily_voucher where planid = 21;
for i in 1..model_count loop
select pzlx,qshm,zzhm,pzsl
into v_pzlx,v_qshm,v_zzhm,v_pzsl
from(select row_number() over(order by id) as rownumber, *
from tb_daily_voucher
where planid = 21
) temp
where temp.rownumber = i;
insert into yw_050504(sle_czlx,sle_dfgy,sle_pzzl,txt_qshm,txt_zzhm,txt_pzsl,operator,addtime,userid,examid,taskid,planid)
values(czlx,dfgy,v_pzlx,v_qshm,v_zzhm,v_pzsl,null,sysdate,userid,examid,taskid,planid);
commit;
v_voucher_font := substr(v_qshm,length(v_qshm)-5);
v_start := substr(v_qshm,length(v_qshm)-5,6);
v_end := substr(v_zzhm,length(v_zzhm)-5,6);
while (v_start<= v_end) loop
insert into zhyw_formvoucherno(formid,vouchertype,voucherno,banksiteid,userid,planid,taskid,examid,createdate,status)
values(formid,v_pzlx,v_voucher_font,'00000000000000000000',ltrim(v_start),banksiteid,userid,planid,taskid,examid,sysdate,'未使用');
commit;
v_start := v_start+1;
end loop;
end loop;
end batchcreatevoucherno;
uj5u.com熱心網友回復：
抽空改了下，不容易呀，望采納！轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/81506.html
標籤：開發
上一篇：請教一個sql問題