CREATE OR REPLACE PACKAGE BODY PKG_JUN IS
procedure procedure_jun_item is
--V_PART_NO CHAR(10);--场腹跑秖
--V_MAT_NO CHAR(20);--腹夹跑
V_COUNT char(2);--兜Ω跑秖
BEGIN
-----羘村夹村夹ず甧埃¨兜Ω〃璶础JUN_MIS_ADV_SIZE_0914_2い癘魁
DECLARE 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='WL0105G2A33J120K0013');
BEGIN
-------碻吏村夹盢村夹癘魁秈ITEM1い
--狦い竒Τ腹玥V_COUNT腹程兜Ω+1
--狦い⊿Τ腹弧琌贺
--玥р兜Ω跑秖V_COUNT耴箂
FOR ITEM1 IN C1 LOOP
BEGIN
DECLARE CURSOR C2 IS
SELECT distinct mat_no FROM JUN_MAT_NO_23_0915;----顶絏2┪3腹Τ確
BEGIN
-- open c2;
FOR ITEM2 IN C2 LOOP
IF ITEM1.MAT_NO =ITEM2.MAT_NO THEN
SELECT TO_CHAR(MAX(JOINT_NO)+1) into V_COUNT
FROM JUN_MIS_ADV_SIZE_0914_2 M
WHERE ITEM1.ARTIC_NO_IN=M.ARTIC_NO_IN
AND ITEM1.MAT_NO=M.MAT_NO
AND ITEM1.PART_NO=M.PART_NO;
EXIT WHEN ITEM1.MAT_NO =ITEM2.MAT_NO;
ELSE
V_COUNT:='00';
END IF;
END LOOP;
-- IF ITEM1.MAT_NO IN C2.MAT_NO THEN
-- ELSE
--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)
(
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
);
END;
END;
END LOOP;
commit;
END;end procedure_jun_item;end PKG_JUN;
執行之后有的V_COUNT為00,有的則為空啊,結果不對啊!哪位能幫幫看看是哪里有錯!
procedure procedure_jun_item is
--V_PART_NO CHAR(10);--场腹跑秖
--V_MAT_NO CHAR(20);--腹夹跑
V_COUNT char(2);--兜Ω跑秖
BEGIN
-----羘村夹村夹ず甧埃¨兜Ω〃璶础JUN_MIS_ADV_SIZE_0914_2い癘魁
DECLARE 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='WL0105G2A33J120K0013');
BEGIN
-------碻吏村夹盢村夹癘魁秈ITEM1い
--狦い竒Τ腹玥V_COUNT腹程兜Ω+1
--狦い⊿Τ腹弧琌贺
--玥р兜Ω跑秖V_COUNT耴箂
FOR ITEM1 IN C1 LOOP
BEGIN
DECLARE CURSOR C2 IS
SELECT distinct mat_no FROM JUN_MAT_NO_23_0915;----顶絏2┪3腹Τ確
BEGIN
-- open c2;
FOR ITEM2 IN C2 LOOP
IF ITEM1.MAT_NO =ITEM2.MAT_NO THEN
SELECT TO_CHAR(MAX(JOINT_NO)+1) into V_COUNT
FROM JUN_MIS_ADV_SIZE_0914_2 M
WHERE ITEM1.ARTIC_NO_IN=M.ARTIC_NO_IN
AND ITEM1.MAT_NO=M.MAT_NO
AND ITEM1.PART_NO=M.PART_NO;
EXIT WHEN ITEM1.MAT_NO =ITEM2.MAT_NO;
ELSE
V_COUNT:='00';
END IF;
END LOOP;
-- IF ITEM1.MAT_NO IN C2.MAT_NO THEN
-- ELSE
--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)
(
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
);
END;
END;
END LOOP;
commit;
END;end procedure_jun_item;end PKG_JUN;
執行之后有的V_COUNT為00,有的則為空啊,結果不對啊!哪位能幫幫看看是哪里有錯!
IF ITEM1.MAT_NO =ITEM2.MAT_NO THEN
SELECT TO_CHAR(MAX(JOINT_NO)+1) into V_COUNT
FROM JUN_MIS_ADV_SIZE_0914_2 M
WHERE ITEM1.ARTIC_NO_IN=M.ARTIC_NO_IN
AND ITEM1.MAT_NO=M.MAT_NO
AND ITEM1.PART_NO=M.PART_NO;
EXIT WHEN ITEM1.MAT_NO =ITEM2.MAT_NO;
ELSE
V_COUNT:='00';
END IF;
END LOOP; 重点检查这段,这个语句里,要么会跳到v_count=00段
要么SELECT TO_CHAR(MAX(JOINT_NO)+1) into V_COUNT 被赋予空值
两种情况都不会执行
IF LENGTH(V_COUNT) <2 THEN
V_COUNT:='0'||V_COUNT;
值为'00'时,长度为2,值为null是长度也为null,不能用<2判断
CURSOR C1 IS 前面的begin和declare两个关键字都去掉
CREATE OR REPLACE PACKAGE BODY PKG_JUN IS
procedure procedure_jun_item is
--V_PART_NO CHAR(10);--场腹跑秖
--V_MAT_NO CHAR(20);--腹夹跑
V_COUNT char(2);--兜Ω跑秖
-----羘村夹村夹ず甧埃¨兜Ω〃璶础JUN_MIS_ADV_SIZE_0914_2い癘魁
CURSOR C2 IS
SELECT distinct mat_no FROM JUN_MAT_NO_23_0915;
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='WL0105G2A33J120K0013');
BEGIN
-------碻吏村夹盢村夹癘魁秈ITEM1い
--狦い竒Τ腹玥V_COUNT腹程兜Ω+1
--狦い⊿Τ腹弧琌贺
--玥р兜Ω跑秖V_COUNT耴箂
FOR ITEM1 IN C1 LOOP
----顶絏2┪3腹Τ確
-- open c2;
FOR ITEM2 IN C2 LOOP
IF ITEM1.MAT_NO =ITEM2.MAT_NO THEN
SELECT TO_CHAR(MAX(JOINT_NO)+1) into V_COUNT
FROM JUN_MIS_ADV_SIZE_0914_2 M
WHERE ITEM1.ARTIC_NO_IN=M.ARTIC_NO_IN
AND ITEM1.MAT_NO=M.MAT_NO
AND ITEM1.PART_NO=M.PART_NO;
EXIT WHEN ITEM1.MAT_NO =ITEM2.MAT_NO;
ELSE
V_COUNT:='00';
END IF;
END LOOP;
-- IF ITEM1.MAT_NO IN C2.MAT_NO THEN
-- ELSE
--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)
(
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
);
END;
END;
END LOOP;
commit;
END;
end PKG_JUN;
這一句判斷后就直接跳到 V_COUNT:='00';這一句來了,有點郁悶!為什么會是空的??
所以 就直接跳到 V_COUNT:='00',所以會是空的
procedure procedure_jun_item is
V_COUNT varchar2(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='WL0105G2A33J120K0013');
DECLARE CURSOR C2 IS SELECT distinct mat_no FROM JUN_MAT_NO_23_0915;
BEGIN
FOR ITEM1 IN C1 LOOP
FOR ITEM2 IN C2 LOOP
IF ITEM1.MAT_NO =ITEM2.MAT_NO THEN
SELECT TO_CHAR(MAX(JOINT_NO)+1) into V_COUNT
FROM JUN_MIS_ADV_SIZE_0914_2 M
WHERE ITEM1.ARTIC_NO_IN=M.ARTIC_NO_IN
AND ITEM1.MAT_NO=M.MAT_NO
AND ITEM1.PART_NO=M.PART_NO;
EXIT ;
ELSE
V_COUNT:='00';
END IF;
END LOOP;
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)
(
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;
commit;
end PKG_JUN;
运行下试试
问题还是一样的话检查那段if语句