Question

Write PL/SQL Procedure that returns the names of people who spent over $100.

Create table Resturauntorder (

resturauntorder_id int,

resturaunt_name varchar2(50),

hotel_name varchar2(50),

customer_name varchar2(50),

order_date date,

items_orderd varchar2(100),

order_total decimal(5,2),

payment_type varchar2(50),

tip_amount decimal(5,2),

primary key(resturauntorder_id)

);

Insert into Resturauntorder values (123,'Terrace Restaurant', 'Hilton Palm Springs', 'John Smith', date '2019-01-10', 'Omelet Pan, Cinnamon French Toast, California eggs, Orange Juice', 60.49, 'cash', 10.33);

Insert into Resturauntorder values (345,'Spiaggia', 'Hilton Chicago/Magnificent Mile Suite','Ashley Scott', date '2019-02-21', 'Polpo, Tonno Vitellato, Rabarbaro', 189.29, 'credit', 36.00);

Insert into Resturauntorder values (672,'Ocean Prime', 'New York Hilton Midtown', 'James Lowry', date '2019-03-21', 'Lobster roll, Calamari, Chilled Whole Main Lobster', 125.33, 'credit', 31.25);

Answer 1

Below is the SQL statement to create the PL/SQL procedure:

CREATE OR REPLACE
PROCEDURE CustomersSpentOver100(crsr OUT SYS_REFCURSOR)
AS
BEGIN
    OPENcrsr FOR
        SELECT customer_name, (order_total + tip_amount) AS "Amount Spent"
        FROM Resturauntorder
        WHERE (order_total + tip_amount) > 100;
END;

I have added, "Amount Spent" column in result to give a clear picture of the result, you may remove it if needed.

You can execute the above procedure using the below statement:

VARIABLE RC REFCURSOR;
EXEC CustomersSpentOver100( :RC );
PRINT RC;

Below is the result produced:

This completes the requirement. Let me know if you have any questions.

Thanks!