游標(biāo)在Oracle數(shù)據(jù)庫存儲過程中的作用

作為關(guān)系型數(shù)據(jù)庫市場的老大，Oracla占有舉足輕重的地位。雖然在操作上不如SQLSERVER那樣方便，但是他的強(qiáng)大的功能，還是吸引來大批大批的追隨著。本人作為ORACLE菜鳥，在工作當(dāng)中也偶爾使用Oracle。以下記錄的上由于工作需要寫的Oracle的<br>使用游標(biāo)的儲存過程，個人覺得比較有代表性。希望給初學(xué)者一定的幫助，也給自己加深一下印象。

在ORACLE中，他以一個語句塊為一個默認(rèn)的事務(wù)。也就是說，如果你就單單只執(zhí)行一段ORACLE的語句塊，他默認(rèn)是以事務(wù)的形式執(zhí)行的。


01 CREATE OR REPLACE PROCEDURE sp_EditInlayOut(

02 FID NUMBER, --修改記錄的ID T_INLAYOUT表的主鍵

03 InlayBoxIDs varchar2, --修改的記錄

04 BoxCount number, --裝箱數(shù)量

05 ApplyUserID varchar2, --申請人編號

06 StoreUserID varchar2, --庫管編號

07 ConfirmState char, --確認(rèn)狀態(tài)

08 ExistState char, --存在狀態(tài)

09 strErr OUT varchar2 --存儲過程執(zhí)行結(jié)果。成功返回空，失敗返回錯誤原因

10 )

11 AS

12 --定義變量

13 v_Now DATE;

14 v_Now2 date;

15 v_LogID number;

16 v_ChipID number;

17 v_sql varchar2(2000);

18 BEGIN

19

20 --記錄日志

21 INSERT INTO T_InlayOut_Log(F_InlayBoxIDs,f_Boxcount,f_Applyuserid,f_Storeuserid,f_Addtime,f_Confirmstate

22 ,f_Existstate, f_modifyid, f_modifytime, f_modifyuserid )

23 ((SELECT F_InlayBoxIDs,f_Boxcount,f_Applyuserid,f_Storeuserid,f_Addtime,f_Confirmstate,f_Existstate

24 ,FID,SYSDATE,StoreUserID FROM T_InlayOut WHERE F_ID=FID));

25 --取剛插入記錄的ID

26 select seq_t_inlayout_log.currval into v_LogID from dual;

27 --定義游標(biāo)

28 DECLARE CURSOR myCusor IS SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID IN (SELECT f_ID FROM

29 T_InlayBox where F_InlayOutID = FID);

30 --開始使用游標(biāo)取數(shù)據(jù)

31 BEGIN

32 OPEN myCusor;

33

34 LOOP

35 FETCH myCusor INTO v_ChipID;

36 --游標(biāo)取不到數(shù)據(jù)則退出

37 EXIT WHEN myCusor%NOTFOUND;

38

39 SELECT MIN(F_CurrentTime) INTO v_Now FROM t_Chipstatehistory WHERE

40 　　　　　　 (F_HistoryState = 'Confirm_InlayIn') AND F_ChipID = v_ChipID;

41 --改變芯片表的狀態(tài)

42 UPDATEt_chip SET f_State = 'Confirm_InlayIn',F_CompareTime = v_Now WHERE F_ID = v_ChipID;

43 --保存芯片狀態(tài)歷史記錄

44 INSERT INTO T_CHIPSTATEHISTORY(f_chipid, f_Historystate,F_TABLEID,f_Currenttime,F_TABLENAME)

45 VALUES

46 (v_ChipID,'Confirm_InlayIn',v_LogID,SYSDATE,'T_InlayOut_Log');

47

48 END LOOP;

49 CLOSE myCusor;

50 END;

51

52 --選擇最近芯片狀態(tài)變更時間

53 --SELECT MIN(F_CURRENTTIME) INTO v_NOW FROM T_CHIPSTATEHISTORY WHERE F_HISTORYSTATE = 20

54 AND F_CHIPID IN (SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID=(SELECT F_ID FROM T_InlayBox

55 WHERE F_InlayOutID=FID));

56

57 --將芯片表中芯片狀態(tài)更新到以前狀態(tài)

58 --UPDATE T_CHIP SET F_State=20,F_CompareTime=v_NOW WHERE F_InlayBoxID IN (SELECT F_ID FROM

59 T_InlayBox WHERE F_InlayOutID =FID);

60 --記錄芯片狀態(tài)變更日志

61 --INSERT INTO T_ChipStateHistory (F_ChipID,f_Historystate,f_Tableid,f_Currenttime,f_Tablename)VALUES

62 --((SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID=(SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID)),

63 20,v_LogID,SYSDATE,'T_InlayOut_Log');

64

65

66 --將Inlay出庫箱表中以前的數(shù)據(jù)更新到以前狀態(tài)

67 UPDATE T_InlayBox SET F_State=2,F_InlayOutID=null WHERE F_InlayOutID =FID;

68

69 --編輯時將新的INLAY出庫信息更新

70 UPDATE T_InlayOut SET F_InlayBoxIDs=InlayBoxIDs,f_Boxcount=BoxCount,f_Applyuserid=ApplyUserID,

71 f_Storeuserid=StoreUserID,f_Confirmstate=ConfirmState,F_ExistState=ExistState,F_ConfirmTime=null

72 WHERE F_ID=FID;

73

74 --更新T_InlayBox 新的狀態(tài)

75 --UPDATE T_InlayBox SET F_State=3,F_InlayOutID=FID WHERE F_ID in (InlayBoxIDs);

76 v_sql := 'UPDATE T_InlayBox SET F_State=3,F_InlayOutID='||FID||' WHERE F_ID in ('||InlayBoxIDs||')';

77 --立即執(zhí)行v_sql

78 EXECUTE IMMEDIATE v_sql;

79

80 SELECT SYSDATE INTO v_Now2 FROM DUAL;

81 --更新芯片表狀態(tài)

82 UPDATE T_Chip SET F_State='No_Confirm_InlayOut',F_CompareTime=v_Now2 WHERE F_InlayBoxID IN

83 (SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID);

84 --記錄當(dāng)前操作日志

85 INSERT INTO T_ChipStateHistory (F_ChipID,f_Historystate,f_Tableid,f_Currenttime,f_Tablename)

86 SELECT F_ID,'No_Confirm_InlayOut',v_LogID,v_Now2,'T_InlayOut_Log' FROM T_CHIP WHERE F_InlayBoxID IN

87 (SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID);

88 --提交

89 COMMIT;

90 --發(fā)生異常時返回錯誤碼

91 EXCEPTION

92 WHEN OTHERS THEN

93 strErr := substr(sqlerrm,1,100);

94 ROLLBACK;

95 END sp_EditInlayOut;


但是在SQLSERVER中，除非你將所有的T-SQL語句塊以顯示的方式【BEGIN TRANSACTION ....END TRANSACTION】申明在事務(wù)中，否則SQLSERVER會將語句塊中的每一句作為一個單獨的默認(rèn)事務(wù)執(zhí)行。

此外，游標(biāo)是一種比較占I/O資源的操作，使用完后應(yīng)該及時關(guān)閉，以釋放系統(tǒng)資源。