56,687
社区成员
发帖
与我相关
我的任务
分享
CREATE DEFINER=`jusr6dvq8jji`@`%` PROCEDURE `JLTB_EXEC_XSDDSP`()
BEGIN
#Routine body goes here...
DECLARE v_GSID Varchar(4);
DECLARE v_SPSL DECIMAL(16, 4); #金力商品数量
DECLARE v_SPXX02 Varchar(30);
DECLARE v_SPXX04 Varchar(100);
DECLARE V_CK01 Varchar(16);
DECLARE v_IFLAG INT(8);
DECLARE v_JQM INT(8);
DECLARE V_WSDJ DECIMAL(16, 4);
DECLARE V_TZJE DECIMAL(16, 4);
DECLARE V_YHJE DECIMAL(16, 4);
DECLARE V_XSZJ DECIMAL(16, 4);
DECLARE v_YYSPSL DECIMAL(16, 4);
DECLARE v_FFTT VARCHAR(1000);
DECLARE V_FWSPXX01 DECIMAL(16);
DECLARE V_WLBBJ INT(8);
DECLARE V_SSKH DECIMAL(16);
DECLARE v_FLAG int;
DECLARE v_SPZL int;
DECLARE isRecordNotFound bool default 1;
/*声明c_XSDDITEM游标内变量*/
DECLARE V_XSDD03 VARCHAR(100);
DECLARE V_XSDD02 VARCHAR(100);
DECLARE V_XSDD01 VARCHAR(50);
DECLARE V_XSDDI01 VARCHAR(50);
DECLARE V_SL DECIMAL(16,4);
DECLARE V_XSDD34 VARCHAR(50);
DECLARE V_TBSPID DECIMAL(16);
DECLARE V_XSDDI10 DECIMAL(16,4);
DECLARE V_XSDD18 VARCHAR(100);
DECLARE V_C_SPXX02 VARCHAR(100);
DECLARE V_XSDDI03 int(8);
DECLARE V_SGTZJE DECIMAL(16,4);
DECLARE V_C_YHJE DECIMAL(16,4);
DECLARE V_XSDDI05 DECIMAL(16,4);
DECLARE V_SKUID VARCHAR(50);
DECLARE V_SSTATUS VARCHAR(100);
DECLARE V_FPTT VARCHAR(100);
DECLARE V_DP VARCHAR(100);
/*声明c_JLTBSPXX游标变量*/
DECLARE V_SPXX01 DECIMAL(16);
DECLARE v_SPGGITEM01 DECIMAL(16,4);
DECLARE V_ZSPBJ int(8);
DECLARE V_SPGGITEM03 int(8);
DECLARE V_JG DECIMAL(16,4);
/*声明c_CK游标变量*/
DECLARE c_CK01 VARCHAR(16);
DECLARE c_DSKH01 int(8);
/*声明游标 */
DECLARE c_XSDDITEM CURSOR FOR Select A.XSDD03, A.XSDD02, B.XSDD01, B.XSDDI01, B.XSDDI05 SL, A.XSDD34,
B.XSDDI06 TBSPID, A.XSDD18 XSDD18, B.XSDDI20 as SPXX02,
B.XSDDI10, B.XSDDI03, B.XSDDI02 as SGTZJE, B.XSDDI04 as YHJE,
B.XSDDI05, B.XSDDI23 SKUID, A.XSDD36 as SSTATUS,
C.XSDDZ47 as FPTT
From JLTB_DOWN_XSDD A, JLTB_DOWN_XSDDITEM B, JLTB_DOWN_XSDDZT C
Where A.XSDD01 = B.XSDD01
And A.XSDD01 = C.XSDD01
AND A.LYLX = B.LYLX
AND A.LYLX = C.LYLX
AND (A.XSDD02 <> 'WAIT_BUYER_PAY')
AND (A.XSDD02 <> 'TRADE_CLOSED_BY_TAOBAO')
And C.XSDDZ37 = 0
Order By B.XSDD01, B.XSDDI01;
DECLARE c_JLTBSPXX CURSOR FOR SELECT SPXX01, SPGGITEM01 SL, SPGGITEM03 ZSPBJ, SPGGITEM02 JG,SPGGITEM03
From JLTB_SPGGITEM Where SPGG01 = V_SKUID Order By SPGGITEM03 Desc; #主商品排第一
DECLARE c_CK CURSOR FOR SELECT A.CK01, B.Dskh01 From JLTB_DPBMRY A, JLTB_DPXX B
WHERE B.DPNICK=V_DP AND A.DPSID = B.DPSID;
DECLARE CONTINUE HANDLER FOR NOT FOUND set isRecordNotFound=1;
#必须这样写,不然进入不了游标#
SET isRecordNotFound=0;
OPEN c_XSDDITEM;
Loop1: Loop
FETCH c_XSDDITEM Into V_XSDD03,V_XSDD02,V_XSDD01,V_XSDDI01,V_SL,V_XSDD34,
V_TBSPID,V_XSDD18,V_C_SPXX02,V_XSDDI10,V_XSDDI03,
V_SGTZJE,V_C_YHJE,V_XSDDI05,V_SKUID,V_SSTATUS,V_FPTT;
IF isRecordNotFound <> 1 THEN
Set V_WLBBJ = 0;
SET v_FLAG = 1;
Block1:BEGIN
declare continue handler for not found set v_FLAG = 0;
Select JL
Into v_FLAG
From V_XSDD_ZT
WHERE V_XSDD_ZT.XSDD01 = V_XSDD01;
END Block1;
SET v_GSID='';
If (v_FLAG = 0) AND (V_XSDDI03 = 0) Then
open c_JLTBSPXX;
Loop2:Loop
FETCH c_JLTBSPXX Into V_SPXX01,v_SPGGITEM01,V_ZSPBJ,V_JG,V_SPGGITEM03;
SET v_SPZL = 1;
Block2:BEGIN
declare continue handler for not found set v_SPZL = 0;
Select SPXX02, SPXX04, SPXX28
Into v_SPXX02, v_SPXX04, v_JQM
From SPXX
Where SPXX01 = V_SPXX01; #取商品重量
END Block2;
SET V_SPSL = V_SL * v_SPGGITEM01;
SET V_DP = V_XSDD18;
open c_CK;
Loop3 :Loop
FETCH c_CK Into c_CK01,c_DSKH01;
IF isRecordNotFound = 1 THEN
LEAVE Loop3;
END IF;
SET V_CK01 = c_CK01;
SET V_GSID = GET_GSID(V_CK01);
SET V_SSKH = c_DSKH01;
END Loop Loop3;
CLOSE c_CK;
/*防止c_CK游标无值导致isRecordNotFound=1*/
SET isRecordNotFound=0;
IF V_SPGGITEM03 = 1 THEN
SET V_TZJE = V_SGTZJE;
SET V_YHJE = V_C_YHJE;
SET V_WSDJ = V_XSDDI10;
SET V_XSZJ = V_XSDDI10 * V_XSDDI05;
ELSE
SET V_TZJE = 0;
SET V_YHJE = 0;
SET V_WSDJ = 0;
SET V_XSZJ = 0;
END IF;
Insert Into JLTB_DOWN_XSDDSP
(XSDD01, XSDDI01, SPXX01, XSDDS01, XSDDS02, XSDDS03, XSDDS04,
XSDDS08, CK01, NEWCK01, KHSCK01, XSDDS09, XSDDS12,
XSDDS13, XSDDS14, XSDDS15, YSPXX01, XSDDS17,DSKH01)
Values
(V_XSDD01, V_XSDD01,V_SPXX01,
v_SPGGITEM01,v_SPSL, V_XSZJ - V_YHJE + V_TZJE, v_SPZL,
V_TBSPID, V_CK01, V_CK01, NULL, v_JQM, V_TZJE, V_YHJE,
V_WSDJ, 0, V_SPXX01, V_WLBBJ,V_SSKH);
END Loop Loop2;
CLOSE c_JLTBSPXX;
IF V_FPTT IS NOT NULL THEN
SET v_FFTT = '发票抬头:' || V_FPTT;
ELSE
SET v_FFTT = NULL;
END IF;
UPDATE JLTB_DOWN_XSDD
SET GSXX01 = v_GSID, XSDD40 = v_FFTT
WHERE XSDD01 = V_XSDD01;
END IF;
END IF;
END Loop Loop1;
CLOSE c_XSDDITEM;
END