CODEWITHSUNDEEP
stored-proceduresoracle11g
user2482822
user2482822

Reputation: 197

Oracle Stored Procedure update rows if records exist

I have following Oracle stored procedure. Either subcode or stylecode is passed in. Currently it is update rows if value is not null. I want to add logic so that make update only if rows are exists on the table, if not, I like to print message like "The subcode xxxx doesn't exists" or "The stylecode xxxx doesn't exists". I don't think merge into works here.

create or replace PROCEDURE "REMOVE_PRICES"
(
  RESULT OUT VARCHAR2 
  , STYLECODE_ IN NUMBER 
  , SUBCODE_ IN NUMBER 
) AS 
BEGIN
IF (SUBCODE_ is null AND STYLECODE_ is null)
THEN
    raise_application_error(-20005, 'ERROR: Please provide either SUBCODE or STYLECODE!');
END IF;
IF SUBCODE_ IS NOT NULL THEN
  UPDATE prices
  SET type = null
  WHERE subcode=SUBCODE_;
  RESULT := SQL%ROWCOUNT || ' price for subcode ' || SUBCODE_ || ' is    removed';

ELSIF STYLECODE_ IS NOT NULL THEN
  UPDATE prices
  SET type = null
  WHERE stylecode=STYLECODE_;
  RESULT := SQL%ROWCOUNT || ' price for stylecode ' || STYLECODE_ || ' is removed';
END IF;
END REMOVE_PRICES;

Upvotes: 0

Views: 378

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can't only choose to do the update if it will affect rows unless you query with the same conditions first, doing a count; and you could still have a race condition with other sessions that means the data could change between the select and the update, unless you lock the rows. Which all seems a bit excessive and costly.

You can just check the value of SQL%ROWCOUNT and show that message if it is zero, or your current message otherwise:

IF SUBCODE_ IS NOT NULL THEN
  UPDATE prices
  SET type = null
  WHERE subcode=SUBCODE_;
  IF SQL%ROWCOUNT = 0 then
    RESULT := 'The subcode ' || SUBCODE || ' does not exist';
  ELSE
    RESULT := SQL%ROWCOUNT || ' price for subcode ' || SUBCODE_ || ' is removed';
  END IF;
ELSIF STYLECODE_ IS NOT NULL THEN
  UPDATE prices
  SET type = null
  WHERE stylecode=STYLECODE_;
  IF SQL%ROWCOUNT = 0 then
    RESULT := 'The stylecode ' || STYLECODE || ' does not exist';
  ELSE
    RESULT := SQL%ROWCOUNT || ' price for stylecode ' || STYLECODE_ || ' is removed';
  END IF;
END IF;

Upvotes: 1

Related Questions