SqlSerVer存储过程转Oracle求各位大神指教！！！！！！

[qq_36039712]

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

[qq_36039712]

在线等，求教！！！！！！！！！！！！！！！！！

[碧水幽幽泉]

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;

[碧水幽幽泉]

抽空改了下，不容易呀，望采纳！