SQL-001:查询门店零售商品批次无法带出价格检查代码.

这是第一次学习写PLSQL语句，练练手吧，在测试环境，真实环境均通过测试可用的。

主要解决问题：验证有批次商品是否存在零售无法带出销售价格问题，查出的数据提前到批次档案+单品档案中检查，找出问题排查处理。很大一部分原因是两边的商品不一致。

/* Formatted on 2020/11/11 18:03:34 (QP5 v5.326) */
DECLARE
    v_1                      BF_BATCHCODE.VBATCHCODE%TYPE;
    v_11                     BF_BATCHCODE.PK_INVBASDOC%TYPE;
    v_2                      RMB_ONHANDNUM.vbatchcode%TYPE;
    v_22                     RMB_ONHANDNUM.CINVBASID%TYPE;
    v2                       RMB_ONHANDNUM%ROWTYPE;
    v1                       BF_BATCHCODE%ROWTYPE;
    v_bf_batchcode_pk_corp   bf_batchcode.pk_corp%TYPE;
    v_bf_batchcode_vname     bf_batchcode.vname%TYPE;

    CURSOR c1 (v_batchcode BF_BATCHCODE.VBATCHCODE%TYPE)
    IS
        SELECT *
          FROM bf_batchcode
         WHERE vbatchcode = v_batchcode AND vbatchcode IS NOT NULL;

    CURSOR c2 IS
        SELECT *
          FROM rmb_onhandnum
         WHERE nonhandnum > 0 AND vbatchcode IS NOT NULL;

    i                        INT := 0;
    vhcode                   RMB_ONHANDNUM.vbatchcode%TYPE;
BEGIN
    IF NOT c2%ISOPEN
    THEN
        OPEN c2;
    END IF;

    NULL;

    LOOP
        FETCH c2 INTO v2;

        EXIT WHEN c2%NOTFOUND;
        v_2 := v2.vbatchcode;
        v_22 := TRIM (v2.cinvbasid);
        vhcode := v_2;



        IF NOT c1%ISOPEN
        THEN
            OPEN c1 (vhcode);
        END IF;

        NULL;

        LOOP
            FETCH c1 INTO v1;

            EXIT WHEN c1%NOTFOUND;
            v_1 := v1.vbatchcode;
            v_11 := TRIM (v1.pk_invbasdoc);
            v_bf_batchcode_pk_corp := v1.pk_corp;
            v_bf_batchcode_vname := v1.vname;

            IF (v_1 = v_2) AND (NVL (v_11, '') <> NVL (V_22, ''))
            THEN
                i := i + 1;
                DBMS_OUTPUT.put_line (
                       '检查结果显示：'
                    || i
                    || '  批次号='
                    || v_1
                    || '    pk_crop='
                    || v_bf_batchcode_pk_corp
                    || '   ,vname='
                    || v_bf_batchcode_vname);
            END IF;
        END LOOP;

        IF c1%ISOPEN
        THEN
            CLOSE c1;
        END IF;

        NULL;
    END LOOP;

    IF c2%ISOPEN
    THEN
        CLOSE c2;
    END IF;
END;
/