fengying201302
fengying201302
采纳率0%
2016-07-22 08:02 阅读 1.6k

mysql事务回滚问题,嵌套游标,游标里面的可以回滚,外面的回滚不了

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'下面让事务进行回滚,游标里数据回滚了,而游标外面的数据没有回滚,想问一下这是怎么回事,另外如果游标里面嵌套执行过程该怎么办谢谢大家

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • hzaxxsrjzzttdwt 孩子爱学习是让家长最头疼的问题 2016-10-04 07:11

    ATE 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)

    点赞 评论 复制链接分享

相关推荐