SQL & PL/SQL

I am interested to hear your thoughts on this pseudocode, actual test follows.

update table 1
if sql%rowcount >0 then
update table 2
elsif sql%rowcount = 0 then
insert into table 1

One might think that if the first update succeeded and the second update failed then the insert would take place
because sql%rowcount for the second and failed update is 0
but this is not the case



TBLONE AND TBLTWO ARE THE SAME (ID NUMBER, NAME VARCHAR2(50))

sqlplus > DESC TBLONE;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER(38)
NAME VARCHAR2(50)

sqlplus > DESC TBLTWO;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER(38)
NAME VARCHAR2(50)

sqlplus > INSERT INTO TBLONE VALUES(1,'ROW_ONE_TBL_ONE');

1 row created.

sqlplus > INSERT INTO TBLTWO VALUES(1,'ROW_ONE_TBL_TWO');

1 row created.

sqlplus > COMMIT;

Commit complete.


sqlplus > CREATE OR REPLACE PROCEDURE test_rc(p_id IN NUMBER,
2 p_name IN VARCHAR2) AS
3 BEGIN
4 UPDATE tblone SET NAME = p_name WHERE id = p_id;
5 IF SQL%ROWCOUNT > 0
6 THEN
7 UPDATE tbltwo
8 SET NAME = p_name || ' update tbltwo'
9 WHERE id = p_id + 1;
10 ELSIF SQL%ROWCOUNT = 0
11 THEN
12 INSERT INTO tblone VALUES (p_id + 15, p_name || ' insert tblone');
13 END IF;
14 COMMIT;
15 END;
16 /

Procedure created.


THIS SHOULD ONLY INSERT INTO TBLONE
sqlplus > EXEC TEST_RC(2,'UPDATE ROW TWO TBL ONE');

PL/SQL procedure successfully completed.

sqlplus > SELECT * FROM TBLONE;

ID NAME
---------- --------------------------------------------------
1 ROW_ONE_TBL_ONE
17 UPDATE ROW TWO TBL ONE insert tblone



sqlplus > SELECT * FROM TBLTWO;

ID NAME
---------- --------------------------------------------------
1 ROW_ONE_TBL_TWO


THIS SHOULD UPDATE TBLONE, FAIL TO UPDATE TBLTWO, AND THEN ? INSERT TBLONE ?
sqlplus > EXEC TEST_RC(1,'UPDATE ROW ONE TBL ONE');

PL/SQL procedure successfully completed.

sqlplus > SELECT * FROM TBLONE;

ID NAME
---------- --------------------------------------------------
1 UPDATE ROW ONE TBL ONE
17 UPDATE ROW TWO TBL ONE insert tblone

sqlplus > SELECT * FROM TBLTWO;

ID NAME
---------- --------------------------------------------------
1 ROW_ONE_TBL_TWO

!! if the value in sql%rowcount did not persist we would see a 3rd row
in tblone with ( 16, UPDATE ROW TWO TBL ONE insert tblone )

sqlplus > INSERT INTO TBLTWO VALUES(2,'ROW TWO TBL TWO');

1 row created.

sqlplus > COMMIT;

Commit complete.

sqlplus > SELECT * FROM TBLONE;

ID NAME
---------- --------------------------------------------------
1 UPDATE ROW ONE TBL ONE
17 UPDATE ROW TWO TBL ONE insert tblone

sqlplus > SELECT * FROM TBLTWO;

ID NAME
---------- --------------------------------------------------
1 ROW_ONE_TBL_TWO
2 ROW TWO TBL TWO


THIS SHOULD UPDATE TBLONE AND TBLTWO
sqlplus > EXEC TEST_RC(1,'LAST EXECUTION');

PL/SQL procedure successfully completed.

sqlplus >
sqlplus > SELECT * FROM TBLONE;

ID NAME
---------- --------------------------------------------------
1 LAST EXECUTION
17 UPDATE ROW TWO TBL ONE insert tblone

sqlplus > SELECT * FROM TBLTWO;

ID NAME
---------- --------------------------------------------------
1 ROW_ONE_TBL_TWO
2 LAST EXECUTION update tbltwo


SO IT LOOKS LIKE THE VALUE IN SQL%ROWCOUNT PERSISTS
ACROSS FURTHER DML WHEN EMBEDDED IN CONDITIONAL STRUCTURE