先上代码,再做说明,这个确实比以前的要复杂的多一些。
/* Formatted on 2020/12/27 13:42:43 (QP5 v5.326) */ DECLARE CURSOR cur_ccargdoc IS SELECT rs.VSITENAME vsitename, roh.vbatchcode vbatchcode, bdinvcl.INVCLASSCODE invclasscode, bdinvcl.INVCLASSNAME invclassname, rcgd.CSNAME csname, bdinvcl.INVCLASSLEV invclasslev, bdinv.INVNAME invname, bdinv.INVCODE invcode, bdinvcl.PK_INVCL pk_invcl, rcgd.CCARGDOCID ccargdocid FROM rmb_onhandnum roh LEFT JOIN bd_invbasdoc bdinv ON roh.CINVBASID = bdinv.PK_INVBASDOC LEFT JOIN bd_invcl bdinvcl ON bdinv.PK_INVCL = bdinvcl.PK_INVCL LEFT JOIN rmb_site rs ON roh.CSITEID = rs.CSITEID LEFT JOIN rmb_cargdoc rcgd ON roh.CCARGDOCID = rcgd.CCARGDOCID WHERE roh.nonhandnum > 0 AND (NVL (roh.dr, 0) = 0) AND (NVL (bdinv.dr, 0) = 0) AND (NVL (bdinvcl.dr, 0) = 0) AND (roh.ccargdocid IS NOT NULL) ORDER BY vsitename; TYPE ccargdocinfo IS RECORD ( vsitename VARCHAR2 (50), vbatchcode VARCHAR2 (46), invclasscode VARCHAR2 (50), invclassname VARCHAR2 (240), csname VARCHAR2 (50), invclasslev NUMBER (38), invname VARCHAR2 (200), invcode VARCHAR2 (40), pk_invcl CHAR (20), ccargdocid CHAR (20) ); num INT := 0; i INT := 0; R_cargdoc ccargdocinfo; checkDate CHAR (10) := '0000-00-00'; tempInvclasscode VARCHAR2 (50) := '00'; BEGIN SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd') INTO checkDate FROM DUAL; DBMS_OUTPUT.put_line ( '对门店货柜匹配进行检查,检查日期:' || checkDate); IF NOT cur_ccargdoc%ISOPEN THEN OPEN cur_ccargdoc; END IF; NULL; LOOP FETCH cur_ccargdoc INTO R_cargdoc; EXIT WHEN cur_ccargdoc%NOTFOUND; IF (R_cargdoc.invclasslev > 6) THEN tempInvclasscode := SUBSTR (R_cargdoc.invclasscode, 0, 6); ELSE tempInvclasscode := R_cargdoc.invclasscode; END IF; SELECT COUNT (t1.invclasscode) INTO num FROM ( SELECT invclasscode FROM bd_invcl WHERE (NVL (dr, 0) = 0) START WITH invclasscode = tempInvclasscode CONNECT BY invclasscode = PRIOR SUBSTR (invclasscode, 0, LENGTH (invclasscode) - 1)) t1 WHERE EXISTS (SELECT invclasscode FROM (SELECT binv.invclasscode invclasscode FROM rmb_cargdoc rc INNER JOIN rmb_cargdoc_b rcb ON rc.CCARGDOCID = rcb.CCARGDOCID INNER JOIN rmb_site rs ON rc.CSITEID = rs.CSITEID INNER JOIN bd_invcl binv ON binv.pk_invcl = rcb.cinvclassid WHERE (NVL (rc.dr, 0) = 0) AND (NVL (rcb.dr, 0) = 0) AND (NVL (rs.dr, 0) = 0) AND (NVL (binv.dr, 0) = 0) AND rc.CCARGDOCID = R_cargdoc.ccargdocid) t2 WHERE t1.invclasscode = t2.invclasscode); IF (num <= 0) THEN i := i + 1; DBMS_OUTPUT.put_line ( 'No:' || i || ' ,店铺:' || R_cargdoc.vsitename || ' ,商品名称:' || R_cargdoc.invname || ' ,商品编码:' || R_cargdoc.invcode || ' ,批次号:' || R_cargdoc.vbatchcode || ' ,目前货柜位置:' || R_cargdoc.csname || ' ,分类代码:' || R_cargdoc.invclasscode); END IF; END LOOP; IF cur_ccargdoc%ISOPEN THEN CLOSE cur_ccargdoc; END IF; IF (i < 1) THEN DBMS_OUTPUT.put_line ( '对门店货柜匹配进行检查验证正常,放心使用'); ELSE DBMS_OUTPUT.put_line ( '对门店货柜匹配进行检查验证完毕,可能存在匹配问题数量为:' || i); END IF; END; /
流程分析:
1、先从门店里查出库存数量不为0并且货柜主键不为空的数据;
2、检查数据库表bd_invcl与NC系统,发现库存分类的编码规则是*-*-*-*-*-**,代表最长8位,一般是跳1位增加,但当达到第6位时要跳2位了,所以使用以下代码处理:
IF (R_cargdoc.invclasslev > 6) THEN tempInvclasscode := SUBSTR (R_cargdoc.invclasscode, 0, 6); ELSE tempInvclasscode := R_cargdoc.invclasscode; END IF;
3、对于表bd_invcl嵌套查询这块想了好久,因为网上一般常见的3种树形结构和这个表都不一样,最终对于逐位递增的处理SQL结合Oracle如下:
SELECT level,invclasscode,invclassname,pk_corp,pk_invcl from bd_invcl where (nvl(dr,0)=0) start WITH invclasscode ='s2322' connect by invclasscode = prior substr(invclasscode, 0, length(invclasscode)-1)
以上SQL是查询出对应invclasscode的所有上级,但在实际使用中,当invclasscode达到8位就查不到了,看以上第2步处理了;
4、因为某一个货柜有时会对应多个存货编码级别,所以最后就是涉及到2个数据库表对应2个相同字段,但要查询出是否有相同值了,简化代码如下:
SELECT count(t1.tt1)
FROM t1
WHERE EXISTS
(SELECT t2.tt2
FROM t2
WHERE t1.tt1= t2.tt2)
把这个代码套上,应该就OK了吧。
2020-12-27