SET AUTOCOMMIT=0;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET P_ERRCODE='0000';
#START TRANSACTION;
UPDATE P010
SET P01031 = 2
WHERE P01031 <> 1
AND P01004 >= vMINDAT
AND P01004 < vMAXDAT
AND P01002 <> '6'
AND S02002=P_DS02002;
IF P_ERRCODE='0000' THEN
BEGIN
ROLLBACK;
SET P_ERRCODE='000001';
LEAVE VLD;
END;
END IF;
SELECT COUNT(1) INTO vCNT FROM P010 WHERE P01031 = 2 AND (P01002 = '4' OR P01002 = '5') AND S02002=P_DS02002;
IF vCNT>0 THEN
BEGIN
INSERT INTO BASFG (DOCNO,UID,RECDAT)
SELECT vDNO,P01001,NOW() FROM P010 WHERE P01031 = 2 AND (P01002 = '4' OR P01002 = '5') AND S02002=P_DS02002;
IF P_ERRCODE='0000' THEN
BEGIN
ROLLBACK;
SET P_ERRCODE='000002';
LEAVE VLD;
END;
END IF;
END;
END IF;
#在审核交易流水时,发现有没有对应仓库或者门店编号的收款机.
SELECT COUNT(1) INTO vCNT FROM ( SELECT DISTINCT P01005
FROM P010 A, BASFG B
WHERE A.P01001 = B.UID AND B.DOCNO=vDNO
) A LEFT OUTER JOIN
( SELECT P01005,B06001,S02002
FROM P020
WHERE S02002=P_DS02002
) B ON A.P01005 = B.P01005
WHERE (B06001 IS NULL OR S02002 IS NULL);
IF vCNT>0 THEN
BEGIN
SET P_ERRCODE='0120';
ROLLBACK;
LEAVE VLD;
END;
END IF;
SET vD30002 = GETD300(12 * 1000000);
INSERT INTO FP020 (DOCNO ,P01005, S020012, B06001)
SELECT vDNO AS DOCNO ,
A.P01005 AS P01005,
C.S02001 AS S020012,
B.B06001 AS B06001
FROM (SELECT A.P01005 AS P01005
FROM P010 A,
BASFG B
WHERE A.P01001 = B.UID AND B.DOCNO = vDNO
GROUP BY A.P01005 ) A,
P020 B,
S020 C
WHERE A.P01005 = B.P01005
AND B.S02002 = C.S02002
AND B.S02002 = P_DS02002;
IF P_ERRCODE='0000' THEN
BEGIN
ROLLBACK;
SET P_ERRCODE='000003';
LEAVE VLD;
END;
END IF;
INSERT INTO FD121 (DOCNO, P01004, S020012, S02001, B06001, B03001, P01104, P01106, D12112, D12113, D12114)
SELECT vDNO AS DOCNO,
STR_TO_DATE(B.P01004,'%Y%m%d') AS P01004,
C.S02001 AS S020012,
MIN(CASE WHEN C.S02021 = 1 THEN B.S02001 WHEN C.S02021 =2 THEN C.S02001 END) AS S02001,
A.B06001,
B.B03001,
SUM(B.P01104) AS P01104,
SUM(B.P01106) AS P01106,
SUM(B.D12112) AS D12112,
B.D12113,
B.D12114
FROM P020 A,
(SELECT DATE_FORMAT(A.P01004,'%Y%m%d') AS P01004,
A.P01005,
D.S02001,
B.B03001,
SUM(B.P01104) AS P01104,
SUM(B.P01105) AS P01106,
-(SUM(IFNULL(B.P01107,0) + IFNULL(B.P01108,0) + IFNULL(B.P01109,0) + IFNULL(B.P01110,0) + IFNULL(B.P01114,0) + IFNULL(B.P01115,0))) AS D12112,
CASE WHEN A.P01021 <> '' THEN 1 ELSE 0 END AS D12113,
CASE WHEN B.P01111 > 0 THEN 1 ELSE 0 END AS D12114
FROM P010 A,
P011 B,
BASFG C,
B030 D
WHERE A.P01001 = B.P01001
AND A.P01001=C.UID AND C.DOCNO=vDNO
AND B.B03001 = D.B03001
AND (A.P01002 = '4' OR A.P01002 = '5')
AND B.P01102 = 0
GROUP BY DATE_FORMAT(A.P01004,'%Y%m%d'), A.P01005, D.S02001,B.B03001,
(CASE WHEN A.P01021 <> '' THEN 1 ELSE 0 END),
(CASE WHEN B.P01111 > 0 THEN 1 ELSE 0 END)
)B,
S020 C
WHERE A.P01005 = B.P01005
AND A.S02002 = C.S02002
AND A.S02002 = P_DS02002
AND C.S02004 <> 5
GROUP BY B.P01004,C.S02001,A.B06001,B.B03001,B.D12113,B.D12114;
IF P_ERRCODE='0000' THEN
BEGIN
ROLLBACK;
SET P_ERRCODE='000004';
LEAVE VLD;
END;
END IF;
#销售数据中存在没有建立合同的商品信息,审核失败
SELECT COUNT(1) INTO vCNT FROM FD121 A,B036 B LEFT OUTER JOIN B050 C ON B.B040011 = C.B04001 AND B.B050011 = C.B05001
WHERE A.B03001 = B.B03001 AND B.S02001=vDS02001 AND A.DOCNO= vDNO AND C.B05001 IS NULL;
IF vCNT>0 THEN
BEGIN
SET P_ERRCODE='0121';
ROLLBACK;
LEAVE VLD;
END;
END IF;
BEGIN
DECLARE C_01 CURSOR FOR
SELECT P01004,S020012,S02001,B06001,D12113,D12114
FROM FD121 WHERE DOCNO = vDNO
GROUP BY P01004,S020012,S02001,B06001,D12113,D12114
ORDER BY P01004;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDONE=1;
OPEN C_01;
M1:LOOP
FETCH C_01 INTO vNP01004, vS020012, vS02001, vB06001,vD12113,vD12114;
IF vDONE=1 THEN
LEAVE M1;
END IF;
CALL GETDOCNO(vDS02001,12,vD12001);
IF P_ERRCODE='0000' THEN
BEGIN
#LEAVE M1;
CLOSE C_01;
ROLLBACK;
SET P_ERRCODE='000005';
LEAVE VLD;
END;
END IF;
SELECT S02002 INTO vS02002 FROM S020 WHERE S02001 = vS020012;
INSERT INTO D120(D12001, D12002, D12004, D12005, D12006, D12007, D12009, D12010, D12011, S02001, B06001, S020011, S020012, S010011)
VALUES (vD12001, 1, 0, 0, vNP01004, vNP01004, 0, 0, '系统产生', vS02001, vB06001, vS020012, vS020012, P_S01001);
IF P_ERRCODE='0000' THEN
BEGIN
ROLLBACK;
CLOSE C_01;
SET P_ERRCODE='000009';
LEAVE VLD;
END;
END IF;
INSERT INTO D121(B03007, D12103, D12104, D12105, B03105, B03102, D12106, D12107, B03109, D12111, D12001, B03001, B031021, D12113, D12114, E00001)
SELECT B.B03007,
0,
A.P01104 AS D12104,
A.P01104 AS D12105,
C.B03105,
CASE WHEN A.P01104 = 0 THEN 0 ELSE CONVERT((A.P01106 - A.D12112)/A.P01104,DECIMAL(8,2)) END AS B03102,
CONVERT(A.P01104 * C.B03105,DECIMAL(9,2)),
A.P01106 - A.D12112,
C.B03109,
A.P01106,
vD12001,
A.B03001,
CASE WHEN A.P01104 = 0 THEN 0 ELSE CONVERT(A.P01106/A.P01104,DECIMAL(8,2)) END AS B031021,
vD12113,
vD12114,
CONCAT(GetB03301(A.B03001),'=',LTRIM(RTRIM(CONVERT(A.P01104,CHAR(20)))),';') AS E00001
FROM FD121 A,
B030 B,
B036 C
WHERE C.B03001 = B.B03001 AND A.DOCNO = vDNO
AND B.B03001 = A.B03001
AND C.S02001 = vS020012
AND A.P01004 = vNP01004
AND A.S020012 = vS020012
AND A.S02001 = vS02001
AND A.B06001 = vB06001
AND A.D12113 = vD12113
AND A.D12114 = vD12114 ;
IF P_ERRCODE='0000' THEN
BEGIN
ROLLBACK;
CLOSE C_01;
SET P_ERRCODE='000010';
LEAVE VLD;
END;
END IF;
SELECT 'AAAAA',vD12001;
SET P_RET=0,P_ERRCODE='';
-- CALL VALIDD120(vD12001,P_S01001,P_RET,P_ERRCODE,vPLU);
IF (P_RET=0) OR (P_ERRCODE='0000') THEN
BEGIN
ROLLBACK;
CLOSE C_01;
LEAVE VLD;
END;
END IF;
END LOOP M1;
CLOSE C_01;
END;
#将获取单据的审核序列号
SET vP01033=CONCAT(DATE_FORMAT(NOW(),'%Y%m%d%H%i%S'),P_DS02002);
UPDATE P010
SET P01031 = 1,
P01033 = vP01033
WHERE P01031 = 2 AND S02002=P_DS02002;
IF P_ERRCODE='0000' THEN
BEGIN
ROLLBACK;
SET P_ERRCODE='0000';
LEAVE VLD;
END;
END IF;
END;
COMMIT;
这是我存储过程的片段,里面是一个事务处理,在'AAAAA'下面让事务进行回滚,游标里数据回滚了,而游标外面的数据没有回滚,想问一下这是怎么回事,另外如果游标里面嵌套执行过程该怎么办谢谢大家