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.