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;