在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资源的操作，使用完后应该及时关闭，以释放系统资源。
本文出自seven的测试人生公众号最新内容请见作者的GitHub页：http://qaseven.github.io/
