oracle 在测试环境创建是好的，但是在生产环境上就报红叉，不知道是什么问题？？

Answer 1

CREATE OR REPLACE procedure trx_contract_procedure
as 
   vn NUMBER;
   vn1 NUMBER;
   vn2 NUMBER;
  
   vcontractId VARCHAR2(32);
   vfirsPct VARCHAR2(32);
   vsecodePct VARCHAR2(32) ;
   vredundFirst NUMBER(19,2);
   vredundSecode NUMBER(19,2);
   vredundSellTotal NUMBER(19,2);
   CURSOR C IS  
        SELECT 
    A.CONTRACT_ID as contractId,
            max(to_char(Q.FIRST_PCT,'yyyy-MM-dd')) AS firsPct,  
            max(to_char(Q.SECODE_PCT,'yyyy-MM-dd')) AS secodePct,
            sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100) as redundFirst,
            decode(P.FLAG,'validate',max(nvl((SELECT sum(E.VERIFY_QTY) FROM TRX_CONTRACT_VERIFY_2 E WHERE  E.CONTRACT_ID=P.CONTRACT_ID),0))-
                         sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100),
                         'unvalidate', sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))-
                         sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100)
                         ) AS redundSecode,
             sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund'  AND SYSDATE>=B.CASHIER_TIME) ,'0'))*((max(P.SETT_PCT1)+max(P.SETT_PCT2))/100) as redundSellTotal
     
        FROM 
           TRX_CONTRACT_ORDER_2 A,
           META_CONTRACT_TEMP_2 P,
           META_CONTRACT_TEMP_PQ Q
      WHERE  
           A.CONTRACT_ID=P.CONTRACT_ID AND 
           P.CONTRACT_ID=Q.CONTRACT_ID AND  Q.PQ_STATUS='fAuthed'
   GROUP BY  
           A.PAY_PRICE,
   A.CONTRACT_ID,
   A.GOODS_NAME,
   A.AREA_NAME,
   P.FLAG,
   P.PURCH_PRICE;
     
BEGIN
   
   OPEN C ; 
       
       FETCH C INTO  vcontractId,vfirsPct,vsecodePct,vredundFirst,vredundSecode,vredundSellTotal;
         WHILE C %FOUND LOOP
          vn2 :=0;
          --------------------------------判断合同是否存在如过存在查看是否已结算---------------------------------
      SELECT nvl(COUNT(*),0) INTO  vn2 FROM TRX_CONTRACT_PROFIT WHERE   contractId=vcontractId ;
      
      IF vn2>0 THEN
          ---判断一次性结算更新---
          vn1:=0;
           SELECT nvl(COUNT(*),0) INTO  vn1 FROM TRX_CONTRACT_PROFIT WHERE   contractId=vcontractId AND  FIRSPCT=SECODEPCT;
           IF vn1 >0 THEN
              UPDATE TRX_CONTRACT_PROFIT  SET REDUNDSELLTOTAL=vredundSellTotal,SPREADS=vredundSellTotal*(PAYPRICE-PURCHPRICE)   WHERE   contractId=vcontractId AND  FIRSPCT=SECODEPCT;
           ELSE
             NULL;
           END IF;
            ---判断第一,二次性结算更新---
           vn1:=0;
           SELECT nvl(COUNT(*),0) INTO  vn1 FROM TRX_CONTRACT_PROFIT WHERE   contractId=vcontractId AND  FIRSPCT<>SECODEPCT   AND PERIOD='1';
           IF vn1 =1 THEN
              UPDATE TRX_CONTRACT_PROFIT  SET REDUNDSELLTOTAL=vredundFirst,SPREADS=vredundFirst*(PAYPRICE-PURCHPRICE)  WHERE   contractId=vcontractId AND  FIRSPCT=vfirsPct AND SECODEPCT=vsecodePct AND PERIOD='1';
           ELSE
             NULL;
           END IF;
           
           vn1:=0;
           SELECT nvl(COUNT(*),0) INTO  vn1 FROM TRX_CONTRACT_PROFIT  WHERE   contractId=vcontractId AND  FIRSPCT<>SECODEPCT AND PERIOD='2';
           IF vn1 =1 THEN
              UPDATE TRX_CONTRACT_PROFIT  SET REDUNDSELLTOTAL=vredundSecode,SPREADS=vredundSecode*(PAYPRICE-PURCHPRICE)   WHERE   contractId=vcontractId AND  FIRSPCT=vfirsPct AND SECODEPCT=vsecodePct AND PERIOD='2';
           ELSE
             NULL;
           END IF;
        
      ELSE
        ----------------------------判断一次结算和二次结算时间相同就一次结算开始-------------------------------
         vn:=0;
  SELECT 
    nvl(count(*),0) INTO  vn 
    FROM 
                META_CONTRACT_TEMP_PQ Q
       WHERE  
           Q.PQ_STATUS='fAuthed' 
           AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')=vfirsPct 
           AND to_char(Q.SECODE_PCT,'yyyy-MM-dd')=vfirsPct 
           AND Q.CONTRACT_ID=vcontractId;
  
         IF vn=0 THEN
            
      ---------------------------- 一次结算时间---------------
         insert into trx_contract_profit  
   (
      SELECT
    A.CONTRACT_ID as contractId, --合同号
                        D.AREA_NAME as areaName,     --地市
P.AGENT_NAME as  agentName,  --代理商名称
D.AGENT_ACC_NO as agentAccNo, 
D.AGENT_ACC_BANK as agentAccBank,    
    A.PAY_PRICE as payPrice,     --销售价格
sum(A.PAY_CHG) as payChg ,   -- 手续费
A.GOODS_NAME as goodsName,   --商品名称
    P.PURCH_PRICE as purchPrice, --结算单价
    '1' as period,
    sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100) as redundSellTotal,
    sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100)*(A.PAY_PRICE-P.PURCH_PRICE) as spreads,
    max(to_char(Q.FIRST_PCT,'yyyy-MM-dd')) AS firsPct,  
            max(to_char(Q.SECODE_PCT,'yyyy-MM-dd')) AS secodePct,
            nvl(max((SELECT max(ORDER_ID) FROM TRX_ORDER_REFUND_2 B WHERE  B.ORDER_ID=A.ORDER_ID AND B.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd'))),0) AS orderId,
            Q.FIRST_PCT as sysdateTime
       FROM 
           TRX_CONTRACT_ORDER_2 A,
           META_CONTRACT_TEMP_2 P,
           META_CONTRACT_TEMP_PQ Q,
           META_AGENT_TEMP      D
      WHERE  
           A.CONTRACT_ID=P.CONTRACT_ID AND 
           P.CONTRACT_ID=Q.CONTRACT_ID AND  
           D.AGENT_CODE =P.AGENT_CODE AND 
           Q.PQ_STATUS='fAuthed'  AND 
           Q.FIRST_PCT<>Q.SECODE_PCT AND 
           Q.CONTRACT_ID=vcontractId
   GROUP BY  
            A.CONTRACT_ID, 
                        D.AREA_NAME, 
P.AGENT_NAME ,
D.AGENT_ACC_NO, 
D.AGENT_ACC_BANK,    
    A.PAY_PRICE, 
    A.GOODS_NAME,  
    P.PURCH_PRICE,
    Q.FIRST_PCT); 
         

Answer 2

   ---------------------------- 二次结算时间---------------
           insert into trx_contract_profit  
   (
      SELECT
    A.CONTRACT_ID as contractId, --合同号
                        D.AREA_NAME as areaName,     --地市
P.AGENT_NAME as  agentName,  --代理商名称
D.AGENT_ACC_NO as agentAccNo, 
D.AGENT_ACC_BANK as agentAccBank,    
    A.PAY_PRICE as payPrice,     --销售价格
sum(A.PAY_CHG) as payChg ,   -- 手续费
A.GOODS_NAME as goodsName,   --商品名称
    P.PURCH_PRICE as purchPrice, --结算单价
    '2' as period,
     CASE 
                        P.FLAG
                    WHEN  'validate' THEN
                        max(nvl((SELECT sum(E.VERIFY_QTY) FROM TRX_CONTRACT_VERIFY_2 E WHERE  E.CONTRACT_ID=P.CONTRACT_ID),0))-
                                      sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100)
                    ELSE
                        sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))-
                                      sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100)
            END AS redundSellTotal,
            (CASE 
                        P.FLAG
                    WHEN  'validate' THEN
                        max(nvl((SELECT sum(E.VERIFY_QTY) FROM TRX_CONTRACT_VERIFY_2 E WHERE  E.CONTRACT_ID=P.CONTRACT_ID),0))-
                                      sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100)
                    ELSE
                        sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))-
                                      sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')>=to_char(B.CASHIER_TIME,'yyyy-MM-dd')) ,'0'))*(max(P.SETT_PCT1)/100)
            END)*(A.PAY_PRICE-P.PURCH_PRICE) as spreads,
    max(to_char(Q.FIRST_PCT,'yyyy-MM-dd')) AS firsPct,  
            max(to_char(Q.SECODE_PCT,'yyyy-MM-dd')) AS secodePct,
            nvl(max((SELECT max(ORDER_ID) FROM TRX_ORDER_REFUND_2 B WHERE  B.ORDER_ID=A.ORDER_ID AND B.STATUS='refund' AND to_char(Q.FIRST_PCT,'yyyy-MM-dd')<=to_char(B.CASHIER_TIME,'yyyy-MM-dd'))),0) AS orderId,
            Q.SECODE_PCT as sysdateTime
       FROM 
           TRX_CONTRACT_ORDER_2 A,
           META_CONTRACT_TEMP_2 P,
           META_CONTRACT_TEMP_PQ Q,
           META_AGENT_TEMP      D
      WHERE  
           A.CONTRACT_ID=P.CONTRACT_ID AND 
           P.CONTRACT_ID=Q.CONTRACT_ID AND  
           D.AGENT_CODE =P.AGENT_CODE AND 
           Q.PQ_STATUS='fAuthed'  AND 
           Q.FIRST_PCT<>Q.SECODE_PCT AND 
           Q.CONTRACT_ID=vcontractId
   GROUP BY  
            A.CONTRACT_ID, 
                        D.AREA_NAME, 
P.AGENT_NAME ,
D.AGENT_ACC_NO, 
D.AGENT_ACC_BANK,    
    A.PAY_PRICE, 
    A.GOODS_NAME,  
    P.PURCH_PRICE,
    Q.SECODE_PCT,
    P.FLAG); 
         ELSE
         ---------------------------- 判断一次结算和二次结算时间相同就一次结算开始---------------
           insert into trx_contract_profit  
   (
      SELECT
    A.CONTRACT_ID as contractId, --合同号
                        D.AREA_NAME as areaName,     --地市
P.AGENT_NAME as  agentName,  --代理商名称
D.AGENT_ACC_NO as agentAccNo, 
D.AGENT_ACC_BANK as agentAccBank,    
    A.PAY_PRICE as payPrice,     --销售价格
sum(A.PAY_CHG) as payChg ,   -- 手续费
A.GOODS_NAME as goodsName,   --商品名称
    P.PURCH_PRICE as purchPrice, --结算单价
    '1' as period,
sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' ) ,'0'))*((max(P.SETT_PCT1)+max(P.SETT_PCT2))/100) as redundSellTotal,
    sum(A.SELL_QTY-NVL((SELECT sum(b.pay_total)  FROM TRX_ORDER_REFUND_2 b WHERE b.ORDER_ID=A.ORDER_ID AND b.STATUS='refund' ) ,'0'))*((max(P.SETT_PCT1)+max(P.SETT_PCT2))/100)*(A.PAY_PRICE-P.PURCH_PRICE) as spreads,
    max(to_char(Q.FIRST_PCT,'yyyy-MM-dd')) AS firsPct,  
            max(to_char(Q.SECODE_PCT,'yyyy-MM-dd')) AS secodePct,
            nvl(max((SELECT max(ORDER_ID) FROM TRX_ORDER_REFUND_2 B WHERE  B.ORDER_ID=A.ORDER_ID AND B.STATUS='refund' )),0) AS orderId,
            Q.FIRST_PCT as sysdateTime
       FROM 
           TRX_CONTRACT_ORDER_2 A,
           META_CONTRACT_TEMP_2 P,
           META_CONTRACT_TEMP_PQ Q,
           META_AGENT_TEMP      D
      WHERE  
           A.CONTRACT_ID=P.CONTRACT_ID AND 
           P.CONTRACT_ID=Q.CONTRACT_ID AND  
           D.AGENT_CODE =P.AGENT_CODE AND 
           Q.PQ_STATUS='fAuthed'  AND 
           to_char(Q.FIRST_PCT,'yyyy-MM-dd')=vfirsPct AND 
           to_char(Q.SECODE_PCT,'yyyy-MM-dd')=vfirsPct AND 
           Q.CONTRACT_ID=vcontractId
   GROUP BY  
            A.CONTRACT_ID, 
                        D.AREA_NAME, 
P.AGENT_NAME ,
D.AGENT_ACC_NO, 
D.AGENT_ACC_BANK,    
    A.PAY_PRICE, 
    A.GOODS_NAME,  
    P.PURCH_PRICE,
    Q.FIRST_PCT); 
           END IF;
          ----------------------------判断一次结算和二次结算时间相同就一次结算结束-------------------------------
           END IF ;
         
          
            COMMIT ; 
            FETCH C INTO  vcontractId,vfirsPct,vsecodePct,vredundFirst,vredundSecode,vredundSellTotal;
         END LOOP;
         EXCEPTION WHEN OTHERS THEN NULL;
    CLOSE C;
END;