3. Create a stored procedure to register a stove.
The procedure receives the customer's name, phone, email address, and stove serial number.
If the customer already exists in the database (name, phone, and email match), use that customer's CustomerID for the REGISTRATION. Otherwise, create a new CUSTOMER row for the customer. (The CustomerID will be generated from a sequence.)
A stove with the provided serial number should already exist in the database.
If not, display an error and roll back changes to the CUSTOMER table.
Hint consider creating a helper function that accepts a name, phone, and email and returns the customer id (existing or new)
Provide sufficient test cases to demonstrate that this procedure (and any supporting functions, etc.) work according to the specifications.
create or replace procedure register_stove(p_name varchar2, p_phone number, p_email varchar2, p_stoveid number)
is
l_custid number;
begin
-- see if customer exists
select custid into l_custid from a5customer where name=l_name and email=p_email and phone=p_phone;
if l_custid is null then
-- customer doesn't exist, create the customer
insert into a5customer (custid,name, phone, email) values (seq_a5customer.nextval, p_name, p_phone, p_email) returning custid into l_custid;
end if;
-- register the stove, foreign keys should handle the errors if stoveserial or customerid do not exist
-- if an error occures there'll be a rollback of all actions in that procedure;
insert into a5stove (custid, stoveid) values (l_custid, p_stoveid);
end;