CREATE PROCEDURE `addTicket`(in vipsql VARCHAR(255),in ordersql VARCHAR(255),in detailkey varchar(255),in detailsql VARCHAR(255)) comment '挂单（售药窗口）'
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ,NOT FOUND
rollback;
start transaction;
set @vid=@oid='';
set @outstr='';
set @errcode=200;
set @errmsg='成功';
if vipsql=0 then
set @tem=vipsql;
prepare tem from @tem;
execute tem;
set @rows=row_count();
if @rows<=0 then
set @errcode=501;
set @errmsg='插入到【会员表】失败';
rollback;
end if;
set @vid=LAST_INSERT_ID();
else
set @vid=vipsql;
end if;
select order_outpatient_num into @cnum from `order` order by order_outpatient_num desc limit 1;
if locate(curdate()+0,@cnum)=0||@cnum='' then
set @cnum=concat(curdate()+0,'00000001');
else
set @cnum=@cnum+1;
end if;
set ordersql=concat(ordersql,"'",@vid,"','",@cnum,"')");
set @ordersql=ordersql;
prepare temp from @ordersql;
execute temp;
set @rows=row_count();
if @rows<=0 then
set @errcode=502;
set @errmsg='插入到【订单表】失败';
rollback;
end if;
set @oid=LAST_INSERT_ID();
set @num=(length(detailsql)-length(replace(detailsql,'*#*','')))/3+1;
set @i=1;
while @i<=@num do
if @i=1 then
set @strsql=substring_index(detailsql,'*#*',@i);
else
set @strsql=substring_index(detailsql,'*#*',@i);
set @strsql=substring_index(@strsql,'*#*',-1);
end if;
set @outstr=concat(@outstr,@strsql,"'",@cnum,"','",@oid,"'),");
set @i=@i+1;
end while;
set @outstr=concat(detailkey,@outstr);
set @outstr=left(@outstr,char_length(@outstr)-1);
prepare temp0 from @outstr;
execute temp0;
set @rows=row_count();
if @rows<=0 then
set @errcode=503;
set @errmsg='插入到【订单详情表】失败';
rollback;
end if;
commit;
select @errcode as errcode,@errmsg as errmsg;
END
本文转自 Lee_吉 博客，原文链接： http://blog.51cto.com/12173069/1960356 如需转载请自行联系原作者
