存儲過程問題

Question

CREATE OR REPLACE PROCEDURE P_CK_DAYWORK     
(VIS_FACT VARCHAR2,VIS_DATE VARCHAR2,VIS_SEC VARCHAR2,VIS_PNL VARCHAR2,VOS_OUT1 OUT VARCHAR2,VOS_OUT2 OUT VARCHAR2)    IS      
VN_COUNT    NUMBER;          --記錄數 
VN_WEEKNO   NUMBER;          --周序號 
VN_WEEKMK   NUMBER;          --星期几 
VN_RATE_WH  NUMBER;          --工時倍數 
VN_WH_STAND NUMBER;          --日標准工時 
VN_WH_MAXZB NUMBER;          --卡別正班工時 
VN_NOCARD   NUMBER;          --忘記刷卡次數 
VN_NOCARDS  NUMBER;          --忘記刷卡累計次數 
VN_TIME_VALIDBF NUMBER;      --有效時間前 
VN_TIME_VALIDAF NUMBER;      --有效時間后 
VS_SECNO   VARCHAR2(14);     --組別 
VS_CARDNO  VARCHAR2(2);      --卡別 
VS_TURNMK  VARCHAR2(1);      --倒班注記 
VS_DATETN  VARCHAR2(8);      --倒班日期 
VS_DATECH  VARCHAR2(8);      --對調周日 
VS_DATEZB  VARCHAR2(8);      --對調正班日 
VS_UNCARD_NO1  VARCHAR2(1);  --免刷卡次1 
VS_UNCARD_NO2  VARCHAR2(1);  --免刷卡次2 
VS_UNCARD_NO3  VARCHAR2(1);  --免刷卡次3 
VS_UNCARD_NO4  VARCHAR2(1);  --免刷卡次4 
VS_CARDTIME1  VARCHAR2(14);  --第一次刷卡時間
VS_TIME_CARD1  VARCHAR2(1);  --轉鐘卡次1  
VS_TIME_CARD2  VARCHAR2(1);  --轉鐘卡次2  
VS_TIME_CARD3  VARCHAR2(1);  --轉鐘卡次3 
VS_TIME_CARD4  VARCHAR2(1);  --轉鐘卡次4  
VS_SPECARD   VARCHAR2(2);    --特殊設定卡別  
VS_MORN_ON   VARCHAR2(4);    --第一次卡 
VS_MORN_OFF  VARCHAR2(4);    --第二次卡 
VS_AFTE_ON   VARCHAR2(4);    --第三次卡 
VS_AFTE_OFF  VARCHAR2(4);    --第四次卡 
VS_EVEN_ON   VARCHAR2(4);    --第五次卡 
VS_EVEN_OFF  VARCHAR2(4);    --第六次卡  
VS_STATIME   VARCHAR2(4);    --刷卡起始時間 
VS_ENDTIME   VARCHAR2(4);    --刷卡終止時間  
VS_CARDATE   VARCHAR2(8);    --隔日刷卡日期 
VS_TIME_FM   VARCHAR2(12);   --加班申請起始時間 
VS_TIME_TO   VARCHAR2(12);   --加班申請終止時間 
CURSOR C_1 IS    
  SELECT PNL,PNL_NM,SEC_NO,IMARK,CARD_NO,UCARD_MK FROM PE_PERSON   
  WHERE FACT_NO = VIS_FACT AND GREATEST(NVL(DATE_MOVE,0),DATE_IN) <= VIS_DATE 
  AND SEC_NO LIKE VIS_SEC AND PNL LIKE VIS_PNL 
  UNION   
  SELECT PNL,PNL_NM,SEC_NO,IMARK,CARD_NO,UCARD_MK FROM PE_PERDEL   
  WHERE FACT_NO = VIS_FACT AND GREATEST(NVL(DATE_MOVE,0),DATE_IN) <= VIS_DATE AND   
  DATE_OUT > VIS_DATE AND SEC_NO LIKE VIS_SEC AND PNL LIKE VIS_PNL   
  ORDER BY 1;  
C_PNL C_1%ROWTYPE;    
BEGIN  
   --獲得周序號 
   VN_WEEKNO :=TO_NUMBER(TO_CHAR(TO_DATE(VIS_DATE,'yyyy-mm-dd'),'ww'));    
   --獲得星期几(先判斷是否有特殊對調日期或國假日) 
   SELECT NVL(WEEK_MK,0),NVL(RATE_WH,0) 
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE;  
   IF VN_WEEKMK=0 THEN 
      VN_WEEKMK :=TO_NUMBER(TO_CHAR(TO_DATE(VIS_DATE,'yyyy-mm-dd'),'d')); 
   END IF;   
   IF VN_RATE_WH=3 THEN VN_WEEKMK :=8; END IF; 
   OPEN C_1 ;     
   LOOP     
   FETCH C_1 INTO C_PNL ;     
   EXIT WHEN C_1%NOTFOUND;  
   --判斷是否有個人工時對調 
   SELECT NVL(DATE_CH,'')  
   INTO VS_DATECH 
   FROM CK_CHANGEWORK  
   WHERE FACT_NO=VIS_FACT AND DATE_ZB=VIS_DATE AND PNL=C_PNL.PNL; 
   IF VS_DATECH<>'' THEN 
      VN_WEEKMK :=TO_NUMBER(TO_CHAR(TO_DATE(VS_DATECH,'yyyy-mm-dd'),'d')); 
   END IF; 
   SELECT NVL(DATE_ZB,'')   
   INTO VS_DATEZB 
   FROM CK_CHANGEWORK 
   WHERE FACT_NO=VIS_FACT  AND DATE_CH=VIS_DATE AND PNL=C_PNL.PNL; 
   IF VS_DATEZB<>'' THEN 
      VN_WEEKMK :=TO_NUMBER(TO_CHAR(TO_DATE(VS_DATEZB,'yyyy-mm-dd'),'d')); 
   END IF;  
   --獲得當日組別 
   SELECT NVL(SEC_OLD,C_PNL.SEC_NO)  
   INTO VS_SECNO 
   FROM (SELECT * FROM PE_SECCHANGE WHERE FACT_NO=VIS_FACT AND DATE_CHANGE > VIS_DATE 
   AND PNL=C_PNL.PNL ORDER BY DATE_CHANGE ASC) WHERE ROWNUM=1;  
   --獲得當日卡別 
   SELECT NVL(CARD_NNO,C_PNL.CARD_NO),NVL(TURN_MARK ,''),NVL(DATE_TURN,'') 
   INTO VS_CARDNO,VS_TURNMK,VS_DATETN 
   FROM (SELECT * FROM CK_CARDTURN WHERE FACT_NO = VIS_FACT AND DATE_TURN <= VIS_DATE 
   AND PNL=C_PNL.PNL ORDER BY DATE_TURN DESC) WHERE ROWNUM=1; 

Answer 1

接上
 --獲得卡別刷卡時間 
   SELECT NVL(TIME_VALIDBF,0),NVL(TIME_VALIDAF,0),NVL(UNCARD_NO1,''),NVL(UNCARD_NO2,''), 
          NVL(UNCARD_NO3,''),NVL(UNCARD_NO4,''),NVL(MORN_ON,''),NVL(MORN_OFF,''), 
          NVL(AFTE_ON,''),NVL(AFTE_OFF,''),NVL(EVEN_ON,''),NVL(EVEN_OFF,''), 
          NVL(TIME_CARD1,''),NVL(TIME_CARD2,''),NVL(TIME_CARD3,''),NVL(TIME_CARD4,''), 
          NVL(WH_MAXZB,0) 
   INTO   VN_TIME_VALIDBF,VN_TIME_VALIDAF,VS_UNCARD_NO1,VS_UNCARD_NO2,VS_UNCARD_NO3, 
          VS_UNCARD_NO4,VS_CARDTIME1,VS_CARDTIME2,VS_CARDTIME3,VS_CARDTIME4,VS_CARDTIME5, 
          VS_CARDTIME6,VS_TIME_CARD1,VS_TIME_CARD2,VS_TIME_CARD3,VS_TIME_CARD4,VN_WH_MAXZB 
   FROM   VIEW_CARDTIME   
   WHERE  FACT_NO=VIS_FACT AND CARD_NO=VS_CARDNO;  
   --判斷是否有特殊出勤設定       
   SELECT NVL(CARD_NO,'') 
   INTO VS_SPECARD 
   FROM CK_SPECEXTRA  
   WHERE FACT_NO=VIS_FACT AND DATE_INFM=VIS_DATE AND PNL=C_PNL.PNL; 
   IF VS_SPECARD<>'' THEN 
      VS_CARDNO :=VS_SPECARD; 
      SELECT NVL(UNCARD_NO1,''),NVL(UNCARD_NO2,''),NVL(UNCARD_NO3,''),NVL(UNCARD_NO4,''), 
             NVL(MORN_ON,''),NVL(MORN_OFF,''),NVL(AFTE_ON,''),NVL(AFTE_OFF,''), 
             NVL(EVEN_ON,''),NVL(EVEN_OFF,''),NVL(TIME_CARD1,''),NVL(TIME_CARD2,''), 
             NVL(TIME_CARD3,''),NVL(TIME_CARD4,''),NVL(WH_MAXZB,0) 
      INTO   VS_UNCARD_NO1,VS_UNCARD_NO2,VS_UNCARD_NO3,VS_UNCARD_NO4,VS_CARDTIME1, 
             VS_CARDTIME2,VS_CARDTIME3,VS_CARDTIME4,VS_CARDTIME5,VS_CARDTIME6, 
             VS_TIME_CARD1,VS_TIME_CARD2,VS_TIME_CARD3,VS_TIME_CARD4,VN_WH_MAXZB 
      FROM CK_SPECEXTRA  
      WHERE FACT_NO=VIS_FACT AND DATE_INFM=VIS_DATE AND PNL=C_PNL.PNL; 
   END IF; 
   IF VN_WEEKMK<>1 AND VN_WEEKMK<>7 AND VN_WEEKMK<>8 THEN 
       VN_WH_STAND :=VN_WH_MAXZB; --周一至周五日標准工時 
   ELSE 
       VN_WH_STAND :=0; --周日,周六及國假日標准工時 
   END IF; 
   VN_NOCARD :=0; 
   VN_NOCARDS :=0; 
   --第一次卡處理 
   IF VS_CARDTIME1='' OR VS_UNCARD_NO1='1' OR VS_UNCARD_NO2='1' OR VS_UNCARD_NO3='1' OR VS_UNCARD_NO4='1' THEN 
       VS_MORN_ON :=''; 
   ELSE 
       IF VS_TURNMK='2' AND VS_DATETN=VIS_DATE THEN 
           VS_MORN_ON :='';  
       ELSE 
           VS_STATIME :=SUBSTR(VS_CARDTIME1,1,4); 
           VS_ENDTIME :=SUBSTR(VS_CARDTIME1,5,4); 
           IF C_PNL.UCARD_MK='Y' AND VN_WEEKMK<>1 AND VN_WEEKMK<>8 THEN 
               VS_MORN_ON :=SUBSTR(VS_CARDTIME1,1,2)||'05'; 
           ELSE 
               IF VS_TIME_CARD1='1' OR VS_TIME_CARD2='1' OR VS_TIME_CARD3='1' OR VS_TIME_CARD4='1' THEN 
                  --隔日刷卡記錄  
                  VS_CARDATE :=TO_CHAR(TO_DATE(VIS_DATE,'YYYY-MM-DD')+1,'YYYYMMDD');  
                  SELECT NVL(MIN(TIME_CARD),''),NVL(MAX(FORGET_MK),0)  
                  INTO VS_MORN_ON,VN_NOCARD 
                  FROM CK_CARDDATA 
                  WHERE FACT_NO=VIS_FACT AND PNL=C_PNL.PNL AND DATE_CARD=VS_CARDATE 
                  AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')> 
                  (TO_DATE(DATE_CARD||VS_STATIME,'yyyy-mm-dd hh24:mi')-(VN_TIME_VALIDBF*60)/(24*60*60)) 
                  AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')< 
                  (TO_DATE(DATE_CARD||VS_ENDTIME,'yyyy-mm-dd hh24:mi')+(VN_TIME_VALIDAF*60)/(24*60*60)) 
                  GROUP BY FACT_NO,PNL,DATE_CARD,TIME_CARD; 
               ELSE 
                  --當日刷卡記錄 
                  SELECT NVL(MIN(TIME_CARD),''),NVL(MAX(FORGET_MK),0)   
                  INTO VS_MORN_ON,VN_NOCARD 
                  FROM CK_CARDDATA 
                  WHERE FACT_NO=VIS_FACT AND PNL=C_PNL.PNL AND DATE_CARD=VIS_DATE 
                  AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')> 
                  (TO_DATE(DATE_CARD||VS_STATIME,'yyyy-mm-dd hh24:mi')-(VN_TIME_VALIDBF*60)/(24*60*60)) 
                  AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')< 
                  (TO_DATE(DATE_CARD||VS_ENDTIME,'yyyy-mm-dd hh24:mi')+(VN_TIME_VALIDAF*60)/(24*60*60)) 
                  GROUP BY FACT_NO,PNL,DATE_CARD,TIME_CARD; 
               END IF; 
               VN_NOCARDS :=VN_NOCARDS+VN_NOCARD; 
           END IF; 
       END IF; 
   END IF; 
   SELECT NVL(DATE_FM||TIME_FM,''),NVL(DATE_TO||TIME_TO,'')  
   INTO VS_TIME_FM,VS_TIME_TO 
   FROM CK_OTAPPLY    
   WHERE FACT_NO=VIS_FACT AND DATE_FM=VIS_DATE AND FORWARD_MK='N' AND PNL=C_PNL.PNL; 
   --加元一廠加班申請上班卡處理 
   IF VIS_FACT='631' AND VS_EVEN_ON ='' AND VS_TIME_FM<>'' THEN 
      SELECT NVL(MIN(TIME_CARD),'')  
      INTO VS_EVEN_ON 
      FROM CK_CARDDATA 
      WHERE FACT_NO=VIS_FACT AND PNL=C_PNL.PNL AND DATE_CARD=VIS_DATE 
      AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')> 
      (TO_DATE(VS_TIME_FM,'yyyy-mm-dd hh24:mi')-(15*60)/(24*60*60)) 
      AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')< 
      (TO_DATE(VS_TIME_FM,'yyyy-mm-dd hh24:mi')+(15*60)/(24*60*60)) 
      GROUP BY FACT_NO,PNL,DATE_CARD,TIME_CARD;   
   END IF; 
   --加元一廠加班申請下班卡處理 
   IF VIS_FACT='631' AND VS_EVEN_OFF ='' AND VS_TIME_TO<>'' THEN 
      SELECT NVL(MIN(TIME_CARD),'')  
      INTO VS_EVEN_OFF 
      FROM CK_CARDDATA 
      WHERE FACT_NO=VIS_FACT AND PNL=C_PNL.PNL AND DATE_CARD=SUBSTR(VS_TIME_TO,1,8) 
      AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')> 
      (TO_DATE(VS_TIME_TO,'yyyy-mm-dd hh24:mi')-(15*60)/(24*60*60)) 
      AND TO_DATE(DATE_CARD||TIME_CARD,'yyyy-mm-dd hh24:mi')< 
      (TO_DATE(VS_TIME_TO,'yyyy-mm-dd hh24:mi')+(15*60)/(24*60*60)) 
      GROUP BY FACT_NO,PNL,DATE_CARD,TIME_CARD;   
   END IF; 
   SELECT COUNT(*) INTO VN_COUNT FROM CK_DAYWORK  
   WHERE FACT_NO = VIS_FACT AND DATE_CARD = VIS_DATE AND PNL = C_PNL.PNL ;    
   IF VN_COUNT > 0 THEN    
    DELETE FROM CK_DAYWORK   
    WHERE FACT_NO = VIS_FACT AND  DATE_CARD = VIS_DATE AND PNL = C_PNL.PNL ;    
   END IF;    
   INSERT INTO CK_DAYWORK(FACT_NO,DATE_CARD,PNL,PNL_NM,SEC_NO,IMARK,CARD_NO,MORN_ON,MORN_OFF,    
               AFTE_ON,AFTE_OFF,EVEN_ON,EVEN_OFF,WEEK_NO,WEEK_MK,WH_STAND,TIMES_NOCARD,OT_APPLY)    
   VALUES(VIS_FACT,VIS_DATE,C_PNL.PNL,C_PNL.PNL_NM,VS_SECNO,C_PNL.IMARK,VS_CARDNO,VS_MORN_ON,    
          VS_MORN_OFF,VS_AFTE_ON,VS_AFTE_OFF,VS_EVEN_ON,VS_EVEN_OFF,VN_WEEKNO,VN_WEEKMK,VN_WH_STAND, 
          VN_NOCARDS,VS_TIME_FM||'至'||VS_TIME_TO);       
   END LOOP ;    
   CLOSE C_1 ;    
   COMMIT ;    
   VOS_OUT1 := 1 ;    
   VOS_OUT2 := VIS_DATE + '日考勤生成成功!' ;    
END P_CK_DAYWORK  ;  

Answer 2

===========================================================================
其中
================================================
--獲得星期几(先判斷是否有特殊對調日期或國假日) 
   SELECT NVL(WEEK_MK,0),NVL(RATE_WH,0) 
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE;  
================================================
當沒有符合WHERE條件的記錄時是否NVL無效??????
===============================================
我將
 SELECT NVL(WEEK_MK,0),NVL(RATE_WH,0) 
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE; 
改為
 SELECT A.WEEK_HK,A.RATE_WH
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM (SELECT NVL(WEEK_MK,0) WEEK_HK,NVL(RATE_WH,0) RATE_WH  FROM        CK_HOLIDAY
         WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE 
         UNION
         SELECT 0,0 FROM  CK_HOLIDAY WHERE 
                (SELECT COUNT(*) FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT
                 AND DATE_LAW=VIS_DATE )=0 ) A;
時如表CK_HOLIDAY 無任何數據記錄時出錯了,那應該怎麼改才能達到效果::
黨表CK_HOLIDAY 無符合HWERE條件的記錄或表中根本無數據記錄時符0給變量VN_WEEKMK,VN_RATE_WH

Answer 3

begin
SELECT A.WEEK_HK,A.RATE_WH
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM (SELECT NVL(WEEK_MK,0) WEEK_HK,NVL(RATE_WH,0) RATE_WH  FROM        CK_HOLIDAY
         WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE 
         UNION
         SELECT 0,0 FROM  CK_HOLIDAY WHERE 
                (SELECT COUNT(*) FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT
                 AND DATE_LAW=VIS_DATE )=0 ) A;
 exception when others then 
VN_WEEKMK:=0;VN_RATE_WH :=0;
end;

Answer 4

begin
SELECT A.WEEK_HK,A.RATE_WH
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM (SELECT NVL(WEEK_MK,0) WEEK_HK,NVL(RATE_WH,0) RATE_WH  FROM        CK_HOLIDAY
         WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE 
         UNION
         SELECT 0,0 FROM  CK_HOLIDAY WHERE 
                (SELECT COUNT(*) FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT
                 AND DATE_LAW=VIS_DATE )=0 ) A;
 exception when others then 
VN_WEEKMK:=0;VN_RATE_WH :=0;
end;這可以直接寫到存儲過程里面去嗎??

Answer 5

begin
SELECT NVL(WEEK_MK,0),NVL(RATE_WH,0) 
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE; 
EXCEPTION
WHEN NO_DATA_FOUND THEN
VN_WEEKMK:=0;VN_RATE_WH :=0;
end;也可以试：
SELECT NVL(max(WEEK_MK),0),NVL(max(RATE_WH),0) 
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE; 

Answer 6

還有問題,我執行的時候提示ERROR
Compilation errors for PROCEDURE KYHRMS.P_CK_DAYWORK
#13#10Error: PLS-00103: 發現了符號 "SELECT" 當您等待下列事項之一發生時: 
        ( - + mod
          not null others <an identifier>
          <a double-quoted delimited-identifier> <a bind variable> avg
          count current exists max min prior sql stddev sum variance
          execute forall time timestamp interval date
          <a string literal with character set specification>
          <a number> <a single-quoted SQL string>
Line: 87
Text: WHERE (SELECT COUNT(*)
#13#10Error: PLS-00103: 發現了符號 "=" 當您等待下列事項之一發生時: 
        ; return
          returning and or
Line: 90
Text: AND DATE_LAW = VIS_DATE) = 0) A;
怎麼解決??

Answer 7

?：
begin
SELECT A.WEEK_HK,A.RATE_WH
INTO VN_WEEKMK,VN_RATE_WH 
FROM (
 SELECT NVL(WEEK_MK,0) WEEK_HK,NVL(RATE_WH,0) RATE_WH  FROM CK_HOLIDAY
 WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE 
 UNION
 SELECT 0,0 FROM  dual 
 WHERE not exists(
   SELECT * FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT
   AND DATE_LAW=VIS_DATE 
) A;
 exception when others then 
VN_WEEKMK:=0;VN_RATE_WH :=0;
end;
直接用：
begin
SELECT NVL(WEEK_MK,0),NVL(RATE_WH,0) 
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE; 
EXCEPTION
WHEN NO_DATA_FOUND THEN
VN_WEEKMK:=0;VN_RATE_WH :=0;
end;也可以试：
SELECT NVL(max(WEEK_MK),0),NVL(max(RATE_WH),0) 
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE; 

Answer 8

begin
SELECT A.WEEK_HK,A.RATE_WH
   INTO VN_WEEKMK,VN_RATE_WH 
   FROM (SELECT NVL(WEEK_MK,0) WEEK_HK,NVL(RATE_WH,0) RATE_WH  FROM        CK_HOLIDAY
         WHERE FACT_NO=VIS_FACT AND DATE_LAW=VIS_DATE 
         UNION
         SELECT 0,0 FROM  CK_HOLIDAY WHERE 
                (SELECT COUNT(*) FROM CK_HOLIDAY WHERE FACT_NO=VIS_FACT
                 AND DATE_LAW=VIS_DATE )=0 ) A;
 exception when others then 
VN_WEEKMK:=0;VN_RATE_WH :=0;
end;
就是在這段語句中的WHERE (SELECT COUNT(*)和AND DATE_LAW = VIS_DATE) = 0) A;出錯