Oracle資料庫遊標在預存程序中的使用

 　　作為關係型資料庫市場的老大，Oracla佔有舉足輕重的地位。雖然在操作上不如SQLSERVER那樣方便，但是他的強大的功能<br>還是吸引來大批大批的追隨著。本人作為ORACLE菜鳥，在工作當中也偶爾使用Oracle。以下記錄的上由於工作需要寫的Oracle的<br>使用遊標的儲存過程，個人覺得比較有代表性。希望給初學者一定的協助，也給自己加深一下印象。

在ORACLE中，他以一個語句塊為一個預設的事務。也就是說，如果你就單單只執行一段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;

 

   但是在SQLSERVER中，除非你將所有的T-SQL語句塊以顯示的方式【BEGIN TRANSACTION ....END TRANSACTION】申明在事務中，否則SQLSERVER會將語句塊中的每一句作為一個單獨的預設事務執行。

  此外，遊標是一種比較占I/O資源的操作，使用完後應該及時關閉，以釋放系統資源。