Essentially a version is as follows:

The CREATE proc [batchCreateVoucherNo]
@ CZLX nvarchar (50), - operation type
@ dfgy nvarchar (50), - the other cabinet
@ userid int, - the candidate ID
@ examid int,
@ taskid int,
@ planid int,
@ formid nvarchar (20),
@ banksiteid int
As
Declare @ v_sql nvarchar (Max); No. - proof SQL
Declare @ model_count int.
Select @ model_count=COUNT (*) from tb_daily_voucher where planid=21
Declare @ int I;
The set @ I=1;
While (@ i<=@ model_count)
The begin
Declare @ PZLX nvarchar (50); - proof type
Declare @ QSHM nvarchar (50); - the starting number
Declare @ ZZHM nvarchar (50); - end number
Declare @ PZSL int. - certificate number
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

The 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)) +'); ';
The exec (@ v_sql);
- print @ v_sql;


Declare @ voucher_font nvarchar (50);
The set @ voucher_font=the SUBSTRING (@ QSHM, 0, LEN (@ QSHM) - 5). The first half - proof number

Declare @ v_item_sql nvarchar (Max);
Declare @ start int. - after the initial number six
Declare @ end int. - after termination number six
The set @ start=the SUBSTRING (@ QSHM, LEN (@ QSHM) - 5, 6);
The set @ end=the SUBSTRING (@ ZZHM, LEN (@ ZZHM) - 5, 6);

While (@ start<=@ end)
The begin
The 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 (), ' ' ' 'not used). ';
The exec (@ v_item_sql);
- print @ v_item_sql;
The set @ start=@ start + 1;
End
The set @ I=@ I + 1;
End



GO

CodePudding user response:

Online, etc., consult!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CodePudding user response:

 
The create or replace procedure batchcreatevoucherno 
(
CZLX varchar2 (50), - operation type 
Dfgy varchar2 (50), - the other cabinet 
Userid number, - the candidate ID 
Examid number, 
Taskid number, 
Planid number, 
Formid varchar2 (20), 
Banksiteid number 
) 
Is 
V_sql varchar2 (4000); 
Model_count number; 
V_pzlx varchar2 (50); - proof type 
V_qshm varchar2 (50); - the starting number 
V_zzhm varchar2 (50); - end number 
V_pzsl number; - certificate number 
V_start number; - after the initial number six 
V_end number; - after termination number six 
V_voucher_font varchar2 (50); 
The begin 
Select count (*) into model_count from tb_daily_voucher where planid=21; 

For I in 1.. Model_count loop 
The select PZLX, QSHM ZZHM, PZSL 
Into v_pzlx, v_qshm v_zzhm, v_pzsl 
The from (select row_number () over (order by id) as rownumber, * 
The 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 & lt;=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, 'is not used). 
commit; 

V_start:=v_start + 1; 
End loop; 

End loop; 

End batchcreatevoucherno; 

CodePudding user response:

Take time to change, not easy, hope to adopt!