CREATE OR REPLACE PROCEDURE
Insqcrecpn(LINE IN VARCHAR2, SECTION IN VARCHAR2, W_STATION IN VARCHAR2,
DATETIME IN DATE, EC IN VARCHAR2, DATA IN VARCHAR2, MO_DATE IN VARCHAR2,
W_SECTION IN NUMBER, MYGROUP IN VARCHAR2,C_CLASS IN VARCHAR2,C_CLASS_DATE IN VARCHAR2, RES OUT VARCHAR2, EMP IN VARCHAR2) AS
C_MODEL VARCHAR2(25);
OK VARCHAR2(25);
C_SSN VARCHAR2(25);
C_ERROR VARCHAR2(25);
C_TESTTIME DATE;
C_EMP VARCHAR2(25);
C_SECTION VARCHAR2(25);
C_GROUP VARCHAR2(25);
C_STATION VARCHAR2(25);
C_QANO VARCHAR2(25);
C_CHECKQTY NUMBER;
C_SNEXIST NUMBER;
C_KEY_PART VARCHAR2(6);
C_PASSQTY NUMBER;
C_FAILQTY NUMBER;
BEGIN SELECT MODEL_NAME,SHIPPING_SN,ERROR_FLAG,IN_STATION_TIME,EMP_NO,SECTION_NAME,GROUP_NAME,STATION_NAME,QA_NO
INTO C_MODEL,C_SSN,C_ERROR,C_TESTTIME,C_EMP,C_SECTION,C_GROUP,C_STATION,C_QANO
FROM SFISM4.R_WIP_TRACKING_T
WHERE SERIAL_NUMBER=DATA; --GET THE WIP_TRACKING_T IF (C_QANO IS NULL OR C_QANO='N/A') THEN --NOT GENERATE LOT NO INSERT INTO SFISM4.R_QC_SN_TMP_T(MODEL_NAME,SERIAL_NUMBER,SHIPPING_SN,ERROR_FLAG,TEST_TIME,TESTER,COUNTER,SECTION_NAME,
GROUP_NAME,STATION_NAME,CLASS,CLASS_DATE,LINE_NAME)
VALUES(C_MODEL,DATA,C_SSN,C_ERROR,C_TESTTIME,C_EMP,0,C_SECTION,C_GROUP,C_STATION,C_CLASS,C_CLASS_DATE,LINE);
SELECT SUBSTR(KEY_PART_NO,8,6) INTO C_KEY_PART
FROM SFISM4.R_WIP_TRACKING_T WHERE SERIAL_NUMBER=DATA; SELECT COUNT(A.SERIAL_NUMBER ) INTO C_SNEXIST
FROM SFISM4.R_QC_SN_TMP_T A,SFISM4.R_WIP_TRACKING_T B
WHERE A.SERIAL_NUMBER=B.SERIAL_NUMBER
AND SUBSTR(B.KEY_PART_NO,8,6)=C_KEY_PART
AND (A.LOT_NO IS NULL OR A. LOT_NO='N/A') ;
RES := 'OK '||C_KEY_PART||C_SNEXIST; ELSE
SELECT PASS_QTY,FAIL_QTY INTO C_PASSQTY,C_FAILQTY FROM SFISM4.R_CQC_REC_T WHERE LOT_NO=C_QANO; INSERT INTO SFISM4.R_QC_SN_T(LOT_NO,MODEL_NAME,SERIAL_NUMBER,SHIPPING_SN,ERROR_FLAG,TEST_TIME,TESTER,COUNTER,
SECTION_NAME,GROUP_NAME,STATION_NAME,CLASS,CLASS_DATE,LINE_NAME)
VALUES(C_QANO,C_MODEL,DATA,C_SSN,C_ERROR,C_TESTTIME,C_EMP,0,C_SECTION,C_GROUP,C_STATION,C_CLASS,C_CLASS_DATE,LINE); SELECT COUNT(DISTINCT(SERIAL_NUMBER)) INTO C_CHECKQTY FROM SFISM4.R_QC_SN_T WHERE LOT_NO=C_QANO; IF C_ERROR='0' THEN
UPDATE SFISM4.R_CQC_REC_T SET PASS_QTY=C_PASSQTY+1 WHERE LOT_NO=C_QANO;
ELSE
UPDATE SFISM4.R_CQC_REC_T SET FAIL_QTY=C_FAILQTY+1 WHERE LOT_NO=C_QANO;
END IF; RES := 'OK '||'L'||SUBSTR(C_QANO,7,2)||SUBSTR(C_QANO,14,2)||' '||C_CHECKQTY; END IF;END;
/
Insqcrecpn(LINE IN VARCHAR2, SECTION IN VARCHAR2, W_STATION IN VARCHAR2,
DATETIME IN DATE, EC IN VARCHAR2, DATA IN VARCHAR2, MO_DATE IN VARCHAR2,
W_SECTION IN NUMBER, MYGROUP IN VARCHAR2,C_CLASS IN VARCHAR2,C_CLASS_DATE IN VARCHAR2, RES OUT VARCHAR2, EMP IN VARCHAR2) AS
C_MODEL VARCHAR2(25);
OK VARCHAR2(25);
C_SSN VARCHAR2(25);
C_ERROR VARCHAR2(25);
C_TESTTIME DATE;
C_EMP VARCHAR2(25);
C_SECTION VARCHAR2(25);
C_GROUP VARCHAR2(25);
C_STATION VARCHAR2(25);
C_QANO VARCHAR2(25);
C_CHECKQTY NUMBER;
C_SNEXIST NUMBER;
C_KEY_PART VARCHAR2(6);
C_PASSQTY NUMBER;
C_FAILQTY NUMBER;
BEGIN SELECT MODEL_NAME,SHIPPING_SN,ERROR_FLAG,IN_STATION_TIME,EMP_NO,SECTION_NAME,GROUP_NAME,STATION_NAME,QA_NO
INTO C_MODEL,C_SSN,C_ERROR,C_TESTTIME,C_EMP,C_SECTION,C_GROUP,C_STATION,C_QANO
FROM SFISM4.R_WIP_TRACKING_T
WHERE SERIAL_NUMBER=DATA; --GET THE WIP_TRACKING_T IF (C_QANO IS NULL OR C_QANO='N/A') THEN --NOT GENERATE LOT NO INSERT INTO SFISM4.R_QC_SN_TMP_T(MODEL_NAME,SERIAL_NUMBER,SHIPPING_SN,ERROR_FLAG,TEST_TIME,TESTER,COUNTER,SECTION_NAME,
GROUP_NAME,STATION_NAME,CLASS,CLASS_DATE,LINE_NAME)
VALUES(C_MODEL,DATA,C_SSN,C_ERROR,C_TESTTIME,C_EMP,0,C_SECTION,C_GROUP,C_STATION,C_CLASS,C_CLASS_DATE,LINE);
SELECT SUBSTR(KEY_PART_NO,8,6) INTO C_KEY_PART
FROM SFISM4.R_WIP_TRACKING_T WHERE SERIAL_NUMBER=DATA; SELECT COUNT(A.SERIAL_NUMBER ) INTO C_SNEXIST
FROM SFISM4.R_QC_SN_TMP_T A,SFISM4.R_WIP_TRACKING_T B
WHERE A.SERIAL_NUMBER=B.SERIAL_NUMBER
AND SUBSTR(B.KEY_PART_NO,8,6)=C_KEY_PART
AND (A.LOT_NO IS NULL OR A. LOT_NO='N/A') ;
RES := 'OK '||C_KEY_PART||C_SNEXIST; ELSE
SELECT PASS_QTY,FAIL_QTY INTO C_PASSQTY,C_FAILQTY FROM SFISM4.R_CQC_REC_T WHERE LOT_NO=C_QANO; INSERT INTO SFISM4.R_QC_SN_T(LOT_NO,MODEL_NAME,SERIAL_NUMBER,SHIPPING_SN,ERROR_FLAG,TEST_TIME,TESTER,COUNTER,
SECTION_NAME,GROUP_NAME,STATION_NAME,CLASS,CLASS_DATE,LINE_NAME)
VALUES(C_QANO,C_MODEL,DATA,C_SSN,C_ERROR,C_TESTTIME,C_EMP,0,C_SECTION,C_GROUP,C_STATION,C_CLASS,C_CLASS_DATE,LINE); SELECT COUNT(DISTINCT(SERIAL_NUMBER)) INTO C_CHECKQTY FROM SFISM4.R_QC_SN_T WHERE LOT_NO=C_QANO; IF C_ERROR='0' THEN
UPDATE SFISM4.R_CQC_REC_T SET PASS_QTY=C_PASSQTY+1 WHERE LOT_NO=C_QANO;
ELSE
UPDATE SFISM4.R_CQC_REC_T SET FAIL_QTY=C_FAILQTY+1 WHERE LOT_NO=C_QANO;
END IF; RES := 'OK '||'L'||SUBSTR(C_QANO,7,2)||SUBSTR(C_QANO,14,2)||' '||C_CHECKQTY; END IF;END;
/
Exception when others then
DBMS_output.putline(sqlcode);
--用来获得错误的代码,然后再自己检查你也可以单步调试Proc的