Oracle keyword about for update in cursor

The following code is available:

Declare

--
Query EMP
.
Name

Cursor cur_emp

Is

Select empno
,
Ename
,
Job

From EMP

Where empno
=
7369

For
Update of ename
;

Begin

For
Return_cur in cur_emp

Loop

Update EMP

Set ename
=
'Lhg'

Where current of cur_emp
;

End Loop
;

End
;

Where for update of ename

It means to lock the rows in the Table. After testing, the following ename can be written as any field in the table, because the lowest lock level in Oracle is the row lock, and the field is not locked.

The following is the role of the row lock:

1. the row lock starts with the open of a cursor and ends with the commit of a commit or rollback instead of the end of a cursor ).

2. when the row of a table is locked in one cursor, if another cursor is used in this session to operate the row record, it will wait until the first cursor is submitted, the second cursor will start to execute the change operation.

3. When the first cursor cannot be submitted due to an operation error, the second cursor will keep waiting, resulting in a deadlock. To prevent this problem, specify the Nowait option after for update of ename. In this way, when the second cursor does not keep waiting
ORA-00054 [Resource busy and acquire with Nowait specified]


Of
Message.

4. Since the field after for update of can be any one, can it be left empty? If you do not need to specify the Nowait option, the subsequent fields can be left empty. If you must specify the Nowait option, you must specify at least one field.

5. Another method is to use rowid
Replace where currentYour_cursor_name

Statement.

The following code:

Declare

--
Query EMP
.
Name

Cursor cur_emp

Is

Select
.
Deptno
,

A
.
Dname
,

A
.
Rowid
,

B
.
Rowid rowid_1

From dept
,
EMP B

Where empno
=
7369

And
A
.
Deptno
=
B
.
Deptno

For
Update Nowait
;

--
Local Variables

V_deptno Dept
.
Deptno
%
Type
;

V_dname Dept
.
Dname
%
Type
;

V_rowid rowid
;

V_rowid_1 rowid
;

Begin

Open cur_emp
;

Loop

Fetch cur_emp into v_deptno
,
V_dname
,
V_rowid
,
V_rowid_1
;

Exit
When cur_emp
%
Notfound
;

Update Dept

Set dname
=
'Abc'

Where rowid
=
V_rowid
;

Update EMP

Set ename
=
'Frank'

Where rowid
=
V_rowid_1
;

End Loop
;

Close cur_emp
;

Commit
;

Exception

When others then

Rollback
;

Raise
;

End
;

Therefore, the recommended for update habit is:

Nowait
Certainly with for update
Later.

Use rowid directly
Replace where currentYour_cursor_name

Statement,
Especially in the relatively complex procedures.

Commit
The end of the program must exist. To prevent deadlock.

Exception
Rollback in
Is the most basic requirement.