Oracle 游标

最新推荐文章于 2025-06-05 09:27:24 发布

转载最新推荐文章于 2025-06-05 09:27:24 发布 · 69 阅读

本文介绍了一个用于检查SMT产线开始阶段物料与设备状态的Oracle PL/SQL过程。该过程通过比较实际使用与计划配置的数据来确保生产线正确设置。涉及产品型号、产线名称、物料号、设备代码等关键信息。

CREATE OR REPLACE PROCEDURE SFIS1.CHECK_START_LINE_SMT_MO(MO IN VARCHAR2,LINE IN VARCHAR2,
RES OUT VARCHAR2) IS

C_MODEL_NAME   VARCHAR2(25);
C_COUNT        NUMBER;
C_COUNT1       NUMBER;
C_MO_NUMBER    VARCHAR2(25);
MIN_TRACK      VARCHAR2(16);
MAX_TRACK      VARCHAR2(16);
V_FEEDER       VARCHAR2(16);
COUNT_MACHINE NUMBER;

V_MACHINE_CODE SFISM4.R_SMT_LOG_T.MACHINE_CODE%TYPE;
--V_MACHINE_CODE VARCHAR2(32);

CURSOR FIND_MACHINE IS
       SELECT DISTINCT MACHINE_CODE --V_MACHINE_CODE
       FROM SFIS1.C_SMT_BOM_T
       WHERE BOM_NO IN (SELECT BOM_NO
                     FROM SFISM4.R_SMT_PROD_BOM_T
               WHERE PRODUCT_NO = C_MODEL_NAME AND
               LINE_NAME = LINE);

BEGIN
    RES := 'OK';

    SELECT MODEL_NAME ,MO_NUMBER INTO C_MODEL_NAME,C_MO_NUMBER
FROM SFISM4.R_MO_BASE_T
WHERE MO_NUMBER=MO;


    --CHECK ???
    SELECT COUNT(FEEDER_NO) INTO C_COUNT FROM SFIS1.C_SMT_BOM_T
    WHERE FEEDER_NO ='000'
     AND   BOM_NO IN (SELECT BOM_NO
                     FROM SFISM4.R_SMT_PROD_BOM_T
               WHERE PRODUCT_NO = C_MODEL_NAME AND
               LINE_NAME = LINE);

    IF C_COUNT>=1 THEN
    SELECT COUNT(*) INTO C_COUNT1 FROM
    (
       SELECT MACHINE_CODE,FEEDER_NO,KEY_PART_NO
         FROM SFISM4.R_SMT_LOG_T
       WHERE PRODUCT_NO=C_MODEL_NAME
         AND FEEDER_NO='000'
         AND LINE_NAME=LINE
         AND WORK_TIME <= SYSDATE
         AND END_TIME >= SYSDATE
       GROUP BY MACHINE_CODE,FEEDER_NO,KEY_PART_NO
     )A;
       IF C_COUNT1<>C_COUNT THEN
          RES:=LINE||'???????? ';
          RETURN;
       END IF;
    END IF;

    --END CHECK ???


OPEN FIND_MACHINE;
    LOOP
        FETCH FIND_MACHINE INTO V_MACHINE_CODE;
        EXIT WHEN FIND_MACHINE%NOTFOUND;

SELECT COUNT(*) INTO COUNT_MACHINE
FROM SFISM4.R_SMT_LOG_T
WHERE PRODUCT_NO = C_MODEL_NAME AND
              LINE_NAME = LINE AND
           MACHINE_CODE = V_MACHINE_CODE AND
           WORK_TIME <= SYSDATE AND
           END_TIME >= SYSDATE;
    IF COUNT_MACHINE <= 0 THEN
       RES := V_MACHINE_CODE||' NO DATA';
    EXIT;
    END IF;

SELECT MIN(BEGIN_TRACK),MAX(END_TRACK) INTO MIN_TRACK,MAX_TRACK
        FROM SFIS1.C_SMT_MACHINE_TABLE_T
        WHERE MACHINE_CODE=V_MACHINE_CODE AND
              TABLE_SIDE IN (SELECT DISTINCT SIDE FROM SFISM4.R_SMT_LOG_T
                             WHERE PRODUCT_NO = C_MODEL_NAME AND
                                   LINE_NAME = LINE AND
                                MACHINE_CODE = V_MACHINE_CODE AND
                                WORK_TIME <= SYSDATE AND
                                END_TIME >= SYSDATE );

     SELECT COUNT(DISTINCT FEEDER_NO) INTO C_COUNT
        FROM SFIS1.C_SMT_BOM_T
        WHERE BOM_NO IN (SELECT BOM_NO
                      FROM SFISM4.R_SMT_PROD_BOM_T
                WHERE PRODUCT_NO = C_MODEL_NAME AND
                LINE_NAME = LINE) AND
              MACHINE_CODE = V_MACHINE_CODE AND
           FEEDER_NO >= MIN_TRACK AND
           FEEDER_NO <= MAX_TRACK AND
           FEEDER_NO NOT IN (SELECT DISTINCT FEEDER_NO
                             FROM SFISM4.R_SMT_LOG_T
              WHERE PRODUCT_NO = C_MODEL_NAME AND
                    MACHINE_CODE = V_MACHINE_CODE AND
                                WORK_TIME <= SYSDATE AND
                                END_TIME >= SYSDATE );

      IF C_COUNT>0 THEN
       SELECT DISTINCT FEEDER_NO INTO V_FEEDER
             FROM SFIS1.C_SMT_BOM_T
             WHERE BOM_NO IN (SELECT BOM_NO
                           FROM SFISM4.R_SMT_PROD_BOM_T
                     WHERE PRODUCT_NO = C_MODEL_NAME AND
                     LINE_NAME = LINE) AND
                   MACHINE_CODE = V_MACHINE_CODE AND
                FEEDER_NO >= MIN_TRACK AND
                FEEDER_NO <= MAX_TRACK AND
                FEEDER_NO NOT IN (SELECT DISTINCT FEEDER_NO
                                 FROM SFISM4.R_SMT_LOG_T
                  WHERE PRODUCT_NO = C_MODEL_NAME AND
                        MACHINE_CODE = V_MACHINE_CODE AND
                                       WORK_TIME <= SYSDATE AND
                                       END_TIME >= SYSDATE ) AND
          ROWNUM = 1;
          RES:='NO '||V_MACHINE_CODE||'/'||V_FEEDER;
       EXIT;
           END IF;
END LOOP;
    CLOSE FIND_MACHINE;
EXCEPTION