Perform Exception Handling with User-Defined Errors

Question

On occasion, some of Brewbean’s customers mistakenly leave an item out of a basket already checked out, therefore they create a new basket containing the missing items. Though they request that the baskets be combined and hence they are not charged extra shipping. The screen has been developed to permit an employee to modify the basket id of items in the BB_BASKETITEM table to another existing basket to merge the baskets. The block has been constructed to support this screen and can be found at the end of this question. Though an exception requires to be added to trap the condition in which an invalid basket id is entered for original basket. In this situation, the UPDATE affects no rows however does not raise an Oracle error. The handler must display a message stating “invalid original basket id”. Employ a host variable named G_OLD with a value of 30 and a host variable named G_NEW with a value of 4 to give the values to the block. First confirm that no item rows exist in the BB_BASKETITEM table with a basket id of 30.
 
BEGIN
  UPDATE bb_basketitem
   SET idBasket = :g_new
   WHERE idBasket = :g_old;
END;
/

Answer 1

create or replace function "BOB_UPDATE"
(g_old in NUMBER,
g_new in NUMBER)
return VARCHAR2
is
OID NUMBER;
state_missing EXCEPTION;
begin
SELECT count(IDBASKET) INTO OID FROM BB_BASKETITEM WHERE IDBASKET=G_OLD GROUP BY  IDBASKET ;
IF OID IS NULL THEN
  RAISE state_missing;
ELSE
   UPDATE bb_basketitem SET idBasket =g_new WHERE idBasket =g_old;
END IF;
RETURN 'UPDATED SUCCESSFULLY';
EXCEPTION
   WHEN state_missing THEN
      RETURN 'INVALID BASKET ID';
   WHEN OTHERS THEN
      RETURN 'INVALID BASKET ID';
end;


Testing Code:   
SELECT BOB_UPDATE(30,4) from dual;