有啊,有數據啊,我懷疑是不是這里沒有數據 SELECT ITEM1.FACT_NO,ITEM1.ARTIC_NO_IN,ITEM1.ARTIC_NO, ITEM1.PART_NO,V_COUNT ,ITEM1.PROD_SIZE,ITEM1.MAT_SIZE, ITEM1.MAT_NO FROM DUAL
有可能,你可以试试inser into table values的形式 create or replace procedure procedure_jun_item is
V_COUNT char(2); CURSOR C1 IS (SELECT 'B01O' AS FACT_NO, A.ARTIC_NO_IN, (SELECT ARTIC_NO FROM MIS_ARTICM_0827 B WHERE 'B01O'=B.FACT_NO AND A.ARTIC_NO_IN=B.ARTIC_NO_IN) as ARTIC_NO, C.REGION_NO AS PART_NO, --兜Ω, A.PROD_SIZE, A.MAT_SIZE, C.MAT_NO AS MAT_NO FROM JUN_BOM_SIZE_V2_0914 A,JUN_BOM_MAST_V2_0914 C WHERE SUBSTR(C.LAYER_PITEM,1,4)=SUBSTR(A.LAYER_ITEM,1,4) AND C.LAYER_ITEM=A.S_ITEM AND C.ARTIC_NO_IN=A.ARTIC_NO_IN AND C.ARTIC_NO_IN='AT0304CMA07J100A0001'); BEGIN FOR ITEM1 IN C1 LOOP FOR ITEM2 IN (SELECT * FROM JUN_MIS_ADV_SIZE_0914_2) LOOP V_COUNT:='00'; IF ITEM1.MAT_NO=ITEM2.MAT_NO AND ITEM1.PART_NO=ITEM2.PART_NO THEN SELECT TO_CHAR(TO_NUMBER(MAX(JOINT_NO))+1) into V_COUNT FROM JUN_MIS_ADV_SIZE_0914_2 J WHERE J.MAT_NO=ITEM1.MAT_NO; END IF; IF LENGTH(V_COUNT) < 2 THEN V_COUNT:='0'||V_COUNT; END IF; INSERT INTO JUN_MIS_ADV_SIZE_0914_2( FACT_NO, ARTIC_NO_IN, ARTIC_NO, PART_NO, JOINT_NO,--兜Ω
MAT_SIZE, MOLD_SIZE, MAT_NO) VALUES(ITEM1.FACT_NO,ITEM1.ARTIC_NO_IN,ITEM1.ARTIC_NO, ITEM1.PART_NO,V_COUNT , ITEM1.PROD_SIZE,ITEM1.MAT_SIZE,ITEM1.MAT_NO); END LOOP; END LOOP;
因为END IF后面直接INSERT了
嗯嗯,是啊,一樣要插入,只是如果不存在則V_COUNT:=‘00’啊!
有啊,有數據啊,我懷疑是不是這里沒有數據
SELECT ITEM1.FACT_NO,ITEM1.ARTIC_NO_IN,ITEM1.ARTIC_NO,
ITEM1.PART_NO,V_COUNT ,ITEM1.PROD_SIZE,ITEM1.MAT_SIZE,
ITEM1.MAT_NO
FROM DUAL
create or replace procedure procedure_jun_item is
V_COUNT char(2);
CURSOR C1 IS
(SELECT
'B01O' AS FACT_NO,
A.ARTIC_NO_IN,
(SELECT ARTIC_NO
FROM MIS_ARTICM_0827 B
WHERE 'B01O'=B.FACT_NO
AND A.ARTIC_NO_IN=B.ARTIC_NO_IN) as ARTIC_NO,
C.REGION_NO AS PART_NO,
--兜Ω,
A.PROD_SIZE,
A.MAT_SIZE,
C.MAT_NO AS MAT_NO
FROM JUN_BOM_SIZE_V2_0914 A,JUN_BOM_MAST_V2_0914 C
WHERE SUBSTR(C.LAYER_PITEM,1,4)=SUBSTR(A.LAYER_ITEM,1,4)
AND C.LAYER_ITEM=A.S_ITEM
AND C.ARTIC_NO_IN=A.ARTIC_NO_IN
AND C.ARTIC_NO_IN='AT0304CMA07J100A0001'); BEGIN FOR ITEM1 IN C1 LOOP
FOR ITEM2 IN (SELECT * FROM JUN_MIS_ADV_SIZE_0914_2) LOOP
V_COUNT:='00';
IF ITEM1.MAT_NO=ITEM2.MAT_NO AND ITEM1.PART_NO=ITEM2.PART_NO THEN
SELECT TO_CHAR(TO_NUMBER(MAX(JOINT_NO))+1) into V_COUNT
FROM JUN_MIS_ADV_SIZE_0914_2 J
WHERE J.MAT_NO=ITEM1.MAT_NO;
END IF; IF LENGTH(V_COUNT) < 2 THEN
V_COUNT:='0'||V_COUNT;
END IF;
INSERT INTO JUN_MIS_ADV_SIZE_0914_2(
FACT_NO,
ARTIC_NO_IN,
ARTIC_NO,
PART_NO,
JOINT_NO,--兜Ω
MAT_SIZE,
MOLD_SIZE,
MAT_NO)
VALUES(ITEM1.FACT_NO,ITEM1.ARTIC_NO_IN,ITEM1.ARTIC_NO,
ITEM1.PART_NO,V_COUNT , ITEM1.PROD_SIZE,ITEM1.MAT_SIZE,ITEM1.MAT_NO);
END LOOP;
END LOOP;
COMMIT; end procedure_jun_item;