/* Solutions to the MId Term Sample test */
--1
CREATE OR REPLACE PROCEDURE  reduce_cost(percent NUMBER) IS
BEGIN
UPDATE cars 
SET cost = (1 - percent/100)*cost ;
END;
/

--2
CREATE OR REPLACE PROCEDURE least_expensive IS
CURSOR cur_least IS
SELECT Car_group_name, Rate_per_day
FROM Cargroups
ORDER BY Rate_per_day ASC;

rec_least cur_least%ROWTYPE;
i INTEGER :=1;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR rec_least IN cur_least LOOP
 EXIT WHEN cur_least%NOTFOUND OR i>3;
 i := i + 1;
 DBMS_OUTPUT.PUT_LINE(rec_least.Car_group_name|| '  '||rec_least.Rate_per_day);
END LOOP;
END;
/

--3
CREATE OR REPLACE TRIGGER LookBeforeBook
BEFORE INSERT ON bookings
FOR EACH ROW
DECLARE
v_cost_no bookings.cust_no%TYPE;
v_count INTEGER;
BEGIN
DBMS_OUTPUT.ENABLE;
SELECT cust_no, NVL(COUNT(cust_no), 0)
INTO v_cust_no, v_count
FROM bookings
WHERE cust_no = :new.cust_no
AND SYSDATE - Date_reserved <=3;

IF v_count >0 THEN 
 DBMS_OUTPUT.PUT_LINE(v_cust_no);
END IF;
END;

--4
CREATE OR REPLACE PROCEDURE insert_model(
                                v_Model_name Models.Model_name%TYPE,

                                v_Car_goup_name Models.Car_goup_name,
                                v_Description   Models.Description,
                                v_Maint_miles    Models.Maint_miles )
IS
BEGIN
INSERT INTO models
values(  v_Model_name, v_Car_goup_name, v_Description, v_Maint_miles );
END;
/

--5

CREATE OR REPLACE TRIGGER many_miles
AFTER UPDATE OF Miles_in ON bookings
FOR EACH ROW
DECLARE
v_cust_no bookings.cust_no%TYPE;
v_name customers.cust_name%TYPE;

BEGIN
IF (:new.miles_in - :new.miles_out > 5000) THEN 
  v_cust_no := :new.cust_no;
END IF;

SELECT  cust_name
INTO  v_name
FROM customers
WHERE cust_no = v_cust_no;


DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE(v_cust_no|| 'has put on over 5,000 miles');
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(v_cust_no || ' is OK' );
END;

--6

CREATE OR REPLACE PROCEDURE amount( v_booking_no bookings.booking_no%TYPE) IS
v_amount_due bookings.amount_due%TYPE;

BEGIN
DBMS_OUTPUT.ENABLE;
SELECT b.Rental_period*cg.Rate_per_day + (b.Miles_in - b.Miles_out)*cg.Rate_per_mile
INTO v_amount_due
from bookings b, cars c, models m, cargroups cg
WHERE b.Registration = c.Registration
AND c.Model_name = m.Model_name
AND m.Car_group_name = cg.Car_group_name
AND b.booking_no = v_booking_no;

UPDATE bookings
SET amount_due = v_amount_due
where booking_no = v_booking_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' No such booking_no '||v_booking_no );
END;
/