Tips for Using cursor in Oracle stored procedures

 

In Oracle, a statement block is used as a default transaction. That is to say, if you only execute a block of ORACLE statements, it is executed in the form of transactions by default.

Create or replace procedure sp_EditInlayOut (

Fid number, -- modify the primary key of the Record ID T_INLAYOUT table

InlayBoxIDs varchar2, -- Modified record

BoxCount number, -- number of containers

ApplyUserID varchar2, -- requester ID

StoreUserID varchar2, -- Database Management number

ConfirmState char, -- confirm the status

ExistState char, -- existing State

StrErr OUT varchar2 -- stored procedure execution result. The returned result is null. The cause of the failure is returned.

)

AS

-- Define variables

V_Now DATE;

V_Now2 date;

V_LogID number;

V_ChipID number;

V_ SQL varchar2 (2000 );

BEGIN

-- Record logs

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 ));

-- Obtain the ID of the newly inserted record

Select seq_t_inlayout_log.currval into v_LogID from dual;

-- Define a cursor

Declare cursor myCusor is select F_ID FROM T_CHIP WHERE F_InlayBoxID IN (SELECT f_ID FROM

T_InlayBox where F_InlayOutID = FID );

-- Start to use a cursor to retrieve data

BEGIN

OPEN myCusor;

LOOP

FETCH myCusor INTO v_ChipID;

-- Exit if the cursor fails to retrieve data

Exit when myCusor % NOTFOUND;

Select min (F_CurrentTime) INTO v_Now FROM t_Chipstatehistory WHERE

(F_HistoryState = 'Confirm _ InlayIn ') AND F_ChipID = v_ChipID;

-- Change the status of the chip table

UPDATEt_chip SET f_State = 'Confirm _ InlayIn ', F_CompareTime = v_Now WHERE F_ID = v_ChipID;

-- Save the history of the chip status

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 the latest chip status change time
-- 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 the chip status in the chip table to the previous status.
-- 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 );
-- Record the chip status change log
-- 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 ');
-- Update the previous data in the Inlay warehouse receiving table to the previous status.
UPDATE T_InlayBox SET F_State = 2, F_InlayOutID = null WHERE F_InlayOutID = FID;
-- Update the new INLAY library information during editing.
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;
-- Update new T_InlayBox status
-- 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 | ')';
-- Execute v_ SQL immediately
Execute immediate v_ SQL;
Select sysdate into v_Now2 from dual;
-- Update the chip Table Status
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 );
-- Record the current operation log
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 );
-- Submit
COMMIT;
-- An error code is returned when an exception occurs.
EXCEPTION
WHEN OTHERS THEN
StrErr: = substr (sqlerrm, 1,100 );
ROLLBACK;
END sp_EditInlayOut;
However, in SQLSERVER, unless you display all the T-SQL statement blocks... End transaction: Declares the statement in the TRANSACTION. Otherwise, SQLSERVER will execute each sentence in the statement block as a separate default TRANSACTION.
In addition, a cursor is an operation that accounts for I/O resources. It should be closed immediately after use to release system resources.