Oracle數據庫游標在存儲過程中的使用

CREATE OR REPLACE PROCEDURE sp_EditInlayOut(
                 FID     NUMBER,                    --修改記錄的ID T_INLAYOUT表的主鍵
                 InlayBoxIDs varchar2,          --修改的記錄
                 BoxCount number,              --裝箱數量
                 ApplyUserID varchar2,        --申請人編號
                 StoreUserID varchar2,         --庫管編號
                 ConfirmState char,              --確認狀態
                 ExistState char,                    --存在狀態
                 strErr OUT varchar2             --存儲過程執行結果。成功返回空，失敗返回錯誤原因
)
AS
   --定義變量
   v_Now DATE;                                     
   v_Now2 date;                                        
   v_LogID number;
   v_ChipID number;
   v_sql varchar2(2000);
BEGIN

      --記錄日志
      INSERT INTO T_InlayOut_Log(F_InlayBoxIDs,f_Boxcount,f_Applyuserid,f_Storeuserid,f_Addtime,f_Confirmstate
         ,f_Existstate, f_modifyid, f_modifytime, f_modifyuserid )
                        ((SELECT F_InlayBoxIDs,f_Boxcount,f_Applyuserid,f_Storeuserid,f_Addtime,f_Confirmstate,f_Existstate
                         ,FID,SYSDATE,StoreUserID FROM T_InlayOut WHERE F_ID=FID));
      --取剛插入記錄的ID
      select seq_t_inlayout_log.currval into v_LogID from dual;
      --定義游標
       DECLARE CURSOR myCusor IS SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID IN (SELECT f_ID FROM 
       T_InlayBox where F_InlayOutID = FID);
      --開始使用游標取數據
       BEGIN
            OPEN myCusor;

            LOOP
                FETCH myCusor INTO v_ChipID;
                --游標取不到數據則退出
                EXIT WHEN myCusor%NOTFOUND;    

                      SELECT MIN(F_CurrentTime) INTO v_Now FROM t_Chipstatehistory WHERE
　　　　　　 (F_HistoryState = 'Confirm_InlayIn') AND F_ChipID = v_ChipID;
                      --改變芯片表的狀態
                      UPDATEt_chip SET f_State = 'Confirm_InlayIn',F_CompareTime = v_Now  WHERE F_ID = v_ChipID;
                      --保存芯片狀態歷史記錄
                      INSERT INTO T_CHIPSTATEHISTORY(f_chipid, f_Historystate,F_TABLEID,f_Currenttime,F_TABLENAME) 
                     VALUES
                      (v_ChipID,'Confirm_InlayIn',v_LogID,SYSDATE,'T_InlayOut_Log');

            END LOOP;
            CLOSE myCusor;
       END;

      --選擇最近芯片狀態變更時間
      --SELECT MIN(F_CURRENTTIME) INTO v_NOW  FROM T_CHIPSTATEHISTORY WHERE F_HISTORYSTATE = 20 
      AND F_CHIPID IN (SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID=(SELECT F_ID FROM T_InlayBox 
        WHERE F_InlayOutID=FID));

      --將芯片表中芯片狀態更新到以前狀態
      --UPDATE T_CHIP SET F_State=20,F_CompareTime=v_NOW WHERE F_InlayBoxID IN (SELECT F_ID FROM 
       T_InlayBox WHERE F_InlayOutID =FID);
      --記錄芯片狀態變更日志
      --INSERT INTO  T_ChipStateHistory (F_ChipID,f_Historystate,f_Tableid,f_Currenttime,f_Tablename)VALUES
      --((SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID=(SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID)),
          20,v_LogID,SYSDATE,'T_InlayOut_Log');


      --將Inlay出庫箱表中以前的數據更新到以前狀態
      UPDATE T_InlayBox SET F_State=2,F_InlayOutID=null WHERE F_InlayOutID =FID;

      --編輯時將新的INLAY出庫信息更新
      UPDATE T_InlayOut SET F_InlayBoxIDs=InlayBoxIDs,f_Boxcount=BoxCount,f_Applyuserid=ApplyUserID,
      f_Storeuserid=StoreUserID,f_Confirmstate=ConfirmState,F_ExistState=ExistState,F_ConfirmTime=null 
      WHERE F_ID=FID;

      --更新T_InlayBox 新的狀態
      --UPDATE T_InlayBox SET F_State=3,F_InlayOutID=FID WHERE F_ID in (InlayBoxIDs);
      v_sql := 'UPDATE T_InlayBox SET F_State=3,F_InlayOutID='||FID||' WHERE F_ID in ('||InlayBoxIDs||')';
       --立即執行v_sql
      EXECUTE IMMEDIATE  v_sql;

      SELECT SYSDATE INTO  v_Now2 FROM DUAL;
      --更新芯片表狀態
      UPDATE T_Chip SET F_State='No_Confirm_InlayOut',F_CompareTime=v_Now2  WHERE F_InlayBoxID IN
       (SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID);
      --記錄當前操作日志
      INSERT INTO  T_ChipStateHistory (F_ChipID,f_Historystate,f_Tableid,f_Currenttime,f_Tablename) 
     SELECT F_ID,'No_Confirm_InlayOut',v_LogID,v_Now2,'T_InlayOut_Log' FROM T_CHIP WHERE F_InlayBoxID IN
     (SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID);
       --提交
       COMMIT;
     --發生異常時返回錯誤碼
     EXCEPTION
        WHEN OTHERS THEN
        strErr := substr(sqlerrm,1,100);
        ROLLBACK;
END sp_EditInlayOut;