存储过程的作用是产生单据号码,如下:
create procedure p_ins_no
(OprSign varchar(2),DbSign varchar(2),InsName varchar(40),OUT BillId varchar(16))
begin
declare InsNumber varchar(8);
declare InsSign varchar(2);
select Ins_Number, Ins_Id into InsNumber,InsSign from mobile.autoins where Ins_Name =InsName ;
set InsNumber = lpad(CAST(InsNumber+1 AS char),8,'0');
set BillId=concat(InsSign, InsNumber,OprSign,DbSign);
update autoins set Ins_Number = InsNumber where Ins_Name = InsName;end;
想在select查询中为每行添加一个单据号码插入到一个新表中,实现的效果为:
insert into table1(xxx,xxx) select call p_ins_no(xx,xx,xx,xx),其它列 from table2 where xxxxx.....
create procedure p_ins_no
(OprSign varchar(2),DbSign varchar(2),InsName varchar(40),OUT BillId varchar(16))
begin
declare InsNumber varchar(8);
declare InsSign varchar(2);
select Ins_Number, Ins_Id into InsNumber,InsSign from mobile.autoins where Ins_Name =InsName ;
set InsNumber = lpad(CAST(InsNumber+1 AS char),8,'0');
set BillId=concat(InsSign, InsNumber,OprSign,DbSign);
update autoins set Ins_Number = InsNumber where Ins_Name = InsName;end;
想在select查询中为每行添加一个单据号码插入到一个新表中,实现的效果为:
insert into table1(xxx,xxx) select call p_ins_no(xx,xx,xx,xx),其它列 from table2 where xxxxx.....
然后在插入时用insert into table1(xxx,xxx) select @xx,其它列 from table2 where xxxxx.....也就是说要先执行存储过程。
MySql中有没类似的方法