Untitled

CREATE OR REPLACE PROCEDURE UpdateUsrSub(name IN KM9185.USR.USERNAME%TYPE)
  AS
    insufficient_coins EXCEPTION;
    coins_count PLS_INTEGER := 0;
    sub_end TIMESTAMP;
  BEGIN
    DBMS_OUTPUT.ENABLE;

    SELECT coins, subscriptionenddate
    INTO   coins_count, sub_end
    FROM   km9185.usr
    WHERE  username = name;

    IF (coins_count < 500) THEN
      RAISE insufficient_coins;
    ELSE
      IF sub_end IS NULL THEN
        UPDATE km9185.usr SET subscriptionenddate = systimestamp + 365.25, coins = coins - 500
        WHERE username = name;
      ELSE
        UPDATE km9185.usr SET subscriptionenddate = sub_end + 365.25, coins = coins - 500
        WHERE username = name;
      END IF;
    END IF;

  EXCEPTION
    WHEN insufficient_coins THEN
      DBMS_OUTPUT.PUT_LINE(name || ' has insufficient funds');

  END UpdateUsrSub;
/

BEGIN
  UPDATEUSRSUB('me');
END;
/
SELECT SUBSCRIPTIONENDDATE, COINS FROM KM9185.USR WHERE username = 'me';

UPDATE km9185.usr SET subscriptionenddate = null
WHERE username = 'me'

UPDATE km9185.usr SET coins = 1200
WHERE username = 'me'