Sedge

CREATE OR REPLACE PROCEDURE registerStove
(i_name IN VARCHAR2, i_phone IN VARCHAR2,
i_email IN VARCHAR2, i_serial IN NUMBER)
IS
  custID A5Customer.customerID%TYPE;
  stoveSerial A5Stove.serialNumber%TYPE;
BEGIN
  custID := getCustomerID(i_name, i_phone, i_email);
  stoveSerial := getStoveSerial(i_serial);
IF stoveSerial IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('Stove Serial does not exist');
  ROLLBACK;
ELSE
  INSERT INTO A5Registration
  VALUES (custID, stoveSerial, CURRENT_DATE);
  COMMIT;
END IF;
END registerStove;


CREATE OR REPLACE FUNCTION getCustomerID
 (i_custName IN VARCHAR2, i_custPhone IN VARCHAR2, i_custEmail IN VARCHAR2)
 RETURN NUMBER
IS
 r_custID NUMBER;
BEGIN
 SELECT customerID
 INTO r_custID
 FROM A5Customer
 WHERE name=i_custName AND phone=i_custPhone AND emailAddress=i_custEmail;
RETURN r_custID;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    createCustomer(i_custName, i_custPhone, i_custEmail);
    r_custID := getCustomerID(i_custName, i_custPhone, i_custEmail);
    RETURN r_custID;
END getCustomerID;