存储过程定义语法
发布时间：2022-06-30 13:55:51 所属栏目：MySql教程 来源：互联网
导读：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 transacti
|
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 （编辑：航空爱好网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐