存储过程中我使用到了光标,可是他老是说我光标那里出错了,自己检查不出错误来..大家帮忙看看..CREATE PROCEDURE SalesOrder_preUpdate(
v_id NUMERIC(19, 0),
OUT v_returnVal VARCHAR(255),
v_WF_State NUMERIC(19, 0),
v_company_id NUMERIC(19, 0),
v_salesMoney NUMERIC(19, 5),
v_approveDeliveryDt DATETIME,
v_codeNo VARCHAR(70))
SalesOrder_preUpdate:BEGIN
declare v_amount NUMERIC(19, 5);
declare v_product_id NUMERIC(19, 0);
declare v_salesAmount NUMERIC(19, 0);
declare v_productCode VARCHAR(255);
declare v_unit VARCHAR(255);
declare v_product_label VARCHAR(255);
declare v_re VARCHAR(255);
declare v_done NUMERIC(19, 0); if v_WF_State=3 then /*自动扣款,自动生成提货单*/
select v_amount = amount from DYNA_CompanyAccount
where company_id = v_company_id;
if isnull(v_amount) then
set v_returnVal = '{error:true, message:"网店帐户余额为0"}';
leave SalesOrder_preUpdate;
end if;
if v_salesMoney > v_amount then
set v_returnVal = '{error:true, message:"网店帐户余额不足,只有" + v_amount}';
leave SalesOrder_preUpdate;
end if;
update DYNA_CompanyAccount
set amount=amount - v_salesMoney
where company_id = v_company_id;
/*自动生成提货单*/
declare mycursor CURSOR for
select product_id, product_label, salesAmount, unit, productCode, re from DYNA_SalesOrderDetail
where salesOrder_id=v_id;
declare continue handler for not found set v_done = 1;
open mycursor;
myloop:LOOP
fetch mycursor into v_product_id, v_product_label, v_salesAmount, v_unit, v_productCode, v_re;
if v_done=1 then
leave myloop;
end if;
insert into DYNA_LoadingBill(product_id, product_label, amount, unit, productCode, deadline, billType, billNo, billId, re, delivery, qualitycheck)
values(v_product_id, v_product_label, v_salesAmount, v_unit, v_productCode, v_approveDeliveryDt, 3, v_codeNo, v_id, v_re, 0, 0);
end LOOP myloop; close mycursor; end if;
END SalesOrder_preUpdate
v_id NUMERIC(19, 0),
OUT v_returnVal VARCHAR(255),
v_WF_State NUMERIC(19, 0),
v_company_id NUMERIC(19, 0),
v_salesMoney NUMERIC(19, 5),
v_approveDeliveryDt DATETIME,
v_codeNo VARCHAR(70))
SalesOrder_preUpdate:BEGIN
declare v_amount NUMERIC(19, 5);
declare v_product_id NUMERIC(19, 0);
declare v_salesAmount NUMERIC(19, 0);
declare v_productCode VARCHAR(255);
declare v_unit VARCHAR(255);
declare v_product_label VARCHAR(255);
declare v_re VARCHAR(255);
declare v_done NUMERIC(19, 0); if v_WF_State=3 then /*自动扣款,自动生成提货单*/
select v_amount = amount from DYNA_CompanyAccount
where company_id = v_company_id;
if isnull(v_amount) then
set v_returnVal = '{error:true, message:"网店帐户余额为0"}';
leave SalesOrder_preUpdate;
end if;
if v_salesMoney > v_amount then
set v_returnVal = '{error:true, message:"网店帐户余额不足,只有" + v_amount}';
leave SalesOrder_preUpdate;
end if;
update DYNA_CompanyAccount
set amount=amount - v_salesMoney
where company_id = v_company_id;
/*自动生成提货单*/
declare mycursor CURSOR for
select product_id, product_label, salesAmount, unit, productCode, re from DYNA_SalesOrderDetail
where salesOrder_id=v_id;
declare continue handler for not found set v_done = 1;
open mycursor;
myloop:LOOP
fetch mycursor into v_product_id, v_product_label, v_salesAmount, v_unit, v_productCode, v_re;
if v_done=1 then
leave myloop;
end if;
insert into DYNA_LoadingBill(product_id, product_label, amount, unit, productCode, deadline, billType, billNo, billId, re, delivery, qualitycheck)
values(v_product_id, v_product_label, v_salesAmount, v_unit, v_productCode, v_approveDeliveryDt, 3, v_codeNo, v_id, v_re, 0, 0);
end LOOP myloop; close mycursor; end if;
END SalesOrder_preUpdate
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货