Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Short syntax to lock a row (or two) in PL/SQL?

David Balažic2 days ago — edited 2 days ago

In PL/SQL I want to lock a row and the current solution is :

create table t1 (pk1 INTEGER PRIOMARY KEY, c1 number, c2 number);

-- is part of a PACKAGE ...
procedure proc1(p_pk1 IN INTEGER, p_foo IN INTEGER)
is
dummy NUMBER;
begin
    -- first lock the row
    SELECT pk1 INTO dummy FROM t1 WHERE pk1=p_pk1 FOR UPDATE;
    -- now do the work... code omitted for clarity, it does several updates on the locked row
    -- EDIT/NOTE: the variable "dummy" is not used elsewhere, it is there only because the syntax requires it
END;

Is there a way to do it without the dummy variable definition and the “INTO dummy” parts ?

They distract from what the code is actually doing.

How about this:

procedure proc1b(p_pk1 IN INTEGER, p_foo IN INTEGER)
is
begin
    -- first lock the row
    UPDATE t1 SET pk1=pk1 WHERE pk1=p_pk1;
    -- now do the work... code omitted for clarity, it does several updates on the locked row
END;

Looks shorter, clearer, no unused variable. Still has a “dummy” operation (the pk1=pk1 part) tho.

Opinions?

The second version can be also easily changed to lock more than one row (in a single statement).

Am am currently working with database version 11.2 both solutions for 19c would also be welcome.

Comments
Post Details
Added 2 days ago
13 comments
159 views