執行成功但結果不對

Question

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，有的則為空啊，結果不對啊！哪位能幫幫看看是哪里有錯！

Answer 1

 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; 重点检查这段，这个语句里，要么会跳到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判断

Answer 2

格式也有问题
CURSOR C1 IS 前面的begin和declare两个关键字都去掉

Answer 3

晕，先改成这样还有错的话再检查我在3楼说的代码吧
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; 

Answer 4

我單步執行的時候看到IF ITEM1.MAT_NO =ITEM2.MAT_NO THEN
這一句判斷后就直接跳到 V_COUNT:='00';這一句來了，有點郁悶！為什么會是空的？？

Answer 5

因为 ITEM1.MAT_NO <>ITEM2.MAT_NO
所以 就直接跳到 V_COUNT:='00'，所以會是空的

Answer 6

CREATE OR REPLACE PACKAGE BODY PKG_JUN IS 
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语句

Answer 7

cursor c2前的declare要去掉...