Oracle 游标

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

   WHEN OTHERS THEN
      RES:='SMT SCAN ERR ';

END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-706819/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-706819/